[ https://issues.apache.org/jira/browse/CALCITE-5691?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17720650#comment-17720650 ]
Soumyava Das commented on CALCITE-5691: --------------------------------------- This used to work with Calcite 1.21 (which I am using currently). The same example in 1.21 works through sqlline {code:java} > ~/Apache_calcite/calcite/example/csv (main)$ ./sqlline sqlline version 1.8.0 sqlline> !connect jdbc:calcite:model=src/test/resources/model.json admin admin SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder". SLF4J: Defaulting to no-operation (NOP) logger implementation SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details. Transaction isolation level TRANSACTION_REPEATABLE_READ is not supported. Default (TRANSACTION_NONE) will be used instead. 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; +--------+ | EXPR$0 | +--------+ | 0 | +--------+ 1 row selected (0.775 seconds) {code} Seems like a regression issue. I am not sure what changed. Also this error starts coming from 1.22. I tried that as well and get the error {code:java} > ~/Apache_calcite/calcite/example/csv (main)$ ./sqlline sqlline version 1.11.0 sqlline> !connect jdbc:calcite:model=src/test/resources/model.json admin admin SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder". SLF4J: Defaulting to no-operation (NOP) logger implementation SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details. Transaction isolation level TRANSACTION_REPEATABLE_READ is not supported. Default (TRANSACTION_NONE) will be used instead. 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} > 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)