[
https://issues.apache.org/jira/browse/CALCITE-5691?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Soumyava Das updated CALCITE-5691:
----------------------------------
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
{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}
was:
I tried a query which is similar to the one I use in the sqlline component and
I see the query still to fail.
{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}
> 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}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
