Caizhi Weng created FLINK-22450:
-----------------------------------
Summary: SQL parser fails to parse subquery containing INTERSECT
in a view
Key: FLINK-22450
URL: https://issues.apache.org/jira/browse/FLINK-22450
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 1.13.0
Reporter: Caizhi Weng
Add the following test case to {{TableEnvironmentITCase}} to reproduce this bug.
{code:scala}
@Test
def myTest(): Unit = {
tEnv.executeSql(
"""
|CREATE TABLE T1 (
| a INT,
| b BIGINT
|) WITH (
| 'connector'='values'
|)
|""".stripMargin)
tEnv.executeSql(
"""
|CREATE TABLE T2 (
| c INT,
| d BIGINT
|) WITH (
| 'connector'='values'
|)
|""".stripMargin)
tEnv.executeSql(
"""
|CREATE TABLE T3 (
| c INT,
| d BIGINT
|) WITH (
| 'connector'='values'
|)
|""".stripMargin)
tEnv.executeSql("CREATE VIEW myView AS SELECT * FROM T1, (SELECT * FROM T2
WHERE c > 0 INTERSECT SELECT * FROM T3 WHERE c > 0) WHERE a = c")
System.out.println(tEnv.explainSql("SELECT * FROM myView"))
}
{code}
The exception stack is
{code}
org.apache.flink.table.api.SqlParserException: SQL parse failed. Encountered ",
SELECT" at line 2, column 10.
Was expecting one of:
<EOF>
"AS" ...
"EXCEPT" ...
"EXTEND" ...
"FETCH" ...
"FOR" ...
"GROUP" ...
"HAVING" ...
"INTERSECT" ...
"LIMIT" ...
"MATCH_RECOGNIZE" ...
"OFFSET" ...
"ORDER" ...
"PIVOT" ...
"MINUS" ...
"TABLESAMPLE" ...
"UNION" ...
"WHERE" ...
"WINDOW" ...
"(" ...
<BRACKET_QUOTED_IDENTIFIER> ...
<QUOTED_IDENTIFIER> ...
<BACK_QUOTED_IDENTIFIER> ...
<HYPHENATED_IDENTIFIER> ...
<IDENTIFIER> ...
<UNICODE_QUOTED_IDENTIFIER> ...
"/*+" ...
"NATURAL" ...
"JOIN" ...
"INNER" ...
"LEFT" ...
"RIGHT" ...
"FULL" ...
"CROSS" ...
"," <IDENTIFIER> ...
"," <HYPHENATED_IDENTIFIER> ...
"," <QUOTED_IDENTIFIER> ...
"," <BACK_QUOTED_IDENTIFIER> ...
"," <BRACKET_QUOTED_IDENTIFIER> ...
"," <UNICODE_QUOTED_IDENTIFIER> ...
"," "LATERAL" ...
"," "(" ...
"," "UNNEST" ...
"," "TABLE" ...
"OUTER" ...
"." ...
at
org.apache.flink.table.planner.parse.CalciteParser.parse(CalciteParser.java:56)
at
org.apache.flink.table.planner.utils.Expander.expanded(Expander.java:83)
at
org.apache.flink.table.planner.operations.SqlToOperationConverter.convertViewQuery(SqlToOperationConverter.java:849)
at
org.apache.flink.table.planner.operations.SqlToOperationConverter.convertCreateView(SqlToOperationConverter.java:819)
at
org.apache.flink.table.planner.operations.SqlToOperationConverter.convert(SqlToOperationConverter.java:248)
at
org.apache.flink.table.planner.delegation.ParserImpl.parse(ParserImpl.java:99)
at
org.apache.flink.table.api.internal.TableEnvironmentImpl.executeSql(TableEnvironmentImpl.java:723)
at
org.apache.flink.table.api.TableEnvironmentITCase.myTest(TableEnvironmentITCase.scala:116)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at
org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at
org.junit.rules.ExpectedException$ExpectedExceptionStatement.evaluate(ExpectedException.java:239)
at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
at
org.apache.flink.util.TestNameProvider$1.evaluate(TestNameProvider.java:45)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
at org.junit.rules.RunRules.evaluate(RunRules.java:20)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runners.Suite.runChild(Suite.java:128)
at org.junit.runners.Suite.runChild(Suite.java:27)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at
com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
at
com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:230)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:58)
Caused by: org.apache.calcite.sql.parser.SqlParseException: Encountered ",
SELECT" at line 2, column 10.
Was expecting one of:
<EOF>
"AS" ...
"EXCEPT" ...
"EXTEND" ...
"FETCH" ...
"FOR" ...
"GROUP" ...
"HAVING" ...
"INTERSECT" ...
"LIMIT" ...
"MATCH_RECOGNIZE" ...
"OFFSET" ...
"ORDER" ...
"PIVOT" ...
"MINUS" ...
"TABLESAMPLE" ...
"UNION" ...
"WHERE" ...
"WINDOW" ...
"(" ...
<BRACKET_QUOTED_IDENTIFIER> ...
<QUOTED_IDENTIFIER> ...
<BACK_QUOTED_IDENTIFIER> ...
<HYPHENATED_IDENTIFIER> ...
<IDENTIFIER> ...
<UNICODE_QUOTED_IDENTIFIER> ...
"/*+" ...
"NATURAL" ...
"JOIN" ...
"INNER" ...
"LEFT" ...
"RIGHT" ...
"FULL" ...
"CROSS" ...
"," <IDENTIFIER> ...
"," <HYPHENATED_IDENTIFIER> ...
"," <QUOTED_IDENTIFIER> ...
"," <BACK_QUOTED_IDENTIFIER> ...
"," <BRACKET_QUOTED_IDENTIFIER> ...
"," <UNICODE_QUOTED_IDENTIFIER> ...
"," "LATERAL" ...
"," "(" ...
"," "UNNEST" ...
"," "TABLE" ...
"OUTER" ...
"." ...
at
org.apache.flink.sql.parser.impl.FlinkSqlParserImpl.convertException(FlinkSqlParserImpl.java:451)
at
org.apache.flink.sql.parser.impl.FlinkSqlParserImpl.normalizeException(FlinkSqlParserImpl.java:214)
at
org.apache.calcite.sql.parser.SqlParser.handleException(SqlParser.java:140)
at
org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:155)
at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:180)
at
org.apache.flink.table.planner.parse.CalciteParser.parse(CalciteParser.java:54)
... 43 more
Caused by: org.apache.flink.sql.parser.impl.ParseException: Encountered ",
SELECT" at line 2, column 10.
Was expecting one of:
<EOF>
"AS" ...
"EXCEPT" ...
"EXTEND" ...
"FETCH" ...
"FOR" ...
"GROUP" ...
"HAVING" ...
"INTERSECT" ...
"LIMIT" ...
"MATCH_RECOGNIZE" ...
"OFFSET" ...
"ORDER" ...
"PIVOT" ...
"MINUS" ...
"TABLESAMPLE" ...
"UNION" ...
"WHERE" ...
"WINDOW" ...
"(" ...
<BRACKET_QUOTED_IDENTIFIER> ...
<QUOTED_IDENTIFIER> ...
<BACK_QUOTED_IDENTIFIER> ...
<HYPHENATED_IDENTIFIER> ...
<IDENTIFIER> ...
<UNICODE_QUOTED_IDENTIFIER> ...
"/*+" ...
"NATURAL" ...
"JOIN" ...
"INNER" ...
"LEFT" ...
"RIGHT" ...
"FULL" ...
"CROSS" ...
"," <IDENTIFIER> ...
"," <HYPHENATED_IDENTIFIER> ...
"," <QUOTED_IDENTIFIER> ...
"," <BACK_QUOTED_IDENTIFIER> ...
"," <BRACKET_QUOTED_IDENTIFIER> ...
"," <UNICODE_QUOTED_IDENTIFIER> ...
"," "LATERAL" ...
"," "(" ...
"," "UNNEST" ...
"," "TABLE" ...
"OUTER" ...
"." ...
at
org.apache.flink.sql.parser.impl.FlinkSqlParserImpl.generateParseException(FlinkSqlParserImpl.java:40579)
at
org.apache.flink.sql.parser.impl.FlinkSqlParserImpl.jj_consume_token(FlinkSqlParserImpl.java:40390)
at
org.apache.flink.sql.parser.impl.FlinkSqlParserImpl.SqlStmtEof(FlinkSqlParserImpl.java:3922)
at
org.apache.flink.sql.parser.impl.FlinkSqlParserImpl.parseSqlStmtEof(FlinkSqlParserImpl.java:262)
at
org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:153)
... 45 more
{code}
This is because when expanding views, we use {{SqlNode#toString}} to parse a
{{SqlNode}} to its corresponding SQL string (See
{{SqlToOperationConverter#convertViewQuery}}, notice the statement {{String
originalQuery = getQuotedSqlString(query);}} and the following usages of
{{originalQuery}}). However the result of {{SqlNode#toString}} might not be a
valid SQL string. For example
{code:sql}
CREATE VIEW myView AS SELECT * FROM T1, (SELECT * FROM T2 WHERE c > 0 INTERSECT
SELECT * FROM T3 WHERE c > 0) WHERE a = c
{code}
The result of {{SqlNode#toString}} for the {{SqlNode}} above is
{code:sql}
CREATE VIEW myView AS SELECT * FROM T1, SELECT * FROM T2 WHERE c > 0 INTERSECT
SELECT * FROM T3 WHERE c > 0 WHERE a = c
{code}
Notice the lacking of brackets around the subquery.
This is because {{INTERSECT}} is an operator with high precedence, so when
unparsing the {{SqlNode}}, Calcite thinks that it is not necessary to add
brackets around the subquery.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
