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

JDBC adapter fails on query with UNION in FROM clause

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.9.0
    • 1.13.0
    • core
    • 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

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            julianhyde Julian Hyde
            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

                Slack

                  Issue deployment