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

[Steven Hawkins (JIRA)]

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

Steven Hawkins resolved TEIID-3476.
-----------------------------------
    Resolution: Done


Updated the plan creation to modify the parent order by as well.

> 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



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)