[jira] [Created] (CALCITE-4754) Invalid Redshift SQL when executing a PIVOT query

["Javier Rivas Rodriguez (Jira)" <ji...@apache.org>]

Javier Rivas Rodriguez created CALCITE-4754:
-----------------------------------------------

             Summary: 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
             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)