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

[Jan Stastny (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3996?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13168054#comment-13168054 ] 

Jan Stastny commented on TEIID-3996:
------------------------------------

[~shawkins]
I tested with the fix for TEIID-3983 and got it working.

> 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)