[CALCITE-2981] Syntax error with simple nested select query

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Duplicate
• Affects Version/s: 1.18.0
• Fix Version/s: 1.20.0
• Component/s: core
• Assignee: Danny Chen
• Reporter: Vamshi
• Created: 05/Apr/19 23:12
• Updated: 24/Jun/19 21:51
• Resolved: 10/Apr/19 19:27

Description

Below standard  sql-92 query reports  syntax error  in calcite:

create table test1(id integer, name integer);
create table test2(id integer, name integer);

select * from (

( select id,name from test1 ) test3

inner join

( select id,name from test2) test4 on test4.id = test3.id

);

Caused by: org.apache.calcite.parser.ParseException: Encountered "test3" at line 4, column 31.

Was expecting one of:
")" ...
"ORDER" ...
"LIMIT" ...
"OFFSET" ...
"FETCH" ...
"UNION" ...
"INTERSECT" ...
"EXCEPT" ...
"MINUS" ...
"." ...
"NOT" ...
"IN" ...
"<" ...
"<=" ...
">" ...
">=" ...
"=" ...
"<>" ...
"!=" ...
"BETWEEN" ...
"LIKE" ...
"SIMILAR" ...
"+" ...
"-" ...
"*" ...
"/" ...
"%" ...
"||" ...
"AND" ...
"OR" ...
"IS" ...
"MEMBER" ...
"SUBMULTISET" ...
"CONTAINS" ...
"OVERLAPS" ...
"EQUALS" ...
"PRECEDES" ...
"SUCCEEDS" ...
"IMMEDIATELY" ...
"MULTISET" ...
"[" ...
"YEAR" ...
"MONTH" ...
"DAY" ...
"HOUR" ...
"MINUTE" ...
"SECOND" ...

 

The above query works in standard databases like postgres/oracle/sql server. This is a commonly generated query pattern by popular BI tools.

 

Issue Links

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