Recursive CTE don't work when recursion predicate uses a bind variable

[Lukas Eder]

Java program to reproduce this:

Connection con = getConnection();

System.out.println("Wrong result:");

PreparedStatement stmt = con.prepareStatement(

    "WITH recursive t(f) AS ( "+

    "    SELECT 1             "+

    "    UNION ALL            "+

    "    SELECT t.f + 1       "+

    "    FROM t               "+

    "    WHERE t.f < ?        "+

    ")                        "+

    "SELECT t.f               "+

    "FROM t                   "

);

stmt.setInt(1, 10);

ResultSet rs = stmt.executeQuery();

while (rs.next())

    System.out.println(rs.getInt(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.getInt(1));

The produced output is:

Wrong result:

1

Correct result:

1

2

3

4

5

6

7

8

9

10

[Lukas Eder]

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

[Thomas Mueller]

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

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

[Lukas Eder]

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? :-)

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

[Gili]

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

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@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.

[Lukas Eder]

... which is kind of proof that there is no such thing as an "experimental" feature :-)

people will use those features all the same.

[Gili]

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

[Gili]

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

[Lukas Eder]

For the record, I've posted this issue also to Stack Overflow:

http://stackoverflow.com/q/32827660/521799

It will help poor souls stumbling upon this issue and spending hours to debug it :)

(Still +1 for throwing an UnsupportedOperationException instead of returning the wrong result!)

[howard...@verizon.net]

Is this the right place to vote for prioritizing this feature (implementation of bind variables for CTE)?

We're using H2 for desktop testing of an application that will use Oracle in production.  This feature would give us a x-platform way to develop recursive queries efficiently.