Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-5691

IN sub-query inside FILTER clause throws IndexOutOfBoundsException

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.34.0
    • 1.35.0
    • None
    • SqlLine

    Description

      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 

      SELECT
      count(*) FILTER (WHERE trim(both from name) IN (SELECT city FROM emps))
      FROM emps; 
      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)

      The stack trace that I typically see for this is 

      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) 

      Attachments

        Issue Links

          Activity

            People

              Runking Runkang He
              soumyava Soumyava Das
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: