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

Can't partition by expression when columns are explicitly specified in the CTAS column list

    XMLWordPrintableJSON

Details

    Description

      Query below throws an error:

      create table test(x1, x2) partition by (x1) as 
      select    sum(a1),  b1 
      from       t1 
      group by  b1;
      
      0: jdbc:drill:schema=dfs> create table test(x1, x2) partition by (x1) as select sum(a1), b1 from t1 group by b1;
      Error: SYSTEM ERROR: IllegalArgumentException: partition col x1 could not be resolved in table's column lists!
      [Error Id: ab5624e8-e4dd-4752-95af-8bc2eef5d056 on atsqa4-133.qa.lab:31010] (state=,code=0)
      

      When column aliases are used, it works:

      0: jdbc:drill:schema=dfs> create table test partition by (x1) as select sum(a1) x1, b1 x2 from t1 group by b1;
      +-----------+----------------------------+
      | Fragment  | Number of records written  |
      +-----------+----------------------------+
      | 0_0       | 10                         |
      +-----------+----------------------------+
      1 row selected (0.904 seconds)
      0: jdbc:drill:schema=dfs> select * from test;
      +-------+--------+
      |  x1   |   x2   |
      +-------+--------+
      | null  | hhhhh  |
      | 2     | bbbbb  |
      | 10    | jjjjj  |
      | 1     | aaaaa  |
      | 3     | ccccc  |
      | 4     | null   |
      | 5     | eeeee  |
      | 7     | ggggg  |
      | 6     | fffff  |
      | 9     | iiiii  |
      +-------+--------+
      10 rows selected (0.161 seconds)
      0: jdbc:drill:schema=dfs> select * from test order by x1;
      +-------+--------+
      |  x1   |   x2   |
      +-------+--------+
      | 1     | aaaaa  |
      | 2     | bbbbb  |
      | 3     | ccccc  |
      | 4     | null   |
      | 5     | eeeee  |
      | 6     | fffff  |
      | 7     | ggggg  |
      | 9     | iiiii  |
      | 10    | jjjjj  |
      | null  | hhhhh  |
      +-------+--------+
      10 rows selected (0.299 seconds)
      

      Attachments

        Activity

          People

            jni Jinfeng Ni
            vicky Victoria Markman
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: