Infinite loop in H2 query

Question

I am executing the following query on an H2 database through a test ran using JUnit, on a table with 3 entries:

WITH ancestors(ID, PARENT_ID) AS 
(
  SELECT ID, PARENT_ID FROM PEOPLE WHERE ID = <person_id>
    UNION ALL
  SELECT P1.ID, P1.PARENT_ID FROM PEOPLE P1, PEOPLE P2 WHERE P1.ID = P2.PARENT_ID
)
SELECT ID FROM ancestors;

The query is executed on a self-referencing table that contains people. It finds the ids of all ancestors of a person.

This for some reason runs what I would guess to be an infinite loop as the test hangs and I can see the RAM usage shoot up fast (>2GB). Why is this happening and how can I fix it?

Observations:

• this executes fine in Oracle
• if there is no PARENT_ID specified in the table it executes fine but if there is, it hangs and the RAM usage increases continuously.

Answer 1

So the problem is related to your 2nd select statement which needs to be related to the CTE acenstors table

So based on my query, what it's doing is

1) So the 1st query will add the starting node and be executed once. For example, ID="John", Parent_ID="Rob"

2) The 2nd query, which will be executed many times, will look for a person ID="Rob", since we're joining "ON P1.PARENT_ID = P2.ID". For ex) this will add [ID="ROB", Parent_ID="Susan"] and ID="ROB", Parent_ID="Paul"]

3) behind the scene, it'll execute step 2 again, and this time it'll look the person name Susan and Paul and add it to the ancestor table. This is done until it returns no more records.

WITH ancestors(ID, PARENT_ID) AS 
(
    --child
    SELECT 
        ID, 
        PARENT_ID 
    FROM PEOPLE 
    WHERE ID = <person_id>

    UNION ALL

    --get my lineage
    SELECT 
        P2.ID, 
        P2.PARENT_ID 
    FROM ancestors P1 
    INNER JOIN PEOPLE P2 
        ON P1.PARENT_ID = P2.ID
)
SELECT ID FROM ancestors;