[ https://issues.apache.org/jira/browse/CALCITE-5100?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Roman Churganov updated CALCITE-5100: ------------------------------------- Description: Execute query like: {code:sql} select C11, LISTAGG( distinct C12, ', ' ) from FOO group by C11 {code} {{In case of JDBC driver doesn't support LISTAGG function, JDBC adapter creates subquery: }} {code:sql} SELECT "C11", "C12", ', ' AS "$f2" FROM "FOO" GROUP BY "C11", "C12", ', ' {code} which cannot by executed on PG and gives error : {noformat} [42601] ERROR: non-integer constant in GROUP BY {noformat} was: Execute query like: {code:sql} SELECT f.id FROM sch1.foo as f JOIN sch1.foo br ON br.id = f.id JOIN sch1.baz bz ON bz.id = br.id WHERE f.id = '0' {code} {{Calcite prepares SQL using JDBC adapter like: }} {code:sql} SELECT "t1"."ID" FROM ( SELECT "ID" FROM "BAZ" ) AS "t" INNER JOIN ( ( SELECT "ID" FROM "FOO" WHERE "ID" = '0') AS "t1" INNER JOIN (SELECT "ID" FROM "FOO") AS "t2" ON "t1"."ID" = "t2"."ID" ) ON "t"."ID" = "t2"."ID" {code} though most of the databases can execute it, but Caclite itself cannot parse and gives error like: {noformat} Caused by: org.apache.calcite.sql.parser.impl.ParseException: Encountered "AS" at line 3, column 88. Was expecting one of: "EXCEPT" ... "FETCH" ... "INTERSECT" ... "LIMIT" ... "OFFSET" ... "ORDER" ... "MINUS" ... "UNION" ... ")" ... "." ... "NOT" ... "IN" ... {noformat} {{see example [https://www.db-fiddle.com/f/oCr3VKkT2QmKgWro54Wzfc/4] }} > Incorrect syntax in sub-query, unsupported LISTAGG > -------------------------------------------------- > > Key: CALCITE-5100 > URL: https://issues.apache.org/jira/browse/CALCITE-5100 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.29.0 > Reporter: Roman Churganov > Priority: Blocker > > Execute query like: > {code:sql} > select C11, LISTAGG( distinct C12, ', ' ) > from FOO > group by C11 > {code} > {{In case of JDBC driver doesn't support LISTAGG function, JDBC adapter > creates subquery: }} > {code:sql} > SELECT "C11", "C12", ', ' AS "$f2" > FROM "FOO" > GROUP BY "C11", "C12", ', ' > {code} > which cannot by executed on PG and gives error : > {noformat} > [42601] ERROR: non-integer constant in GROUP BY > {noformat} > -- This message was sent by Atlassian Jira (v8.20.1#820001)