[ https://issues.apache.org/jira/browse/CALCITE-3463?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16963848#comment-16963848 ]
Yunpeng Niu edited comment on CALCITE-3463 at 10/31/19 10:29 AM: ----------------------------------------------------------------- Hi [~danny0405], Thanks for following up. I believe you mean: {code:sql} SELECT * FROM departments d INNER JOIN dept_manager m ON d.dept_no = m.dept_no INNER JOIN employees e ON m.emp_no = e.emp_no {code} (i.e., there is no "and" at the end) Yes, if I changed the query to the one you suggest, it would work. I think the problem is: Calcite's query parser *cannot* understand the pair of brackets in the original query (which I believe is definitely valid in standard SQL syntax). Although the original query and the modified query are equivalent semantically, they are still two different queries. Parser module itself (as you said) should support all valid SQL syntax. To make it even worse, we cannot remove the brackets easily if the join type is changed (i.e., if the join type is not inner join anymore). For example, if we change it outer join: {code:sql} SELECT * FROM departments d LEFT JOIN ( dept_manager m INNER JOIN employees e ON m.emp_no = e.emp_no ) ON d.dept_no = m.dept_no {code} Please let me know your thoughts. Thanks. was (Author: yunpengn): Hi [~danny0405], Thanks for following up. I believe you mean: {code:sql} SELECT * FROM departments d INNER JOIN dept_manager m ON d.dept_no = m.dept_no INNER JOIN employees e ON m.emp_no = e.emp_no {code} (i.e., there is no "and" at the end) Yes, if I changed the query to the one you suggest, it would work. I think the problem is: Calcite's query parser *cannot* understand the pair of brackets in the original query (which I believe is definitely valid in standard SQL syntax). Although the original query and the modified query are equivalent semantically, they are still two different queries. Parser module itself (as you said) should support all valid SQL syntax. To make it even worse, we cannot remove the brackets easily if the join type is changed (i.e., if the join type is not inner join anymore). For example, if we change it outer join: {code:sql} SELECT * FROM departments d LEFT JOIN ( dept_manager m INNER JOIN employees e ON m.emp_no = e.emp_no ) ON d.dept_no = m.dept_no {code} > Parse SQL Query - Non-query expression encountered in illegal context > --------------------------------------------------------------------- > > Key: CALCITE-3463 > URL: https://issues.apache.org/jira/browse/CALCITE-3463 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.21.0 > Environment: OS: macOS Catalina 10.15 > Java: java 12.0.2 2019-07-16 > Reporter: Yunpeng Niu > Priority: Major > > I tried to ask calcite-core to parse the following SQL query: > > {code:java} > final SqlParser.Config parserConfig = SqlParser.configBuilder() > .setConformance(SqlConformanceEnum.MYSQL_5) > .setLex(Lex.MYSQL) > .build(); > String query = "SELECT * FROM departments d INNER JOIN (dept_manager m INNER > JOIN employees e ON m.emp_no = e.emp_no) ON d.dept_no = m.dept_no"; > SqlParser.create(query, parserConfig).parseQuery(); > {code} > > > But there is the following exception: > {noformat} > Exception in thread "main" org.apache.calcite.sql.parser.SqlParseException: > Non-query expression encountered in illegal contextException in thread "main" > org.apache.calcite.sql.parser.SqlParseException: Non-query expression > encountered in illegal context at > org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:362) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:147) > at > org.apache.calcite.sql.parser.SqlParser.handleException(SqlParser.java:148) > at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:163) at > org.apache.calcite.Runner.main(Runner.java:60)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.newInstanceWithCaller(Constructor.java:500) > at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481) > at > org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) > at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572) at > org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:835) at > org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:820) at > org.apache.calcite.sql.parser.impl.SqlParserImpl.checkNonQueryExpression(SqlParserImpl.java:306) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression3(SqlParserImpl.java:13780) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression2b(SqlParserImpl.java:13449) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression2(SqlParserImpl.java:13490) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression(SqlParserImpl.java:13421) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQueryOrExpr(SqlParserImpl.java:13398) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.QueryOrExpr(SqlParserImpl.java:12874) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.OrderedQueryOrExpr(SqlParserImpl.java:478) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.ParenthesizedExpression(SqlParserImpl.java:639) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.TableRef2(SqlParserImpl.java:8145) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.TableRef(SqlParserImpl.java:8068) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.FromClause(SqlParserImpl.java:7969) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlSelect(SqlParserImpl.java:3722) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQuery(SqlParserImpl.java:604) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQueryOrExpr(SqlParserImpl.java:13404) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.QueryOrExpr(SqlParserImpl.java:12874) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.OrderedQueryOrExpr(SqlParserImpl.java:478) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmt(SqlParserImpl.java:3626) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmtEof(SqlParserImpl.java:3664) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.parseSqlStmtEof(SqlParserImpl.java:194) > at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:161) > ... 1 more > Process finished with exit code 1 > {noformat} > > I have tried to run the above query in MySQL database and I am sure it is > valid syntax. Can anyone help to explain why the above exception is thrown? > Thanks. And how should I fix this? -- This message was sent by Atlassian Jira (v8.3.4#803005)