17.05 - Depth Control to Avoid Infinite Recursion - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

If the data hierarchy is cyclic, or if the recursive query specifies an improper join condition, a recursive query can produce a request that never completes with a finite result.

In this context, a bad join is defined as a join that contains one or more of these errors.
  • Joining incorrect columns.
  • Selecting the wrong columns from the join.
  • Specifying an OR operator instead of an AND operator with multiple join conditions.
  • Specifying a join condition that is always true.

The following statement specifies an incorrect join condition in the recursive query. The join condition WHERE indirect.employee_number IN (1003, 1004) is not correct because the result is always true.

     WITH RECURSIVE temp_table (employee_id, level) AS (
       SELECT root.employee_number, 0 AS level       
       FROM employee AS root
       WHERE root.employee_number = 1003
     UNION ALL
       SELECT direct.employee_id, direct.level + 1  /* <--recursive statement*/ 
       FROM temp_table AS direct, employee AS indirect
       WHERE indirect.employee_number IN (1003,1004)
       )
     SELECT *
     FROM temp_table
     ORDER BY level;

The result set returned by this query is as follows:

  employee_id  level
  -----------  -----
         1003      0
         1003      1
         1003      1
         1003      2
         1003      2
         1003      2
         1003      2
         1003      3
         1003      3
         1003      3
         1003      3
         1003      3
         1003      3
         1003      3
         1003      3
          ...    ...

and so on infinitely.

The best practice is to control the depth of the recursion as follows:
  • Specify a depth control column in the column list of the recursive named query.
  • Initialize the column value to 0 in the seed statement.
  • Increment the column value by 1 in the recursive statement.
  • Specify a limit for the value of the depth control column in the join condition of the recursive statements.

The following example adds a join condition (AND direct.level < 2) to the recursive named query in the WITH modifier of the previous query to limit the number of levels of recursion.

     WITH RECURSIVE temp_table (employee_id, level) AS (
       SELECT root.employee_number, 0 AS level    
       FROM employee AS root
       WHERE root.employee_number = 1003
     UNION ALL
       SELECT direct.employee_id, direct.level+1  
       FROM temp_table AS direct, employee AS indir
       WHERE indir.employee_number IN (1003,1004) 
       AND   direct.level < 2                     
       )
     SELECT *
     FROM temp_table
     ORDER BY level;

The data type of the numeric literal you specify for the initial value of the depth control column is the smallest data type that can contain the value. In the preceding query, the data type of the numeric literal 0 is BYTEINT because it is the smallest type that can fit the value 0.

The data type of the initial value of the depth control column limits the number of levels of recursion to the maximum value that the data type can represent.

For example, the maximum value of a BYTEINT is 127. If you need more than 127 levels of recursion, you must cast the numeric literal that you specify for the initial value of the depth control column to a larger type.