[teiid-issues] [JBoss JIRA] (TEIID-3996) SAP HANA materialization: MATVIEW_ONERROR_ACTION set to WAIT problem

[Jan Stastny (JIRA)]

Jan Stastny created TEIID-3996:
----------------------------------

             Summary: SAP HANA materialization: MATVIEW_ONERROR_ACTION set to WAIT problem
                 Key: TEIID-3996
                 URL: https://issues.jboss.org/browse/TEIID-3996
             Project: Teiid
          Issue Type: Bug
          Components: Query Engine
    Affects Versions: 8.12.x
            Reporter: Jan Stastny
            Assignee: Steven Hawkins


When using materialization wit SAP HANA datasource, a problem emerges when "teiid_rel:MATVIEW_ONERROR_ACTION" 'WAIT' is defined for the materialized view.
The query which initiates matview's loading doesn't wait for its completion, thus returns stale data (empty resultset in case of the initial invocation).
Materialized view definition:
{code:sql}
CREATE VIEW external_long_ttl (
   customer_id integer NOT NULL,
   total_amount integer
) OPTIONS (
   MATERIALIZED 'TRUE',
   UPDATABLE 'FALSE',
   MATERIALIZED_TABLE 'Source.JSTASTNY.dv_matviews_mat_view',
   "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
   "teiid_rel:MATVIEW_STATUS_TABLE" 'Source.JSTASTNY.dv_matviews_statustable',
   "teiid_rel:ON_VDB_START_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_create) SELECT id, vdb_create+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
   "teiid_rel:ON_VDB_DROP_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_drop) SELECT id, vdb_drop+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
   "teiid_rel:MATVIEW_LOAD_SCRIPT" 'INSERT INTO dv_matviews_mat_view_stage(customer_id,total_amount) SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;',
   "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'exec Source.native(''truncate table dv_matviews_mat_view_stage'');MERGE INTO dv_matviews_check_table(id,before_load) SELECT id, before_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
   "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view'');MERGE INTO dv_matviews_check_table(id,after_load) SELECT id, after_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
   "teiid_rel:MATVIEW_ONERROR_ACTION" 'WAIT',
   "teiid_rel:MATVIEW_TTL" 20000
) AS SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;
{code}

Query performed:
{code:sql}
SELECT * FROM external_long_ttl;
{code}

Reproducer:
{code:java}
Statement statement = connection.createStatement();
 // invoke initial load, won't return loaded data even though it should
ResultSet rs1 = statement.executeQuery("SELECT * FROM external_long_ttl;");
Assert.assertFalse(rs1.next());
 // wait explicitly for the loading to finish
Thread.sleep(7000);
 // repeat the query, to get the data after load finished
ResultSet rs2 = statement.executeQuery("SELECT * FROM external_long_ttl;");
Assert.assertTrue(rs2.next());
.
.
.
{code}




--
This message was sent by Atlassian JIRA
(v6.4.11#64026)