[teiid-issues] [JBoss JIRA] (TEIID-3995) SAP HANA materialization:function loadMatView with argument invalidate set to true problem
Jan Stastny (JIRA)
issues at jboss.org
Thu Feb 25 03:24:00 EST 2016
[ https://issues.jboss.org/browse/TEIID-3995?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13168005#comment-13168005 ]
Jan Stastny commented on TEIID-3995:
------------------------------------
VDB is defined as:
{code:plain}
<?xml version="1.0" encoding="UTF-8"?>
<vdb name="saphana" version="1">
<description>For testing of materialized views</description>
<model name="Source">
<source name="Source" translator-name="hana-override"
connection-jndi-name="java:/saphana-ds" />
<metadata type="DDL"><![CDATA[
CREATE FOREIGN TABLE "JSTASTNY.DV_MATVIEWS_CHECK_TABLE" (
ID string(255) NOT NULL OPTIONS (NAMEINSOURCE '"ID"', NATIVE_TYPE 'VARCHAR'),
VDB_CREATE integer NOT NULL DEFAULT '0' OPTIONS (NAMEINSOURCE '"VDB_CREATE"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
VDB_DROP integer NOT NULL DEFAULT '0' OPTIONS (NAMEINSOURCE '"VDB_DROP"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
BEFORE_LOAD integer NOT NULL DEFAULT '0' OPTIONS (NAMEINSOURCE '"BEFORE_LOAD"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
AFTER_LOAD integer NOT NULL DEFAULT '0' OPTIONS (NAMEINSOURCE '"AFTER_LOAD"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
CONSTRAINT "_SYS_TREE_RS_#160979_#0_#P0" PRIMARY KEY(ID)
) OPTIONS (NAMEINSOURCE '"JSTASTNY"."DV_MATVIEWS_CHECK_TABLE"', UPDATABLE TRUE);
CREATE FOREIGN TABLE "JSTASTNY.DV_MATVIEWS_CUSTOMERS" (
ID integer NOT NULL OPTIONS (NAMEINSOURCE '"ID"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
NAME string(255) OPTIONS (NAMEINSOURCE '"NAME"', NATIVE_TYPE 'VARCHAR'),
ADDRESS string(255) OPTIONS (NAMEINSOURCE '"ADDRESS"', NATIVE_TYPE 'VARCHAR'),
CONSTRAINT "_SYS_TREE_RS_#160987_#0_#P0" PRIMARY KEY(ID)
) OPTIONS (NAMEINSOURCE '"JSTASTNY"."DV_MATVIEWS_CUSTOMERS"', UPDATABLE TRUE);
CREATE FOREIGN TABLE "JSTASTNY.DV_MATVIEWS_MAT_VIEW" (
CUSTOMER_ID integer NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
TOTAL_AMOUNT integer OPTIONS (NAMEINSOURCE '"TOTAL_AMOUNT"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
CONSTRAINT "_SYS_TREE_RS_#160993_#0_#P0" PRIMARY KEY(CUSTOMER_ID)
) OPTIONS (NAMEINSOURCE '"JSTASTNY"."DV_MATVIEWS_MAT_VIEW"', UPDATABLE TRUE);
CREATE FOREIGN TABLE "JSTASTNY.DV_MATVIEWS_MAT_VIEW_STAGE" (
CUSTOMER_ID integer NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
TOTAL_AMOUNT integer OPTIONS (NAMEINSOURCE '"TOTAL_AMOUNT"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
CONSTRAINT "_SYS_TREE_RS_#160998_#0_#P0" PRIMARY KEY(CUSTOMER_ID)
) OPTIONS (NAMEINSOURCE '"JSTASTNY"."DV_MATVIEWS_MAT_VIEW_STAGE"', UPDATABLE TRUE);
CREATE FOREIGN TABLE "JSTASTNY.DV_MATVIEWS_ORDERS" (
ID integer NOT NULL OPTIONS (NAMEINSOURCE '"ID"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
AMOUNT integer OPTIONS (NAMEINSOURCE '"AMOUNT"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
CUSTOMER_ID integer OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
CONSTRAINT "_SYS_TREE_RS_#161003_#0_#P0" PRIMARY KEY(ID),
CONSTRAINT FKARBPDQCPCCILHM7T1D3HI3L48 FOREIGN KEY(CUSTOMER_ID) REFERENCES JSTASTNY.DV_MATVIEWS_CUSTOMERS (ID)
) OPTIONS (NAMEINSOURCE '"JSTASTNY"."DV_MATVIEWS_ORDERS"', UPDATABLE TRUE);
CREATE FOREIGN TABLE "JSTASTNY.DV_MATVIEWS_STATUSTABLE" (
VDBNAME string(50) NOT NULL OPTIONS (NAMEINSOURCE '"VDBNAME"', NATIVE_TYPE 'VARCHAR'),
VDBVERSION integer NOT NULL OPTIONS (NAMEINSOURCE '"VDBVERSION"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
SCHEMANAME string(50) NOT NULL OPTIONS (NAMEINSOURCE '"SCHEMANAME"', NATIVE_TYPE 'VARCHAR'),
Name string(256) NOT NULL OPTIONS (NAMEINSOURCE '"NAME"', NATIVE_TYPE 'VARCHAR'),
TARGETSCHEMANAME string(50) OPTIONS (NAMEINSOURCE '"TARGETSCHEMANAME"', NATIVE_TYPE 'VARCHAR'),
TARGETNAME string(256) OPTIONS (NAMEINSOURCE '"TARGETNAME"', NATIVE_TYPE 'VARCHAR'),
VALID boolean NOT NULL OPTIONS (NAMEINSOURCE '"VALID"', CHAR_OCTET_LENGTH 1, NATIVE_TYPE 'TINYINT'),
LOADSTATE string(25) NOT NULL OPTIONS (NAMEINSOURCE '"LOADSTATE"', NATIVE_TYPE 'VARCHAR'),
CARDINALITY long OPTIONS (NAMEINSOURCE '"CARDINALITY"', CHAR_OCTET_LENGTH 20, NATIVE_TYPE 'BIGINT'),
UPDATED timestamp NOT NULL OPTIONS (NAMEINSOURCE '"UPDATED"', CHAR_OCTET_LENGTH 16, NATIVE_TYPE 'TIMESTAMP'),
LOADNUMBER long NOT NULL OPTIONS (NAMEINSOURCE '"LOADNUMBER"', CHAR_OCTET_LENGTH 20, NATIVE_TYPE 'BIGINT'),
CONSTRAINT "_SYS_TREE_RS_#161009_#0_#P0" PRIMARY KEY(VDBNAME, VDBVERSION, SCHEMANAME, Name)
) OPTIONS (NAMEINSOURCE '"JSTASTNY"."DV_MATVIEWS_STATUSTABLE"', UPDATABLE TRUE);
]]>
</metadata>
</model>
<model name="View" type="VIRTUAL">
<metadata type="DDL"><![CDATA[
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;
]]>
</metadata>
</model>
<translator name="hana-override" type="hana">
<property name="SupportsDirectQueryProcedure" value="true" />
</translator>
</vdb>
{code}
The stacktrace I get is:
{code:plain}
08:18:07,640 ERROR [org.teiid.TRANSPORT] (New I/O worker #19) TEIID40113 Unhandled exception, aborting operation: org.teiid.transport.ObjectEncoder$FailedWriteException: org.teiid.core.TeiidRuntimeException: TEIID20001 The modeled datatype integer for column 0 doesn't match the runtime type "org.teiid.core.types.ArrayImpl". Please ensure that the column's modeled datatype matches the expected data.
at org.teiid.transport.ObjectEncoder.handleDownstream(ObjectEncoder.java:136) [teiid-runtime-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.jboss.netty.channel.Channels.write(Channels.java:704) [netty-3.6.10.Final-redhat-1.jar:3.6.10.Final-redhat-1]
at org.jboss.netty.channel.Channels.write(Channels.java:671) [netty-3.6.10.Final-redhat-1.jar:3.6.10.Final-redhat-1]
at org.jboss.netty.channel.AbstractChannel.write(AbstractChannel.java:248) [netty-3.6.10.Final-redhat-1.jar:3.6.10.Final-redhat-1]
at org.teiid.transport.SSLAwareChannelHandler$ObjectChannelImpl.write(SSLAwareChannelHandler.java:93) [teiid-runtime-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.transport.SocketClientInstance.send(SocketClientInstance.java:88) [teiid-runtime-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.transport.ServerWorkItem.sendResult(ServerWorkItem.java:135) [teiid-runtime-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.transport.ServerWorkItem$1.onCompletion(ServerWorkItem.java:105) [teiid-runtime-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135) [teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40) [teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79) [teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.dqp.internal.process.RequestWorkItem.sendResultsIfNeeded(RequestWorkItem.java:992) [teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.dqp.internal.process.RequestWorkItem$1.flushBatchDirect(RequestWorkItem.java:674) [teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.query.processor.BatchCollector.flushBatch(BatchCollector.java:223) [teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:194) [teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) [teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:470) [teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348) [teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274) [teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) [teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_51]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_51]
at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_51]
Caused by: org.teiid.core.TeiidRuntimeException: TEIID20001 The modeled datatype integer for column 0 doesn't match the runtime type "org.teiid.core.types.ArrayImpl". Please ensure that the column's modeled datatype matches the expected data.
at org.teiid.client.BatchSerializer.writeBatch(BatchSerializer.java:878) [teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.client.ResultsMessage.writeExternal(ResultsMessage.java:319) [teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at java.io.ObjectOutputStream.writeExternalData(ObjectOutputStream.java:1459) [rt.jar:1.8.0_51]
at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1430) [rt.jar:1.8.0_51]
at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1178) [rt.jar:1.8.0_51]
at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:348) [rt.jar:1.8.0_51]
at org.teiid.net.socket.Message.writeExternal(Message.java:61) [teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at java.io.ObjectOutputStream.writeExternalData(ObjectOutputStream.java:1459) [rt.jar:1.8.0_51]
at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1430) [rt.jar:1.8.0_51]
at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1178) [rt.jar:1.8.0_51]
at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:348) [rt.jar:1.8.0_51]
at org.teiid.transport.ObjectEncoder.handleDownstream(ObjectEncoder.java:131) [teiid-runtime-8.12.5.redhat-1.jar:8.12.5.redhat-1]
... 25 more
Caused by: java.lang.ClassCastException: org.teiid.core.types.ArrayImpl cannot be cast to java.lang.Integer
at org.teiid.client.BatchSerializer$IntColumnSerializer.writeObject(BatchSerializer.java:563) [teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.client.BatchSerializer$ColumnSerializer.writeColumn(BatchSerializer.java:534) [teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.client.BatchSerializer.writeBatch(BatchSerializer.java:867) [teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
... 36 more
{code}
> SAP HANA materialization:function loadMatView with argument invalidate set to true problem
> ------------------------------------------------------------------------------------------
>
> Key: TEIID-3995
> URL: https://issues.jboss.org/browse/TEIID-3995
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.x
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
>
> There appears to be an issue with loadMatView function.
> Specifically when the function is invoked with invalidate=>'true' argument, when it is expected, that invocation of this function invalidates the current contents of the materialized view until the initiated load is completed and new data are populated.
> But when calling this function as described, an error occurs:
> {code:plain}
> TEIID20001 The modeled datatype integer for column 0 doesn't match the runtime type "org.teiid.core.types.ArrayImpl". Please ensure that the column's modeled datatype matches the expected data.
> {code}
> Please note, that there is no column of type array in my schema.
> There is declaration of the view:
> {code:sql}
> CREATE VIEW external_long_ttl (
> customer_id integer NOT NULL,
> total_amount integer
> )
> {code}
> and the query used to load the view:
> {code:sql}
> "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;',
> {code}
> Complete definition of the view is:
> {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}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
More information about the teiid-issues
mailing list