Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
Description
ReloSqlConverter should generate double parentheses when the input to Unnest is a query statement.
For unit tests in RelToSqlConverterTest:
@Test void testUnnest() { final String sql = "select * from UNNEST(array [1, 2, 3])"; final String expected = "SELECT *\n" + "FROM UNNEST (SELECT ARRAY[1, 2, 3]\n" + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")) AS \"t0\" (\"col_0\")"; sql(sql).ok(expected).withPostgresql().ok(expected); }
The generated SQL is valid in CALCITE but not in PG.
In PostgreSQL:
CREATE TABLE IF NOT EXISTS public."unnestTable" ( array_int integer[], "valueInt" integer ); SELECT "valueInt","t10"."col_0" FROM public."unnestTable",unnest(( SELECT "array_int" FROM (VALUES (0)) AS "t" ("ZERO"))) AS "t10" ("col_0"); select * from unnest((SELECT "array_int" from public."unnestTable")) as "t" ("ZERO"); select * from unnest((SELECT ARRAY_AGG("valueInt") from public."unnestTable")) as "t" ("ZERO"); select * from unnest((SELECT ARRAY_AGG("valueInt") from public."unnestTable")) WITH ORDINALITY AS "t" ("ZERO","ZERO1");
The PG always needs double parentheses.
The Calcite supports double parentheses too.
Attachments
Issue Links
- links to