Depth Control to Avoid Infinite Recursion - Teradata Database - Teradata Vantage NewSQL Engine

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.

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 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.