order by on query with union or union all
gadeyne.bram May 13, 2015 4:51 AMHi,
I'm using teiid 8.9.1.
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