Found issues with CTE query with parameter in it.
I think it is similar issue
https://groups.google.com/d/msg/h2-database/Sn4C5BZhzYg/AGGq_NWklPEJ
code for reproducing issue:
public class PreparedStatmentParamInWithClause
{
@Test
public void runQuery() throws Exception
{
Class.forName("org.h2.Driver");
try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:testdb;"))
{
Statement stat = conn.createStatement();
stat.execute("create table test(id int primary key, parent_id int, name
varchar(255));");
stat.execute("insert into test values(1, null, 'Hello')");
String query = "with r (id) as (select id from test where name = ? union
all select s.id from r inner join test s on s.parent_id = r.id) select *
from r;";
// No parameter just value in query
try (ResultSet rs = stat.executeQuery(query.replace("?", "'Hello'")))
{
Integer id = null;
if (rs.next())
{
id = rs.getInt(1);
}
// all is good
Assert.assertTrue(new Integer(1).equals(id));
}
// Same query but set parameter value
PreparedStatement ps = conn.prepareStatement(query);
ps.setString(1, "Hello");
try (ResultSet rs = ps.executeQuery())
{
Integer id = null;
if (rs.next()) // empty rs
{
id = rs.getInt(1);
}
// fail
Assert.assertTrue(new Integer(1).equals(id));
}
}
}
}
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
