Hmm, wouldn't it be better to raise an error, rather than silently returning a wrong result, then? Or is this kind of experimentally malfunctional? :-)
2014-06-05 18:52 GMT+02:00 Thomas Mueller <[email protected]>: > Hi, > > Yes, this is a known limitation. It is actually documented: "Parameters > are only supported within the last SELECT statement (a workaround is to use > session variables like @start within the table expression).". > > Regards, > Thomas > > > > On Tuesday, June 3, 2014, Lukas Eder <[email protected]> wrote: > >> In fact, there seems to be a second issue related to bind variables and >> recursive CTE. Consider the following alternative program: >>> >>> >> Connection con = getConnection(); >> System.out.println("Wrong result:"); >> PreparedStatement stmt = con.prepareStatement( >> "WITH recursive t(f) AS ( "+ >> " SELECT ? "+ >> " UNION ALL "+ >> " SELECT t.f + 1 "+ >> " FROM t "+ >> " WHERE t.f < 10 "+ >> ") "+ >> "SELECT t.f "+ >> "FROM t " >> ); >> stmt.setInt(1, 1); >> ResultSet rs = stmt.executeQuery(); >> >> while (rs.next()) >> System.out.println(rs.getString(1)); >> >> System.out.println("Correct result:"); >> rs = con.createStatement().executeQuery( >> "WITH recursive t(f) AS ( "+ >> " SELECT 1 "+ >> " UNION ALL "+ >> " SELECT t.f + 1 "+ >> " FROM t "+ >> " WHERE t.f < 10 "+ >> ") "+ >> "SELECT t.f "+ >> "FROM t " >> ); >> >> while (rs.next()) >> System.out.println(rs.getString(1)); >> >> The output is now: >> >> Wrong result: >> *null* >> Correct result: >> 1 >> 2 >> 3 >> 4 >> 5 >> 6 >> 7 >> 8 >> 9 >> >> -- >> 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 http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > -- > You received this message because you are subscribed to a topic in the > Google Groups "H2 Database" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/h2-database/OJfqNF_Iqyo/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
