[
https://issues.apache.org/jira/browse/CALCITE-4702?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17414812#comment-17414812
]
Stamatis Zampetakis commented on CALCITE-4702:
----------------------------------------------
Indeed the previous queries are invalid, I was a bit careless sorry about that.
However the following queries are valid but all fail with errors in Informix:
{noformat}
0: jdbc:informix-sqli://localhost:9088/sysuse> select count(*) from person
group by (select true);
Error: A syntax error has occurred. (state=42000,code=-201)
0: jdbc:informix-sqli://localhost:9088/sysuse> select count(*) from person
group by (select 1);
Error: A syntax error has occurred. (state=42000,code=-201)
0: jdbc:informix-sqli://localhost:9088/sysuse> select count(*) from person
group by ();
Error: A syntax error has occurred. (state=42000,code=-201)
0: jdbc:informix-sqli://localhost:9088/sysuse> select count(*) from person
group by 'a';
Error: A syntax error has occurred. (state=42000,code=-201)
0: jdbc:informix-sqli://localhost:9088/sysuse> select count(*) from person
group by true;
Error: Column (true) not found in any table in the query (or SLV is undefined).
(state=IX000,code=-217)
{noformat}
[~julianhyde] we are on the same page, I am mainly trying to handle {{GROUP BY
CONSTANT}}. I was thinking that by introducing the following methods in
{{SqlDialect}} we should be able to handle easily the "simple" transformation
and also keep the road open for handling the "complex" transformation in the
future.
{code:java}
public boolean supportsGroupByWithConstant(RelDataType);
public boolean supportsGroupByWithSubquery();
{code}
The first method could also be simplified to
{code:java}
public boolean supportsGroupByWithConstant()
{code}
if we can ensure that constant is always of BOOLEAN type (i.e., TRUE, FALSE)
but may not worth the effort.
The second method may also make sense in other use-cases since there are
databases which support (scalar) subqueries in the {{GROUP BY}} clause and
others that don't.
> 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
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> 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)
