Describe the issue: See https://issues.jboss.org/browse/TEIID-3490 Suggestions for improvement: Additional information:
Steven Hawkins <shawkins> updated the status of jira TEIID-3490 to Resolved
When I test with following query, the problem still persists. The query is being sent to oracle, where it causes problems (STRING vs. VARCHAR, CONVERT vs. CAST,..) query: WITH WHATEVER AS (SELECT a,CONVERT(b,STRING) AS b_string FROM mytable) SELECT mytable.a,mytable.b FROM mytable WHERE mytable.a=(SELECT LENGTH(WHATEVER.b_string) FROM WHATEVER WHERE mytable.a=WHATEVER.a) command log: 08:21:52,784 DEBUG [org.teiid.COMMAND_LOG] (New I/O worker #1) START USER COMMAND: startTime=2015-08-10 08:21:52.784 requestID=sUiPGB9vG9e5.0 txID=null sessionID=sUiPGB9vG9e5 applicationName=JDBC principal=user@teiid-security vdbName=with-clause vdbVersion=1 sql=WITH WHATEVER AS (SELECT a,CONVERT(b,STRING) AS b_string FROM mytable) SELECT mytable.a,mytable.b FROM mytable WHERE mytable.a=(SELECT LENGTH(WHATEVER.b_string) FROM WHATEVER WHERE mytable.a=WHATEVER.a) 08:21:52,879 DEBUG [org.teiid.COMMAND_LOG] (Worker1_QueryProcessorQueue1) START DATA SRC COMMAND: startTime=2015-08-10 08:21:52.879 requestID=sUiPGB9vG9e5.0 sourceCommandID=2 executionID=0 txID=null modelName=my translatorName=oracle sessionID=sUiPGB9vG9e5 principal=user@teiid-security sql=WITH WHATEVER (A, b_string) AS (SELECT g_0.A, CONVERT(g_0.B, STRING) FROM my.MYTABLE AS g_0) SELECT g_0.A, g_0.B FROM my.MYTABLE AS g_0 WHERE g_0.A = convert((WITH WHATEVER (A, b_string) AS (SELECT g_0.A, CONVERT(g_0.B, STRING) FROM my.MYTABLE AS g_0) SELECT LENGTH(g_1.b_string) FROM WHATEVER AS g_1 WHERE g_1.A = g_0.A), bigdecimal) 08:21:53,044 DEBUG [org.teiid.COMMAND_LOG] (Worker1_QueryProcessorQueue1) ERROR SRC COMMAND: endTime=2015-08-10 08:21:53.043 requestID=sUiPGB9vG9e5.0 sourceCommandID=2 executionID=0 txID=null modelName=my translatorName=oracle sessionID=sUiPGB9vG9e5 principal=user@teiid-security finalRowCount=null 08:21:53,052 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue2) ERROR USER COMMAND: endTime=2015-08-10 08:21:53.052 requestID=sUiPGB9vG9e5.0 txID=null sessionID=sUiPGB9vG9e5 principal=user@teiid-security vdbName=with-clause vdbVersion=1 finalRowCount=null
Steven Hawkins <shawkins> updated the status of jira TEIID-3490 to Closed
Created attachment 1105911 [details] reproducer
It is partially unresolved. Please see how the attached reproducer works. 1. untar the attached archive 2. copy standalone.xml 3. deploy withclause/oracle-vdb.vdb and withclause/ojdbc6_g.jar 4. execute the following query:- with eee as (select * from emp.EMP) select * from dept where dept.deptno in (select deptno from eee) Then, you get the exception:- 17:09:56,056 WARN [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue3) TEIID30020 Processing exception for request V4Qb+6zjhg4Z.0 'TEIID30504 emp: 942 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.DEPTNO AS c_0 FROM eee g_0 ORDE R BY c_0]'. Originally TeiidProcessingException 'ORA-00942: table or view does not exist ' T4CTTIoer.java:452.: org.teiid.core.TeiidProcessingException: TEIID30504 emp: 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.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:381) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:154) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:369) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:159) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:206) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.JoinNode.prefetch(JoinNode.java:231) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:223) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:159) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:141) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:444) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:326) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:254) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_45] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_45] at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_45] Caused by: 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:329) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_45] at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] ... 6 more 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 The following query works fine. with eee as (select * from emp.EMP) select * from eee
It is observed in JDV6.2.0.
https://issues.jboss.org/browse/TEIID-3866 has been captured. I don't think further updates should be tracked here regarding this.
Created attachment 1106559 [details] standalone.xml Updated standalone.xml in the reproducer to bind with the IP address instead the hostname "rhel6" resolvable only on my local setup.