[JBoss JIRA] (TEIID-2605) Optimization substitutes wrong column in where clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2605?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-2605.
---------------------------------
> Optimization substitutes wrong column in where clause
> -----------------------------------------------------
>
> Key: TEIID-2605
> URL: https://issues.jboss.org/browse/TEIID-2605
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.1
> Environment: z/OS
> Reporter: Jeff Hayes
> Assignee: Steven Hawkins
> Attachments: query_plan.txt, views.xml
>
>
> Optimization results in a query with a different column in the WHERE clause producing an empty result set.
> Full query plan is attached but the beginning and ending queries are shown below. Note that the subject column of the IN clause is SCOPEID but optimization changes it to AUTHID for some reason.
> USER COMMAND:
> SELECT * FROM SECURITY.SCPXREF AS CHORUS_B WHERE (CHORUS_B.SYSID = 'DE29') AND ((CHORUS_B.SCOPEID IN (SELECT SN5.SCOPEID FROM SECURI
> TY.SCPNEXT AS SN5 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN4.SCOPEID FROM SECURITY.SCPNEXT AS SN4 WHERE (SYSID = 'DE29') AND
> (NEXTREC IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURI
> TY.SCPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))))))) OR (CHORUS_B.SCOPEID IN (SELECT SN4.SCOPEID FROM SECURIT
> Y.SCPNEXT AS SN4 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND
> (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURITY.SCPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))))) OR (CHORUS_B.SC
> OPEID IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURITY.S
> CPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))) OR (CHORUS_B.SCOPEID IN (SELECT SN2.SCOPEID FROM SECURITY.SCPNEX
> T AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))) OR (CHORUS_B.SCOPEID = 'CHRDEPT1'))
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> AccessNode(10) output=[x.sysid AS sysid, x.scopeid AS authid, x.authid AS scopeid, x.authtype AS authtype] SELECT g_0.SYSID, g_0.SCO
> PEID, g_0.AUTHID, g_0.AUTHTYPE FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPXREF AS g_0 WHERE (g_0.SYSID = 'DE29') AND ((g_0.AUTHID IN
> (SELECT g_1.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_1 WHERE (g_1.SYSID = 'DE29') AND (g_1.NEXTREC IN (SELECT g_2
> .SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_2 WHERE (g_2.SYSID = 'DE29') AND (g_2.NEXTREC IN (SELECT g_3.SCOPEID FR
> OM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_3 WHERE (g_3.SYSID = 'DE29') AND (g_3.NEXTREC IN (SELECT g_4.SCOPEID FROM SECURITY
> _CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_4 WHERE (g_4.SYSID = 'DE29') AND (g_4.NEXTREC = 'CHRDEPT1'))))))))) OR (g_0.AUTHID IN (SELECT
> g_5.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_5 WHERE (g_5.SYSID = 'DE29') AND (g_5.NEXTREC IN (SELECT g_6.SCOPEI
> D FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_6 WHERE (g_6.SYSID = 'DE29') AND (g_6.NEXTREC IN (SELECT g_7.SCOPEID FROM SECU
> RITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_7 WHERE (g_7.SYSID = 'DE29') AND (g_7.NEXTREC = 'CHRDEPT1'))))))) OR (g_0.AUTHID IN (SELE
> CT g_8.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_8 WHERE (g_8.SYSID = 'DE29') AND (g_8.NEXTREC IN (SELECT g_9.SCOP
> EID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_9 WHERE (g_9.SYSID = 'DE29') AND (g_9.NEXTREC = 'CHRDEPT1'))))) OR (g_0.AUTH
> ID IN (SELECT g_10.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_10 WHERE (g_10.SYSID = 'DE29') AND (g_10.NEXTREC = 'C
> HRDEPT1'))) OR (g_0.AUTHID = 'CHRDEPT1'))
> The view definitions are shown below:
> <view name="SCPNEXT">
> <columns>
> <column name="sysid" type="varchar"/>
> <column name="scopeid" type="varchar"/>
> <column name="nextrec" type="varchar"/>
> </columns>
> <definition>
> #if ($db.count("select count(*) from sys.tables where Name='config' and SchemaName = 'security_db'") > 0)
> #set ($count = 0)
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> #if ($db.count("select count(*) from sys.tables where SchemaName = 'SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}'") == 0)
> #set ($count = $count + 1)
> #end
> #end
> #if ($count == 0)
> SELECT t.sysid, t.scopeid, t.nextrec
> FROM (
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> SELECT n.sysid, n.scopeid, n.nextrec
> FROM SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}.SCPNEXT n
> #if( $velocityHasNext ) UNION #end
> #end
> ) AS t
> #end
> #end
> </definition>
> </view>
> <view name="SCPXREF">
> <columns>
> <column name="sysid" type="varchar"/>
> <column name="authid" type="varchar"/>
> <column name="scopeid" type="varchar"/>
> <column name="authtype" type="varchar"/>
> </columns>
> <definition>
> #if ($db.count("select count(*) from sys.tables where Name='config' and SchemaName = 'security_db'") > 0)
> #set ($count = 0)
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> #if ($db.count("select count(*) from sys.tables where SchemaName = 'SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}'") == 0)
> #set ($count = $count + 1)
> #end
> #end
> #if ($count == 0)
> SELECT sysid, scopeid, authid, authtype
> FROM (
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> SELECT x.sysid, x.scopeid, x.authid, x.authtype
> FROM SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}.SCPXREF x
> #if( $velocityHasNext ) UNION #end
> #end
> ) AS t
> #end
> #end
> </definition>
> </view>
> I guess the best way to test this is to define these views and run the input query with SHOWPLAN=DEBUG and see if the AUTHID substitution is occurring.
> Thanks!
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
2 days, 6 hours
[JBoss JIRA] (TEIID-3534) Infinispan-dsl-cache translator: can't insert null into column of type boolean
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-3534?page=com.atlassian.jira.plugin... ]
Van Halbert closed TEIID-3534.
------------------------------
Resolution: Rejected
It has been determined that this is a JDG issue.
The possible work arounds are:
- assign defaults in the proto def file
- or always pass a value for the boolean column
> Infinispan-dsl-cache translator: can't insert null into column of type boolean
> ------------------------------------------------------------------------------
>
> Key: TEIID-3534
> URL: https://issues.jboss.org/browse/TEIID-3534
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.7.1.6_2
> Reporter: Filip Elias
> Assignee: Van Halbert
> Attachments: server.log, testVDB.vdb
>
>
> Inserting null into boolean column causes exception.
> Sample queries:
> {code}
> insert into smalla(intKey, stringKey,booleanValue) values(128,'ss',null);
> insert into smalla(intKey, stringKey) values(128,'ss');
> {code}
> This query works:
> {code}
> insert into smalla(intKey, stringKey,booleanValue) values(128,'ss',false);
> {code}
> Exception:
> {code}
> Connector worker process failed for atomic-request=NuZ8Nt3h1bKx.52.0.44: org.infinispan.client.hotrod.exceptions.HotRodClientException:Request for message id[239] returned server error (status=0x85): org.hibernate.search.bridge.BridgeException: Exception while calling bridge#set
> class: org.infinispan.query.remote.indexing.ProtobufValueWrapper
> path:
> at org.infinispan.client.hotrod.impl.protocol.Codec20.checkForErrorsInResponseStatus(Codec20.java:298)
> at org.infinispan.client.hotrod.impl.protocol.Codec20.readPartialHeader(Codec20.java:88)
> at org.infinispan.client.hotrod.impl.protocol.Codec20.readHeader(Codec20.java:74)
> at org.infinispan.client.hotrod.impl.operations.HotRodOperation.readHeaderAndValidate(HotRodOperation.java:56)
> {code}
> This bug breaks data integrity. If this exception is thrown, data will be inserted but it won't be indexed.
> The whole exception is in the attachment.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 3 months
[JBoss JIRA] (TEIID-3557) Can't reload VDB in domain mode
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3557?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-3557:
------------------------------------------------
Anne-Louise Tangring <atangrin(a)redhat.com> changed the Status of [bug 1234822|https://bugzilla.redhat.com/show_bug.cgi?id=1234822] from NEW to MODIFIED
> Can't reload VDB in domain mode
> -------------------------------
>
> Key: TEIID-3557
> URL: https://issues.jboss.org/browse/TEIID-3557
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Affects Versions: 8.0
> Reporter: Van Halbert
> Assignee: Ramesh Reddy
> Priority: Critical
> Fix For: 8.7.1.6_2, 8.12, 8.11.1
>
>
> Can't reload VDB in domain mode, get the following error:
> Server throws the following exception:
> [Server:server-one] 12:26:45,678 ERROR [org.teiid.RUNTIME] (teiid-async-threads - 1) TEIID40088 Could not replicate object org.teiid.query.tempdata.GlobalTableStoreImpl@4e6c9a30: java.lang.IllegalStateException: cluster 'ModeShape.1' is already connected to singleton transport: [dummy-1435055090515, loopback.1, ModeShape.1, loopback-dynamic.1, $TEIID_BM$, dummy-1435055090188, dummy-1435055205654, $TEIID_ED$, teiid-cache, ddl2-vdb.1]
> [Server:server-one] at org.jgroups.stack.ProtocolStack.startStack(ProtocolStack.java:919) [jgroups-3.2.13.Final-redhat-1.jar:3.2.13.Final-redhat-1]
> [Server:server-one] at org.jgroups.JChannel.startStack(JChannel.java:827) [jgroups-3.2.13.Final-redhat-1.jar:3.2.13.Final-redhat-1]
> [Server:server-one] at org.jgroups.JChannel._preConnect(JChannel.java:525) [jgroups-3.2.13.Final-redhat-1.jar:3.2.13.Final-redhat-1]
> [Server:server-one] at org.jgroups.JChannel.connect(JChannel.java:277) [jgroups-3.2.13.Final-redhat-1.jar:3.2.13.Final-redhat-1]
> [Server:server-one] at org.jgroups.JChannel.connect(JChannel.java:268) [jgroups-3.2.13.Final-redhat-1.jar:3.2.13.Final-redhat-1]
> [Server:server-one] at org.teiid.replication.jgroups.JGroupsObjectReplicator.replicate(JGroupsObjectReplicator.java:563) [teiid-runtime-8.7.1.6_2-redhat-2.jar:8.7.1.6_2-redhat-2]
> [Server:server-one] at org.teiid.deployers.CompositeGlobalTableStore.createInstance(CompositeGlobalTableStore.java:57) [teiid-runtime-8.7.1.6_2-redhat-2.jar:8.7.1.6_2-redhat-2]
> [Server:server-one] at org.teiid.jboss.VDBService$1.finishedDeployment(VDBService.java:156) [teiid-jboss-integration-8.7.1.6_2-redhat-2.jar:8.7.1.6_2-redhat-2]
> [Server:server-one] at org.teiid.deployers.VDBRepository.notifyFinished(VDBRepository.java:352) [teiid-runtime-8.7.1.6_2-redhat-2.jar:8.7.1.6_2-redhat-2]
> [Server:server-one] at org.teiid.deployers.VDBRepository.finishDeployment(VDBRepository.java:308) [teiid-runtime-8.7.1.6_2-redhat-2.jar:8.7.1.6_2-redhat-2]
> [Server:server-one] at org.teiid.runtime.AbstractVDBDeployer.metadataLoaded(AbstractVDBDeployer.java:202) [teiid-runtime-8.7.1.6_2-redhat-2.jar:8.7.1.6_2-redhat-2]
> [Server:server-one] at org.teiid.jboss.VDBService.access$1100(VDBService.java:85) [teiid-jboss-integration-8.7.1.6_2-redhat-2.jar:8.7.1.6_2-redhat-2]
> [Server:server-one] at org.teiid.jboss.VDBService$6.run(VDBService.java:411) [teiid-jboss-integration-8.7.1.6_2-redhat-2.jar:8.7.1.6_2-redhat-2]
> [Server:server-one] at org.teiid.jboss.VDBService$7.run(VDBService.java:442) [teiid-jboss-integration-8.7.1.6_2-redhat-2.jar:8.7.1.6_2-redhat-2]
> [Server:server-one] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_25]
> [Server:server-one] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_25]
> [Server:server-one] at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_25]
> [Server:server-one] at org.jboss.threads.JBossThread.run(JBossThread.java:122)
> VDB reload works fine in standalone mode.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 3 months
[JBoss JIRA] (TEIID-3549) Join conditions with OR generate a wrong JDBC query
by Mark Tawk (JIRA)
[ https://issues.jboss.org/browse/TEIID-3549?page=com.atlassian.jira.plugin... ]
Mark Tawk commented on TEIID-3549:
----------------------------------
The fix solved the problem
Thank you
> Join conditions with OR generate a wrong JDBC query
> ---------------------------------------------------
>
> Key: TEIID-3549
> URL: https://issues.jboss.org/browse/TEIID-3549
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Fix For: 8.12
>
>
> I'm using Teiid 8.10 with h2 translator
> I have the following query containing a join with conditions separated by OR logical operator:
> select "BilanBanque"."SIGNEDDATA" as "CalculatedField1"
> from "implify_data"."Evolution_PCy_050615" "Evolution_PCy_050615"
> LEFT JOIN "implify_data"."Evolution_PCy_050615" "MidCat" ON "Evolution_PCy_050615"."ID" = "MidCat"."PID" AND ( ( ("MidCat"."Level" IN (1) ) ) )
> LEFT JOIN "implify_data"."Evolution_PCy_050615" "Data" ON ( ( ("Data"."Level" IN (2) ) ) AND ( ("Data"."PID" = "Evolution_PCy_050615"."ID") OR ("Data"."PID" = "MidCat"."ID") ) )
> LEFT JOIN "implify_data"."Sheet1_haU_010615" "BilanBanque" ON "Data"."ID" = "BilanBanque"."Account"
> where ("BilanBanque"."BankName" IN ('Bank1') )
> The corresponding JDBC query executed by Teiid contains 2 times ON after the join:
> SELECT g_3."SIGNEDDATA" FROM "implify_data"."Evolution_PCy_050615" AS g_0 LEFT OUTER JOIN "implify_data"."Evolution_PCy_050615" AS g_1 ON g_0."ID" = g_1."PID" AND g_1."Level" = 1 INNER JOIN "implify_data"."Evolution_PCy_050615" AS g_2 INNER JOIN "implify_data"."Sheet1_haU_010615" AS g_3 ON g_2."ID" = g_3."Account" ON g_2."PID" = g_0."ID" OR g_2."PID" = g_1."ID" WHERE g_2."Level" = 2 AND g_3."BankName" = 'Bank1'
> if I remove the conditions with OR from the join, the query executes with success.
> You find below the execution plan:
> <?xml version='1.0' encoding='UTF-8'?><node name="AccessNode"><property name="Relational Node ID"><value>0</value></property><property name="Output Columns"><value>CalculatedField1 (bigdecimal)</value></property><property name="Statistics"><value>Node Output Rows: 0</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 16</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 1</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT g_3.SIGNEDDATA FROM (implify_dataModel.implify_data.Evolution_PCy_050615 AS g_0 LEFT OUTER JOIN implify_dataModel.implify_data.Evolution_PCy_050615 AS g_1 ON g_0.ID = g_1.PID AND g_1.Level = 1) INNER JOIN (implify_dataModel.implify_data.Evolution_PCy_050615 AS g_2 INNER JOIN implify_dataModel.implify_data.Sheet1_haU_010615 AS g_3 ON g_2.ID = g_3.Account) ON ((g_2.PID = g_0.ID) OR (g_2.PID = g_1.ID)) WHERE (g_2.Level = 2) AND (g_3.BankName = 'Bank1')</value></property><property name="Model Name"><value>implify_dataModel</value></property><property name="Data Bytes Sent"><value>0</value></property></node>
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 3 months
[JBoss JIRA] (TEIID-3532) Infinispan-dsl-cache translator: Exception is thrown when order by clause is used
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3532?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-3532:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1232169|https://bugzilla.redhat.com/show_bug.cgi?id=1232169] from NEW to MODIFIED
> Infinispan-dsl-cache translator: Exception is thrown when order by clause is used
> ---------------------------------------------------------------------------------
>
> Key: TEIID-3532
> URL: https://issues.jboss.org/browse/TEIID-3532
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.7.1.6_2
> Reporter: Filip Elias
> Assignee: Van Halbert
> Attachments: server.log
>
>
> Infinispan-dsl-cache translator will fail if order by clause is pushed down.
> Sample query:
> {code}
> select intKey from smalla where intkey > 1 order by intKey
> {code}
> Exception:
> {code}
> Connector worker process failed for atomic-request=iLuNd1gO/2N0.4.0.7: org.infinispan.client.hotrod.exceptions.HotRodClientException:Request for message id[38] returned server error (status=0x85): org.hibernate.hql.ParsingException: HQLPARSER000002: The query FROM org.jboss.qe.jdg_remote.protobuf.SmallA _gen0 WHERE _gen0.intKey > 1 ORDER BY _gen0.null ASC is not valid; Parser error messages: [[statement, statementElement, selectStatement, orderByClause, sortSpecification, sortKey, concatenation, additiveExpression, multiplyExpression, unaryExpression, atom, identPrimary]: line 1:89 mismatched token: [@32,89:92='null',<124>,1:89]; expecting type IDENTIFIER].
> at org.infinispan.client.hotrod.impl.protocol.Codec20.checkForErrorsInResponseStatus(Codec20.java:298)
> at org.infinispan.client.hotrod.impl.protocol.Codec20.readPartialHeader(Codec20.java:88)
> at org.infinispan.client.hotrod.impl.protocol.Codec20.readHeader(Codec20.java:74)
> {code}
> Full log is in the attachment.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 4 months