Need help regarding ordinal expressions in GROUP BY clause

[Kushal Chavada <ku...@intellicus.com>]

Hi,


I have written simple query planner class in java which takes string SQL as an input & generates logical plan using Calcite.

Input SQLs contain queries having ordinal expressions in GROUP BY clause. e.g.

        "SELECT x, sum(y) FROM t GROUP BY 1"
On validation, I get this validation exception:
        SEVERE: org.apache.calcite.runtime.CalciteContextException: .... Expression 't.x' is not being grouped
When I replace "GROUP BY 1" with "GROUP BY x", it works fine. Whereas ordinal expressions works fine with ORDER BY clause.
So, am I missing anything like configuration properties, etc.
I am using ORACLE as lexical policy.


With Regards,
Kushal


________________________________






NOTE: This message may contain information that is confidential, proprietary, privileged or otherwise protected by law. The message is intended solely for the named addressee. If received in error, please destroy and notify the sender. Any use of this email is prohibited when received in error. Impetus does not represent, warrant and/or guarantee, that the integrity of this communication has been maintained nor that the communication is free of errors, virus, interception or interference.

Re: Need help regarding ordinal expressions in GROUP BY clause

[Julian Hyde <jh...@apache.org>]

I’ll refer you to the “Column Ordinal Support in GROUP BY” thread, and Gian’s excellent answer[1].

Julian

[1] https://mail-archives.apache.org/mod_mbox/calcite-dev/201612.mbox/%3CCACZNdYAupYPCLLCV8aDWydc8p3gch4fgt%2B8-VvCLrZQxp1BpXg%40mail.gmail.com%3E <https://mail-archives.apache.org/mod_mbox/calcite-dev/201612.mbox/%3CCACZNdYAupYPCLLCV8aDWydc8p3gch4fgt+8-VvCLrZQxp1BpXg@mail.gmail.com%3E>

> On Dec 13, 2016, at 2:15 AM, Kushal Chavada <ku...@intellicus.com> wrote:
> 
> Hi,
> 
> 
> I have written simple query planner class in java which takes string SQL as an input & generates logical plan using Calcite.
> 
> Input SQLs contain queries having ordinal expressions in GROUP BY clause. e.g.
> 
>        "SELECT x, sum(y) FROM t GROUP BY 1"
> On validation, I get this validation exception:
>        SEVERE: org.apache.calcite.runtime.CalciteContextException: .... Expression 't.x' is not being grouped
> When I replace "GROUP BY 1" with "GROUP BY x", it works fine. Whereas ordinal expressions works fine with ORDER BY clause.
> So, am I missing anything like configuration properties, etc.
> I am using ORACLE as lexical policy.
> 
> 
> With Regards,
> Kushal
> 
> 
> ________________________________
> 
> 
> 
> 
> 
> 
> NOTE: This message may contain information that is confidential, proprietary, privileged or otherwise protected by law. The message is intended solely for the named addressee. If received in error, please destroy and notify the sender. Any use of this email is prohibited when received in error. Impetus does not represent, warrant and/or guarantee, that the integrity of this communication has been maintained nor that the communication is free of errors, virus, interception or interference.