[teiid-issues] [JBoss JIRA] (TEIID-4211) Wrong rewriting of CTEs when pushed down to PostgreSQL

[Salvatore R (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4211?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13244181#comment-13244181 ] 

Salvatore R edited comment on TEIID-4211 at 5/27/16 11:23 AM:
--------------------------------------------------------------

Ok, thanks for the quick reply. I logged a new issue for this problem in TEIID-4232


was (Author: fox123):
Ok, thanks for the quick reply. I will log a new issue for this problem.

> Wrong rewriting of CTEs when pushed down to PostgreSQL
> ------------------------------------------------------
>
>                 Key: TEIID-4211
>                 URL: https://issues.jboss.org/browse/TEIID-4211
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.13.4
>            Reporter: Salvatore R
>            Assignee: Steven Hawkins
>             Fix For: 9.0, 8.12.5, 8.13.5
>
>
> Running the following query:
> {code:sql}
> WITH 
> 	cte1 as (SELECT * from pg.test_a),
> 	cte2 as (select * from cte1),
> 	cte3 as (select * from cte1)
> SELECT * FROM cte2 join cte3 on cte2.a=cte3.a
> {code} 
> where pg.test_a is a table defined in PostgreSQL, this exception is thrown:
> {code:sql}
> 19:42:22,287 WARN  [org.teiid.CONNECTOR] (Worker20_QueryProcessorQueue228) ezaCNEtY3C+P Connector worker process failed for atomic-request=ezaCNEtY3C+P.121.3.48: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH cte1 (a, b) AS (SELECT g_0."a", g_0."b" FROM "public"."test_a" AS g_0), cte1 (a, b) AS (SELECT g_0."a", g_0."b" FROM "public"."test_a" AS g_0), cte2 (a,b) AS (SELECT g_0.a, g_0.b FROM cte1 AS g_0), cte3 (a, b) AS (SELECT g_0.a, g_0.b FROM cte1 AS g_0) SELECT g_1.a, g_1.b, g_0.a, g_0.b FROM cte2 AS g_0, cte3 AS g_1 WHERE g_0.a = g_1.a]
>         at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
>         at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:359)
>         at sun.reflect.GeneratedMethodAccessor92.invoke(Unknown Source)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>         at java.lang.reflect.Method.invoke(Method.java:606)
>         at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
>         at com.sun.proxy.$Proxy56.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)
>         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)
>         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
>         at java.lang.Thread.run(Thread.java:745)
> Caused by: org.postgresql.util.PSQLException: ERROR: WITH query name "cte1" specified more than once
>   Position: 78
>         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:504)
>         at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
>         ... 17 more
> {code}
> I am not sure if the problem below might be also related, but an exception is thrown by running the following query with nested CTEs:
> {code:sql}
> WITH 
> 	 cte1 as (SELECT 1 as a)
> 	,cte3 as (with cte3_1 as (select cte1.a from cte1 join pg.test_a t1 on cte1.a=t1.a) select * from cte3_1)
> SELECT * FROM cte3;;
> {code}
> The exception is:
> {code:sql}
> 20:00:37,731 WARN  [org.teiid.PROCESSOR] (Worker21_QueryProcessorQueue239) ezaCNEtY3C+P TEIID30020 Processing exception for request ezaCNEtY3C+P.125 'TEIID30226 Temporary table "cte1" does not exist.'. Originally QueryProcessingException TempTableStore.java:564. Enable more detailed logging to see the entire stacktrace.
> {code}



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