[TEIID-3874] Invalid source query for subquery referencing a common table - JBoss Issue Tracker

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Done
Affects Version/s: 8.7.2.6_2, 8.12.3
Fix Version/s: 8.7.2.6_2, 8.13, 8.12.4
Component/s: Query Engine
Labels:
None

Steps to Reproduce:

Hide

1. untar the attached archive
2. specify [1] in standalone.xml
3. deploy withclause/oracle-vdb.vdb and withclause/ojdbc6_g.jar
4. execute the following query: resulting in [2].

with eee as (select * from emp.EMP)

select * from dept where dept.deptno in (select deptno from eee)

[1]

                <datasource jndi-name="java:/emp" pool-name="emp" enabled="true">

                    <connection-url>jdbc:oracle:thin:@10.64.221.57:1521:ORCL</connection-url>

                    <driver>ojdbc6_g.jar</driver>

                    <security>

                        <user-name>scott</user-name>

                        <password>tiger</password>

                    </security>

                </datasource>

                <datasource jndi-name="java:/dept" pool-name="dept" enabled="true">

                    <connection-url>jdbc:oracle:thin:@10.64.221.57:1521:ORCL</connection-url>

                    <driver>ojdbc6_g.jar</driver>

                    <security>

                        <user-name>scott</user-name>

                        <password>tiger</password>

                    </security>

                </datasource>

                <datasource jndi-name="java:/oracle-vdb" pool-name="oracle-vdb" enabled="true">

                    <connection-url>jdbc:teiid:oracle-vdb</connection-url>

                    <driver>teiid</driver>

                    <security>

                        <user-name>teiidUser</user-name>

                        <password>Passw0rd.</password>

                    </security>

                </datasource>

[2]

WARN  [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue2) n/1W7OUsfDt9 Connector worker process failed for atomic-request=n/1W7OUsfDt9.0.2.1: org.teiid.translator.jdbc.JDBCExecutionException: 942 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.DEPTNO AS c_0 FROM eee g_0 ORDER BY c_0]

	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)

	at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:330) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2]

	at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2]

	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2]

	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2]

	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) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2]

	at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2]

	at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2]

	at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2]

	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: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:452)

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:400)

	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:884)

	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:471)

	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:199)

	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:535)

	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:238)

	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1196)

	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1444)

	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1662)

	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4364)

	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4445)

	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:6268)

	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)

	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)

	... 12 more

Show

1. untar the attached archive 2. specify [1] in standalone.xml 3. deploy withclause/oracle-vdb.vdb and withclause/ojdbc6_g.jar 4. execute the following query: resulting in [2] . with eee as (select * from emp.EMP) select * from dept where dept.deptno in (select deptno from eee) [1] <datasource jndi-name= "java:/emp" pool-name= "emp" enabled= "true" > <connection-url>jdbc:oracle:thin: @10 .64. 221.57 : 1521 :ORCL</connection-url> <driver>ojdbc6_g.jar</driver> <security> <user-name>scott</user-name> <password>tiger</password> </security> </datasource> <datasource jndi-name= "java:/dept" pool-name= "dept" enabled= "true" > <connection-url>jdbc:oracle:thin: @10 .64. 221.57 : 1521 :ORCL</connection-url> <driver>ojdbc6_g.jar</driver> <security> <user-name>scott</user-name> <password>tiger</password> </security> </datasource> <datasource jndi-name= "java:/oracle-vdb" pool-name= "oracle-vdb" enabled= "true" > <connection-url>jdbc:teiid:oracle-vdb</connection-url> <driver>teiid</driver> <security> <user-name>teiidUser</user-name> <password>Passw0rd.</password> </security> </datasource> [2] WARN [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue2) n/1W7OUsfDt9 Connector worker process failed for atomic-request=n/1W7OUsfDt9.0.2.1: org.teiid.translator.jdbc.JDBCExecutionException: 942 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.DEPTNO AS c_0 FROM eee g_0 ORDER BY c_0] at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:330) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2] at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2] at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2] at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2] 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) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2] at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2] at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2] at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.7.3.6_2.jar:8.7.3.6_2] 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: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:452) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:400) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:884) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:471) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:199) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:535) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:238) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1196) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1444) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1662) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4364) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4445) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:6268) at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462) at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) ... 12 more

Bugzilla References:
https://bugzilla.redhat.com/show_bug.cgi?id=1292490
Bugzilla Update:

Perform

Description

TeiidProcessingException when using a With Clause requiring semi-join optimization and having a cross-source with clause subquery.

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

withclause.tar.gz
3.65 MB
17/Dec/15 9:49 AM

Invalid source query for subquery referencing a common table

Details

Description

Gliffy Diagrams

Attachments

Attachments

Activity

People

Dates