Preventing Infinite Recursion with Acyclic Data - Teradata Vantage

The following example shows 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 loops forever without 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. With no limit, the query continues until it runs 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;