drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jiang Wu <jiang...@mulesoft.com.INVALID>
Subject Column alias in group by behavior in 1.16
Date Mon, 19 Aug 2019 23:54:20 GMT
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