[ https://issues.apache.org/jira/browse/CALCITE-1800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020643#comment-16020643 ]
MinJi Kim edited comment on CALCITE-1800 at 5/23/17 4:24 AM: ------------------------------------------------------------- It looks like the problem is whether we should create a new AliasContext. The plan we get after conversion is as follows. After LogicalUnion, we have an alias "t1" for the subtree including LogicalUnion and below, with row type (product_id, net_weight). {code} LogicalAggregate(group=[{}], NET_WEIGHT=[SUM($0)]) LogicalProject($f0=[CASE(=($0, 0), $1, 0)]) LogicalUnion(all=[true]) LogicalProject(product_id=[$1], net_weight=[$7]) LogicalTableScan(table=[[foodmart, product]]) LogicalProject(product_id=[$0], net_weight=[0]) LogicalTableScan(table=[[foodmart, sales_fact_1997]]) {code} Sine Unions are SET_OP operations, in the Result.builder(), we set the flag needNew to true (selectList is null in this case) when visiting LogicalProject above the Union, and so we enter the following if-clause. Now, in this case, the LogicalProject is reducing the row type to a single column, so we now generate an incorrect row type for alias "t1". Basically, when the alias is unchanging, we shouldn't be creating a new alias. {code} } else { boolean qualified = !dialect.hasImplicitTableAlias() || aliases.size() > 1; if (needNew) { // <--- this is where the patch fixes so that we don't enter here. newContext = aliasContext(ImmutableMap.of(neededAlias, rel.getRowType()), qualified); } else { newContext = aliasContext(aliases, qualified); } } {code} At the minimum, I would change the patch so that it checks that aliases.size() == 1 and that the aliases are the same (i.e. neededAlias is equal to the only key is aliases) -- if there are two aliases say "t1" and "t2" in aliases, and we are collapsing the two aliases into a single alias "t1", even if "t1" is contained in aliases, we should actually create a new alias. But, I wonder if we should get rid of it all and revert the code back to what it used to be. Because I think in this case, we have no selectList, there shouldn't be any rowtype change. {code} } else { boolean qualified = !dialect.hasImplicitTableAlias() || aliases.size() > 1; newContext = aliasContext(aliases, qualified); } {code} In this case, only one test fails RelToSqlConverterTest.testSelectQueryWithGroupByHaving3(). I am looking into that right now. was (Author: minjikim): It looks like the problem is whether we should create a new AliasContext. The plan we get after conversion is as follows. After LogicalUnion, we have an alias "t1" for the subtree including LogicalUnion and below, with row type (product_id, net_weight). {code} LogicalAggregate(group=[{}], NET_WEIGHT=[SUM($0)]) LogicalProject($f0=[CASE(=($0, 0), $1, 0)]) LogicalUnion(all=[true]) LogicalProject(product_id=[$1], net_weight=[$7]) LogicalTableScan(table=[[foodmart, product]]) LogicalProject(product_id=[$0], net_weight=[0]) LogicalTableScan(table=[[foodmart, sales_fact_1997]]) {code} Sine Unions are SET_OP operations, in the Result.builder(), we set the flag needNew to true (selectList is null in this case) when visitng LogicalProject above the Union, and so we enter the following if-clause. Now, in this case, the LogicalProject is reducing the row type to a single column, so we now generate an incorrect row type for aliase "t1". I think basically, when the alias is unchanging, we shouldn't be creating a new alias. {code} } else { boolean qualified = !dialect.hasImplicitTableAlias() || aliases.size() > 1; if (needNew) { // <--- this is where the patch fixes so that we don't enter here. newContext = aliasContext(ImmutableMap.of(neededAlias, rel.getRowType()), qualified); } else { newContext = aliasContext(aliases, qualified); } } {code} At the minimum, I would change the patch so that it checks that aliases.size() == 1 and that the aliases are the same (i.e. neededAlias is equal to the only key is aliases). But, I wonder if we should get rid of it all and revert the code back to what it used to be. Because I think in this case, we have no selectList, there shouldn't be any rowtype change. {code} } else { boolean qualified = !dialect.hasImplicitTableAlias() || aliases.size() > 1; newContext = aliasContext(aliases, qualified); } {code} In this case, only one test fails RelToSqlConverterTest.testSelectQueryWithGroupByHaving3(). I am looking into that right now. > JDBC adapter fails to SELECT FROM a UNION query > ------------------------------------------------ > > Key: CALCITE-1800 > URL: https://issues.apache.org/jira/browse/CALCITE-1800 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.9.0 > Reporter: Viktor Batytskyi > Assignee: Julian Hyde > Fix For: 1.13.0 > > Original Estimate: 2h > Remaining Estimate: 2h > > Relational representation of the following query fails to be converter to sql > string in Calcite: > {code} > select sum(case when "product_id"=0 then "net_weight" else 0 end) as > net_weight > from ( select "product_id", "net_weight" from "product" > union all > select "product_id", 0 as "net_weight" from "sales_fact_1997" ) t0 > {code} > The conversion fails with: > {code} > java.lang.RuntimeException: While invoking method 'public > org.apache.calcite.rel.rel2sql.SqlImplementor$Result > org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Aggregate)' > at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528) > at > org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:96) > at > org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:100) > at > org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.ok(RelToSqlConverterTest.java:1559) > at > org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.testUnionWrappedInASelect(RelToSqlConverterTest.java:654) > 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:117) > at > com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42) > at > com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262) > at > com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84) > 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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) > Caused by: java.lang.reflect.InvocationTargetException > 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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:525) > ... 31 more > Caused by: java.lang.RuntimeException: While invoking method 'public > org.apache.calcite.rel.rel2sql.SqlImplementor$Result > org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Project)' > at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528) > at > org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:96) > at > org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:100) > at > org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:179) > ... 36 more > Caused by: java.lang.reflect.InvocationTargetException > 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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:525) > ... 39 more > Caused by: java.lang.AssertionError: field ordinal 0 out of range > {t1=RecordType(DOUBLE $f0)} > at > org.apache.calcite.rel.rel2sql.SqlImplementor$AliasContext.field(SqlImplementor.java:889) > at > org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:521) > {code} > Test case for the issue (RelToSqlConverterTest): > {code} > @Test public void testUnionWrappedInASelect() { > String query = > "select sum(case when \"product_id\"=0 then \"net_weight\" else > 0 end) as net_weight from ( " > + "select \"product_id\", \"net_weight\" from \"product\" " > + " union all " > + "select \"product_id\", 0 as \"net_weight\" from > \"sales_fact_1997\" " > + ") t0"; > String expected = > "SELECT SUM(CASE WHEN \"product_id\" = 0 THEN \"net_weight\" > ELSE 0 END) AS \"NET_WEIGHT\"\n" > + "FROM (SELECT \"product_id\", \"net_weight\"\n" > + "FROM \"foodmart\".\"product\"\n" > + "UNION ALL\n" > + "SELECT \"product_id\", 0 AS \"net_weight\"\n" > + "FROM \"foodmart\".\"sales_fact_1997\") AS \"t1\""; > sql(query).ok(expected); > } > {code} > The issue is caused by the fix: CALCITE-1332 > The issue should be resolved, as the case is pretty common and the failing > sql is fairly simple. -- This message was sent by Atlassian JIRA (v6.3.15#6346)