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

Reply via email to