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}