[jira] [Updated] (CALCITE-5100) Incorrect syntax in sub-query, unsupported LISTAGG

["Roman Churganov (Jira)" <ji...@apache.org>]

     [ 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)