[IMPALA-2028] select trunc(dt) as dt ... group by 1 causes AnalysisException: select list expression not produced by aggregation output

• 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
• Labels: planner ramp-up sql-language
• Environment: Server version: impalad version 2.2.0-cdh5 RELEASE (build 2ffd73a4255cefd521362ffe1cfb37463f67f75c)
• Target Version: Product Backlog
• Assignee: Tim Armstrong
• Reporter: Nickolay Ponomarev
• Created: 25/May/15 05:12
• Updated: 04/Jan/17 23:58
• Resolved: 05/Aug/15 20:47

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.