Details
-
Improvement
-
Status: Resolved
-
Major
-
Resolution: Duplicate
-
None
-
None
-
None
Description
Outer joins are not associative ( .e.g. "(t1 LEFT JOIN t2) LEFT JOIN t3" != "t1 LEFT JOIN (t2 LEFT JOIN t3)" ). For this SQL allows using parenthesis to specify associativity. For example the MySQL docs (https://dev.mysql.com/doc/refman/5.7/en/nested-join-optimization.html ) have this as an example:
SELECT * FROM (t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL;
vs.
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ON t1.a=t2.a;
The Calcite parser fails parsing this with a "Non-query expression encountered in illegal context" message (see stack trace of calcite 1.30 below).
What I'd like to request as a feature is supporting parenthesis for join order in parser and SQL formatting. Support on a grammar, parsing, AST and formatting would be sufficient for me.
Stack trace parsing the first example:{}
org.apache.calcite.sql.parser.SqlParseException: Non-query expression encountered in illegal context at org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:389) at org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:153) at org.apache.calcite.sql.parser.SqlParser.handleException(SqlParser.java:145) at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:160) at com.linkedin.espresso.store.sql.TestCalciteParser.testCalciteQueryPrototyping(TestCalciteParser.java:70) ... 24 more Caused by: org.apache.calcite.runtime.CalciteException: Non-query expression encountered in illegal context at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505) at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:599) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:932) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917) at org.apache.calcite.sql.parser.impl.SqlParserImpl.checkNonQueryExpression(SqlParserImpl.java:312) at org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression3(SqlParserImpl.java:16502) at org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression2b(SqlParserImpl.java:16155) at org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression2(SqlParserImpl.java:16196) at org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression(SqlParserImpl.java:16126) at org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQueryOrExpr(SqlParserImpl.java:16103) at org.apache.calcite.sql.parser.impl.SqlParserImpl.QueryOrExpr(SqlParserImpl.java:15557) at org.apache.calcite.sql.parser.impl.SqlParserImpl.OrderedQueryOrExpr(SqlParserImpl.java:505) at org.apache.calcite.sql.parser.impl.SqlParserImpl.ParenthesizedExpression(SqlParserImpl.java:666) at org.apache.calcite.sql.parser.impl.SqlParserImpl.TableRef2(SqlParserImpl.java:9343) at org.apache.calcite.sql.parser.impl.SqlParserImpl.TableRef(SqlParserImpl.java:9268) at org.apache.calcite.sql.parser.impl.SqlParserImpl.FromClause(SqlParserImpl.java:9158) at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlSelect(SqlParserImpl.java:4419) at org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQuery(SqlParserImpl.java:631) at org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQueryOrExpr(SqlParserImpl.java:16109) at org.apache.calcite.sql.parser.impl.SqlParserImpl.QueryOrExpr(SqlParserImpl.java:15557) at org.apache.calcite.sql.parser.impl.SqlParserImpl.OrderedQueryOrExpr(SqlParserImpl.java:505) at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmt(SqlParserImpl.java:3790) at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmtEof(SqlParserImpl.java:3828) at org.apache.calcite.sql.parser.impl.SqlParserImpl.parseSqlStmtEof(SqlParserImpl.java:201) at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:158) ... 25 more
Attachments
Issue Links
- is related to
-
CALCITE-35 Support parenthesized sub-clause in JOIN
-
- Closed
-