drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Arina Yelchiyeva <arina.yelchiy...@gmail.com>
Subject Re: Column alias in group by behavior in 1.16
Date Tue, 20 Aug 2019 13:43:28 GMT
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
View raw message