Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-6631

Wrong result from LateralUnnest query with aggregation and order by

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.14.0
    • Fix Version/s: 1.15.0
    • Component/s: None
    • Labels:

      Description

      Reported by Chun:

      The following query gives correct result:

      0: jdbc:drill:zk=10.10.30.166:5181> select customer.c_custkey, customer.c_name, orders.totalprice from customer, lateral (select sum(t.o.o_totalprice) as totalprice from unnest(customer.c_orders) t(o) WHERE t.o.o_totalprice in (89230.03,270087.44,246408.53,82657.72,153941.38,65277.06,180309.76)) orders where customer.c_custkey = 101276;
      +------------+---------------------+-------------+
      | c_custkey  |       c_name        | totalprice  |
      +------------+---------------------+-------------+
      | 101276     | Customer#000101276  | 82657.72    |
      +------------+---------------------+-------------+
      1 row selected (6.184 seconds)
      

      But if I remove the where clause and replace it with order by and limit, I got the following empty result set. This is wrong.

      0: jdbc:drill:zk=10.10.30.166:5181> select customer.c_custkey, customer.c_name, orders.totalprice from customer, lateral (select sum(t.o.o_totalprice) as totalprice from unnest(customer.c_orders) t(o) WHERE t.o.o_totalprice in (89230.03,270087.44,246408.53,82657.72,153941.38,65277.06,180309.76)) orders order by customer.c_custkey limit 50;
      +------------+---------+-------------+
      | c_custkey  | c_name  | totalprice  |
      +------------+---------+-------------+
      +------------+---------+-------------+
      No rows selected (2.753 seconds)
      

      Here is the plan for the query giving the correct result:

      00-00    Screen : rowType = RecordType(ANY c_custkey, ANY c_name, ANY totalprice): rowcount = 472783.35, cumulative cost = {8242193.7349999985 rows, 4.102185433499999E7 cpu, 0.0 io, 5.809561804799999E9 network, 0.0 memory}, id = 14410
      00-01      Project(c_custkey=[$0], c_name=[$1], totalprice=[$2]) : rowType = RecordType(ANY c_custkey, ANY c_name, ANY totalprice): rowcount = 472783.35, cumulative cost = {8194915.3999999985 rows, 4.097457599999999E7 cpu, 0.0 io, 5.809561804799999E9 network, 0.0 memory}, id = 14409
      00-02        UnionExchange : rowType = RecordType(ANY c_custkey, ANY c_name, ANY totalprice): rowcount = 472783.35, cumulative cost = {7722132.049999999 rows, 3.9556225949999996E7 cpu, 0.0 io, 5.809561804799999E9 network, 0.0 memory}, id = 14408
      01-01          LateralJoin(correlation=[$cor1], joinType=[inner], requiredColumns=[{0}], column excluded from output: =[`c_orders`]) : rowType = RecordType(ANY c_custkey, ANY c_name, ANY totalprice): rowcount = 472783.35, cumulative cost = {7249348.699999999 rows, 3.577395915E7 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 14407
      01-03            SelectionVectorRemover : rowType = RecordType(ANY c_orders, ANY c_custkey, ANY c_name): rowcount = 472783.35, cumulative cost = {6776561.35 rows, 2.442713975E7 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 14403
      01-05              Filter(condition=[=($1, 101276)]) : rowType = RecordType(ANY c_orders, ANY c_custkey, ANY c_name): rowcount = 472783.35, cumulative cost = {6303778.0 rows, 2.39543564E7 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 14402
      01-07                Scan(groupscan=[EasyGroupScan [selectionRoot=maprfs:/drill/testdata/lateral/tpchsf1/json/customer, numFiles=10, columns=[`c_orders`, `c_custkey`, `c_name`], files=[maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_6.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_4.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_3.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_7.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_5.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_2.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_0.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_8.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_1.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_9.json]]]) : rowType = RecordType(ANY c_orders, ANY c_custkey, ANY c_name): rowcount = 3151889.0, cumulative cost = {3151889.0 rows, 9455667.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 14401
      01-02            StreamAgg(group=[{}], totalprice=[SUM($0)]) : rowType = RecordType(ANY totalprice): rowcount = 1.0, cumulative cost = {4.0 rows, 19.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 14406
      01-04              Filter(condition=[OR(=($0, 89230.03), =($0, 270087.44), =($0, 246408.53), =($0, 82657.72), =($0, 153941.38), =($0, 65277.06), =($0, 180309.76))]) : rowType = RecordType(ANY ITEM): rowcount = 1.0, cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 14405
      01-06                Project(ITEM=[ITEM($0, 'o_totalprice')]) : rowType = RecordType(ANY ITEM): rowcount = 1.0, cumulative cost = {2.0 rows, 2.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 14404
      01-08                  Unnest [srcOp=01-01]  : rowType = RecordType(ANY c_orders): rowcount = 1.0, cumulative cost = {1.0 rows, 1.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 14240
      

      And here is the plan for the query giving wrong result:

      00-00    Screen : rowType = RecordType(ANY c_custkey, ANY c_name, ANY totalprice): rowcount = 50.0, cumulative cost = {2.2063382E7 rows, 2.6342088596853146E8 cpu, 0.0 io, 7.7460824064E10 network, 0.0 memory}, id = 15076
      00-01      Project(c_custkey=[$0], c_name=[$1], totalprice=[$2]) : rowType = RecordType(ANY c_custkey, ANY c_name, ANY totalprice): rowcount = 50.0, cumulative cost = {2.2063377E7 rows, 2.6342088096853146E8 cpu, 0.0 io, 7.7460824064E10 network, 0.0 memory}, id = 15075
      00-02        SelectionVectorRemover : rowType = RecordType(ANY c_custkey, ANY c_name, ANY totalprice): rowcount = 50.0, cumulative cost = {2.2063327E7 rows, 2.6342073096853146E8 cpu, 0.0 io, 7.7460824064E10 network, 0.0 memory}, id = 15074
      00-03          Limit(fetch=[50]) : rowType = RecordType(ANY c_custkey, ANY c_name, ANY totalprice): rowcount = 50.0, cumulative cost = {2.2063277E7 rows, 2.6342068096853146E8 cpu, 0.0 io, 7.7460824064E10 network, 0.0 memory}, id = 15073
      00-04            SingleMergeExchange(sort0=[0]) : rowType = RecordType(ANY c_custkey, ANY c_name, ANY totalprice): rowcount = 3151889.0, cumulative cost = {2.2063227E7 rows, 2.6342048096853146E8 cpu, 0.0 io, 7.7460824064E10 network, 0.0 memory}, id = 15072
      01-01              OrderedMuxExchange(sort0=[0]) : rowType = RecordType(ANY c_custkey, ANY c_name, ANY totalprice): rowcount = 3151889.0, cumulative cost = {1.8911338E7 rows, 2.1299025696853146E8 cpu, 0.0 io, 3.8730412032E10 network, 0.0 memory}, id = 15071
      02-01                SelectionVectorRemover : rowType = RecordType(ANY c_custkey, ANY c_name, ANY totalprice): rowcount = 3151889.0, cumulative cost = {1.5759449E7 rows, 2.0983836796853146E8 cpu, 0.0 io, 3.8730412032E10 network, 0.0 memory}, id = 15070
      02-02                  TopN(limit=[50]) : rowType = RecordType(ANY c_custkey, ANY c_name, ANY totalprice): rowcount = 3151889.0, cumulative cost = {1.260756E7 rows, 2.0668647896853146E8 cpu, 0.0 io, 3.8730412032E10 network, 0.0 memory}, id = 15069
      02-03                    HashToRandomExchange(dist0=[[$0]]) : rowType = RecordType(ANY c_custkey, ANY c_name, ANY totalprice): rowcount = 3151889.0, cumulative cost = {9455671.0 rows, 1.35531246E8 cpu, 0.0 io, 3.8730412032E10 network, 0.0 memory}, id = 15068
      03-01                      LateralJoin(correlation=[$cor1], joinType=[inner], requiredColumns=[{0}], column excluded from output: =[`c_orders`]) : rowType = RecordType(ANY c_custkey, ANY c_name, ANY totalprice): rowcount = 3151889.0, cumulative cost = {6303782.0 rows, 8.5101022E7 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 15067
      03-03                        Scan(groupscan=[EasyGroupScan [selectionRoot=maprfs:/drill/testdata/lateral/tpchsf1/json/customer, numFiles=10, columns=[`c_orders`, `c_custkey`, `c_name`], files=[maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_6.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_4.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_3.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_7.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_5.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_2.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_0.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_8.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_1.json, maprfs:///drill/testdata/lateral/tpchsf1/json/customer/0_0_9.json]]]) : rowType = RecordType(ANY c_orders, ANY c_custkey, ANY c_name): rowcount = 3151889.0, cumulative cost = {3151889.0 rows, 9455667.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 15063
      03-02                        StreamAgg(group=[{}], totalprice=[SUM($0)]) : rowType = RecordType(ANY totalprice): rowcount = 1.0, cumulative cost = {4.0 rows, 19.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 15066
      03-04                          Filter(condition=[OR(=($0, 89230.03), =($0, 270087.44), =($0, 246408.53), =($0, 82657.72), =($0, 153941.38), =($0, 65277.06), =($0, 180309.76))]) : rowType = RecordType(ANY ITEM): rowcount = 1.0, cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 15065
      03-05                            Project(ITEM=[ITEM($0, 'o_totalprice')]) : rowType = RecordType(ANY ITEM): rowcount = 1.0, cumulative cost = {2.0 rows, 2.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 15064
      03-06                              Unnest [srcOp=03-01]  : rowType = RecordType(ANY c_orders): rowcount = 1.0, cumulative cost = {1.0 rows, 1.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 14860

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                parthc Parth Chandra
                Reporter:
                parthc Parth Chandra
                Reviewer:
                Sorabh Hamirwasia
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: