Prepared Statements with parameter in "where in" clause

[JPT2]

Found something odd with the "select where in (select)" construct
using a Prepared Statement -- only the first execution works. Please
see the following test case. The expected result is "Hello" followed
by "World". However, the actual output is just "Hello". Changing the
word "in" to "=" yields the expected result, as does rewriting the
query as an inner join.

public static void main(String... args) throws Exception {
DeleteDbFiles.execute("~", "test", true);
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:~/test");
Statement stat = conn.createStatement();
stat.execute("create table test(id int primary key, name
varchar(255))");
stat.execute("insert into test values(1, 'Hello')");
stat.execute("insert into test values(2, 'World')");
PreparedStatement ps;
ResultSet rs;

ps = conn.prepareStatement("select name from test where id in
(select id from test where name = ?)");
ps.setString(1, "Hello");
rs = ps.executeQuery();
while (rs.next()) System.out.println(rs.getString("name"));
rs.close();

ps.setString(1, "World");
rs = ps.executeQuery();
while (rs.next()) System.out.println(rs.getString("name"));
rs.close();

conn.close();
}

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Christian Peter]

Hi,

this is a bug. I just sent a patch to Thomas for review. It should be
fixed in the next release.

Regards

Christian

[Christian Peter]

Fix is included in the next version. Thanks for reporting.

Regards

Christian

On May 9, 2:05 pm, Christian Peter <christian.peter...@googlemail.com>
wrote: