[ 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. -- 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