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