Thomas, I just wasted 2 days because of this issue.
Please please please make H2 throw an error (pointing to the workaround listed at http://www.h2database.com/html/advanced.html#recursive_queries) instead of silently returning the wrong result. It will save other users a lot of time :) Had I known this was a limitation I would have used the workaround. Instead, I spent 2 days trying to figure out what was wrong with the query logic (when in fact there was nothing wrong). Thank you, Gili On Thursday, June 5, 2014 1:13:07 PM UTC-4, Lukas Eder wrote: > > 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] > <javascript:>>: > >> 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] <javascript:>> >> 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] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> 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.
