[DRILL-2190] Failure to order by function if DISTINCT clause is present

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.8.0
• Fix Version/s: 1.2.0
• Component/s: Query Planning & Optimization
• Assignee: Sean Hsuan-Yi Chu
• Reporter: Victoria Markman
• Created: 09/Feb/15 17:48
• Updated: 11/Sep/15 16:09
• Resolved: 11/Sep/15 15:28

Description

0: jdbc:drill:schema=dfs> select * from t1;
+------------+------------+------------+
|     a1     |     b1     |     c1     |
+------------+------------+------------+
| 1          | aaaaa      | 2015-01-01 |
| 2          | bbbbb      | 2015-01-02 |
| 3          | ccccc      | 2015-01-03 |
| 4          | null       | 2015-01-04 |
| 5          | eeeee      | 2015-01-05 |
| 6          | fffff      | 2015-01-06 |
| 7          | ggggg      | 2015-01-07 |
| null       | hhhhh      | 2015-01-08 |
| 9          | iiiii      | null       |
| 10         | jjjjj      | 2015-01-10 |
+------------+------------+------------+
10 rows selected (0.092 seconds)

0: jdbc:drill:schema=dfs> select distinct count(distinct a1) from t1 group by b1 order by 1;
Query failed: SqlValidatorException: Expression 'COUNT(`t1`.`a1`)' is not in the select clause
Error: exception while executing query: Failure while executing query. (state=,code=0)

Replaced ordinal with function: fails

0: jdbc:drill:schema=dfs> select distinct count(distinct a1) from t1 group by b1 order by count(distinct a1);
Query failed: SqlValidatorException: Expression 'COUNT(DISTINCT `a1`)' is not in the select clause
Error: exception while executing query: Failure while executing query. (state=,code=0)

Different aggregate function without DISTINCT clause: fails

0: jdbc:drill:schema=dfs> select  distinct sum(a1) from t1 group by b1 order by 1;
Query failed: SqlValidatorException: Expression 'SUM(`t1`.`a1`)' is not in the select clause
Error: exception while executing query: Failure while executing query. (state=,code=0)

Added alias to the function and order by alias: fails

0: jdbc:drill:schema=dfs> select  distinct sum(a1) as x from t1 group by b1 order by x;
Query failed: SqlValidatorException: Expression 'SUM(`t1`.`a1`)' is not in the select clause
Error: exception while executing query: Failure while executing query. (state=,code=0)