[teiid-issues] [JBoss JIRA] (TEIID-3476) Order by on query with union or union all orders parts independently

Order by on query with union or union all orders parts independently -------------------------------------------------------------------- Key: TEIID-3476 URL: https://issues.jboss.org/browse/TEIID-3476 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 8.4 Reporter: Bram Gadeyne Assignee: Steven Hawkins Fix For: 8.11 Attachments: debugplan_orderbyproblem.txt I have this query that consists of a UNION ALL of 2 select queries that have a group by statement. I'd like to sort the whole result on the column aliased admtime. However this results in an IndexOutOfBoundsException. select gd.patientid as admissionid,gd.PatGroup as patgroup,wr.Abbreviation as ward,gd.AdmissionTime as admtime, dd.DischargeTime as distime, 'wh' as origin from wh_P_GeneralData gd join wh_P_DischargeData dd on gd.patientid = dd.patientid join prod_S_WardRef wr on wr.WardID = gd.WardID where gd.patientid in ( select patientid from wh_P_PharmaRec pr where pr.PharmaID = 1002463 and bitand(pr.Status, 2) <> 2 group by patientid ) group by gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime UNION ALL select gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime , 'prod' from prod_P_GeneralData gd join prod_P_DischargeData dd on gd.patientid = dd.patientid join prod_S_WardRef wr on wr.WardID = gd.WardID where gd.patientid in ( select patientid from prod_P_PharmaRec pr where pr.PharmaID = 1002463 and bitand(pr.Status, 2) <> 2 group by patientid ) and gd.Status >= 4 and gd.status <> 5 group by gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime order by admtime java.lang.IndexOutOfBoundsException: Index: 6, Size: 6 at java.util.ArrayList.rangeCheck(ArrayList.java:604) [rt.jar:1.7.0_25] at java.util.ArrayList.get(ArrayList.java:382) [rt.jar:1.7.0_25] at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.setColStatEstimates(NewCalculateCostUtil.java:411) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.setCardinalityEstimate(NewCalculateCostUtil.java:313) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.estimateSetOpCost(NewCalculateCostUtil.java:252) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.computeNodeCost(NewCalculateCostUtil.java:204) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:136) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:133) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:133) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.computeCostForTree(NewCalculateCostUtil.java:122) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.query.optimizer.relational.rules.RuleCalculateCost.execute(RuleCalculateCost.java:50) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:739) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:221) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:159) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:411) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.dqp.internal.process.Request.processRequest(Request.java:438) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:614) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:320) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:259) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.9.1.jar:8.9.1] at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.9.1.jar:8.9.1] 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] I then changed the query to this form: select * from ( previous query ) v order by admtime In this case there is no error but the ordering seemed to have happened for each part of the select statement independently. So the collection of columns with origin 'prod' is sorted and the collection of rows with 'wh' as origin are sorted independently and then unioned toghether. I've attached the debug log