[ https://issues.apache.org/jira/browse/CALCITE-5691?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Ruben Q L updated CALCITE-5691: ------------------------------- Fix Version/s: 1.35.0 > IN sub-query inside FILTER clause throws IndexOutOfBoundsException > ------------------------------------------------------------------ > > 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 > Assignee: Runkang He > Priority: Major > Labels: pull-request-available > Fix For: 1.35.0 > > > 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:sql} > SELECT > count(*) FILTER (WHERE trim(both from name) IN (SELECT city FROM emps)) > FROM emps; {code} > {code:sql} > 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:sql} > 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)