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

Failure to order by function if DISTINCT clause is present

    XMLWordPrintableJSON

    Details

      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)
      

        Attachments

          Activity

            People

            • Assignee:
              seanhychu Sean Hsuan-Yi Chu
              Reporter:
              vicky Victoria Markman
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: