[jira] [Commented] (CALCITE-709) Errors with LIMIT inside scalar sub-query

["zhen wang (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-709?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15054288#comment-15054288 ] 

zhen wang commented on CALCITE-709:
-----------------------------------

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

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 fix this specific scenario, but broke a lot existing tests. 

[~julianhyde] any ideas regarding how error No1 should be fixed ?


> Errors with LIMIT inside scalar sub-query
> -----------------------------------------
>
>                 Key: CALCITE-709
>                 URL: https://issues.apache.org/jira/browse/CALCITE-709
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Julian Hyde
>            Assignee: Julian Hyde
>
> Various when you use LIMIT inside a scalar sub-query.
> Error 1:
> {code}select deptno, (select empno 
>     from "scott".emp
>     where deptno = dept.deptno
>     order by empno limit 1) as x
> from "scott".dept;{code}
> gives {code}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{code}
> Error 2:
> {code}select deptno, (select sum(empno)
>     from "scott".emp
>     where deptno = dept.deptno
>     limit 0) as x
> from "scott".dept;{code}
> gives {code}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){code}
> The test cases are included in scalar.oq, commented out usind '!if (false) ...'; you can run via JdbcTest.testRunScalar.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)