[ https://issues.apache.org/jira/browse/CALCITE-4754?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Javier Rivas Rodriguez updated CALCITE-4754: -------------------------------------------- Labels: pull-request-available (was: ) > 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)