Hi folks, we are testing an upgrade to 1.16 from 1.14 and noticed a behavior change in 1.16 related to the support for allowing select column alias in group by clause. When there is an ambiguity to the alias name, Drill 1.16 is exhibiting a different behavior comparing to expectation.
Here is an example to illustrate: *-- Test query 1:* select *MOD(x,10) as x * from (select 14 *as x* UNION ALL select 4 *as x*) TableA *group by x; --- x refers to select alias <or> from column name?* --- returns 2 rows if x refers to from clause table column x --- returns 1 row if x refers to select column alias x *-- Test query 2:* select *MOD(x,10) as x * from (select 14 *as x* UNION ALL select 4 *as x*) TableA *group by MOD(x,10); ** --- x refers to select alias <or> from column name?* --- returns 1 row as x here should refer to the from clause table column x ================ *PostgreSQL 9.6* Test Query 1 --> 2 rows (==> x refers to from clause table column) Test Query 2 --> 1 row (==> x refers to from clause table column) *Drill 1.14 -- same as PostgreSQL 9.6* Test Query 1 --> 2 rows (==> x refers to from clause table column) Test Query 2 --> 1 row (==> x refers to from clause table column) *Drill 1.16 -- different results:* Test Query 1 --> 1 row (==> x refers to select column alias) Test Query 2 --> SQL Error: VALIDATION ERROR: At line 1, column 12: Expression 'TableA.x' is not being grouped (==> also implies that x refers to column alias) Given the logical order of processing for a SQL statement: from --> where --> group by --> ... --> select, ... it would seem that precedence order should be given to the from clause in case of ambiguity. Also to be consistent with test query 2, the alias in group by also refer to from clause before select clause. Love to hear more expert opinions here on this issue. -- Jiang
