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.

dfdsfdsfsdf · Accepted Answer · 2016-05-10T15:24:38.237

2

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;

edited May 10 '16 at 15:24

answered May 10 '16 at 14:52

dfdsfdsfsdf

653
3
7

Second select should definitely return P1. I tried changing to INNER JOIN instead of WHERE but that makes no difference. – Vlad Schnakovszki May 10 '16 at 15:00
Oops. I made a mistake with my query. Your 2nd query should join with the CTE table "ancestors" and it should return P2 values. Otherwise, you'll have the same root node. – dfdsfdsfsdf May 10 '16 at 15:07
I tried this as well and it only returns the value (i.e. no ancestors). – Vlad Schnakovszki May 10 '16 at 15:10
Opps my code get's the subtree. I've updated the code. You just have to change the joins. – dfdsfdsfsdf May 10 '16 at 15:13
Excellent, that worked, thank you! Please add a bit more information as to what was causing the infinite loop and why your answer fixes it and I'll award it :) – Vlad Schnakovszki May 10 '16 at 15:17
I added some more info. it might be easier if you draw a tree and map the query to it. Best of luck. – dfdsfdsfsdf May 10 '16 at 15:26

Infinite loop in H2 query

1 Answers1

Linked