SQLite Forum

short circuiting subqueries with `IN`?
You need to add a "terminiating condition" in order to terminate the recursion.

For example:

               WITH found(id) AS NOT MATERIALIZED
                        SELECT self_hash_id
                          FROM causal
                         WHERE self_hash_id == ?2
                     UNION ALL
                        SELECT parent_id
                          FROM causal_parent
                          JOIN found
                            ON found.id == causal_id
             SELECT id
               FROM found
              WHERE id == ?1
              LIMIT 1

which will cause the list population to cease once the value is found.  If the value is not found then the recursion will complete when the recursion is exhausted.

** NB ** NOT MATERIALIZED is the default, however, the short-circuit termination only applies if `found(id)` is not materialized.