6:03 a.m.
[
https://issues.jboss.org/browse/TEIID-4211?page=com.atlassian.jira.plugin...
]
Salvatore R commented on TEIID-4211:
------------------------------------
Thanks for addressing and fixing this problem.
I downloaded the latest code from the master including this issue and I am experiencing
another problem.
I defined the following view:
{code:sql}
create view v1 as
WITH mycte as (SELECT 1 as col1) SELECT col1 FROM mycte;
{code}
If I run this query:
{code:sql}
WITH mycte as (SELECT * FROM views.v1) SELECT * from mycte ;
{code}
it fails on generate the query plan with this exception:
{code}
TEIID31124 Recursive plan detected. Command type Query was already issued against mycte.
Planning cycle: [Query mycte, Query views.v1]
{code}
Using /*+ no_inline */ hint to prevent the inlining doesn't fail and returns the
correct result.
I wondered if this is an expected behavior in Teiid, since similar queries work correctly
in databases like PostgreSQL, for example.
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)