[JBoss JIRA] (TEIID-2822) Not a Group By expression witn non-equi join
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2822?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-2822:
----------------------------------
Summary: Not a Group By expression witn non-equi join (was: Teiid 8.1, 8.6 - Not a Group By expression on Oracle)
Fix Version/s: 8.7
Affects Version/s: 7.7
(was: 8.1)
(was: 8.6)
Component/s: Query Engine
The issue manifests itself when issuing an inner join with no equi-join criteria. In which case the necessary column references from the non-equi-join predicates are added to the select under the join, but not to the group by which results in an invalid query.
> Not a Group By expression witn non-equi join
> --------------------------------------------
>
> Key: TEIID-2822
> URL: https://issues.jboss.org/browse/TEIID-2822
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 7.7
> Reporter: Alex K.
> Assignee: Steven Hawkins
> Fix For: 8.7
>
>
> I'm facing a problem with the following statement on Teiid 8.1 and 8.6 and Oracle
> {code:sql}
> SELECT
> A.a
> FROM
> oracle_db.TEST_A A, (select B from mysql.TEST_B) B
> WHERE
> A.b = '1' OR B.b IS NULL
> GROUP BY a
> {code}
> The exception is "ora-00979 not a group by expression" like following:
> {code:sql}
> 23:21:47,810 WARN [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue28) ffPh7k6iRVl7 Connector worker process failed for atomic-request=ffPh7k6iRVl7.9.6.2: org.teiid.tra
> nslator.jdbc.JDBCExecutionException: 979 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."B", g_0."A" FROM "ORACLE"."TEST_A" g_0
> GROUP BY g_0."A"]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:317) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:135) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:369) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.MergeJoinStrategy.process(MergeJoinStrategy.java:149) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:211) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:89) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.SortUtility.initialSort(SortUtility.java:269) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.SortUtility.sort(SortUtility.java:202) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.GroupingNode.sortPhase(GroupingNode.java:392) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:331) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:102) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:159) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:141) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:435) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:320) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:273) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_25]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_25]
> at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_25]
> Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: Not a GROUP BY expression
>
>
> at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
> at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
> at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
> at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
> at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 37 more {code}
> It looks like the group by clause is wrongly pushed down to Oracle (should be processed directly by Teiid, as Oracle cannot handle g_0.B as it is not in the group by clause):
> {code}
> ============================================================================
> CONVERTING PLAN TREE TO PROCESS TREE
>
>
> PROCESS PLAN =
> ProjectNode(2) output=[anon_grp0.gcol0 AS A] [anon_grp0.gcol0 AS A]
> LimitNode(3) output=[anon_grp0.gcol0] limit 100
> GroupingNode(4) output=[anon_grp0.gcol0] [anon_grp1.gcol0]{anon_grp0.gcol0=anon_grp1.gcol0}
> JoinNode(5) [NESTED LOOP JOIN] [INNER JOIN] criteria=[(A.B = 1) OR (mysql.test_b.b IS NULL)] output=[anon_grp1.gcol0]
> AccessNode(6) output=[A.B, anon_grp1.gcol0] SELECT g_0.B, g_0.A FROM oracle_DB.TEST_A AS g_0 GROUP BY g_0.A
> AccessNode(7) output=[mysql.test_b.b] SELECT g_0.b FROM mysql.test_b AS g_0
> {code}
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 2 months
[JBoss JIRA] (TEIID-2821) Getting invalid Query against Postgres DB
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2821?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-2821:
---------------------------------------
Changing this to an enhancement lobbed of the workaround, which was to use an inline view:
SELECT SUM_BALANCE FROM (SELECT SUM(TBALANCE) AS SUM_BALANCE, TELLERS.BID FROM TELLERS JOIN BRANCHES ON BRANCHES.BID=TELLERS.BID GROUP BY TELLERS.BID) AS X ORDER BY BID
> Getting invalid Query against Postgres DB
> -----------------------------------------
>
> Key: TEIID-2821
> URL: https://issues.jboss.org/browse/TEIID-2821
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 7.7
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Priority: Minor
>
> Issuing the following query when postgres datasource is the target:
> SELECT SUM(TBALANCE) FROM TELLERS JOIN BRANCHES ON BRANCHES.BID=TELLERS.BID GROUP BY TELLERS.BID ORDER BY TELLERS.BID
> (which works when run directly against postgres)
> And getting the following exception from Teiid:
> Caused by: org.teiid.core.TeiidProcessingException: TEIID30088 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30088 Unrelated order by column TELLERS.BID cannot be used in a SET query, with SELECT DISTINCT, or GROUP BY
> at org.teiid.query.resolver.util.ResolverUtil.resolveOrderBy(ResolverUtil.java:422)
> at org.teiid.query.resolver.command.SimpleQueryResolver.resolveCommand(SimpleQueryResolver.java:94)
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:270)
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:124)
> at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:260)
> at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:370)
> at org.teiid.dqp.internal.process.Request.processRequest(Request.java:436)
> at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:600)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:309)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:722)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 2 months
[JBoss JIRA] (TEIID-2821) Getting invalid Query against Postgres DB
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2821?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-2821:
----------------------------------
Issue Type: Enhancement (was: Bug)
Description:
Issuing the following query when postgres datasource is the target:
SELECT SUM(TBALANCE) FROM TELLERS JOIN BRANCHES ON BRANCHES.BID=TELLERS.BID GROUP BY TELLERS.BID ORDER BY TELLERS.BID
(which works when run directly against postgres)
And getting the following exception from Teiid:
Caused by: org.teiid.core.TeiidProcessingException: TEIID30088 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30088 Unrelated order by column TELLERS.BID cannot be used in a SET query, with SELECT DISTINCT, or GROUP BY
at org.teiid.query.resolver.util.ResolverUtil.resolveOrderBy(ResolverUtil.java:422)
at org.teiid.query.resolver.command.SimpleQueryResolver.resolveCommand(SimpleQueryResolver.java:94)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:270)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:124)
at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:260)
at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:370)
at org.teiid.dqp.internal.process.Request.processRequest(Request.java:436)
at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:600)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:309)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
was:
Issuing the following query when postgres datasource is thetarget:
SELECT SUM(TBALANCE) FROM TELLERS JOIN BRANCHES ON BRANCHES.BID=TELLERS.BID GROUP BY TELLERS.BID ORDER BY TELLERS.BID
(which works when run directly against postgres)
And getting the following exception from Teiid:
Caused by: org.teiid.core.TeiidProcessingException: TEIID30088 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30088 Unrelated order by column TELLERS.BID cannot be used in a SET query, with SELECT DISTINCT, or GROUP BY
at org.teiid.query.resolver.util.ResolverUtil.resolveOrderBy(ResolverUtil.java:422)
at org.teiid.query.resolver.command.SimpleQueryResolver.resolveCommand(SimpleQueryResolver.java:94)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:270)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:124)
at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:260)
at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:370)
at org.teiid.dqp.internal.process.Request.processRequest(Request.java:436)
at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:600)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:309)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
Priority: Minor (was: Major)
Affects Version/s: 7.7
(was: 8.4)
Workaround Description:
Use an inline view. From the sample sql above:
select sum_balance from (SELECT SUM(TBALANCE) as sum_balance, TELLERS.BID FROM TELLERS JOIN BRANCHES ON BRANCHES.BID=TELLERS.BID GROUP BY TELLERS.BID) x ORDER BY BID
Workaround: Workaround Exists
Modifying to be a minor enhancement. Strictly following the spec this query is not valid - only simple queries may have unrelated order by columns. This specific query though since the unrelated column is still a valid selectable column and there is no distinct is still logically correct. See the workaround for an inline view to make this query valid wrt the current validation.
> Getting invalid Query against Postgres DB
> -----------------------------------------
>
> Key: TEIID-2821
> URL: https://issues.jboss.org/browse/TEIID-2821
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 7.7
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Priority: Minor
>
> Issuing the following query when postgres datasource is the target:
> SELECT SUM(TBALANCE) FROM TELLERS JOIN BRANCHES ON BRANCHES.BID=TELLERS.BID GROUP BY TELLERS.BID ORDER BY TELLERS.BID
> (which works when run directly against postgres)
> And getting the following exception from Teiid:
> Caused by: org.teiid.core.TeiidProcessingException: TEIID30088 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30088 Unrelated order by column TELLERS.BID cannot be used in a SET query, with SELECT DISTINCT, or GROUP BY
> at org.teiid.query.resolver.util.ResolverUtil.resolveOrderBy(ResolverUtil.java:422)
> at org.teiid.query.resolver.command.SimpleQueryResolver.resolveCommand(SimpleQueryResolver.java:94)
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:270)
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:124)
> at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:260)
> at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:370)
> at org.teiid.dqp.internal.process.Request.processRequest(Request.java:436)
> at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:600)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:309)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:722)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 2 months
[JBoss JIRA] (TEIID-2822) Teiid 8.1, 8.6 - Not a Group By expression on Oracle
by Alex K. (JIRA)
[ https://issues.jboss.org/browse/TEIID-2822?page=com.atlassian.jira.plugin... ]
Alex K. updated TEIID-2822:
---------------------------
Issue Type: Bug (was: Feature Request)
> Teiid 8.1, 8.6 - Not a Group By expression on Oracle
> ----------------------------------------------------
>
> Key: TEIID-2822
> URL: https://issues.jboss.org/browse/TEIID-2822
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.1, 8.6
> Reporter: Alex K.
> Assignee: Steven Hawkins
>
> I'm facing a problem with the following statement on Teiid 8.1 and 8.6 and Oracle
> {code:sql}
> SELECT
> A.a
> FROM
> oracle_db.TEST_A A, (select B from mysql.TEST_B) B
> WHERE
> A.b = '1' OR B.b IS NULL
> GROUP BY a
> {code}
> The exception is "ora-00979 not a group by expression" like following:
> {code:sql}
> 23:21:47,810 WARN [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue28) ffPh7k6iRVl7 Connector worker process failed for atomic-request=ffPh7k6iRVl7.9.6.2: org.teiid.tra
> nslator.jdbc.JDBCExecutionException: 979 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."B", g_0."A" FROM "ORACLE"."TEST_A" g_0
> GROUP BY g_0."A"]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:317) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:135) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:369) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.MergeJoinStrategy.process(MergeJoinStrategy.java:149) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:211) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:89) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.SortUtility.initialSort(SortUtility.java:269) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.SortUtility.sort(SortUtility.java:202) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.GroupingNode.sortPhase(GroupingNode.java:392) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:331) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:102) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:159) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:141) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:435) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:320) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:273) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_25]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_25]
> at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_25]
> Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: Not a GROUP BY expression
>
>
> at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
> at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
> at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
> at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
> at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 37 more {code}
> It looks like the group by clause is wrongly pushed down to Oracle (should be processed directly by Teiid, as Oracle cannot handle g_0.B as it is not in the group by clause):
> {code}
> ============================================================================
> CONVERTING PLAN TREE TO PROCESS TREE
>
>
> PROCESS PLAN =
> ProjectNode(2) output=[anon_grp0.gcol0 AS A] [anon_grp0.gcol0 AS A]
> LimitNode(3) output=[anon_grp0.gcol0] limit 100
> GroupingNode(4) output=[anon_grp0.gcol0] [anon_grp1.gcol0]{anon_grp0.gcol0=anon_grp1.gcol0}
> JoinNode(5) [NESTED LOOP JOIN] [INNER JOIN] criteria=[(A.B = 1) OR (mysql.test_b.b IS NULL)] output=[anon_grp1.gcol0]
> AccessNode(6) output=[A.B, anon_grp1.gcol0] SELECT g_0.B, g_0.A FROM oracle_DB.TEST_A AS g_0 GROUP BY g_0.A
> AccessNode(7) output=[mysql.test_b.b] SELECT g_0.b FROM mysql.test_b AS g_0
> {code}
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 2 months
[JBoss JIRA] (TEIID-2822) Teiid 8.1, 8.6 - Not a Group By expression on Oracle
by Alex K. (JIRA)
Alex K. created TEIID-2822:
------------------------------
Summary: Teiid 8.1, 8.6 - Not a Group By expression on Oracle
Key: TEIID-2822
URL: https://issues.jboss.org/browse/TEIID-2822
Project: Teiid
Issue Type: Feature Request
Affects Versions: 8.6, 8.1
Reporter: Alex K.
Assignee: Steven Hawkins
I'm facing a problem with the following statement on Teiid 8.1 and 8.6 and Oracle
{code:sql}
SELECT
A.a
FROM
oracle_db.TEST_A A, (select B from mysql.TEST_B) B
WHERE
A.b = '1' OR B.b IS NULL
GROUP BY a
{code}
The exception is "ora-00979 not a group by expression" like following:
{code:sql}
23:21:47,810 WARN [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue28) ffPh7k6iRVl7 Connector worker process failed for atomic-request=ffPh7k6iRVl7.9.6.2: org.teiid.tra
nslator.jdbc.JDBCExecutionException: 979 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."B", g_0."A" FROM "ORACLE"."TEST_A" g_0
GROUP BY g_0."A"]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:317) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:135) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:369) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.MergeJoinStrategy.process(MergeJoinStrategy.java:149) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:211) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:89) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.SortUtility.initialSort(SortUtility.java:269) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.SortUtility.sort(SortUtility.java:202) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.GroupingNode.sortPhase(GroupingNode.java:392) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:331) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:102) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:159) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:141) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:435) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:320) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:273) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_25]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_25]
at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_25]
Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: Not a GROUP BY expression
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
... 37 more {code}
It looks like the group by clause is wrongly pushed down to Oracle (should be processed directly by Teiid, as Oracle cannot handle g_0.B as it is not in the group by clause):
{code}
============================================================================
CONVERTING PLAN TREE TO PROCESS TREE
PROCESS PLAN =
ProjectNode(2) output=[anon_grp0.gcol0 AS A] [anon_grp0.gcol0 AS A]
LimitNode(3) output=[anon_grp0.gcol0] limit 100
GroupingNode(4) output=[anon_grp0.gcol0] [anon_grp1.gcol0]{anon_grp0.gcol0=anon_grp1.gcol0}
JoinNode(5) [NESTED LOOP JOIN] [INNER JOIN] criteria=[(A.B = 1) OR (mysql.test_b.b IS NULL)] output=[anon_grp1.gcol0]
AccessNode(6) output=[A.B, anon_grp1.gcol0] SELECT g_0.B, g_0.A FROM oracle_DB.TEST_A AS g_0 GROUP BY g_0.A
AccessNode(7) output=[mysql.test_b.b] SELECT g_0.b FROM mysql.test_b AS g_0
{code}
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 2 months
[JBoss JIRA] (TEIID-2821) Getting invalid Query against Postgres DB
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-2821?page=com.atlassian.jira.plugin... ]
Van Halbert updated TEIID-2821:
-------------------------------
Description:
Issuing the following query when postgres datasource is thetarget:
SELECT SUM(TBALANCE) FROM TELLERS JOIN BRANCHES ON BRANCHES.BID=TELLERS.BID GROUP BY TELLERS.BID ORDER BY TELLERS.BID
(which works when run directly against postgres)
And getting the following exception from Teiid:
Caused by: org.teiid.core.TeiidProcessingException: TEIID30088 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30088 Unrelated order by column TELLERS.BID cannot be used in a SET query, with SELECT DISTINCT, or GROUP BY
at org.teiid.query.resolver.util.ResolverUtil.resolveOrderBy(ResolverUtil.java:422)
at org.teiid.query.resolver.command.SimpleQueryResolver.resolveCommand(SimpleQueryResolver.java:94)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:270)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:124)
at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:260)
at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:370)
at org.teiid.dqp.internal.process.Request.processRequest(Request.java:436)
at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:600)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:309)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
was:
Issuing the following query when postgres datasource is the target:
SELECT SUM(TBALANCE) FROM TELLERS JOIN BRANCHES ON BRANCHES.BID=TELLERS.BID GROUP BY TELLERS.BID ORDER BY TELLERS.BID
(which works when run directly against postgres)
And getting the following exception from Teiid:
Caused by: org.teiid.core.TeiidProcessingException: TEIID30088 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30088 Unrelated order by column TELLERS.BID cannot be used in a SET query, with SELECT DISTINCT, or GROUP BY
at org.teiid.query.resolver.util.ResolverUtil.resolveOrderBy(ResolverUtil.java:422)
at org.teiid.query.resolver.command.SimpleQueryResolver.resolveCommand(SimpleQueryResolver.java:94)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:270)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:124)
at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:260)
at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:370)
at org.teiid.dqp.internal.process.Request.processRequest(Request.java:436)
at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:600)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:309)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
> Getting invalid Query against Postgres DB
> -----------------------------------------
>
> Key: TEIID-2821
> URL: https://issues.jboss.org/browse/TEIID-2821
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.4
> Reporter: Van Halbert
> Assignee: Steven Hawkins
>
> Issuing the following query when postgres datasource is thetarget:
> SELECT SUM(TBALANCE) FROM TELLERS JOIN BRANCHES ON BRANCHES.BID=TELLERS.BID GROUP BY TELLERS.BID ORDER BY TELLERS.BID
> (which works when run directly against postgres)
> And getting the following exception from Teiid:
> Caused by: org.teiid.core.TeiidProcessingException: TEIID30088 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30088 Unrelated order by column TELLERS.BID cannot be used in a SET query, with SELECT DISTINCT, or GROUP BY
> at org.teiid.query.resolver.util.ResolverUtil.resolveOrderBy(ResolverUtil.java:422)
> at org.teiid.query.resolver.command.SimpleQueryResolver.resolveCommand(SimpleQueryResolver.java:94)
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:270)
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:124)
> at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:260)
> at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:370)
> at org.teiid.dqp.internal.process.Request.processRequest(Request.java:436)
> at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:600)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:309)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:722)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 2 months
[JBoss JIRA] (TEIID-2821) Getting invalid Query against Postgres DB
by Van Halbert (JIRA)
Van Halbert created TEIID-2821:
----------------------------------
Summary: Getting invalid Query against Postgres DB
Key: TEIID-2821
URL: https://issues.jboss.org/browse/TEIID-2821
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 8.4
Reporter: Van Halbert
Assignee: Steven Hawkins
Issuing the following query when postgres datasource is the target:
SELECT SUM(TBALANCE) FROM TELLERS JOIN BRANCHES ON BRANCHES.BID=TELLERS.BID GROUP BY TELLERS.BID ORDER BY TELLERS.BID
(which works when run directly against postgres)
And getting the following exception from Teiid:
Caused by: org.teiid.core.TeiidProcessingException: TEIID30088 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30088 Unrelated order by column TELLERS.BID cannot be used in a SET query, with SELECT DISTINCT, or GROUP BY
at org.teiid.query.resolver.util.ResolverUtil.resolveOrderBy(ResolverUtil.java:422)
at org.teiid.query.resolver.command.SimpleQueryResolver.resolveCommand(SimpleQueryResolver.java:94)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:270)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:124)
at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:260)
at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:370)
at org.teiid.dqp.internal.process.Request.processRequest(Request.java:436)
at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:600)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:309)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 2 months