drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chun Chang (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-1062) DRILL does not handle NULLS FIRST/LAST correctly in ORDER BY clause
Date Wed, 23 Jul 2014 19:03:38 GMT

    [ https://issues.apache.org/jira/browse/DRILL-1062?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14072154#comment-14072154
] 

Chun Chang commented on DRILL-1062:
-----------------------------------

#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


> DRILL does not handle NULLS FIRST/LAST correctly in ORDER BY clause
> -------------------------------------------------------------------
>
>                 Key: DRILL-1062
>                 URL: https://issues.apache.org/jira/browse/DRILL-1062
>             Project: Apache Drill
>          Issue Type: Bug
>            Reporter: Jinfeng Ni
>            Assignee: Jinfeng Ni
>
> 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)



--
This message was sent by Atlassian JIRA
(v6.2#6252)

Mime
View raw message