[CALCITE-1800] JDBC adapter fails on query with UNION in FROM clause

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.9.0
• Fix Version/s: 1.13.0
• Component/s: core
• Assignee: Julian Hyde
• Reporter: Viktor Batytskyi
• Created: 22/May/17 16:16
• Updated: 27/Feb/24 22:23
• Resolved: 23/May/17 21:53

Description

Relational representation of the following query fails to be converter to sql string in Calcite:

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

The conversion fails with:

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)

Test case for the issue (RelToSqlConverterTest):

  @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);
  }

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.