The change in behavior occurred in Drill 1.15 when the group-by alias support was added [1]. Before this, we could not even group by an alias in the SELECT list. However, as Arina mentioned, the behavior is dependent on Calcite which is used by Drill. Does MySQL or other systems behave the same as Postgres for this scenario ?
[1] https://issues.apache.org/jira/browse/DRILL-1248 On Tue, Aug 20, 2019 at 6:43 AM Arina Yelchiyeva <arina.yelchiy...@gmail.com> wrote: > Resolutions of aliases and columns names is not done in Drill, on the > contrary, it done by Calcite. > There were some discussions around inconsistencies you mentioning but it's > up to the Calcite community to fix them. > > [1] https://issues.apache.org/jira/browse/CALCITE-2799 > > Kind regards, > Arina > > > On Aug 20, 2019, at 2:54 AM, Jiang Wu <jiang...@mulesoft.com.INVALID> > wrote: > > > > 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 > >
