[teiid-issues] [JBoss JIRA] (TEIID-3949) Subqueries are wrongly pushed down to the underlying datasource

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-3949?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-3949.
-----------------------------------
    Fix Version/s: 8.12.5
                   8.13.1
       Resolution: Done


Updated the logic to use the aggregates from the grouping node symbol map to ensure that the pre-evaluation flag is properly set.

> Subqueries are wrongly pushed down to the underlying datasource
> ---------------------------------------------------------------
>
>                 Key: TEIID-3949
>                 URL: https://issues.jboss.org/browse/TEIID-3949
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.12
>            Reporter: Salvatore R
>            Assignee: Steven Hawkins
>             Fix For: 9.0, 8.12.5, 8.13.1
>
>
> I have two schemas, one in PostgreSQL and another one in MySQL, called "pg" and "my" respectively.
> I defined the following tables in PG:
> {code:sql}
> CREATE TABLE test_a (a integer, b integer);
> CREATE TABLE test_only_pg (a integer, b integer);
> {code}
> and this table in MySQL:
> {code:sql}
> CREATE TABLE `test_b` (
>   `b` int(11),
>   `c` int(11)
> )
> {code}
> When I run this query:
> {code:sql}
> SELECT
> 	SUM(x.b - (SELECT a FROM pg.test_only_pg WHERE b = 1))
> FROM my.test_b x
> INNER JOIN pg.test_a y ON x.b = y.b
> {code}
> I get the following exception:
> {code:sql}
> 16:44:54,776 WARN  [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue28) QWfZdmacM17x Connector worker process failed for atomic-request=QWfZdmacM17x.12.7.4: org.teiid.translator.jdbc.JDBCExecutionException: 1146 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.`b` AS c_0, SUM((g_0.`b` - (SELECT g_1."a" AS c_0 FROM "public"."test_only_pg" AS g_1 WHERE g_1."b" = 1 LIMIT 2))) AS c_1 FROM `test_tables`.`test_b` AS g_0 GROUP BY g_0.`b` ORDER BY c_0]
>         at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.4.jar:8.12.4]
>         at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:356)
>         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:306)
>         at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
>         at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
>         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:65)
>         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: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'public.test_only_pg' doesn't exist
>         at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [rt.jar:1.7.0_67]
>         at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) [rt.jar:1.7.0_67]
>         at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [rt.jar:1.7.0_67]
>         at java.lang.reflect.Constructor.newInstance(Constructor.java:526) [rt.jar:1.7.0_67]
>         at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
>         at com.mysql.jdbc.Util.getInstance(Util.java:386)
>         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
>         at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
>         at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
>         at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
>         at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
>         at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
>         at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
>         at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
>         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.4.jar:8.12.4]
>         ... 18 more
> {code}
> I could reproduce this behavior in Teiid 8.12.4.



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)