Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-2028

select trunc(dt) as dt ... group by 1 causes AnalysisException: select list expression not produced by aggregation output

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: Impala 2.2, Impala 2.0.3
    • Fix Version/s: Impala 2.3.0
    • Component/s: None
    • Environment:
      Server version: impalad version 2.2.0-cdh5 RELEASE (build 2ffd73a4255cefd521362ffe1cfb37463f67f75c)

      Description

      Running:

      select trunc(dt, 'DD') as dt, count( *) from test1 group by 1;

      ...where the query creates a column with the same name as in the original table, then groups by the created column with 'group by 1' triggers AnalysisException.

      Changing 'as dt' to 'as dt1' fixes the problem. This doesn't seem to happen with simple arithmetic ('id+1 as id').

      Full testcase follows:

      --drop table test1;
      create table test1 (dt timestamp);
      insert into test1 (dt) values ('2015-05-01');
      insert into test1 (dt) values ('2015-05-02');
      select * from test1;
      
      select trunc(dt, 'DD') as dt, count( *) from test1 group by 1;
      

      The complete text of the error message:

      ERROR: AnalysisException: select list expression not produced by aggregation output (missing from GROUP BY clause?): trunc(dt, 'DD')

      MySQL seems to handle a similar case fine: http://sqlfiddle.com/#!9/5b35d/1/0

      There were no obvious duplicates. I can't test with a recent version easily, sorry about that.

        Attachments

          Activity

            People

            • Assignee:
              tarmstrong Tim Armstrong
              Reporter:
              nickolay_impala_96f8 Nickolay Ponomarev
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: