[
https://issues.apache.org/jira/browse/CALCITE-4754?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17409082#comment-17409082
]
Javier Rivas Rodriguez commented on CALCITE-4754:
-------------------------------------------------
Thanks for the feedback [~julianhyde]. I'll work on splitting up the commits.
Just to give you more context on "supportsIsTrueInsideCaseWhen", at the moment
I'm working exclusively with Redshift, and after dealing with Redshift not
being able to handle FILTER (and removing the bit of code that was preventing
the PIVOT aggregation from being genereatied), the SQL produced by Calcite was
something like
{code:java}
SELECT
field3,
SUM(CASE WHEN field2 = val1 IS TRUE THEN field1 ELSE NULL END),
SUM(CASE WHEN field2 = val2 IS TRUE THEN field1 ELSE NULL END)
FROM my_table
GROUP BY field3
{code}
And that isn't valid SQL that Redshift can execute. That's why I added both
test cases.
My concern of opening a PR only for this fix is that after the first PR (only
including Redshift not being able to handle FILTER), we would be in an
intermediate state where PIVOT queries would still not work in Redshift. That
is why I thought of having one that after merging it, PIVOT queries could run
successfully.
Also while debugging, I noticed that when the Redshift dialect is created, the
databaseProduct field is set to Unknown, as opposed to the DEFAULT instance
inside the RedshiftDialect class where it is actually populated, that is the
reason why I have also included it for completion and symmetry.
> 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)
