Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1800

JDBC adapter fails on query with UNION in FROM clause

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.9.0
    • Fix Version/s: 1.13.0
    • Component/s: core
    • Labels:
      None

      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.

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              victor.batyt@gmail.com Viktor Batytskyi
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - 2h
                2h
                Remaining:
                Remaining Estimate - 2h
                2h
                Logged:
                Time Spent - Not Specified
                Not Specified