[teiid-issues] [JBoss JIRA] (TEIID-4953) JOIN of CTEs fails if one of them is dependent and has window functions with inner DISTINCT in underlying queries

JOIN of CTEs fails if one of them is dependent and has window functions with inner DISTINCT in underlying queries ----------------------------------------------------------------------------------------------------------------- Key: TEIID-4953 URL: https://issues.jboss.org/browse/TEIID-4953 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 9.3 Environment: teiid-9.3.0.Final on WildFly Full 10.0.0.Final (WildFly Core 2.0.10.Final) Reporter: dalex dalex Assignee: Steven Hawkins Priority: Blocker Fix For: 10.0, 9.1.5, 9.3.1, 9.2.4 Running the following script: {code:sql|linenumbers|highlight=6,10} with CTE as ( select a from test_pg.test_a ), CTE_WIN_FUNCT as ( select count(distinct a) over (partition by a) as c from CTE ), CTE_UNION as ( select c from CTE_WIN_FUNCT union all select c from CTE_WIN_FUNCT ) select * from CTE join CTE_UNION on true ;; {code} leads to the following error message: {code} 2017-06-07 14:11:09,772 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue35) QOZCg3H0H+do Connector worker process failed for atomic-request=QOZCg3H0H+do.12.3.2: org.teiid.tra nslator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.a FROM CTE AS g_0] at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:220) at com.sun.proxy.$Proxy28.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:266) at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:282) 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:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: org.postgresql.util.PSQLException: relation "cte" does not exist Position: 19 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) ... 18 more {code} but removing window function completely or just DISTINCT on line 6 makes problem to go away. Removing union all in the "CTE_UNION" eliminates it too.