16.20 - Preventing Infinite Recursion With Acyclic Data - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

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.

T
Col_1 Col_2
2 3
4 5
6 7
8 9

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.

A
2
2
2
2
2
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;