Wrong array produced when using ARRAY_AGG() on UNNEST(ARRAY[CAST(? AS INT)]) expression in a PreparedStatement #4033
Description
Using H2 2.2.224, this statement:
try (PreparedStatement s = connection.prepareStatement("""
select array_agg(e)
from unnest(array[cast(? as int), cast(? as int)]) t (e)
""")
) {
s.setInt(1, 1);
s.setInt(2, 2);
try (ResultSet rs = s.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getString(1));
System.out.println(Arrays.asList((Object[]) rs.getArray(1).getArray()));
}
}
}Produces a wrong result:
[null, null]
[null, null]
It seems to be the combination of:
- Using a
PreparedStatementwith cast bind variables (without a cast, there would be an exception about missing data type information) - Using
UNNESTand then againARRAY_AGG
E.g. this doesn't use ARRAY_AGG, and it works correctly:
try (PreparedStatement s = connection.prepareStatement("""
select e
from unnest(array[cast(? as int), cast(? as int)]) t (e)
""")
) {
s.setInt(1, 1);
s.setInt(2, 2);
try (ResultSet rs = s.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
}Producing
1
2
This doesn't use bind variables, and it also works correctly:
try (PreparedStatement s = connection.prepareStatement("""
select array_agg(e)
from unnest(array[1, 2]) t (e)
""")
) {
try (ResultSet rs = s.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getString(1));
System.out.println(Arrays.asList((Object[]) rs.getArray(1).getArray()));
}
}
}Producing:
[1, 2]
[1, 2]