Bug 1224377 (TEIID-3490) - WITH [TABLENAME] AS clause being sent to source query
Summary: WITH [TABLENAME] AS clause being sent to source query
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: TEIID-3490
Product: JBoss Data Virtualization 6
Classification: JBoss
Component: Teiid
Version: 6.0.0,6.1.0
Hardware: All
OS: All
high
high
Target Milestone: ER3
: 6.2.0
Assignee: Van Halbert
QA Contact: Filip Elias
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2015-05-22 17:49 UTC by dsteigne
Modified: 2020-01-17 15:30 UTC (History)
6 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2016-02-10 08:50:07 UTC
Type: Bug
Embargoed:


Attachments (Terms of Use)
reproducer (3.65 MB, application/x-gzip)
2015-12-15 08:14 UTC, Hisanobu Okuda
no flags Details
standalone.xml (32.75 KB, application/xml)
2015-12-17 00:32 UTC, Hisanobu Okuda
no flags Details


Links
System ID Private Priority Status Summary Last Updated
Red Hat Issue Tracker TEIID-3490 0 Major Closed WITH [TABLENAME] AS clause being sent to source query 2016-09-13 08:57:57 UTC
Red Hat Knowledge Base (Solution) 1456903 0 None None None Never

Description dsteigne 2015-05-22 17:49:12 UTC
Describe the issue: 

See https://issues.jboss.org/browse/TEIID-3490

Suggestions for improvement: 

Additional information:

Comment 2 JBoss JIRA Server 2015-05-22 18:31:10 UTC
Steven Hawkins <shawkins> updated the status of jira TEIID-3490 to Resolved

Comment 4 Jan Stastny 2015-08-10 07:28:10 UTC
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

Comment 6 JBoss JIRA Server 2015-10-11 19:18:37 UTC
Steven Hawkins <shawkins> updated the status of jira TEIID-3490 to Closed

Comment 7 Hisanobu Okuda 2015-12-15 08:14:56 UTC
Created attachment 1105911 [details]
reproducer

Comment 8 Hisanobu Okuda 2015-12-15 08:22:01 UTC
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

Comment 9 Hisanobu Okuda 2015-12-15 08:37:13 UTC
It is observed in JDV6.2.0.

Comment 10 jolee 2015-12-15 20:38:26 UTC
https://issues.jboss.org/browse/TEIID-3866 has been captured.  I don't think further updates should be tracked here regarding this.

Comment 11 Hisanobu Okuda 2015-12-17 00:32:53 UTC
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.


Note You need to log in before you can comment on or make changes to this bug.