Details

    • Type: Bug
    • Status: In Progress
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      Various when you use LIMIT inside a scalar sub-query.

      Error 1:

      select deptno, (select empno 
          from "scott".emp
          where deptno = dept.deptno
          order by empno limit 1) as x
      from "scott".dept;

      gives

      java.sql.SQLException: error while executing SQL "select deptno, (select empno from "scott".emp where deptno = dept.deptno order by empno limit 1) as x from "scott".dept
      ": Error while compiling generated Java code:
      Caused by: org.codehaus.commons.compiler.CompileException: Line 65, Column 49: Not a boolean expression

      Error 2:

      select deptno, (select sum(empno)
          from "scott".emp
          where deptno = dept.deptno
          limit 0) as x
      from "scott".dept;

      gives

      java.sql.SQLException: error while executing SQL "select deptno, (select sum(empno) from "scott".emp where deptno = dept.deptno limit 0) as x from "scott".dept
      Target must be less than target count, 2
      	at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
      	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:112)
      	at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:130)
      	at net.hydromatic.quidem.Quidem$CheckResultCommand.execute(Quidem.java:690)
      	at net.hydromatic.quidem.Quidem$CompositeCommand.execute(Quidem.java:926)
      	at net.hydromatic.quidem.Quidem.execute(Quidem.java:193)
      	at org.apache.calcite.test.JdbcTest.checkRun(JdbcTest.java:4402)
      	at org.apache.calcite.test.JdbcTest.testRunScalar(JdbcTest.java:4370)
      Caused by: java.lang.IllegalArgumentException: Target must be less than target count, 2
      	at org.apache.calcite.util.mapping.Mappings$PartialFunctionImpl.set(Mappings.java:1607)
      	at org.apache.calcite.util.mapping.Mappings.target(Mappings.java:323)
      	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:401)
      	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:497)
      	at org.apache.calcite.util.ReflectUtil.invokeVisitorInternal(ReflectUtil.java:256)
      	at org.apache.calcite.util.ReflectUtil.invokeVisitor(ReflectUtil.java:213)
      	at org.apache.calcite.util.ReflectUtil$1.invokeVisitor(ReflectUtil.java:476)
      	at org.apache.calcite.sql2rel.RelDecorrelator$DecorrelateRelVisitor.visit(RelDecorrelator.java:1421)
      	at org.apache.calcite.rel.BiRel.childrenAccept(BiRel.java:47)

      The test cases are included in scalar.oq, commented out usind '!if (false) ...'; you can run via JdbcTest.testRunScalar.

        Issue Links

          Activity

          Hide
          zhenw zhen wang added a comment - - edited

          Two observations for this issue ( https://github.com/zinking/calcite/commit/b79c452f3756b68721d12d19a60e74d1f807480d )

          PR: https://github.com/apache/calcite/pull/178/files

          Error No2. after decor relate, output column number might be different between old/new relation. according to the code context, the mapping should't be forced as well.

          Error No1. this is more trouble some,
          generated code

              `final byte $cor0_0 = org.apache.calcite.runtime.SqlFunctions.toByte($cor0[0]);`
              `if (inp7_ != null && $cor0_0 && inp7_.byteValue() == $cor0_0) {`  // error line
          

          the generated expression for correlation variable is incorrect. type of the variable (byte) already had the implication that this variable is not null. while the expression is still generated.

          so the fix I give could adapt to this, with boolean expressions can always be generated

                   if (!tec.isPrimitive() || tec.equals(boolean.class)) {
                      list.add(te);
                    }
          

          Julian Hyde let me know what you think.

          Show
          zhenw zhen wang added a comment - - edited Two observations for this issue ( https://github.com/zinking/calcite/commit/b79c452f3756b68721d12d19a60e74d1f807480d ) PR: https://github.com/apache/calcite/pull/178/files Error No2. after decor relate, output column number might be different between old/new relation. according to the code context, the mapping should't be forced as well. Error No1. this is more trouble some, generated code ` final byte $cor0_0 = org.apache.calcite.runtime.SqlFunctions.toByte($cor0[0]);` ` if (inp7_ != null && $cor0_0 && inp7_.byteValue() == $cor0_0) {` // error line the generated expression for correlation variable is incorrect. type of the variable (byte) already had the implication that this variable is not null. while the expression is still generated. so the fix I give could adapt to this, with boolean expressions can always be generated if (!tec.isPrimitive() || tec.equals( boolean .class)) { list.add(te); } Julian Hyde let me know what you think.
          Hide
          julianhyde Julian Hyde added a comment -

          I agree with your analysis. There are quite a few off-by-one problems during decorrelation. (You might be running into a null semantics issue, similar to CALCITE-980; I can't tell.) I am fixing the decorrelation issues as part of CALCITE-816. This take a new approach, so I don't t think it's worth trying to fix this issue (709) on the old code line.

          Show
          julianhyde Julian Hyde added a comment - I agree with your analysis. There are quite a few off-by-one problems during decorrelation. (You might be running into a null semantics issue, similar to CALCITE-980 ; I can't tell.) I am fixing the decorrelation issues as part of CALCITE-816 . This take a new approach, so I don't t think it's worth trying to fix this issue (709) on the old code line.
          Hide
          julianhyde Julian Hyde added a comment -

          I started work on this in https://github.com/julianhyde/calcite/tree/709-sub-query-limit. Things have improved since CALCITE-1494, and now the queries plan and execute without throwing exceptions. But they give the wrong results when the sub-query is correlated, because the plan is wrong. After de-correlating, a "LIMIT 1" sub-query should return the first row for each correlation variable value.

          Show
          julianhyde Julian Hyde added a comment - I started work on this in https://github.com/julianhyde/calcite/tree/709-sub-query-limit . Things have improved since CALCITE-1494 , and now the queries plan and execute without throwing exceptions. But they give the wrong results when the sub-query is correlated, because the plan is wrong. After de-correlating, a "LIMIT 1" sub-query should return the first row for each correlation variable value.

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              julianhyde Julian Hyde
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:

                Development