[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
6 days, 6 hours
[JBoss JIRA] (TEIID-4231) OData - NPE when accessing inactive vdb
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4231?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-4231:
----------------------------------
Fix Version/s: 8.12.x-6.4
> OData - NPE when accessing inactive vdb
> ---------------------------------------
>
> Key: TEIID-4231
> URL: https://issues.jboss.org/browse/TEIID-4231
> Project: Teiid
> Issue Type: Sub-task
> Components: OData
> Affects Versions: 8.12.5
> Reporter: Filip Elias
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.x-6.4
>
>
> Teiid throws NPE when user tries to access inactive vdb via OData4.
> Exception:
> {code}
> 209 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/odata4].[odata4]] (http-localhost/127.0.0.1:8080-1) JBWEB000236: Servlet.service() for servlet odata4 threw exception: java.lang.NullPointerException
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.teiid.olingo.service.LocalClient.getMetadataStore(LocalClient.java:176) [teiid-olingo-8.12.5.redhat-4.jar:8.12.5.redhat-4]
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.teiid.olingo.service.OlingoBridge.getHandler(OlingoBridge.java:46) [teiid-olingo-8.12.5.redhat-4.jar:8.12.5.redhat-4]
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.teiid.olingo.web.ODataFilter.internalDoFilter(ODataFilter.java:223) [teiid-olingo-8.12.5.redhat-4.jar:8.12.5.redhat-4]
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.teiid.olingo.web.ODataFilter.doFilter(ODataFilter.java:100) [teiid-olingo-8.12.5.redhat-4.jar:8.12.5.redhat-4]
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246) [jbossweb-7.5.11.Final-redhat-1.jar:7.5.11.Final-redhat-1]
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214) [jbossweb-7.5.11.Final-redhat-1.jar:7.5.11.Final-redhat-1]
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:231) [jbossweb-7.5.11.Final-redhat-1.jar:7.5.11.Final-redhat-1]
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149) [jbossweb-7.5.11.Final-redhat-1.
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 7 months
[JBoss JIRA] (TEIID-4518) Add support for JDG 7.1
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-4518?page=com.atlassian.jira.plugin... ]
Van Halbert resolved TEIID-4518.
--------------------------------
Resolution: Done
The moving of the JDG related code from Teiid repo to the JDG repo included the support for JDG 7.1. The 8.12.x support the original commit to the JDG repo and now is on its own 8.12.x branch.
> Add support for JDG 7.1
> -----------------------
>
> Key: TEIID-4518
> URL: https://issues.jboss.org/browse/TEIID-4518
> Project: Teiid
> Issue Type: Enhancement
> Components: JDG Connector
> Affects Versions: 8.12.7.6_3
> Reporter: Van Halbert
> Assignee: Van Halbert
> Fix For: 9.3, 8.12.10.6_3
>
>
> The JDG 7.1 modules need to be added as dependencies in the infinispan translator and connector module.xml files.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 7 months
[JBoss JIRA] (TEIID-4231) OData - NPE when accessing inactive vdb
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4231?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-4231:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1340429|https://bugzilla.redhat.com/show_bug.cgi?id=1340429] from NEW to CLOSED
> OData - NPE when accessing inactive vdb
> ---------------------------------------
>
> Key: TEIID-4231
> URL: https://issues.jboss.org/browse/TEIID-4231
> Project: Teiid
> Issue Type: Sub-task
> Components: OData
> Affects Versions: 8.12.5
> Reporter: Filip Elias
> Assignee: Steven Hawkins
> Fix For: 9.0
>
>
> Teiid throws NPE when user tries to access inactive vdb via OData4.
> Exception:
> {code}
> 209 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/odata4].[odata4]] (http-localhost/127.0.0.1:8080-1) JBWEB000236: Servlet.service() for servlet odata4 threw exception: java.lang.NullPointerException
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.teiid.olingo.service.LocalClient.getMetadataStore(LocalClient.java:176) [teiid-olingo-8.12.5.redhat-4.jar:8.12.5.redhat-4]
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.teiid.olingo.service.OlingoBridge.getHandler(OlingoBridge.java:46) [teiid-olingo-8.12.5.redhat-4.jar:8.12.5.redhat-4]
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.teiid.olingo.web.ODataFilter.internalDoFilter(ODataFilter.java:223) [teiid-olingo-8.12.5.redhat-4.jar:8.12.5.redhat-4]
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.teiid.olingo.web.ODataFilter.doFilter(ODataFilter.java:100) [teiid-olingo-8.12.5.redhat-4.jar:8.12.5.redhat-4]
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246) [jbossweb-7.5.11.Final-redhat-1.jar:7.5.11.Final-redhat-1]
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214) [jbossweb-7.5.11.Final-redhat-1.jar:7.5.11.Final-redhat-1]
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:231) [jbossweb-7.5.11.Final-redhat-1.jar:7.5.11.Final-redhat-1]
> 13:23:41,210 INFO [MultiPlatformProcessRunner] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149) [jbossweb-7.5.11.Final-redhat-1.
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 7 months
[JBoss JIRA] (TEIID-4843) Integration test failures for 6.4
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-4843:
-------------------------------------
Summary: Integration test failures for 6.4
Key: TEIID-4843
URL: https://issues.jboss.org/browse/TEIID-4843
Project: Teiid
Issue Type: Quality Risk
Components: Integration Tests
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 8.12.x-6.4
There are two failing tests that need corrected.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 7 months
[JBoss JIRA] (TEIID-4611) Infinispan DSL translator - error deleting rows from table
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4611?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-4611:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1400555|https://bugzilla.redhat.com/show_bug.cgi?id=1400555] from NEW to ASSIGNED
> Infinispan DSL translator - error deleting rows from table
> ----------------------------------------------------------
>
> Key: TEIID-4611
> URL: https://issues.jboss.org/browse/TEIID-4611
> Project: Teiid
> Issue Type: Bug
> Components: JDG Connector, Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Van Halbert
> Priority: Blocker
> Fix For: 9.2, 8.12.x-6.4
>
>
> DSL translator is not able to delete rows from table:
> {code:sql|title=Example queries}
> insert into smalla (intkey, stringkey) values (1, '1');
> insert into smalla (intkey, stringkey) values (2, '2');
> delete from smalla
> {code}
> Teiid seems to try to delete record repeatedly:
> {code:plain|title=Exception}
> 14:05:20,351 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue37) TEIID21013 ObjectUpdateExecution: Warning - The object does not exist when trying to delete from table SmallA with key 2
> 14:05:20,352 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue37) Connector worker process failed for atomic-request=IuoK2RpItaN2.13.0.6: org.teiid.translator.TranslatorException: TEIID21012 Processing error: Unable to delete the object in table SmallA with key 2
> at org.teiid.translator.object.ObjectUpdateExecution.handleDelete(ObjectUpdateExecution.java:385) [translator-object-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> at org.teiid.translator.object.ObjectUpdateExecution.executeUpdate(ObjectUpdateExecution.java:137) [translator-object-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> at org.teiid.translator.object.ObjectUpdateExecution.execute(ObjectUpdateExecution.java:100) [translator-object-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:404) [teiid-engine-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:366) [teiid-engine-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0-internal]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0-internal]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0-internal]
> at java.lang.reflect.Method.invoke(Method.java:483) [rt.jar:1.8.0-internal]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211) [teiid-engine-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> at com.sun.proxy.$Proxy47.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306) [teiid-engine-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112) [teiid-engine-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) [teiid-engine-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0-internal]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65) [teiid-engine-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:280) [teiid-engine-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0-internal]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0-internal]
> at java.lang.Thread.run(Thread.java:744) [rt.jar:1.8.0-internal]
> {code}
> Moreover, Teiid removes only one row (I will log this issue separately):
> *Data in table before delete:*
> |IntKey|StringKey|...|
> |1|1|...|
> |2|2|...|
> *Data in table after delete:*
> |IntKey|StringKey|...|
> |2|2|...|
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 7 months