I'm got an adjacency list consisting of two tables:

CREATE TABLE permission_graph (parent_id SMALLINT NOT NULL, child_id SMALLINT NOT NULL,
    UNIQUE KEY (parent_id, child_id),
    FOREIGN KEY (parent_id) REFERENCES permission(id) ON DELETE CASCADE,
    FOREIGN KEY (child_id) REFERENCES permission(id) ON DELETE CASCADE);

When I run the following CTE I get an empty set:

WITH RECURSIVE cte (parent_id, child_id)
    SELECT anchor.parent_id, anchor.child_id
    FROM permission_graph anchor
    WHERE anchor.child_id = -32763
    SELECT recursive.parent_id, recursive.child_id
    FROM cte, permission_graph recursive
    WHERE recursive.child_id = cte.child_id
SELECT cte.parent_id, cte.child_id
FROM cte

But if I run the anchor condition:

SELECT anchor.parent_id, anchor.child_id
FROM permission_graph anchor
WHERE anchor.child_id = -32763

I get:

[parent_id = -32767, child_id = -32763]
[parent_id = -32768, child_id = -32763]

Why is the CTE returning an empty set when the anchor result is non-empty? Shouldn't the CTE result contain the anchor result?

  • 1
    You have your join condition wrong. I guess WHERE recursive.child_id = cte.child_id should be WHERE recursive.parent_id = cte.child_id Unrelated, but: the parentheses around the individual selects are useless. – a_horse_with_no_name Oct 6 '14 at 6:02
  • @a_horse_with_no_name: even if the JOIN condition is wrong, shouldn't I still get back the anchor resultset? (I tried changing the JOIN condition and it did not help.) It sounds like something is wrong with the anchor subquery. – Gili Oct 6 '14 at 6:07
  • When I run your statement (with the invalid join) in Postgres, it seems the recursion never finishes - my statement only stops because I have set a limit on the number of rows. So I would assume H2 silently stops the infinite recursion somehow. Btw: when I run your statement with H2 1.3.174 I get an General error: "java.lang.IndexOutOfBoundsException: Index: 0, Size: 0" – a_horse_with_no_name Oct 6 '14 at 6:21
  • @a_horse_with_no_name, I replaced WHERE in the recursive block with a condition that will always be false, and got the same result. So I don't think I'm seeing an endless recursion here. I even tried renaming "recursion" to some other name (thinking the database might confuse the row with the reserved word) but it did not help. Also, H2 1.4.181 does not exhibit the IndexOutOfBoundsException you mentioned. At least not on my end. – Gili Oct 6 '14 at 6:35
  • See here for the Postgres example: sqlfiddle.com/#!15/04fab/3 When I try your statement with 1.4.181 (which is still beta btw), the statement never finishes (I killed it after 2 minutes) which smells like an infinite recursion. – a_horse_with_no_name Oct 6 '14 at 6:49

I figured it out. (Apologies to those of you who tried to help me. There was no way you could have figured this out.)

After a lot of experimentation, I noticed that dynamic queries (Connection.createStatement()) were returning results but parameterized queries (Connection.prepareStatement()) were returning an empty set. You had no way of knowing this because my question incorrectly listed the query as:

anchor.child_id = -32763

whereas in actuality it was a PreparedStatement

anchor.child_id = ?

with a value of -32763.

Deep within H2's release notes I ran across this wonderful sentence:

Parameters are only supported within the last SELECT statement (a workaround is to use session variables like @start within the table expression).

In short, this looks like an H2 limitation/bug. I really wish H2 would have thrown an exception instead of returning an empty set.

https://groups.google.com/d/msg/h2-database/OJfqNF_Iqyo/Z748UP7W3NAJ confirms this issue (I am getting an empty set instead of null, but otherwise the problem description is identical).

Thank you to those of you who tried helping!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.