Apache Drill
  1. Apache Drill
  2. DRILL-1062

DRILL does not handle NULLS FIRST/LAST correctly in ORDER BY clause

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.8.0
    • Component/s: Functions - Drill
    • Labels:
      None

      Description

      ORDER BY clause could specify nulls first or nulls last. Currently, DRILL will always use nulls last policy.

      select tbl.topping[3].type from dfs.`/Users/jni/work/incubator-drill/exec/ref/target/test-classes/donuts.json` as tbl order by 1 nulls last;
      ------------

      EXPR$0

      ------------

      Chocolate
      Maple
      Powdered Sugar
      Powdered Sugar
      null

      ------------
      5 rows selected (0.156 seconds)
      0: jdbc:drill:zk=local> select tbl.topping[3].type from dfs.`/Users/jni/work/incubator-drill/exec/ref/target/test-classes/donuts.json` as tbl order by 1 nulls first;
      ------------

      EXPR$0

      ------------

      Chocolate
      Maple
      Powdered Sugar
      Powdered Sugar
      null

      ------------
      5 rows selected (0.186 seconds)

      1. DRILL-1062.2.patch
        126 kB
        Daniel Barclay (Drill)
      2. DRILL-1062.3.patch.txt
        127 kB
        Daniel Barclay (Drill)
      3. DRILL-1062.4.patch.txt
        271 kB
        Daniel Barclay (Drill)

        Issue Links

          Activity

          Jinfeng Ni created issue -
          Jacques Nadeau made changes -
          Field Original Value New Value
          Link This issue is duplicated by DRILL-1156 [ DRILL-1156 ]
          Hide
          Chun Chang added a comment -

          #Mon Jul 14 10:10:52 PDT 2014
          git.commit.id.abbrev=699851b
          For this mondrian query (query117.q), with group by, result set is stitched together in a wrong order. For example, for some columns, like c6, c7, c8, c9, row oder is wrong.
          Her is the correct result from postgres:
          foodmart=# select store.store_country as c0, store.store_state as c1, store.store_city as c2, store.store_name as c3, store.store_type as c4, store.store_manager as c5, store.store_sqft as c6, store.grocery_sqft as c7, store.frozen_sqft as c8, store.meat_sqft as c9, store.coffee_bar as c10, store.store_street_address as c11 from store as store group by store.store_country, store.store_state, store.store_city, store.store_name, store.store_type, store.store_manager, store.store_sqft, store.grocery_sqft, store.frozen_sqft, store.meat_sqft, store.coffee_bar, store.store_street_address order by store.store_country ASC NULLS LAST, store.store_state ASC NULLS LAST, store.store_city ASC NULLS LAST, store.store_name ASC NULLS LAST;
          c0 | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c11
          ------------------------------------------------------------------------------------+-------------------------
          Canada | BC | Vancouver | Store 19 | Deluxe Supermarket | Ruth | 23112 | 16418 | 4016 | 2678 | t | 6644 Sudance Drive
          Canada | BC | Victoria | Store 20 | Mid-Size Grocery | Cobb | 34452 | 27463 | 4193 | 2795 | t | 3706 Marvelle Ln
          Mexico | DF | Mexico City | Store 9 | Mid-Size Grocery | Stuber | 36509 | 22450 | 8435 | 5624 | f | 1872 El Pintado Road
          Mexico | DF | San Andres | Store 21 | Deluxe Supermarket | Jones | | | | | t | 4093 Steven Circle
          Mexico | Guerrero | Acapulco | Store 1 | Supermarket | Jones | 23593 | 17475 | 3671 | 2447 | f | 2853 Bailey Rd
          Mexico | Jalisco | Guadalajara | Store 5 | Small Grocery | Green | 24597 | 15012 | 5751 | 3834 | t | 1250 Coggins Drive
          Mexico | Veracruz | Orizaba | Store 10 | Supermarket | Merz | 34791 | 26354 | 5062 | 3375 | f | 7894 Rotherham Dr
          Mexico | Yucatan | Merida | Store 8 | Deluxe Supermarket | Williams | 30797 | 20141 | 6393 | 4262 | t | 3173 Buena Vista Ave
          Mexico | Zacatecas | Camacho | Store 4 | Gourmet Supermarket | Johnson | 23759 | 16844 | 4149 | 2766 | t | 433 St George Dr
          Mexico | Zacatecas | Hidalgo | Store 12 | Deluxe Supermarket | Kalman | 30584 | 21938 | 5188 | 3458 | t | 1120 Westchester Pl
          Mexico | Zacatecas | Hidalgo | Store 18 | Mid-Size Grocery | Brown | 38382 | 30351 | 4819 | 3213 | f | 6764 Glen Road
          USA | CA | Alameda | HQ | HeadQuarters | | | | | | f | 1 Alameda Way
          USA | CA | Beverly Hills | Store 6 | Gourmet Supermarket | Maris | 23688 | 15337 | 5011 | 3340 | t | 5495 Mitchell Canyon Road
          USA | CA | Los Angeles | Store 7 | Supermarket | White | 23598 | 14210 | 5633 | 3755 | f | 1077 Wharf Drive
          USA | CA | San Diego | Store 24 | Supermarket | Byrd | | | | | t | 2342 Waltham St.
          USA | CA | San Francisco | Store 14 | Small Grocery | Strehlo | 22478 | 15321 | 4294 | 2863 | t | 4365 Indigo Ct

          Show
          Chun Chang added a comment - #Mon Jul 14 10:10:52 PDT 2014 git.commit.id.abbrev=699851b For this mondrian query (query117.q), with group by, result set is stitched together in a wrong order. For example, for some columns, like c6, c7, c8, c9, row oder is wrong. Her is the correct result from postgres: foodmart=# select store.store_country as c0, store.store_state as c1, store.store_city as c2, store.store_name as c3, store.store_type as c4, store.store_manager as c5, store.store_sqft as c6, store.grocery_sqft as c7, store.frozen_sqft as c8, store.meat_sqft as c9, store.coffee_bar as c10, store.store_street_address as c11 from store as store group by store.store_country, store.store_state, store.store_city, store.store_name, store.store_type, store.store_manager, store.store_sqft, store.grocery_sqft, store.frozen_sqft, store.meat_sqft, store.coffee_bar, store.store_street_address order by store.store_country ASC NULLS LAST, store.store_state ASC NULLS LAST, store.store_city ASC NULLS LAST, store.store_name ASC NULLS LAST; c0 | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c11 ------------------------------------------------------------------------------------ + ------------------------- Canada | BC | Vancouver | Store 19 | Deluxe Supermarket | Ruth | 23112 | 16418 | 4016 | 2678 | t | 6644 Sudance Drive Canada | BC | Victoria | Store 20 | Mid-Size Grocery | Cobb | 34452 | 27463 | 4193 | 2795 | t | 3706 Marvelle Ln Mexico | DF | Mexico City | Store 9 | Mid-Size Grocery | Stuber | 36509 | 22450 | 8435 | 5624 | f | 1872 El Pintado Road Mexico | DF | San Andres | Store 21 | Deluxe Supermarket | Jones | | | | | t | 4093 Steven Circle Mexico | Guerrero | Acapulco | Store 1 | Supermarket | Jones | 23593 | 17475 | 3671 | 2447 | f | 2853 Bailey Rd Mexico | Jalisco | Guadalajara | Store 5 | Small Grocery | Green | 24597 | 15012 | 5751 | 3834 | t | 1250 Coggins Drive Mexico | Veracruz | Orizaba | Store 10 | Supermarket | Merz | 34791 | 26354 | 5062 | 3375 | f | 7894 Rotherham Dr Mexico | Yucatan | Merida | Store 8 | Deluxe Supermarket | Williams | 30797 | 20141 | 6393 | 4262 | t | 3173 Buena Vista Ave Mexico | Zacatecas | Camacho | Store 4 | Gourmet Supermarket | Johnson | 23759 | 16844 | 4149 | 2766 | t | 433 St George Dr Mexico | Zacatecas | Hidalgo | Store 12 | Deluxe Supermarket | Kalman | 30584 | 21938 | 5188 | 3458 | t | 1120 Westchester Pl Mexico | Zacatecas | Hidalgo | Store 18 | Mid-Size Grocery | Brown | 38382 | 30351 | 4819 | 3213 | f | 6764 Glen Road USA | CA | Alameda | HQ | HeadQuarters | | | | | | f | 1 Alameda Way USA | CA | Beverly Hills | Store 6 | Gourmet Supermarket | Maris | 23688 | 15337 | 5011 | 3340 | t | 5495 Mitchell Canyon Road USA | CA | Los Angeles | Store 7 | Supermarket | White | 23598 | 14210 | 5633 | 3755 | f | 1077 Wharf Drive USA | CA | San Diego | Store 24 | Supermarket | Byrd | | | | | t | 2342 Waltham St. USA | CA | San Francisco | Store 14 | Small Grocery | Strehlo | 22478 | 15321 | 4294 | 2863 | t | 4365 Indigo Ct
          Hide
          Chun Chang added a comment -

          Here is the wrong result from Drill:

          Canada BC Vancouver Store 19 Deluxe Supermarket Ruth 34452 27463 4193 2795 t 6644 Sudance Drive
          Canada BC Victoria Store 20 Mid-Size Grocery Cobb 0 0 0 0 t 3706 Marvelle Ln
          Mexico DF Mexico City Store 9 Mid-Size Grocery Stuber 34791 26354 5062 3375 f 1872 El Pintado Road
          Mexico DF San Andres Store 21 Deluxe Supermarket Jones null null null null t 4093 Steven Circle
          Mexico Guerrero Acapulco Store 1 Supermarket Jones 28206 22271 3561 2374 f 2853 Bailey Rd
          Mexico Jalisco Guadalajara Store 5 Small Grocery Green 23688 15337 5011 3340 t 1250 Coggins Drive
          Mexico Veracruz Orizaba Store 10 Supermarket Merz 20319 16232 2452 1635 f 7894 Rotherham Dr
          Mexico Yucatan Merida Store 8 Deluxe Supermarket Williams 36509 22450 8435 5624 t 3173 Buena Vista Ave
          Mexico Zacatecas Camacho Store 4 Gourmet Supermarket Johnson 24597 15012 5751 3834 t 433 St George Dr
          Mexico Zacatecas Hidalgo Store 12 Deluxe Supermarket Kalman 27694 18670 5415 3610 t 1120 Westchester Pl
          Mexico Zacatecas Hidalgo Store 18 Mid-Size Grocery Brown 23112 16418 4016 2678 f 6764 Glen Road
          USA CA Alameda HQ HeadQuarters null null null null null f 1 Alameda Way
          USA CA Beverly Hills Store 6 Gourmet Supermarket Maris 23598 14210 5633 3755 t 5495 Mitchell Canyon Road
          USA CA Los Angeles Store 7 Supermarket White 30797 20141 6393 4262 f 1077 Wharf Drive
          USA CA San Francisco Store 14 Small Grocery Strehlo 21215 13305 4746 3164 t 4365 Indigo Ct
          USA OR Portland Store 11 Supermarket Erickson 30584 21938 5188 3458 f 5371 Holland Circle

          Show
          Chun Chang added a comment - Here is the wrong result from Drill: Canada BC Vancouver Store 19 Deluxe Supermarket Ruth 34452 27463 4193 2795 t 6644 Sudance Drive Canada BC Victoria Store 20 Mid-Size Grocery Cobb 0 0 0 0 t 3706 Marvelle Ln Mexico DF Mexico City Store 9 Mid-Size Grocery Stuber 34791 26354 5062 3375 f 1872 El Pintado Road Mexico DF San Andres Store 21 Deluxe Supermarket Jones null null null null t 4093 Steven Circle Mexico Guerrero Acapulco Store 1 Supermarket Jones 28206 22271 3561 2374 f 2853 Bailey Rd Mexico Jalisco Guadalajara Store 5 Small Grocery Green 23688 15337 5011 3340 t 1250 Coggins Drive Mexico Veracruz Orizaba Store 10 Supermarket Merz 20319 16232 2452 1635 f 7894 Rotherham Dr Mexico Yucatan Merida Store 8 Deluxe Supermarket Williams 36509 22450 8435 5624 t 3173 Buena Vista Ave Mexico Zacatecas Camacho Store 4 Gourmet Supermarket Johnson 24597 15012 5751 3834 t 433 St George Dr Mexico Zacatecas Hidalgo Store 12 Deluxe Supermarket Kalman 27694 18670 5415 3610 t 1120 Westchester Pl Mexico Zacatecas Hidalgo Store 18 Mid-Size Grocery Brown 23112 16418 4016 2678 f 6764 Glen Road USA CA Alameda HQ HeadQuarters null null null null null f 1 Alameda Way USA CA Beverly Hills Store 6 Gourmet Supermarket Maris 23598 14210 5633 3755 t 5495 Mitchell Canyon Road USA CA Los Angeles Store 7 Supermarket White 30797 20141 6393 4262 f 1077 Wharf Drive USA CA San Francisco Store 14 Small Grocery Strehlo 21215 13305 4746 3164 t 4365 Indigo Ct USA OR Portland Store 11 Supermarket Erickson 30584 21938 5188 3458 f 5371 Holland Circle
          Jacques Nadeau made changes -
          Fix Version/s 0.5.0 [ 12324880 ]
          Sudheesh Katkam made changes -
          Fix Version/s 0.6.0 [ 12327472 ]
          Fix Version/s 0.5.0 [ 12324880 ]
          Parth Chandra made changes -
          Fix Version/s 0.8.0 [ 12328812 ]
          Fix Version/s 0.6.0 [ 12327472 ]
          Jacques Nadeau made changes -
          Assignee Jinfeng Ni [ jni ] Daniel Barclay [ dbarclay ]
          Daniel Barclay (Drill) made changes -
          Status Open [ 1 ] Patch Available [ 10002 ]
          Fix Version/s 0.8.0 [ 12328812 ]
          Daniel Barclay (Drill) made changes -
          Status Patch Available [ 10002 ] Open [ 1 ]
          Daniel Barclay (Drill) made changes -
          Status Open [ 1 ] Patch Available [ 10002 ]
          Hide
          Daniel Barclay (Drill) added a comment -

          Initial draft of patch. (See comments with "TODO" and "REVIEW".)

          Show
          Daniel Barclay (Drill) added a comment - Initial draft of patch. (See comments with "TODO" and "REVIEW".)
          Daniel Barclay (Drill) made changes -
          Attachment DRILL-1062.1.patch.txt [ 12680002 ]
          Daniel Barclay (Drill) made changes -
          Status Patch Available [ 10002 ] Open [ 1 ]
          Daniel Barclay (Drill) made changes -
          Attachment DRILL-1062.1.patch.txt [ 12680002 ]
          Hide
          Daniel Barclay (Drill) added a comment -

          Initial version of patch for review.

          Show
          Daniel Barclay (Drill) added a comment - Initial version of patch for review.
          Daniel Barclay (Drill) made changes -
          Attachment DRILL-1062.1.patch.txt [ 12680018 ]
          Daniel Barclay (Drill) made changes -
          Status Open [ 1 ] Patch Available [ 10002 ]
          Daniel Barclay (Drill) made changes -
          Attachment DRILL-1062.1.patch.txt [ 12680020 ]
          Daniel Barclay (Drill) made changes -
          Attachment DRILL-1062.1.patch.txt [ 12680020 ]
          Daniel Barclay (Drill) made changes -
          Status Patch Available [ 10002 ] Open [ 1 ]
          USE OTHER Daniel Barclay made changes -
          Assignee Daniel Barclay (x) [ dbarclay ] Daniel Barclay [ dsbos ]
          Hide
          Mehant Baid added a comment -

          Marking this as 'patch available' to be able to distinguish from JIRA's that have error logs or plan as an attachment.

          Show
          Mehant Baid added a comment - Marking this as 'patch available' to be able to distinguish from JIRA's that have error logs or plan as an attachment.
          Mehant Baid made changes -
          Status Open [ 1 ] Patch Available [ 10002 ]
          Daniel Barclay (Drill) made changes -
          Attachment DRILL-1062.2.patch [ 12681610 ]
          Daniel Barclay (Drill) made changes -
          Attachment DRILL-1062.1.patch.txt [ 12680018 ]
          Jacques Nadeau made changes -
          Assignee Daniel Barclay (Drill/MapR) [ dsbos ] Jinfeng Ni [ jni ]
          Fix Version/s 0.7.0 [ 12327473 ]
          Hide
          Jacques Nadeau added a comment -

          Jinfeng, please review.

          Show
          Jacques Nadeau added a comment - Jinfeng, please review.
          Jacques Nadeau made changes -
          Assignee Jinfeng Ni [ jni ] Steven Phillips [ sphillips ]
          Hide
          Jacques Nadeau added a comment -

          Steven, please review in Jinfeng's absence.

          Show
          Jacques Nadeau added a comment - Steven, please review in Jinfeng's absence.
          Jacques Nadeau made changes -
          Fix Version/s 0.8.0 [ 12328812 ]
          Fix Version/s 0.7.0 [ 12327473 ]
          Jacques Nadeau made changes -
          Assignee Steven Phillips [ sphillips ] Jinfeng Ni [ jni ]
          Hide
          Jacques Nadeau added a comment -

          Jinfeng, can you review.

          Show
          Jacques Nadeau added a comment - Jinfeng, can you review.
          Jinfeng Ni made changes -
          Assignee Jinfeng Ni [ jni ] Daniel Barclay (Drill/MapR) [ dsbos ]
          Hide
          Jinfeng Ni added a comment -

          Can you please rebase your patch on today's master branch, and re-submit the patch ? I tried to apply the patch to test some queries, but could not apply the patch to master branch, due to merging conflicts.

          Show
          Jinfeng Ni added a comment - Can you please rebase your patch on today's master branch, and re-submit the patch ? I tried to apply the patch to test some queries, but could not apply the patch to master branch, due to merging conflicts.
          Daniel Barclay (Drill) made changes -
          Attachment DRILL-1062.3.patch.txt [ 12697168 ]
          Hide
          Daniel Barclay (Drill) added a comment -

          Rebased to today's master.

          Show
          Daniel Barclay (Drill) added a comment - Rebased to today's master.
          Daniel Barclay (Drill) made changes -
          Attachment DRILL-1062.4.patch.txt [ 12699393 ]
          Daniel Barclay (Drill) made changes -
          Status Patch Available [ 10002 ] Open [ 1 ]
          Daniel Barclay (Drill) made changes -
          Attachment DRILL-1062.4.patch.txt [ 12699419 ]
          Daniel Barclay (Drill) made changes -
          Attachment DRILL-1062.4.patch.txt [ 12699393 ]
          Jacques Nadeau made changes -
          Component/s Functions - Drill [ 12322695 ]
          Hide
          Parth Chandra added a comment -

          Resolved in commit 5efc7e6

          Show
          Parth Chandra added a comment - Resolved in commit 5efc7e6
          Parth Chandra made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Daniel Barclay (Drill) made changes -
          Link This issue incorporates DRILL-1763 [ DRILL-1763 ]
          Tony Stevenson made changes -
          Workflow no-reopen-closed, patch-avail, testing [ 12871587 ] Drill workflow [ 12934820 ]
          Hide
          Chun Chang added a comment -

          verified
          #Generated by Git-Commit-Id-Plugin
          #Wed Apr 29 14:39:22 EDT 2015
          git.commit.id.abbrev=f5b0f49

          There is an existing test case Advanced/Passing/mondrian/query117.q covers this.

          Show
          Chun Chang added a comment - verified #Generated by Git-Commit-Id-Plugin #Wed Apr 29 14:39:22 EDT 2015 git.commit.id.abbrev=f5b0f49 There is an existing test case Advanced/Passing/mondrian/query117.q covers this.
          Chun Chang made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Assignee Daniel Barclay (Drill) [ dsbos ] Chun Chang [ cchang@maprtech.com ]
          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open Patch Available Patch Available
          140d 3h 2m 4 Mehant Baid 12/Nov/14 02:14
          Patch Available Patch Available Open Open
          98d 4h 47m 4 Daniel Barclay (Drill) 18/Feb/15 04:58
          Open Open Resolved Resolved
          6d 1h 39m 1 Parth Chandra 24/Feb/15 06:37
          Resolved Resolved Closed Closed
          64d 15h 36m 1 Chun Chang 29/Apr/15 23:14

            People

            • Assignee:
              Chun Chang
              Reporter:
              Jinfeng Ni
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development