Out of desperation if nothing else... I am attempting to implement an adjacency list and (as far as I know) there is no efficient way to implement it without recursive CTE. Firing a separate query per node is very problematic in my case because this is a frequently-run query.
Gili On Monday, October 6, 2014 3:23:00 AM UTC-4, Lukas Eder wrote: > > ... 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] <javascript:>>: > >> 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] <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.
