Details
-
Bug
-
Status: Resolved
-
Blocker
-
Resolution: Fixed
-
Impala 3.0
-
ghx-label-9
Description
SQL defines the idea of ordinals, which is, apparently, an old way to specify columns in the ORDER BY and GROUP BY clauses:
SELECT int_col, id FROM functional.alltypestiny GROUP BY 1 ORDER BY 2
The use of an ordinal is semi-ambiguous (is it an ordinal or a literal), but DBs (including Impala) usually interpret a single integer as an ordinal, but interpret any expression as a constant. (For example, 1 is an ordinal, but 2 - 1 is the constant value 1.)
The use of ordinals works because ORDER BY and GROUP BY are lists: it is clear when an integer stands alone as an ordinal.
The HAVING (and WHERE) clauses are expressions. For this reason, DB's do not support ordinals in these clauses. For example, what is the meaning below:
SELECT int_col, id FROM functional.alltypestiny WHERE 1 = 2
Does this mean that the first column equals 2? That the second column equals 1? The first and second columns are equal? That the constant 1 equals the constant 2?
To avoid such ambiguity, neither the SQL standard nor any implementations support ordinals in the HAVING (or WHERE) clauses.
Yet, Impala attempts to do so:
havingPred_ = substituteOrdinalOrAlias(havingClause_, "HAVING", analyzer_);
This ticket proposes to remove this code to make it clear that the HAVING clause cannot contain an ordinal.
References:
- Redshift HAVING clause
- Impala docs are silent on this question.
- SQL Standard BNF for HAVING