[JBoss JIRA] Created: (TEIID-1737) Error: Temporary table does not exist when using WITH clause and SQL Server
12:15 p.m.
Error: Temporary table does not exist when using WITH clause and SQL Server
---------------------------------------------------------------------------
Key: TEIID-1737
URL: https://issues.jboss.org/browse/TEIID-1737
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.5
Environment: JBoss AS 5.1, TEIID 7.5, SQL Server 2008
Reporter: Mark Brocato
Assignee: Steven Hawkins
It appears that the WITH clause is not compatible with SQL Server. The following simple
query errors out:
with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
The error message is: Remote org.teiid.api.exception.query.QueryProcessingException:
Temporary table "T1" does not exist.
With Log4J debugging turned on I was able to do a little digging and it looks like to me
that TEIID is sending the correct SQL down into SQL Server, but then gets confused when
processing the result. Here is the relevant portion of the log:
12:56:29,910 DEBUG [COMMAND_LOG] START USER COMMAND: startTime=2011-08-31
12:56:29.91 requestID=QsxTHCTkSXXC.0 txID=null sessionID=QsxTHCTkSXXC
applicationName=JDBC principal=user@teiid-security vdbName=Qiagram vdbVersion=1
sql=with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
12:56:29,912 DEBUG [PROCESSOR] Request Thread QsxTHCTkSXXC.0 with state NEW
12:56:29,913 DEBUG [PROCESSOR] QsxTHCTkSXXC.0 No cache directive
12:56:29,913 DEBUG [PROCESSOR] QsxTHCTkSXXC.0 executing with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
12:56:29,916 DEBUG [PROCESSOR] ProcessTree for QsxTHCTkSXXC.0 AccessNode(1) output=[id]
WITH T1 (T1.id) AS (SELECT g_0.id FROM SCRAP.DEMO.dbo.ACCESS_GROUP AS g_0) SELECT g_0.id
AS c_0 FROM T1 AS g_0
12:56:29,917 DEBUG [TXN_LOG] before
getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@1a53c407(QsxTHCTkSXXC)
12:56:29,917 DEBUG [TXN_LOG] after getOrCreateTransactionContext : QsxTHCTkSXXC NONE
ID:NONE
12:56:29,917 DEBUG [BUFFER_MGR] Creating FileStore: 53
12:56:29,917 DEBUG [BUFFER_MGR] Creating TupleBuffer: 53 [id] [integer] of type PROCESSOR
12:56:29,918 DEBUG [PROCESSOR] QueryProcessor: closing processor
12:56:29,918 DEBUG [PROCESSOR] Request Thread QsxTHCTkSXXC.0 - error occurred
[QueryProcessingException]Temporary table "T1" does not exist.
at
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240)
at
org.teiid.query.tempdata.TempTableDataManager.registerQuery(TempTableDataManager.java:487)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:188)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at
org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at
org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:352)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:207)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
12:56:29,923 DEBUG [COMMAND_LOG] ERROR USER COMMAND: endTime=2011-08-31
12:56:29.923 requestID=QsxTHCTkSXXC.0 txID=null sessionID=QsxTHCTkSXXC
principal=user@teiid-security vdbName=Qiagram vdbVersion=1 finalRowCount=null
12:56:29,924 WARN [PROCESSOR] Processing exception 'Temporary table "T1"
does not exist.' for request QsxTHCTkSXXC.0. Exception type
org.teiid.api.exception.query.QueryProcessingException thrown from
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240).
Enable more detailed logging to see the entire stacktrace.
12:56:29,924 DEBUG [PROCESSOR] Removing tuplesource for the request QsxTHCTkSXXC.0
12:56:29,924 DEBUG [BUFFER_MGR] Removing TupleBuffer: 53
12:56:29,924 DEBUG [PROCESSOR] Sending error to client QsxTHCTkSXXC.0
[QueryProcessingException]Temporary table "T1" does not exist.
at
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240)
at
org.teiid.query.tempdata.TempTableDataManager.registerQuery(TempTableDataManager.java:487)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:188)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at
org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at
org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:352)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:207)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
1:30 p.m.
New subject: [JBoss JIRA] Updated: (TEIID-1737) Error: Temporary table does not exist when using WITH clause and SQL Server
[
https://issues.jboss.org/browse/TEIID-1737?page=com.atlassian.jira.plugin...
]
Steven Hawkins updated TEIID-1737:
----------------------------------
Fix Version/s: 7.4.1
7.6
Affects Version/s: 7.2
(was: 7.5)
Error: Temporary table does not exist when using WITH clause and SQL
Server
---------------------------------------------------------------------------
Key: TEIID-1737
URL: https://issues.jboss.org/browse/TEIID-1737
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.2
Environment: JBoss AS 5.1, TEIID 7.5, SQL Server 2008
Reporter: Mark Brocato
Assignee: Steven Hawkins
Fix For: 7.4.1, 7.6
It appears that the WITH clause is not compatible with SQL Server. The following simple
query errors out:
with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
The error message is: Remote org.teiid.api.exception.query.QueryProcessingException:
Temporary table "T1" does not exist.
With Log4J debugging turned on I was able to do a little digging and it looks like to me
that TEIID is sending the correct SQL down into SQL Server, but then gets confused when
processing the result. Here is the relevant portion of the log:
12:56:29,910 DEBUG [COMMAND_LOG] START USER COMMAND: startTime=2011-08-31
12:56:29.91 requestID=QsxTHCTkSXXC.0 txID=null sessionID=QsxTHCTkSXXC
applicationName=JDBC principal=user@teiid-security vdbName=Qiagram vdbVersion=1
sql=with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
12:56:29,912 DEBUG [PROCESSOR] Request Thread QsxTHCTkSXXC.0 with state NEW
12:56:29,913 DEBUG [PROCESSOR] QsxTHCTkSXXC.0 No cache directive
12:56:29,913 DEBUG [PROCESSOR] QsxTHCTkSXXC.0 executing with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
12:56:29,916 DEBUG [PROCESSOR] ProcessTree for QsxTHCTkSXXC.0 AccessNode(1) output=[id]
WITH T1 (T1.id) AS (SELECT g_0.id FROM SCRAP.DEMO.dbo.ACCESS_GROUP AS g_0) SELECT g_0.id
AS c_0 FROM T1 AS g_0
12:56:29,917 DEBUG [TXN_LOG] before
getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@1a53c407(QsxTHCTkSXXC)
12:56:29,917 DEBUG [TXN_LOG] after getOrCreateTransactionContext : QsxTHCTkSXXC NONE
ID:NONE
12:56:29,917 DEBUG [BUFFER_MGR] Creating FileStore: 53
12:56:29,917 DEBUG [BUFFER_MGR] Creating TupleBuffer: 53 [id] [integer] of type
PROCESSOR
12:56:29,918 DEBUG [PROCESSOR] QueryProcessor: closing processor
12:56:29,918 DEBUG [PROCESSOR] Request Thread QsxTHCTkSXXC.0 - error occurred
[QueryProcessingException]Temporary table "T1" does not exist.
at
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240)
at
org.teiid.query.tempdata.TempTableDataManager.registerQuery(TempTableDataManager.java:487)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:188)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at
org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at
org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:352)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:207)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
12:56:29,923 DEBUG [COMMAND_LOG] ERROR USER COMMAND: endTime=2011-08-31
12:56:29.923 requestID=QsxTHCTkSXXC.0 txID=null sessionID=QsxTHCTkSXXC
principal=user@teiid-security vdbName=Qiagram vdbVersion=1 finalRowCount=null
12:56:29,924 WARN [PROCESSOR] Processing exception 'Temporary table "T1"
does not exist.' for request QsxTHCTkSXXC.0. Exception type
org.teiid.api.exception.query.QueryProcessingException thrown from
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240).
Enable more detailed logging to see the entire stacktrace.
12:56:29,924 DEBUG [PROCESSOR] Removing tuplesource for the request QsxTHCTkSXXC.0
12:56:29,924 DEBUG [BUFFER_MGR] Removing TupleBuffer: 53
12:56:29,924 DEBUG [PROCESSOR] Sending error to client QsxTHCTkSXXC.0
[QueryProcessingException]Temporary table "T1" does not exist.
at
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240)
at
org.teiid.query.tempdata.TempTableDataManager.registerQuery(TempTableDataManager.java:487)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:188)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at
org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at
org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:352)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:207)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
1:32 p.m.
New subject: [JBoss JIRA] Resolved: (TEIID-1737) Error: Temporary table does not exist when using WITH clause and SQL Server
[
https://issues.jboss.org/browse/TEIID-1737?page=com.atlassian.jira.plugin...
]
Steven Hawkins resolved TEIID-1737.
-----------------------------------
Resolution: Done
Updated the temptabledatamanager to preform a better check of whether it should handle a
given query.
Error: Temporary table does not exist when using WITH clause and SQL
Server
---------------------------------------------------------------------------
Key: TEIID-1737
URL: https://issues.jboss.org/browse/TEIID-1737
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.2
Environment: JBoss AS 5.1, TEIID 7.5, SQL Server 2008
Reporter: Mark Brocato
Assignee: Steven Hawkins
Fix For: 7.4.1, 7.6
It appears that the WITH clause is not compatible with SQL Server. The following simple
query errors out:
with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
The error message is: Remote org.teiid.api.exception.query.QueryProcessingException:
Temporary table "T1" does not exist.
With Log4J debugging turned on I was able to do a little digging and it looks like to me
that TEIID is sending the correct SQL down into SQL Server, but then gets confused when
processing the result. Here is the relevant portion of the log:
12:56:29,910 DEBUG [COMMAND_LOG] START USER COMMAND: startTime=2011-08-31
12:56:29.91 requestID=QsxTHCTkSXXC.0 txID=null sessionID=QsxTHCTkSXXC
applicationName=JDBC principal=user@teiid-security vdbName=Qiagram vdbVersion=1
sql=with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
12:56:29,912 DEBUG [PROCESSOR] Request Thread QsxTHCTkSXXC.0 with state NEW
12:56:29,913 DEBUG [PROCESSOR] QsxTHCTkSXXC.0 No cache directive
12:56:29,913 DEBUG [PROCESSOR] QsxTHCTkSXXC.0 executing with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
12:56:29,916 DEBUG [PROCESSOR] ProcessTree for QsxTHCTkSXXC.0 AccessNode(1) output=[id]
WITH T1 (T1.id) AS (SELECT g_0.id FROM SCRAP.DEMO.dbo.ACCESS_GROUP AS g_0) SELECT g_0.id
AS c_0 FROM T1 AS g_0
12:56:29,917 DEBUG [TXN_LOG] before
getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@1a53c407(QsxTHCTkSXXC)
12:56:29,917 DEBUG [TXN_LOG] after getOrCreateTransactionContext : QsxTHCTkSXXC NONE
ID:NONE
12:56:29,917 DEBUG [BUFFER_MGR] Creating FileStore: 53
12:56:29,917 DEBUG [BUFFER_MGR] Creating TupleBuffer: 53 [id] [integer] of type
PROCESSOR
12:56:29,918 DEBUG [PROCESSOR] QueryProcessor: closing processor
12:56:29,918 DEBUG [PROCESSOR] Request Thread QsxTHCTkSXXC.0 - error occurred
[QueryProcessingException]Temporary table "T1" does not exist.
at
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240)
at
org.teiid.query.tempdata.TempTableDataManager.registerQuery(TempTableDataManager.java:487)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:188)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at
org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at
org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:352)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:207)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
12:56:29,923 DEBUG [COMMAND_LOG] ERROR USER COMMAND: endTime=2011-08-31
12:56:29.923 requestID=QsxTHCTkSXXC.0 txID=null sessionID=QsxTHCTkSXXC
principal=user@teiid-security vdbName=Qiagram vdbVersion=1 finalRowCount=null
12:56:29,924 WARN [PROCESSOR] Processing exception 'Temporary table "T1"
does not exist.' for request QsxTHCTkSXXC.0. Exception type
org.teiid.api.exception.query.QueryProcessingException thrown from
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240).
Enable more detailed logging to see the entire stacktrace.
12:56:29,924 DEBUG [PROCESSOR] Removing tuplesource for the request QsxTHCTkSXXC.0
12:56:29,924 DEBUG [BUFFER_MGR] Removing TupleBuffer: 53
12:56:29,924 DEBUG [PROCESSOR] Sending error to client QsxTHCTkSXXC.0
[QueryProcessingException]Temporary table "T1" does not exist.
at
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240)
at
org.teiid.query.tempdata.TempTableDataManager.registerQuery(TempTableDataManager.java:487)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:188)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at
org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at
org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:352)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:207)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
6:22 p.m.
New subject: [JBoss JIRA] Reopened: (TEIID-1737) Error: Temporary table does not exist when using WITH clause and SQL Server
[
https://issues.jboss.org/browse/TEIID-1737?page=com.atlassian.jira.plugin...
]
Mark Brocato reopened TEIID-1737:
---------------------------------
I've downloaded the latest from the continuous build. I am trying to run the
following SQL:
with x as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from x
I get the following stacktrace:
19:16:35,546 WARN [CONNECTOR] Connector worker process failed for
atomic-request=lwSRybVfyA6x.8.1.3
[JDBCExecutionException] 156: Error Code:156 Message:'Incorrect syntax near the
keyword 'SELECT'.' error executing statement(s): [Prepared Values: [] SQL:
WITH SELECT TOP 100 g_0.id AS c_0 FROM x g_0]
1 [SQLServerException]Incorrect syntax near the keyword 'SELECT'.
at
org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:90)
at
org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:264)
at
org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:338)
at
org.teiid.dqp.internal.process.DataTierTupleSource.access$000(DataTierTupleSource.java:80)
at
org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:138)
at
org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:135)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:111)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'SELECT'.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:283)
at
org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
at
org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:86)
... 14 more
19:16:36,996 WARN [PROCESSOR] Processing exception 'Error Code:156 Message:scrap:
Error Code:156 Message:'Incorrect syntax near the keyword 'SELECT'.' error
executing statement(s): [Prepared Values: [] SQL: WITH SELECT TOP 100 g_0.id AS c_0 FROM x
g_0]' for request lwSRybVfyA6x.8. Exception type
org.teiid.core.TeiidProcessingException thrown from
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197).
Enable more detailed logging to see the entire stacktrace.
Aside from the hard error, curiously, it looks like TEIID is injecting a 'TOP 100'
into the SQL for no reason.
Error: Temporary table does not exist when using WITH clause and SQL
Server
---------------------------------------------------------------------------
Key: TEIID-1737
URL: https://issues.jboss.org/browse/TEIID-1737
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.2
Environment: JBoss AS 5.1, TEIID 7.5, SQL Server 2008
Reporter: Mark Brocato
Assignee: Steven Hawkins
Fix For: 7.4.1, 7.6
It appears that the WITH clause is not compatible with SQL Server. The following simple
query errors out:
with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
The error message is: Remote org.teiid.api.exception.query.QueryProcessingException:
Temporary table "T1" does not exist.
With Log4J debugging turned on I was able to do a little digging and it looks like to me
that TEIID is sending the correct SQL down into SQL Server, but then gets confused when
processing the result. Here is the relevant portion of the log:
12:56:29,910 DEBUG [COMMAND_LOG] START USER COMMAND: startTime=2011-08-31
12:56:29.91 requestID=QsxTHCTkSXXC.0 txID=null sessionID=QsxTHCTkSXXC
applicationName=JDBC principal=user@teiid-security vdbName=Qiagram vdbVersion=1
sql=with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
12:56:29,912 DEBUG [PROCESSOR] Request Thread QsxTHCTkSXXC.0 with state NEW
12:56:29,913 DEBUG [PROCESSOR] QsxTHCTkSXXC.0 No cache directive
12:56:29,913 DEBUG [PROCESSOR] QsxTHCTkSXXC.0 executing with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
12:56:29,916 DEBUG [PROCESSOR] ProcessTree for QsxTHCTkSXXC.0 AccessNode(1) output=[id]
WITH T1 (T1.id) AS (SELECT g_0.id FROM SCRAP.DEMO.dbo.ACCESS_GROUP AS g_0) SELECT g_0.id
AS c_0 FROM T1 AS g_0
12:56:29,917 DEBUG [TXN_LOG] before
getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@1a53c407(QsxTHCTkSXXC)
12:56:29,917 DEBUG [TXN_LOG] after getOrCreateTransactionContext : QsxTHCTkSXXC NONE
ID:NONE
12:56:29,917 DEBUG [BUFFER_MGR] Creating FileStore: 53
12:56:29,917 DEBUG [BUFFER_MGR] Creating TupleBuffer: 53 [id] [integer] of type
PROCESSOR
12:56:29,918 DEBUG [PROCESSOR] QueryProcessor: closing processor
12:56:29,918 DEBUG [PROCESSOR] Request Thread QsxTHCTkSXXC.0 - error occurred
[QueryProcessingException]Temporary table "T1" does not exist.
at
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240)
at
org.teiid.query.tempdata.TempTableDataManager.registerQuery(TempTableDataManager.java:487)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:188)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at
org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at
org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:352)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:207)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
12:56:29,923 DEBUG [COMMAND_LOG] ERROR USER COMMAND: endTime=2011-08-31
12:56:29.923 requestID=QsxTHCTkSXXC.0 txID=null sessionID=QsxTHCTkSXXC
principal=user@teiid-security vdbName=Qiagram vdbVersion=1 finalRowCount=null
12:56:29,924 WARN [PROCESSOR] Processing exception 'Temporary table "T1"
does not exist.' for request QsxTHCTkSXXC.0. Exception type
org.teiid.api.exception.query.QueryProcessingException thrown from
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240).
Enable more detailed logging to see the entire stacktrace.
12:56:29,924 DEBUG [PROCESSOR] Removing tuplesource for the request QsxTHCTkSXXC.0
12:56:29,924 DEBUG [BUFFER_MGR] Removing TupleBuffer: 53
12:56:29,924 DEBUG [PROCESSOR] Sending error to client QsxTHCTkSXXC.0
[QueryProcessingException]Temporary table "T1" does not exist.
at
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240)
at
org.teiid.query.tempdata.TempTableDataManager.registerQuery(TempTableDataManager.java:487)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:188)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at
org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at
org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:352)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:207)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:49 p.m.
New subject: [JBoss JIRA] Resolved: (TEIID-1737) Error: Temporary table does not exist when using WITH clause and SQL Server
[
https://issues.jboss.org/browse/TEIID-1737?page=com.atlassian.jira.plugin...
]
Steven Hawkins resolved TEIID-1737.
-----------------------------------
Resolution: Done
Mark, there were another set of issues once correct command got to the pushdown logic.
That has been corrected now too. Thanks for bringing this to our attention.
Error: Temporary table does not exist when using WITH clause and SQL
Server
---------------------------------------------------------------------------
Key: TEIID-1737
URL: https://issues.jboss.org/browse/TEIID-1737
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.2
Environment: JBoss AS 5.1, TEIID 7.5, SQL Server 2008
Reporter: Mark Brocato
Assignee: Steven Hawkins
Fix For: 7.4.1, 7.6
It appears that the WITH clause is not compatible with SQL Server. The following simple
query errors out:
with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
The error message is: Remote org.teiid.api.exception.query.QueryProcessingException:
Temporary table "T1" does not exist.
With Log4J debugging turned on I was able to do a little digging and it looks like to me
that TEIID is sending the correct SQL down into SQL Server, but then gets confused when
processing the result. Here is the relevant portion of the log:
12:56:29,910 DEBUG [COMMAND_LOG] START USER COMMAND: startTime=2011-08-31
12:56:29.91 requestID=QsxTHCTkSXXC.0 txID=null sessionID=QsxTHCTkSXXC
applicationName=JDBC principal=user@teiid-security vdbName=Qiagram vdbVersion=1
sql=with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
12:56:29,912 DEBUG [PROCESSOR] Request Thread QsxTHCTkSXXC.0 with state NEW
12:56:29,913 DEBUG [PROCESSOR] QsxTHCTkSXXC.0 No cache directive
12:56:29,913 DEBUG [PROCESSOR] QsxTHCTkSXXC.0 executing with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
12:56:29,916 DEBUG [PROCESSOR] ProcessTree for QsxTHCTkSXXC.0 AccessNode(1) output=[id]
WITH T1 (T1.id) AS (SELECT g_0.id FROM SCRAP.DEMO.dbo.ACCESS_GROUP AS g_0) SELECT g_0.id
AS c_0 FROM T1 AS g_0
12:56:29,917 DEBUG [TXN_LOG] before
getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@1a53c407(QsxTHCTkSXXC)
12:56:29,917 DEBUG [TXN_LOG] after getOrCreateTransactionContext : QsxTHCTkSXXC NONE
ID:NONE
12:56:29,917 DEBUG [BUFFER_MGR] Creating FileStore: 53
12:56:29,917 DEBUG [BUFFER_MGR] Creating TupleBuffer: 53 [id] [integer] of type
PROCESSOR
12:56:29,918 DEBUG [PROCESSOR] QueryProcessor: closing processor
12:56:29,918 DEBUG [PROCESSOR] Request Thread QsxTHCTkSXXC.0 - error occurred
[QueryProcessingException]Temporary table "T1" does not exist.
at
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240)
at
org.teiid.query.tempdata.TempTableDataManager.registerQuery(TempTableDataManager.java:487)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:188)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at
org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at
org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:352)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:207)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
12:56:29,923 DEBUG [COMMAND_LOG] ERROR USER COMMAND: endTime=2011-08-31
12:56:29.923 requestID=QsxTHCTkSXXC.0 txID=null sessionID=QsxTHCTkSXXC
principal=user@teiid-security vdbName=Qiagram vdbVersion=1 finalRowCount=null
12:56:29,924 WARN [PROCESSOR] Processing exception 'Temporary table "T1"
does not exist.' for request QsxTHCTkSXXC.0. Exception type
org.teiid.api.exception.query.QueryProcessingException thrown from
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240).
Enable more detailed logging to see the entire stacktrace.
12:56:29,924 DEBUG [PROCESSOR] Removing tuplesource for the request QsxTHCTkSXXC.0
12:56:29,924 DEBUG [BUFFER_MGR] Removing TupleBuffer: 53
12:56:29,924 DEBUG [PROCESSOR] Sending error to client QsxTHCTkSXXC.0
[QueryProcessingException]Temporary table "T1" does not exist.
at
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240)
at
org.teiid.query.tempdata.TempTableDataManager.registerQuery(TempTableDataManager.java:487)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:188)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at
org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at
org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:352)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:207)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
9:39 a.m.
New subject: [JBoss JIRA] Commented: (TEIID-1737) Error: Temporary table does not exist when using WITH clause and SQL Server
[
https://issues.jboss.org/browse/TEIID-1737?page=com.atlassian.jira.plugin...
]
Mark Brocato commented on TEIID-1737:
-------------------------------------
This appears to have fixed the problem on my end. Thanks for getting to this so quickly!
Error: Temporary table does not exist when using WITH clause and SQL
Server
---------------------------------------------------------------------------
Key: TEIID-1737
URL: https://issues.jboss.org/browse/TEIID-1737
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.2
Environment: JBoss AS 5.1, TEIID 7.5, SQL Server 2008
Reporter: Mark Brocato
Assignee: Steven Hawkins
Fix For: 7.4.1, 7.6
It appears that the WITH clause is not compatible with SQL Server. The following simple
query errors out:
with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
The error message is: Remote org.teiid.api.exception.query.QueryProcessingException:
Temporary table "T1" does not exist.
With Log4J debugging turned on I was able to do a little digging and it looks like to me
that TEIID is sending the correct SQL down into SQL Server, but then gets confused when
processing the result. Here is the relevant portion of the log:
12:56:29,910 DEBUG [COMMAND_LOG] START USER COMMAND: startTime=2011-08-31
12:56:29.91 requestID=QsxTHCTkSXXC.0 txID=null sessionID=QsxTHCTkSXXC
applicationName=JDBC principal=user@teiid-security vdbName=Qiagram vdbVersion=1
sql=with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
12:56:29,912 DEBUG [PROCESSOR] Request Thread QsxTHCTkSXXC.0 with state NEW
12:56:29,913 DEBUG [PROCESSOR] QsxTHCTkSXXC.0 No cache directive
12:56:29,913 DEBUG [PROCESSOR] QsxTHCTkSXXC.0 executing with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
12:56:29,916 DEBUG [PROCESSOR] ProcessTree for QsxTHCTkSXXC.0 AccessNode(1) output=[id]
WITH T1 (T1.id) AS (SELECT g_0.id FROM SCRAP.DEMO.dbo.ACCESS_GROUP AS g_0) SELECT g_0.id
AS c_0 FROM T1 AS g_0
12:56:29,917 DEBUG [TXN_LOG] before
getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@1a53c407(QsxTHCTkSXXC)
12:56:29,917 DEBUG [TXN_LOG] after getOrCreateTransactionContext : QsxTHCTkSXXC NONE
ID:NONE
12:56:29,917 DEBUG [BUFFER_MGR] Creating FileStore: 53
12:56:29,917 DEBUG [BUFFER_MGR] Creating TupleBuffer: 53 [id] [integer] of type
PROCESSOR
12:56:29,918 DEBUG [PROCESSOR] QueryProcessor: closing processor
12:56:29,918 DEBUG [PROCESSOR] Request Thread QsxTHCTkSXXC.0 - error occurred
[QueryProcessingException]Temporary table "T1" does not exist.
at
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240)
at
org.teiid.query.tempdata.TempTableDataManager.registerQuery(TempTableDataManager.java:487)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:188)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at
org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at
org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:352)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:207)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
12:56:29,923 DEBUG [COMMAND_LOG] ERROR USER COMMAND: endTime=2011-08-31
12:56:29.923 requestID=QsxTHCTkSXXC.0 txID=null sessionID=QsxTHCTkSXXC
principal=user@teiid-security vdbName=Qiagram vdbVersion=1 finalRowCount=null
12:56:29,924 WARN [PROCESSOR] Processing exception 'Temporary table "T1"
does not exist.' for request QsxTHCTkSXXC.0. Exception type
org.teiid.api.exception.query.QueryProcessingException thrown from
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240).
Enable more detailed logging to see the entire stacktrace.
12:56:29,924 DEBUG [PROCESSOR] Removing tuplesource for the request QsxTHCTkSXXC.0
12:56:29,924 DEBUG [BUFFER_MGR] Removing TupleBuffer: 53
12:56:29,924 DEBUG [PROCESSOR] Sending error to client QsxTHCTkSXXC.0
[QueryProcessingException]Temporary table "T1" does not exist.
at
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240)
at
org.teiid.query.tempdata.TempTableDataManager.registerQuery(TempTableDataManager.java:487)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:188)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at
org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at
org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:352)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:207)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
9:12 a.m.
New subject: [JBoss JIRA] (TEIID-1737) Error: Temporary table does not exist when using WITH clause and SQL Server
[
https://issues.jboss.org/browse/TEIID-1737?page=com.atlassian.jira.plugin...
]
Steven Hawkins closed TEIID-1737.
---------------------------------
Error: Temporary table does not exist when using WITH clause and SQL
Server
---------------------------------------------------------------------------
Key: TEIID-1737
URL: https://issues.jboss.org/browse/TEIID-1737
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.2
Environment: JBoss AS 5.1, TEIID 7.5, SQL Server 2008
Reporter: Mark Brocato
Assignee: Steven Hawkins
Fix For: 7.4.1, 7.6
It appears that the WITH clause is not compatible with SQL Server. The following simple
query errors out:
with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
The error message is: Remote org.teiid.api.exception.query.QueryProcessingException:
Temporary table "T1" does not exist.
With Log4J debugging turned on I was able to do a little digging and it looks like to me
that TEIID is sending the correct SQL down into SQL Server, but then gets confused when
processing the result. Here is the relevant portion of the log:
12:56:29,910 DEBUG [COMMAND_LOG] START USER COMMAND: startTime=2011-08-31
12:56:29.91 requestID=QsxTHCTkSXXC.0 txID=null sessionID=QsxTHCTkSXXC
applicationName=JDBC principal=user@teiid-security vdbName=Qiagram vdbVersion=1
sql=with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
12:56:29,912 DEBUG [PROCESSOR] Request Thread QsxTHCTkSXXC.0 with state NEW
12:56:29,913 DEBUG [PROCESSOR] QsxTHCTkSXXC.0 No cache directive
12:56:29,913 DEBUG [PROCESSOR] QsxTHCTkSXXC.0 executing with T1 as (
select id from DEMO.dbo.ACCESS_GROUP
)
select id from T1
12:56:29,916 DEBUG [PROCESSOR] ProcessTree for QsxTHCTkSXXC.0 AccessNode(1) output=[id]
WITH T1 (T1.id) AS (SELECT g_0.id FROM SCRAP.DEMO.dbo.ACCESS_GROUP AS g_0) SELECT g_0.id
AS c_0 FROM T1 AS g_0
12:56:29,917 DEBUG [TXN_LOG] before
getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@1a53c407(QsxTHCTkSXXC)
12:56:29,917 DEBUG [TXN_LOG] after getOrCreateTransactionContext : QsxTHCTkSXXC NONE
ID:NONE
12:56:29,917 DEBUG [BUFFER_MGR] Creating FileStore: 53
12:56:29,917 DEBUG [BUFFER_MGR] Creating TupleBuffer: 53 [id] [integer] of type
PROCESSOR
12:56:29,918 DEBUG [PROCESSOR] QueryProcessor: closing processor
12:56:29,918 DEBUG [PROCESSOR] Request Thread QsxTHCTkSXXC.0 - error occurred
[QueryProcessingException]Temporary table "T1" does not exist.
at
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240)
at
org.teiid.query.tempdata.TempTableDataManager.registerQuery(TempTableDataManager.java:487)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:188)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at
org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at
org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:352)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:207)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
12:56:29,923 DEBUG [COMMAND_LOG] ERROR USER COMMAND: endTime=2011-08-31
12:56:29.923 requestID=QsxTHCTkSXXC.0 txID=null sessionID=QsxTHCTkSXXC
principal=user@teiid-security vdbName=Qiagram vdbVersion=1 finalRowCount=null
12:56:29,924 WARN [PROCESSOR] Processing exception 'Temporary table "T1"
does not exist.' for request QsxTHCTkSXXC.0. Exception type
org.teiid.api.exception.query.QueryProcessingException thrown from
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240).
Enable more detailed logging to see the entire stacktrace.
12:56:29,924 DEBUG [PROCESSOR] Removing tuplesource for the request QsxTHCTkSXXC.0
12:56:29,924 DEBUG [BUFFER_MGR] Removing TupleBuffer: 53
12:56:29,924 DEBUG [PROCESSOR] Sending error to client QsxTHCTkSXXC.0
[QueryProcessingException]Temporary table "T1" does not exist.
at
org.teiid.query.tempdata.TempTableStore.getOrCreateTempTable(TempTableStore.java:240)
at
org.teiid.query.tempdata.TempTableDataManager.registerQuery(TempTableDataManager.java:487)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:188)
at
org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at
org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at
org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:352)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:207)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
4291
days inactive
4641
days old
6 comments
2 participants
participants (2)
-
Mark Brocato (JIRA) -
Steven Hawkins (JIRA)