[IMPALA-501] impala order by uses nulls first by default, should use nulls last

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: Impala 1.1
• Fix Version/s: Impala 1.2
• Labels: wrongresults
• Assignee: Nong Li
• Reporter: Greg Rahn
• Created: 31/Jul/13 07:32
• Updated: 10/Oct/13 23:44
• Resolved: 23/Sep/13 00:24

Description

Impala

impala> select version();
Query: select version()
Query finished, fetching results ...
+---------------------------------------------------------------------------------------+
| _c0                                                                                   |
+---------------------------------------------------------------------------------------+
| impalad version 1.2-INTERNAL RELEASE (build 09d353be0f9b28ab40da2a302d33078f1e40b407) |
| Built on Sun, 21 Jul 2013 13:58:44 PST                                                |
+---------------------------------------------------------------------------------------+
Returned 1 row(s) in 0.22s

impala> select s_store_name, count(*) as total from store group by 1 order by 1 limit 100;
Query: select s_store_name, count(*) as total from store group by 1 order by 1 limit 100
Query finished, fetching results ...
+--------------+-------+
| s_store_name | total |
+--------------+-------+
|              | 8     | << nulls listed first
| able         | 162   |
| anti         | 99    |
| ation        | 100   |
| bar          | 166   |
| cally        | 161   |
| eing         | 158   |
| ese          | 165   |
| n st         | 113   |
| ought        | 103   |
| pri          | 115   |
+--------------+-------+
Returned 11 row(s) in 0.92s

Postgres

tpcds=# select s_store_name, count(*) from store group by 1 order by 1;
 s_store_name | count 
--------------+-------
 able         |   162
 anti         |    99
 ation        |   100
 bar          |   166
 cally        |   161
 eing         |   158
 ese          |   165
 n st         |   113
 ought        |   103
 pri          |   115
              |     8  << nulls listed last
(11 rows)

tpcds=# select s_store_name, count(*) from store group by 1 order by 1 nulls first;
 s_store_name | count 
--------------+-------
              |     8  << nulls first when default is changed
 able         |   162
 anti         |    99
 ation        |   100
 bar          |   166
 cally        |   161
 eing         |   158
 ese          |   165
 n st         |   113
 ought        |   103
 pri          |   115
(11 rows)