[jira] [Commented] (CALCITE-4702) Error when executing query with GROUP BY constant via JDBC adapter

["Stamatis Zampetakis (Jira)" <ji...@apache.org>]

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

Stamatis Zampetakis commented on CALCITE-4702:
----------------------------------------------

Based on the JOOQ blog [post|https://blog.jooq.org/2018/05/25/how-to-group-by-nothing-in-sql/] and the tests performed so far when the GROUP BY contains a constant expression we can split the rewriting into two categories:

+Simple+
The constant expression in the GROUP BY can be replaced "locally" with another constant expression. For instance:
GROUP BY TRUE -> GROUP BY (SELECT TRUE) (Redshift)

+Complex+
The constant expression in the GROUP BY clause requires adding a dummy table in the FROM clause. 

For the "Simple" case performing the rewrite in the AST (RelToSqlConverter) seems reasonable but I am not sure how easy it is to cover the complex case at this level (i.e., RelToSql). It feels slightly more natural to handle the "Complex" case (if necessary) using an optimizer rule. 

Before freezeing the APIs it might make sense to agree on how (and if) we want to handle the "Complex" rewriting. I am not saying to necessarily implement the "Complex" rewritting as part of this issue  but having a high level idea on how to move forward will save us from breaking the APIs in the future. 

Concretely, I was thinking adding the following methods in the API of {{SqlDialect}} would be sufficient to cover all the cases encountered so far.
{code:java}
public boolean supportsGroupByWithConstant(RelDataType);
public boolean supportsGroupByWithSubquery();
{code}

Any thoughts [~soumyakanti.das], [~julianhyde]?

> Error when executing query with GROUP BY constant via JDBC adapter
> ------------------------------------------------------------------
>
>                 Key: CALCITE-4702
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4702
>             Project: Calcite
>          Issue Type: Bug
>          Components: core, jdbc-adapter
>    Affects Versions: 1.27.0
>            Reporter: Stamatis Zampetakis
>            Assignee: Soumyakanti Das
>            Priority: Major
>
> The following functionally equivalent SQL queries are accepted by Calcite and produce a valid plan 
> {noformat}
> select avg(salary) from employee group by true
> select avg(salary) from employee group by 'a'
> {noformat}
> but they may fail if they are executed via the JDBC adapter since not all DBMS allow grouping by constants expressions. Moreover, what works for one may not work for the other. 
> +Examples+
> The {{GROUP BY TRUE}} query works in Postgres, and MySQL but fails in Redshift with the following exception:
> {noformat}
> com.amazon.redshift.util.RedshiftException: ERROR: non-integer constant in GROUP BY
> {noformat}
> The {{GROUP BY 'a'}} query works in MySQL but fails in Postgres with the following exception:
> {noformat}
> ERROR:  non-integer constant in GROUP BY
> {noformat}
> +Edit:+
> The {{GROUP BY}} constant is similar to {{GROUP BY ()}} "nothing" but as shown in the discussion below they are not equivalent. There is a nice [blog post|https://blog.jooq.org/2018/05/25/how-to-group-by-nothing-in-sql/] listing some limitations of various DBMS when it comes to {{GROUP BY ()}}. 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)