Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-3476

Order by on query with union or union all orders parts independently

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 8.11
    • 8.4
    • Query Engine
    • None

    Description

      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

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            gadeynebram Bram Gadeyne (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: