drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jiang Wu <jiang...@mulesoft.com.INVALID>
Subject Re: Column alias in group by behavior in 1.16
Date Tue, 20 Aug 2019 17:38:19 GMT
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 <amansinha@gmail.com> 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 <
> arina.yelchiyeva@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.wu@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
> >
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message