[jira] [Comment Edited] (CALCITE-1710) GroupBy columns support arithmetic expression

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1710?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15933031#comment-15933031 ] 

Julian Hyde edited comment on CALCITE-1710 at 3/20/17 4:51 PM:
---------------------------------------------------------------

I was under the impression that Calcite supports the same as Oracle. Probably similar to PostgreSQL. I wouldn't heed what MySQL does, because it has very loose rules about GROUP BY.

We do allow {code}select x + y from t group by x + y{code} and even {code}select x + y + 1 from t group by x + y{code} because we match expressions structurally based on their parse tree. We do not allow {code}select 1 + x + y from t group by x + y{code} because "+" is left-associative, and the expression {{x + y}} does not occur in the tree {{(1 + x) + y}}. Calcite's behavior is identical to Oracle in that regard.

Regarding your third example. Calcite uses {{MOD(x, y)}} rather than {{x % y}} but if you change your example to {{MOD}} it should work.


was (Author: julianhyde):
I was under the impression that Calcite supports the same as Oracle. Probably similar to PostgreSQL. I wouldn't heed what MySQL does, because it has very loose rules about GROUP BY.

We do allow {code}select x + y from t group by x + y{code} and even {code}select x + y + 1 from t group by x + y{code} because we match expressions structurally based on their parse tree. We do not allow {code}select 1 + x + y from t group by x + y}} because "+" is left-associative, and the expression {{x + y}} does not occur in the tree {{(1 + x) + y}}. Calcite's behavior is identical to Oracle in that regard.

Regarding your third example. Calcite uses {{MOD(x, y)}} rather than {{x % y}} but if you change your example to {{MOD}} it should work.

> GroupBy columns support arithmetic expression
> ---------------------------------------------
>
>                 Key: CALCITE-1710
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1710
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>            Reporter: lincoln.lee
>            Assignee: Julian Hyde
>            Priority: Minor
>
> Currently only original column reference(s) and column(s) with UDF valid in group by clause  but arithmetic expression(s) not allowed,  while most RDBMS support
> like thus:
> {code}
> SELECT 
>     ColumnA + ColumnB 
> FROM T 
> GROUP BY ColumnA + ColumnB;
>   
> SELECT 
>     ColumnA + ColumnB + constant
> FROM T 
> GROUP BY ColumnA, ColumnB; 
> SELECT 
>     ColumnA % 3
> FROM T 
> GROUP BY ColumnA %3;
> {code}
> we can treat these arithmetic operators as builtin UDFs and support these kind of query, it'll be a useful feature.
> What do you think?



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)