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

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

Roman Churganov created CALCITE-5100:
----------------------------------------

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


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]  }}

 



--
This message was sent by Atlassian Jira
(v8.20.1#820001)