[ https://issues.apache.org/jira/browse/CALCITE-5691?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17719963#comment-17719963 ]
Soumyava Das commented on CALCITE-5691: --------------------------------------- I have observed that in `RelOptPredicateList.java` this part of the code is creating the IndexOutOfBoundsException {code:java} if (SqlKind.COMPARISON.contains(e.getKind())) { // A comparison with a (non-null) literal, such as 'ref < 10', is not null if 'ref' // is not null. RexCall call = (RexCall) e; if (call.getOperands().get(1) instanceof RexLiteral && !((RexLiteral) call.getOperands().get(1)).isNull()) { return isEffectivelyNotNull(call.getOperands().get(0)); } } {code} > Planning error with subquery inside IN > -------------------------------------- > > Key: CALCITE-5691 > URL: https://issues.apache.org/jira/browse/CALCITE-5691 > Project: Calcite > Issue Type: Bug > Affects Versions: 1.34.0 > Environment: SqlLine > Reporter: Soumyava Das > Priority: Major > > I tried a query which is similar to the one I use in the sqlline component > and I see the query still to fail. The query is > {code:java} > select > count(*) filter (where trim(both from name) in (select city from emps)) > from emps; {code} > {code:java} > 0: jdbc:calcite:model=src/test/resources/mode> select * from emps; > +-------+-------+--------+--------+---------------+-------+------+---------+---------+------------+ > | EMPNO | NAME | DEPTNO | GENDER | CITY | EMPID | AGE | SLACKER | > MANAGER | JOINEDAT | > +-------+-------+--------+--------+---------------+-------+------+---------+---------+------------+ > | 100 | Fred | 10 | | | 30 | 25 | true | > false | 1996-08-03 | > | 110 | Eric | 20 | M | San Francisco | 3 | 80 | | > false | 2001-01-01 | > | 110 | John | 40 | M | Vancouver | 2 | null | false | > true | 2002-05-03 | > | 120 | Wilma | 20 | F | | 1 | 5 | | > true | 2005-09-07 | > | 130 | Alice | 40 | F | Vancouver | 2 | null | false | > true | 2007-01-01 | > +-------+-------+--------+--------+---------------+-------+------+---------+---------+------------+ > 5 rows selected (0.025 seconds) > 0: jdbc:calcite:model=src/test/resources/mode> select > . . . . . . . . . . . . . . . . . . semicolon> count(*) filter (where > trim(both from name) in (select city from emps)) > . . . . . . . . . . . . . . . . . . semicolon> from emps; > Error: Error while executing SQL "select > count(*) filter (where trim(both from name) in (select city from emps)) > from emps": index (1) must be less than size (1) (state=,code=0){code} > The stack trace that I typically see for this is > {code:java} > java.lang.IndexOutOfBoundsException: index (1) must be less than size (1) > at > com.google.common.base.Preconditions.checkElementIndex(Preconditions.java:313) > at > com.google.common.base.Preconditions.checkElementIndex(Preconditions.java:295) > at > com.google.common.collect.SingletonImmutableList.get(SingletonImmutableList.java:45) > at > org.apache.calcite.plan.RelOptPredicateList.isEffectivelyNotNull(RelOptPredicateList.java:240) > at > org.apache.calcite.rex.RexSimplify.simplifyIs2(RexSimplify.java:911) at > org.apache.calcite.rex.RexSimplify.simplifyIs1(RexSimplify.java:869) at > org.apache.calcite.rex.RexSimplify.simplifyIs(RexSimplify.java:840) at > org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:301) at > org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:248) > at > org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:187) > at > org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:182) > at org.apache.calcite.tools.RelBuilder.project_(RelBuilder.java:1993) > at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1840) at > org.apache.calcite.tools.RelBuilder.projectNamed(RelBuilder.java:2161) > at org.apache.calcite.tools.RelBuilder.projectNamed(RelBuilder.java:2098) > at > org.apache.calcite.sql2rel.SqlToRelConverter.createAggImpl(SqlToRelConverter.java:3464) > at > org.apache.calcite.sql2rel.SqlToRelConverter.convertAgg(SqlToRelConverter.java:3367) > at > org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:754) > at > org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:680) > at > org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3765) > at > org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:600) > {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)