[
https://issues.apache.org/jira/browse/CALCITE-4754?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17406070#comment-17406070
]
Julian Hyde commented on CALCITE-4754:
--------------------------------------
The PR is a good start, and thanks for the contribution.
I would be happier if we broke this case into problems and solutions, and if
there were separate commits for each. For example, the main problem is that the
JDBC adapter doesn't know that Redshift can't handle FILTER. (Sure, it shows up
if you send Calcite a PIVOT query, but I bet that it can be reproduced
independently.)
So, let's have a commit that just deals with FILTER. Let's have test cases for
now just Redshift but some other dialects.
Now, onto "supportsIsTrueInsideCaseWhen". You imply that there is a dialect
that supports IS TRUE but only when it is not inside CASE. Or that there is a
dialect that supports IS FALSE but does not support IS TRUE. I am skeptical.
So, can you log a bug that describes the full extent of the missing
functionality, and fix it, with tests.
I suspect that when generating SQL, if we are in a context that treats unknown
as false (such as WHERE, ON, HAVING, WHEN) we always (in all dialects) want to
simplify 'x IS TRUE' to 'x', and 'x IS NOT TRUE' to 'NOT x'.
Why is Redshift the only dialect that requires withDatabaseProduct to be
called? There is either a bigger bug (applies to other dialects) or no bug at
all.
> Invalid Redshift SQL when executing a PIVOT query
> -------------------------------------------------
>
> Key: CALCITE-4754
> URL: https://issues.apache.org/jira/browse/CALCITE-4754
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.27.0
> Reporter: Javier Rivas Rodriguez
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.28.0
>
>
> Given a Calcite connection to Redshift
> When running a query like the one below
> {code:java}
> SELECT *
> FROM
> (SELECT field1, field2, field3
> FROM my_table)
> PIVOT SUM(field1) FOR field2 IN (val1, val2){code}
> The SQL produced is something like
> {code:java}
> SELECT
> field3,
> SUM(field1) FILTER (WHERE field2 = val1),
> SUM(field1) FILTER (WHERE field2 = val2)
> FROM my_table
> GROUP BY field3{code}
> This SQL transformation is not accepted by Redshift
> The expected result would be something like
> {code:java}
> SELECT
> field3,
> SUM(CASE WHEN field2 = val1 THEN field1 ELSE NULL END),
> SUM(CASE WHEN field2 = val2 THEN field1 ELSE NULL END)
> FROM my_table
> GROUP BY field3
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
