Wrong result when intersecting unnested arrays #3693
Description
Consider this test case:
try (PreparedStatement s = connection.prepareStatement(
"""
select * from unnest(cast(? as int array))
intersect
select * from unnest(cast(? as int array))
"""
)) {
s.setObject(1, new Integer[] { 1, 2, 3 });
s.setObject(2, new Integer[] { 4, 5, 6 });
try (ResultSet rs = s.executeQuery()) {
while (rs.next())
System.out.println(rs.getInt(1));
}
}The expected result is no result, but I'm getting:
0
0
0
The result seems consistent with this example, where I'm expecting to get the values 1, 2, 3, 4, 5, 6:
try (PreparedStatement s = connection.prepareStatement(
"""
select * from unnest(cast(? as int array))
union all
select * from unnest(cast(? as int array))
"""
)) {
s.setObject(1, new Integer[] { 1, 2, 3 });
s.setObject(2, new Integer[] { 4, 5, 6 });
try (ResultSet rs = s.executeQuery()) {
while (rs.next())
System.out.println(rs.getInt(1));
}
}But instead, I'm just getting:
0
0
0
0
0
0