[CALCITE-4675] Error executing query with SUM and multiplication via JdbcAdapter

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.27.0
• Fix Version/s: 1.28.0
• Labels: pull-request-available
• Assignee: Stamatis Zampetakis
• Reporter: Stamatis Zampetakis
• Created: 01/Jul/21 15:28
• Updated: 20/Oct/21 05:24
• Resolved: 12/Jul/21 10:36

Description

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"

The query above using a custom ruleset and the standard JdbcRules generates the following SQL (e.g., RedShift dialect):

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"

and raises exceptions when executed over some DBMS which cannot handle STAR symbol (*) properly.

In RedShift, the query above will raise the following error:

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)

Issue Links

• links to: GitHub Pull Request #2456