Stamatis Zampetakis created CALCITE-4675:
--------------------------------------------
Summary: 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
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)
