CTE returns an empty set even though anchor is non-empty

Question

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

CREATE TABLE permission (id SMALLINT AUTO_INCREMENT(-32768, 1) PRIMARY KEY);
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)
AS
(
  (
    SELECT anchor.parent_id, anchor.child_id
    FROM permission_graph anchor
    WHERE anchor.child_id = -32763
  )
  UNION ALL
  (
    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?

score 4 · Accepted Answer · answered Oct 06 '14 at 07:16

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!

CTE returns an empty set even though anchor is non-empty

1 Answers1