[
https://issues.apache.org/jira/browse/CALCITE-4675?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17372889#comment-17372889
]
Stamatis Zampetakis commented on CALCITE-4675:
----------------------------------------------
The generated SQL query is valid from an SQL standard perspective and it runs
fine in many DBMS.
However, Redshift in many cases treats {{"t0"."*"}} and {{"t0".*}} as the same
thing, which is not. {{"*"}} is a delimited (quoted) identifier while {{*}} is
syntactic sugar for returning all columns.
The problem comes from the fact that {{sum}} function expects a single column
as an input and we are passing a record since "t0"."*" is wrongly expanded
leading to the exception above.
The official documentation of Redshift about [delimited
identifiers|https://docs.aws.amazon.com/redshift/latest/dg/r_names.html] does
not mention that the asterisk cannot be used so I would classify this mainly as
a Redshift bug.
Nevertheless, using the STAR as an alias can be confusing and as we have seen
here create problems for some DBMS so what we could do on our side is to avoid
using it.
The alias in this case is generated by
[PushProjector|https://github.com/apache/calcite/blob/c1bf421c0816909fe53d557eb5ee012f779bfa11/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L567],
which in this case is called by {{ProjectJoinTransposeRule}}.
It would probably be better to rely upon and generate unique names instead of
using unconventional aliases such as \*, +, and other symbols.
> Error executing query with SUM and multiplication via JdbcAdapter
> -----------------------------------------------------------------
>
> Key: CALCITE-4675
> URL: https://issues.apache.org/jira/browse/CALCITE-4675
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.27.0
> Reporter: Stamatis Zampetakis
> Assignee: Stamatis Zampetakis
> Priority: Major
> Fix For: 1.28.0
>
>
> {code:sql}
> SELECT s."customer_id", sum(s."store_sales" * s."store_cost")
> FROM "sales_fact_1997" AS s
> JOIN "customer" AS c ON s."customer_id" = c."customer_id"
> GROUP BY s."customer_id"
> {code}
> The query above using a custom ruleset and the standard JdbcRules generates
> the following SQL (e.g., RedShift dialect):
> {code:sql}
> SELECT "t"."customer_id", SUM("t"."*")
> FROM
> (SELECT "customer_id", "store_sales" * "store_cost" AS "*"
> FROM "foodmart"."sales_fact_1997") AS "t"
> INNER JOIN
> (SELECT "customer_id"
> FROM "foodmart"."customer") AS "t0" ON "t"."customer_id" =
> "t0"."customer_id"
> GROUP BY "t"."customer_id"
> {code}
> and raises exceptions when executed over some DBMS which cannot handle STAR
> symbol (\*) properly.
> In RedShift, the query above will raise the following error:
> {noformat}
> Caused by: com.amazon.redshift.util.RedshiftException: ERROR: function
> sum(record) does not exist
> Hint: No function matches the given name and argument types. You may need
> to add explicit type casts.
> at
> com.amazon.redshift.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2579)
> at
> com.amazon.redshift.core.v3.QueryExecutorImpl.processResultsOnThread(QueryExecutorImpl.java:2253)
> at
> com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1880)
> at
> com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1872)
> at
> com.amazon.redshift.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
> at
> com.amazon.redshift.jdbc.RedshiftStatementImpl.executeInternal(RedshiftStatementImpl.java:514)
> at
> com.amazon.redshift.jdbc.RedshiftStatementImpl.execute(RedshiftStatementImpl.java:435)
> at
> com.amazon.redshift.jdbc.RedshiftPreparedStatement.executeWithFlags(RedshiftPreparedStatement.java:200)
> at
> com.amazon.redshift.jdbc.RedshiftPreparedStatement.executeQuery(RedshiftPreparedStatement.java:115)
> {noformat}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
