[teiid-issues] [JBoss JIRA] (TEIID-2821) Getting invalid Query against Postgres DB

[Van Halbert (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-2821?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

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