Preventing Infinite Recursion With Acyclic Data - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™
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;