Yes. MySQL 8.0.7 behaves the same as PostgreSQL 9.6: Query 1 --> returns 2 rows Query 2 --> returns 1 row
-- Jiang On Tue, Aug 20, 2019 at 8:09 AM Aman Sinha <[email protected]> wrote: > 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 < > [email protected]> > 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 <[email protected]> > > 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 > > > > >
