[IMPALA-5191] Behavior of GROUP BY, HAVING, ORDER BY with column aliases should be more standard conforming

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: Impala 3.0
• Component/s: Frontend
• Labels: incompatibility ramp-up sql-language
• Epic Color: ghx-label-3
• Assignee: Zoltán Borók-Nagy
• Reporter: Adriano
• Created: 10/Apr/17 08:58
• Updated: 13/Nov/18 22:09
• Resolved: 26/Jan/18 12:56

Description

Allow Impala to accept "column alias" and "Column name" indistinctly in the group by clause (also if Column is made by a function).
We are confident that this can contribute to the growing of this Engine.

This is a sample of a statement that we would like to execute with success:

with w_test as
  (select '1' as one,
          2 as two,
          '3' as three)
select one as one,
       substring(cast(two as string), 1, 1) as two,
       three as three,
       count(1) as cnt
from w_test
group by one,
         substring(cast(two as string), 1, 1),
         three

ERROR: AnalysisException: select list expression not produced by aggregation output (missing from GROUP BY clause?): substring(CAST(two AS STRING), 1, 1)

It works writing the statements as:

workaround.1:

with w_test as
  (select '1' as one,
          2 as two,
          '3' as three)
select one,
       substring(cast(two as string), 1, 1),
       three,
       count(1)
from w_test
group by one,
         substring(cast(two as string), 1, 1),
         three

workaround.2:

with w_test as
  (select '1' as one,
          2 as two,
          '3' as three)
select one as one,
       substring(cast(two as string), 1, 1) as two,
       three as three,
       count(1) as cnt
from w_test
group by 1,
         2,
         3

workaround 3:

with w_test as
  (select '1' as one,
          2 as two,
          '3' as three)
select one as t_one,
       substring(cast(two as string), 1, 1) as t_two,
       three as t_three,
       count(1) as cnt
from w_test
group by t_one,
         t_two,
         t_three

Issue Links

• Blocked: IMPALA-6415 Impala 3 Doc: Document breaking change of alias and ordinal substitution
• breaks: IMPALA-6495 targeted-perf tests broken by column alias change
• is related to: IMPALA-7845 Clarify Impala's policy for use of column aliases
• is related to: IMPALA-6322 Group by expression fails when expression includes a CAST
• relates to: IMPALA-1423 Identifier in HAVING expr should fall back from SELECT item aliases to columns in FROM table refs