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 <[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.
