Note: This bug is displayed in read-only format because the product is no longer active in Red Hat Bugzilla.

Bug 1224377 (TEIID-3490)

Summary:

WITH [TABLENAME] AS clause being sent to source query

Product:

[JBoss] JBoss Data Virtualization 6

Reporter:

dsteigne

Component:

Teiid

Assignee:

Van Halbert <vhalbert>

Status:

CLOSED CURRENTRELEASE

QA Contact:

Filip Elias <felias>

Severity:

high

Docs Contact:

Priority:

high

Version:

6.0.0, 6.1.0

CC:

atangrin, hokuda, jolee, jstastny, mbaluch, vhalbert

Target Milestone:

ER3

Keywords:

QA-Closed

Target Release:

6.2.0

Hardware:

All

OS:

All

Whiteboard:

Fixed In Version:

Doc Type:

Bug Fix

Doc Text:

Story Points:

---

Clone Of:

Environment:

Last Closed:

2016-02-10 08:50:07 UTC

Type:

Bug

Regression:

---

Mount Type:

---

Documentation:

---

CRM:

Verified Versions:

Category:

---

oVirt Team:

---

RHEL 7.3 requirements from Atomic Host:

Cloudforms Team:

---

Target Upstream Version:

Embargoed:

Attachments:

Description	Flags
reproducer	none
standalone.xml	none

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.