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

Data verification failure with lateral unnest query having filter in 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

      Data verification failure with lateral unnest query having filter in and order by .

      lateral query -

      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;
      

      result :-

      +------------+---------------------+-------------+
      | c_custkey | c_name | totalprice |
      +------------+---------------------+-------------+
      | 101276 | Customer#000101276 | 82657.72 |
      | 120295 | Customer#000120295 | 266119.96 |
      | 120376 | Customer#000120376 | 180309.76 |
      +------------+---------------------+-------------+
      

      flatten query -

      select f.c_custkey, f.c_name, sum(f.o.o_totalprice) from (select c_custkey, c_name, flatten(c_orders) as o from customer) f WHERE f.o.o_totalprice in (89230.03,270087.44,246408.53,82657.72,153941.38,65277.06,180309.76) group by f.c_custkey, f.c_name order by f.c_custkey limit 50;
      

      result :-

      +------------+---------------------+------------+
      | c_custkey | c_name | EXPR$2 |
      +------------+---------------------+------------+
      | 101276 | Customer#000101276 | 82657.72 |
      | 120376 | Customer#000120376 | 180309.76 |
      +------------+---------------------+------------+
      

      PS :- The above results are for Parquet type data .The same query for JSON data gives identical result given as follows :-

      +------------+---------------------+------------+
      | c_custkey | c_name | EXPR$2 |
      +------------+---------------------+------------+
      | 101276 | Customer#000101276 | 82657.72 |
      | 120376 | Customer#000120376 | 180309.76 |
      

        Attachments

        1. flatten.pdf
          22 kB
          Kedar Sankar Behera
        2. Lateral json.pdf
          26 kB
          Kedar Sankar Behera
        3. Lateral Parquet.pdf
          23 kB
          Kedar Sankar Behera

          Issue Links

            Activity

              People

              • Assignee:
                shamirwasia Sorabh Hamirwasia
                Reporter:
                kedar.behera Kedar Sankar Behera
                Reviewer:
                Parth Chandra
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: