[CALCITE-5042] Incorrect syntax in sub-query

• Type: Bug
• Status: Closed
• Priority: Blocker
• Resolution: Duplicate
• Affects Version/s: 1.29.0
• Fix Version/s: 1.32.0
• Component/s: core
• Assignee: Unassigned
• Reporter: Roman Churganov
• Created: 14/Mar/22 19:13
• Updated: 10/Sep/22 08:42
• Resolved: 14/Mar/22 19:37

Description

Execute query like: 

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'

Calcite prepares SQL using JDBC adapter like: 

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" 

though most of the databases can execute it, but Caclite itself cannot parse and gives error like:

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" ...

see example https://www.db-fiddle.com/f/oCr3VKkT2QmKgWro54Wzfc/4  

 

Issue Links

• duplicates: CALCITE-35 Support parenthesized sub-clause in JOIN