[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
1 month, 2 weeks
[JBoss JIRA] (TEIID-3847) HiveTranslator should not use a Calendar for the Hive getDate
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3847?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3847.
-----------------------------------
Fix Version/s: 8.13
8.12.3
Resolution: Done
Thanks Dave for reporting this. The translator has been updated to avoid all calendar based calls and I updated the docs to indicate that the databasetimezone translator property is not used.
> HiveTranslator should not use a Calendar for the Hive getDate
> -------------------------------------------------------------
>
> Key: TEIID-3847
> URL: https://issues.jboss.org/browse/TEIID-3847
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.9
> Environment: Windows 7 and 64 bit linux.
> Reporter: Dave Nicodemus
> Assignee: Steven Hawkins
> Fix For: 8.13, 8.12.3
>
>
> The Hive JDBC driver does not support the get Date/Timestamp methods with Calendar parameters. This is handled correctly for time stamp but not for date. Data should be handled as well.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years
[JBoss JIRA] (TEIID-3846) Wrong alias rewriting in subqueries
by Salvatore R (JIRA)
[ https://issues.jboss.org/browse/TEIID-3846?page=com.atlassian.jira.plugin... ]
Salvatore R commented on TEIID-3846:
------------------------------------
No problem. Thanks for fixing this issue so quickly.
> Wrong alias rewriting in subqueries
> -----------------------------------
>
> Key: TEIID-3846
> URL: https://issues.jboss.org/browse/TEIID-3846
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.13, 8.12.3
>
>
> I defined two tables in PostgreSQL (but it's reproducible also in other databases like MySQL, for example) as follows:
> {code:sql}
> CREATE TABLE test_a
> (
> a integer,
> b integer
> );
> CREATE TABLE test_b
> (
> b integer,
> c integer
> );
> {code}
> Running this query:
> {code:sql}
> SELECT
> *
> FROM
> ( SELECT
> ( SELECT x.a FROM pg.test_a AS x WHERE x.b = g_0.c ) AS c_2
> FROM
> pg.test_b AS g_0
> ) AS v_0
> {code}
> I get the following exception:
> {code:sql}
> 13:11:30,073 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue50) 1iTDksSjXt1V Connector worker process failed for atomic-request=1iTDksSjXt1V.16.1.11: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT v_0.c_0 FROM (SELECT (SELECT g_1."a" FROM "public"."test_a" AS g_1 WHERE g_1."b" = g_1."c") AS c_0 FROM "public"."test_b" AS g_0) AS v_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:349)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_67]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_67]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_67]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_67]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy47.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_67]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]
> Caused by: org.postgresql.util.PSQLException: ERROR: column g_1.c does not exist
> Position: 91
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
> ... 18 more
> {code}
> It seems that the problem is related to the alias "g_0" used for the source table "pg.test_b". In fact, if a different alias is used, the query runs without errors:
> {code:sql}
> SELECT
> *
> FROM
> ( SELECT
> ( SELECT x.a FROM pg.test_a AS x WHERE x.b = y.c ) AS c_2
> FROM
> pg.test_b AS y
> ) AS v_0
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years
[JBoss JIRA] (TEIID-3847) HiveTranslator should not use a Calendar for the Hive getDate
by Dave Nicodemus (JIRA)
Dave Nicodemus created TEIID-3847:
-------------------------------------
Summary: HiveTranslator should not use a Calendar for the Hive getDate
Key: TEIID-3847
URL: https://issues.jboss.org/browse/TEIID-3847
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Affects Versions: 8.12.2
Environment: Windows 7 and 64 bit linux.
Reporter: Dave Nicodemus
Assignee: Steven Hawkins
The Hive JDBC driver does not support the get Date/Timestamp methods with Calendar parameters. This is handled correctly for time stamp but not for date. Data should be handled as well.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years
[JBoss JIRA] (TEIID-3846) Wrong alias rewriting in subqueries
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3846?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3846.
-----------------------------------
Resolution: Done
Thanks Salvatore. Added a check for correlated reference groups prior to running the alias generator to prevent using a duplicate.
> Wrong alias rewriting in subqueries
> -----------------------------------
>
> Key: TEIID-3846
> URL: https://issues.jboss.org/browse/TEIID-3846
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.13, 8.12.3
>
>
> I defined two tables in PostgreSQL (but it's reproducible also in other databases like MySQL, for example) as follows:
> {code:sql}
> CREATE TABLE test_a
> (
> a integer,
> b integer
> );
> CREATE TABLE test_b
> (
> b integer,
> c integer
> );
> {code}
> Running this query:
> {code:sql}
> SELECT
> *
> FROM
> ( SELECT
> ( SELECT x.a FROM pg.test_a AS x WHERE x.b = g_0.c ) AS c_2
> FROM
> pg.test_b AS g_0
> ) AS v_0
> {code}
> I get the following exception:
> {code:sql}
> 13:11:30,073 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue50) 1iTDksSjXt1V Connector worker process failed for atomic-request=1iTDksSjXt1V.16.1.11: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT v_0.c_0 FROM (SELECT (SELECT g_1."a" FROM "public"."test_a" AS g_1 WHERE g_1."b" = g_1."c") AS c_0 FROM "public"."test_b" AS g_0) AS v_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:349)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_67]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_67]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_67]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_67]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy47.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_67]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]
> Caused by: org.postgresql.util.PSQLException: ERROR: column g_1.c does not exist
> Position: 91
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
> ... 18 more
> {code}
> It seems that the problem is related to the alias "g_0" used for the source table "pg.test_b". In fact, if a different alias is used, the query runs without errors:
> {code:sql}
> SELECT
> *
> FROM
> ( SELECT
> ( SELECT x.a FROM pg.test_a AS x WHERE x.b = y.c ) AS c_2
> FROM
> pg.test_b AS y
> ) AS v_0
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years
[JBoss JIRA] (TEIID-3846) Wrong alias rewriting in subqueries
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3846?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3846:
----------------------------------
Component/s: Query Engine
> Wrong alias rewriting in subqueries
> -----------------------------------
>
> Key: TEIID-3846
> URL: https://issues.jboss.org/browse/TEIID-3846
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.13, 8.12.3
>
>
> I defined two tables in PostgreSQL (but it's reproducible also in other databases like MySQL, for example) as follows:
> {code:sql}
> CREATE TABLE test_a
> (
> a integer,
> b integer
> );
> CREATE TABLE test_b
> (
> b integer,
> c integer
> );
> {code}
> Running this query:
> {code:sql}
> SELECT
> *
> FROM
> ( SELECT
> ( SELECT x.a FROM pg.test_a AS x WHERE x.b = g_0.c ) AS c_2
> FROM
> pg.test_b AS g_0
> ) AS v_0
> {code}
> I get the following exception:
> {code:sql}
> 13:11:30,073 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue50) 1iTDksSjXt1V Connector worker process failed for atomic-request=1iTDksSjXt1V.16.1.11: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT v_0.c_0 FROM (SELECT (SELECT g_1."a" FROM "public"."test_a" AS g_1 WHERE g_1."b" = g_1."c") AS c_0 FROM "public"."test_b" AS g_0) AS v_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:349)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_67]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_67]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_67]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_67]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy47.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_67]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]
> Caused by: org.postgresql.util.PSQLException: ERROR: column g_1.c does not exist
> Position: 91
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
> ... 18 more
> {code}
> It seems that the problem is related to the alias "g_0" used for the source table "pg.test_b". In fact, if a different alias is used, the query runs without errors:
> {code:sql}
> SELECT
> *
> FROM
> ( SELECT
> ( SELECT x.a FROM pg.test_a AS x WHERE x.b = y.c ) AS c_2
> FROM
> pg.test_b AS y
> ) AS v_0
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years
[JBoss JIRA] (TEIID-3846) Wrong alias rewriting in subqueries
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3846?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3846:
----------------------------------
Fix Version/s: 8.13
8.12.3
Affects Version/s: 8.7
Yes, having what we effectively use as a generated alias in the outer scope is not accounted for correctly with a subquery.
> Wrong alias rewriting in subqueries
> -----------------------------------
>
> Key: TEIID-3846
> URL: https://issues.jboss.org/browse/TEIID-3846
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.13, 8.12.3
>
>
> I defined two tables in PostgreSQL (but it's reproducible also in other databases like MySQL, for example) as follows:
> {code:sql}
> CREATE TABLE test_a
> (
> a integer,
> b integer
> );
> CREATE TABLE test_b
> (
> b integer,
> c integer
> );
> {code}
> Running this query:
> {code:sql}
> SELECT
> *
> FROM
> ( SELECT
> ( SELECT x.a FROM pg.test_a AS x WHERE x.b = g_0.c ) AS c_2
> FROM
> pg.test_b AS g_0
> ) AS v_0
> {code}
> I get the following exception:
> {code:sql}
> 13:11:30,073 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue50) 1iTDksSjXt1V Connector worker process failed for atomic-request=1iTDksSjXt1V.16.1.11: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT v_0.c_0 FROM (SELECT (SELECT g_1."a" FROM "public"."test_a" AS g_1 WHERE g_1."b" = g_1."c") AS c_0 FROM "public"."test_b" AS g_0) AS v_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:349)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_67]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_67]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_67]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_67]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy47.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_67]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]
> Caused by: org.postgresql.util.PSQLException: ERROR: column g_1.c does not exist
> Position: 91
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
> ... 18 more
> {code}
> It seems that the problem is related to the alias "g_0" used for the source table "pg.test_b". In fact, if a different alias is used, the query runs without errors:
> {code:sql}
> SELECT
> *
> FROM
> ( SELECT
> ( SELECT x.a FROM pg.test_a AS x WHERE x.b = y.c ) AS c_2
> FROM
> pg.test_b AS y
> ) AS v_0
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years
[JBoss JIRA] (TEIID-3842) Teiid's query's explain not consistence with other Database
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3842?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3842:
---------------------------------------
It's not a prefix, it's a statement that is run after show plan is set - See https://docs.jboss.org/author/display/TEIID/SET+Statement
> Teiid's query's explain not consistence with other Database
> ------------------------------------------------------------
>
> Key: TEIID-3842
> URL: https://issues.jboss.org/browse/TEIID-3842
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 9.x
> Reporter: Kylin Soong
> Assignee: Kylin Soong
> Fix For: 9.x
>
>
> h2. The way to get execution plan is not consistence with others
> For most of RDBMS(Oracle, Mysql), the execution plan get via SQL query
> {code}
> explain select ...
> {code}
> But in Teiid, we need use Teiid API, and need enable showplan in advance:
> {code}
> statement.execute("set showplan on");
> ResultSet rs = statement.executeQuery("select ...");
> TeiidStatement tstatement = statement.unwrap(TeiidStatement.class);
> PlanNode queryPlan = tstatement.getPlanDescription();
> System.out.println(queryPlan);
> {code}
> Cache Hint can be added as prefix, so I think we can enhance that show plan also as prefix, in order for consistence with others.
> h2. The plan structure is too complex
> [1] is the query plan of dynamicvdb-datafederation which is 2 datasources' federation, it long, complex, for the users/customers, it's not easy for them to understand. So can we enhance that simplify the output, like add a format option that can output as matrix/table.
> [1] dynamicvdb-datafederation's 'select * from Stock' query plan
> {code}
> ProjectNode
> + Relational Node ID:0
> + Output Columns:
> 0: product_id (integer)
> 1: symbol (string)
> 2: price (bigdecimal)
> 3: company_name (string)
> + Statistics:
> 0: Node Output Rows: 9
> 1: Node Next Batch Process Time: 0
> 2: Node Cumulative Next Batch Process Time: 12
> 3: Node Cumulative Process Time: 21
> 4: Node Next Batch Calls: 3
> 5: Node Blocks: 2
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> JoinNode
> + Relational Node ID:1
> + Output Columns:
> 0: ID (integer)
> 1: symbol (string)
> 2: price (bigdecimal)
> 3: COMPANY_NAME (string)
> + Statistics:
> 0: Node Output Rows: 9
> 1: Node Next Batch Process Time: 4
> 2: Node Cumulative Next Batch Process Time: 12
> 3: Node Cumulative Process Time: 21
> 4: Node Next Batch Calls: 3
> 5: Node Blocks: 2
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> JoinNode
> + Relational Node ID:2
> + Output Columns:
> 0: symbol (string)
> 1: price (bigdecimal)
> + Statistics:
> 0: Node Output Rows: 10
> 1: Node Next Batch Process Time: 2
> 2: Node Cumulative Next Batch Process Time: 7
> 3: Node Cumulative Process Time: 7
> 4: Node Next Batch Calls: 2
> 5: Node Blocks: 1
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> ProjectNode
> + Relational Node ID:3
> + Output Columns:file (clob)
> + Statistics:
> 0: Node Output Rows: 1
> 1: Node Next Batch Process Time: 0
> 2: Node Cumulative Next Batch Process Time: 2
> 3: Node Cumulative Process Time: 2
> 4: Node Next Batch Calls: 1
> 5: Node Blocks: 0
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> AccessNode
> + Relational Node ID:4
> + Output Columns:
> 0: file (clob)
> 1: filePath (string)
> + Statistics:
> 0: Node Output Rows: 1
> 1: Node Next Batch Process Time: 2
> 2: Node Cumulative Next Batch Process Time: 2
> 3: Node Cumulative Process Time: 2
> 4: Node Next Batch Calls: 1
> 5: Node Blocks: 0
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Query:EXEC MarketData.getTextFiles('*.txt')
> + Model Name:MarketData
> + Select Columns:MarketData.getTextFiles.file
> + Child 1:
> TextTableNode
> + Relational Node ID:5
> + Output Columns:
> 0: symbol (string)
> 1: price (bigdecimal)
> + Statistics:
> 0: Node Output Rows: 10
> 1: Node Next Batch Process Time: 3
> 2: Node Cumulative Next Batch Process Time: 3
> 3: Node Cumulative Process Time: 3
> 4: Node Next Batch Calls: 2
> 5: Node Blocks: 1
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Table Function:TEXTTABLE(f.file COLUMNS symbol string, price bigdecimal HEADER) AS SP
> + Join Strategy:NESTED TABLE JOIN
> + Join Type:CROSS JOIN
> + Join Criteria
> + Child 1:
> AccessNode
> + Relational Node ID:6
> + Output Columns:
> 0: SYMBOL (string)
> 1: ID (integer)
> 2: COMPANY_NAME (string)
> + Statistics:
> 0: Node Output Rows: 25
> 1: Node Next Batch Process Time: 1
> 2: Node Cumulative Next Batch Process Time: 1
> 3: Node Cumulative Process Time: 17
> 4: Node Next Batch Calls: 5
> 5: Node Blocks: 4
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Query:SELECT g_0.SYMBOL AS c_0, g_0.ID AS c_1, g_0.COMPANY_NAME AS c_2 FROM Accounts.PRODUCT AS g_0 ORDER BY c_0
> + Model Name:Accounts
> + Join Strategy:ENHANCED SORT JOIN RAN AS SORT MERGE (SORT/ALREADY_SORTED)
> + Join Type:INNER JOIN
> + Join Criteria:SP.symbol=A.SYMBOL
> + Select Columns:
> 0: A.ID AS product_id
> 1: SP.symbol
> 2: SP.price
> 3: A.COMPANY_NAME AS company_name
> + Data Bytes Sent:0
> + Planning Time:128
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years
[JBoss JIRA] (TEIID-3846) Wrong alias rewriting in subqueries
by Salvatore R (JIRA)
Salvatore R created TEIID-3846:
----------------------------------
Summary: Wrong alias rewriting in subqueries
Key: TEIID-3846
URL: https://issues.jboss.org/browse/TEIID-3846
Project: Teiid
Issue Type: Bug
Reporter: Salvatore R
Assignee: Steven Hawkins
I defined two tables in PostgreSQL (but it's reproducible also in other databases like MySQL, for example) as follows:
{code:sql}
CREATE TABLE test_a
(
a integer,
b integer
);
CREATE TABLE test_b
(
b integer,
c integer
);
{code}
Running this query:
{code:sql}
SELECT
*
FROM
( SELECT
( SELECT x.a FROM pg.test_a AS x WHERE x.b = g_0.c ) AS c_2
FROM
pg.test_b AS g_0
) AS v_0
{code}
I get the following exception:
{code:sql}
13:11:30,073 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue50) 1iTDksSjXt1V Connector worker process failed for atomic-request=1iTDksSjXt1V.16.1.11: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT v_0.c_0 FROM (SELECT (SELECT g_1."a" FROM "public"."test_a" AS g_1 WHERE g_1."b" = g_1."c") AS c_0 FROM "public"."test_b" AS g_0) AS v_0]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:349)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_67]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_67]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_67]
at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_67]
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
at com.sun.proxy.$Proxy47.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_67]
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]
at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]
Caused by: org.postgresql.util.PSQLException: ERROR: column g_1.c does not exist
Position: 91
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
... 18 more
{code}
It seems that the problem is related to the alias "g_0" used for the source table "pg.test_b". In fact, if a different alias is used, the query runs without errors:
{code:sql}
SELECT
*
FROM
( SELECT
( SELECT x.a FROM pg.test_a AS x WHERE x.b = y.c ) AS c_2
FROM
pg.test_b AS y
) AS v_0
{code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years