... which is kind of proof that there is no such thing as an "experimental" feature :-) people will use those features all the same.
2014-10-06 9:12 GMT+02:00 Gili <[email protected]>: > 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]>: >> >>> 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 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.
