Problems with infinite recursion are not restricted to cyclic data. A semantically incorrect query can also recurse indefinitely even if the data it queries in acyclic. Semantically incorrect join conditions that result in an infinite loop are a particular problem. A partial list of such conditions includes the following.
- Joining on the wrong columns.
- Selecting the wrong columns from the join.
- Using an OR operator instead of an AND operator with multiple join conditions.
- Giving a joined table a correlation name and then mistakenly referencing its original name in the join condition.
In this case, the system interprets the query as a specification to join 3 tables rather than 2. As a result, it then makes a cross join because there is no join condition for the “third” table.
- Specifying a tautological join condition.
The following example illustrates a tautological case that is limited only by the predicate rec.depth <= 1000. The tautology arises because the seed query WHERE condition returns only 1 row, having a col_1 value of 2, while the recursive query WHERE condition returns only rows that have either the col_1 value 2 or 3. Because every iteration of the recursion produces a row with a col_1 value of 2, it would be an infinite loop were it not for the condition WHERE rec.depth <=1000.
Suppose you have table T with the following data.
You then create the following recursive view.
CREATE RECURSIVE VIEW rec (a, depth) AS ( SELECT col_1, 0 AS depth FROM t WHERE col_1 = 2 UNION ALL SELECT a, rec.depth + 1 FROM t, rec WHERE t.col_1 IN (2, 3) AND rec.depth <= 1000);
The following query on this view produces a result set with 1,001 rows, all having the value 2. The recursion stops only when the query reaches its limiting value. If no limit had been established, the query would have continued until it ran out of spool or disk space.
SELECT a FROM rec;
The result set for the query looks like this, where the ellipsis character indicates 994 additional rows, each having the value 2.
The same problem occurs with the following recursive view definition, which is based on the same table t. This recursive view is also defined with a tautological search condition, but specifies an EXISTS clause in place of the IN clause specified in the previous example.
CREATE RECURSIVE VIEW rec (a, depth) AS ( SELECT col_1, 0 AS depth FROM t WHERE col_1 = 2 AND EXISTS (SELECT col_1 FROM t) UNION ALL SELECT a, rec.depth + 1 FROM t, rec WHERE rec.a = t.col_1 AND rec.depth <= 1000);
The following query on this recursive view produces the identical result set as the IN clause example: 1,001 rows, all having the value 2.
SELECT a FROM rec;