# Preventing Infinite Recursion With Acyclic Data - Advanced SQL Engine - Teradata Database

## SQL Data Definition Language Detailed Topics

Product
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category

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;```