For the second time in a month, H2 taught me the same lesson: don't break your head trying to optimize queries.
It turns out when I run H2 in embedded mode, there is no performance difference whether I use recursion in the database (CTE) or in the application (Java). The latter is a lot more readable, stable, and runs just as fast. Gili On Monday, October 6, 2014 3:39:13 AM UTC-4, Gili wrote: > > 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 <cow...@bbs.darktech.org>: >> >>> 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 <thomas.to...@gmail.com>: >>>> >>>>> 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 <lukas...@gmail.com> 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 h2-database+unsubscr...@googlegroups.com. >>>>>> >>>>>> To post to this group, send email to h2-database@googlegroups.com. >>>>>> 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 >>>>> h2-database...@googlegroups.com. >>>>> To post to this group, send email to h2-da...@googlegroups.com. >>>>> 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 >>> h2-database...@googlegroups.com. >>> To post to this group, send email to h2-da...@googlegroups.com. >>> 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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
