[
https://issues.apache.org/jira/browse/CALCITE-2152?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17429029#comment-17429029
]
Ian Bertolacci commented on CALCITE-2152:
-----------------------------------------
We solved this our RelToSqlConverter extending class by cloning the parent join
and inserting a projection between it and an RHS join:
{code}
override def visit(join: Join): Result = {
if (join.getRight.isInstanceOf[Join]) {
super.visit(
join.copy(
join.getTraitSet,
join.getCondition,
join.getLeft,
new LogicalProject(
join.getCluster,
join.getTraitSet,
join.getHints,
join.getRight,
// Construct projection of all expressions from the rhs join
join.getRight.getRowType.getFieldList.asScala.map(field => new
RexInputRef(field.getIndex, field.getType)).asJava,
join.getRight.getRowType
),
join.getJoinType,
join.isSemiJoinDone
)
)
} else {
super.visit(join)
}
}
{code}
We tried to do it "correctly" by constructing a proper Result object, but found
it too difficult.
Hopefully someone else can figure out how do it the right way.
It would be nice if this functionality could be fixed in Calcite's
RelToSqlConverter.
> SQL parser unable to parse SQL with nested joins produced by RelToSqlConverter
> ------------------------------------------------------------------------------
>
> Key: CALCITE-2152
> URL: https://issues.apache.org/jira/browse/CALCITE-2152
> Project: Calcite
> Issue Type: Bug
> Reporter: Samuel Waggoner
> Priority: Major
>
> I created this test case in RelToSqlConverterTest:
> {code:java}
> @Test
> public void testNestedJoin() {
> final String query = "select *"
> + "from \"sales_fact_1997\" "
> + "inner join (select * from \"customer\""
> + "inner join \"employee\" on (\"customer\".\"city\" =
> \"employee\".\"store_id\") ) AS \"customer_employee\""
> + "on (\"sales_fact_1997\".\"store_id\" = \"customer_employee\".\"city\")";
> String result = sql(query).exec();
> System.out.println(result);
> sql(result).exec();
> }
> {code}
> result looks like this:
> {code:java}
> SELECT *
> FROM "foodmart"."sales_fact_1997"
> INNER JOIN ("foodmart"."customer"
> INNER JOIN "foodmart"."employee" ON "customer"."city" =
> "employee"."store_id") ON "sales_fact_1997"."store_id" =
> "customer"."city"{code}
>
> The resulting stack trace:
>
> {code:java}
> java.lang.RuntimeException: org.apache.calcite.sql.parser.SqlParseException:
> Non-query expression encountered in illegal context
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.exec(RelToSqlConverterTest.java:2420)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.testNestedJoin(RelToSqlConverterTest.java:2314)
> 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.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.runner.JUnitCore.run(JUnitCore.java:137)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
> at
> com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
> at
> com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
> at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
> Caused by: org.apache.calcite.sql.parser.SqlParseException: Non-query
> expression encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:350)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:131)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at org.apache.calcite.prepare.PlannerImpl.parse(PlannerImpl.java:179)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.exec(RelToSqlConverterTest.java:2407)
> ... 23 more
> Caused by: org.apache.calcite.runtime.CalciteException: Non-query expression
> encountered in illegal context
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> 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:803)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:788)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.checkNonQueryExpression(SqlParserImpl.java:294)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression3(SqlParserImpl.java:3323)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression2b(SqlParserImpl.java:3069)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression2(SqlParserImpl.java:3095)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression(SqlParserImpl.java:3048)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQueryOrExpr(SqlParserImpl.java:3030)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.QueryOrExpr(SqlParserImpl.java:2952)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.OrderedQueryOrExpr(SqlParserImpl.java:466)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.ParenthesizedExpression(SqlParserImpl.java:586)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.TableRef2(SqlParserImpl.java:1812)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.TableRef(SqlParserImpl.java:1756)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.FromClause(SqlParserImpl.java:1668)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlSelect(SqlParserImpl.java:918)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQuery(SqlParserImpl.java:555)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQueryOrExpr(SqlParserImpl.java:3033)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.QueryOrExpr(SqlParserImpl.java:2952)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.OrderedQueryOrExpr(SqlParserImpl.java:466)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmt(SqlParserImpl.java:844)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmtEof(SqlParserImpl.java:872)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.parseSqlStmtEof(SqlParserImpl.java:187)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:131)
> ... 26 more
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
