Regression in 2.1.214 when joining 2 recursive CTE containing bind values #3554
Description
I have an integration test that uses 2 recursive CTE and joins them like this:
with recursive
t1(f1, f2) as (
select
1,
cast('a' as varchar(15))
union all
select
(t1.f1 + 1),
cast(('a' || t1.f2) as varchar(15))
from t1
where t1.f1 < 10
),
t2(g1, g2) as (
select
1,
cast('b' as varchar(15))
union all
select
(t2.g1 + 1),
cast(('b' || t2.g2) as varchar(15))
from t2
where t2.g1 < 10
)
select t1.f1, t1.f2, t2.g1, t2.g2
from t1
join t2
on t1.f1 = t2.g1The result being:
|F1 |F2 |G1 |G2 |
|---|----------|---|----------|
|1 |a |1 |b |
|2 |aa |2 |bb |
|3 |aaa |3 |bbb |
|4 |aaaa |4 |bbbb |
|5 |aaaaa |5 |bbbbb |
|6 |aaaaaa |6 |bbbbbb |
|7 |aaaaaaa |7 |bbbbbbb |
|8 |aaaaaaaa |8 |bbbbbbbb |
|9 |aaaaaaaaa |9 |bbbbbbbbb |
|10 |aaaaaaaaaa|10 |bbbbbbbbbb|
However, when I use bind variables, it seems like something goes wrong:
try (PreparedStatement s = connection.prepareStatement("""
with recursive
t1(f1, f2) as (
select
cast(? as int),
cast(? as varchar(15))
union all
select
(t1.f1 + cast(? as int)),
cast((cast(? as varchar) || t1.f2) as varchar(15))
from t1
where t1.f1 < 10
),
t2(g1, g2) as (
select
cast(? as int),
cast(? as varchar(15))
union all
select
(t2.g1 + 1),
cast(('b' || t2.g2) as varchar(15))
from t2
where t2.g1 < 10
)
select t1.f1, t1.f2, t2.g1, t2.g2
from t1
join t2
on t1.f1 = t2.g1
""")) {
int i = 1;
s.setInt(i++, 1);
s.setString(i++, "a");
s.setInt(i++, 1);
s.setString(i++, "a");
s.setInt(i++, 1);
s.setString(i++, "b");
try (ResultSet rs = s.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getString(2));
}
}
}I'm getting this error now with H2 2.1.214:
General error: "java.lang.IndexOutOfBoundsException: Index 4 out of bounds for length 2"; SQL statement:
with recursive
t1(f1, f2) as (
select
cast(? as int),
cast(? as varchar(15))
union all
select
(t1.f1 + cast(? as int)),
cast((cast(? as varchar) || t1.f2) as varchar(15))
from t1
where t1.f1 < 10
),
t2(g1, g2) as (
select
cast(? as int),
cast(? as varchar(15))
union all
select
(t2.g1 + 1),
cast(('b' || t2.g2) as varchar(15))
from t2
where t2.g1 < 10
)
select t1.f1, t1.f2, t2.g1, t2.g2
from t1
join t2
on t1.f1 = t2.g1 [50000-214]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:554)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
at org.h2.message.DbException.get(DbException.java:212)
at org.h2.message.DbException.convert(DbException.java:395)
at org.h2.command.Command.executeQuery(Command.java:211)
at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:128)
at org.jooq.testscripts.JDBC.jdbc(JDBC.java:98)
at org.jooq.testscripts.JDBC.main(JDBC.java:43)
Caused by: java.lang.IndexOutOfBoundsException: Index 4 out of bounds for length 2
at java.base/jdk.internal.util.Preconditions.outOfBounds(Preconditions.java:64)
at java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Preconditions.java:70)
at java.base/jdk.internal.util.Preconditions.checkIndex(Preconditions.java:266)
at java.base/java.util.Objects.checkIndex(Objects.java:359)
at java.base/java.util.ArrayList.get(ArrayList.java:427)
at org.h2.command.Parser.readParameter(Parser.java:4877)
at org.h2.command.Parser.readTermWithoutIdentifier(Parser.java:4950)
at org.h2.command.Parser.readTerm(Parser.java:4901)
at org.h2.command.Parser.readFactor(Parser.java:3398)
at org.h2.command.Parser.readSum(Parser.java:3385)
at org.h2.command.Parser.readConcat(Parser.java:3350)
at org.h2.command.Parser.readCondition(Parser.java:3132)
at org.h2.command.Parser.readExpression(Parser.java:3053)
at org.h2.command.Parser.readTermWithoutIdentifier(Parser.java:5105)
at org.h2.command.Parser.readTerm(Parser.java:4901)
at org.h2.command.Parser.readFactor(Parser.java:3398)
at org.h2.command.Parser.readSum(Parser.java:3385)
at org.h2.command.Parser.readConcat(Parser.java:3350)
at org.h2.command.Parser.readCondition(Parser.java:3132)
at org.h2.command.Parser.readExpression(Parser.java:3053)
at org.h2.command.Parser.parseSelectExpressions(Parser.java:2853)
at org.h2.command.Parser.parseSelect(Parser.java:2871)
at org.h2.command.Parser.parseQueryPrimary(Parser.java:2762)
at org.h2.command.Parser.parseQueryTerm(Parser.java:2633)
at org.h2.command.Parser.parseQueryExpressionBody(Parser.java:2612)
at org.h2.command.Parser.parseQueryExpressionBodyAndEndOfQuery(Parser.java:2605)
at org.h2.command.Parser.parseQueryPrimary(Parser.java:2756)
at org.h2.command.Parser.parseQueryTerm(Parser.java:2633)
at org.h2.command.Parser.parseQueryExpressionBody(Parser.java:2612)
at org.h2.command.Parser.parseQueryExpressionBodyAndEndOfQuery(Parser.java:2605)
at org.h2.command.Parser.parseQueryExpression(Parser.java:2598)
at org.h2.command.Parser.parseQuery(Parser.java:2567)
at org.h2.command.Parser.parsePrepared(Parser.java:724)
at org.h2.command.Parser.parse(Parser.java:689)
at org.h2.command.Parser.parse(Parser.java:661)
at org.h2.command.Parser.prepare(Parser.java:537)
at org.h2.index.QueryExpressionIndex.findRecursive(QueryExpressionIndex.java:171)
at org.h2.index.QueryExpressionIndex.find(QueryExpressionIndex.java:266)
at org.h2.index.QueryExpressionIndex.find(QueryExpressionIndex.java:152)
at org.h2.index.IndexCursor.find(IndexCursor.java:161)
at org.h2.table.TableFilter.next(TableFilter.java:394)
at org.h2.table.TableFilter.next(TableFilter.java:464)
at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1832)
at org.h2.result.LazyResult.hasNext(LazyResult.java:78)
at org.h2.result.FetchedResult.next(FetchedResult.java:34)
at org.h2.command.query.Select.queryFlat(Select.java:728)
at org.h2.command.query.Select.queryWithoutCache(Select.java:833)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197)
at org.h2.command.query.Query.query(Query.java:512)
at org.h2.command.query.Query.query(Query.java:475)
at org.h2.command.CommandContainer.query(CommandContainer.java:251)
at org.h2.command.Command.executeQuery(Command.java:190)
... 3 more
This seems to be a regression. With H2 2.1.212, the output was as expected:
a
aa
aaa
aaaa
aaaaa
aaaaaa
aaaaaaa
aaaaaaaa
aaaaaaaaa
aaaaaaaaaa
The workaround seems to be to avoid bind values in CTE.
This change might have caused it? 90caa1d