Infinite loop warning

[Mladen Gogala]

Hi!
I am getting a strange warning:

db2 => with rec(ename,empno,mgr,job,sal,lev) as
(select ename,empno,mgr,job,sal,0 as lev from emp where ename='KING'
union all
select e.ename,e.empno,e.mgr,e.job,e.sal,lev+1 from rec r,emp e
where e.mgr=r.empno)
select * from rec;
db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.)
=>
ENAME EMPNO MGR JOB SAL LEV
---------- ----------- ----------- --------- -------- -----------
SQL0347W The recursive common table expression "SCOTT.REC" may contain
an
infinite loop. SQLSTATE=01605

KING 7839 - PRESIDENT 5000.00 0
JONES 7566 7839 MANAGER 2975.00 1
BLAKE 7698 7839 MANAGER 2850.00 1
CLARK 7782 7839 MANAGER 2450.00 1
SCOTT 7788 7566 ANALYST 3000.00 2
FORD 7902 7566 ANALYST 3000.00 2
ALLEN 7499 7698 SALESMAN 1600.00 2
WARD 7521 7698 SALESMAN 1250.00 2
MARTIN 7654 7698 SALESMAN 1250.00 2
TURNER 7844 7698 SALESMAN 1500.00 2
JAMES 7900 7698 CLERK 950.00 2
MILLER 7934 7782 CLERK 1300.00 2
ADAMS 7876 7788 CLERK 1100.00 3
SMITH 7369 7902 CLERK 800.00 3

14 record(s) selected with 1 warning messages printed.


I can supress it by setting +W in DB2OPTIONS, but I wonder how did the
database conclude that there may be a possible infinite loop? The result
is correct, the same as displayed by Oracle. The course of my confusion
is the

SQL0347W The recursive common table expression "SCOTT.REC" may contain
an infinite loop. SQLSTATE=01605

warning. How did the engine conclude that there may be an infinite
recursion?

[Mladen Gogala]

Post by Mladen Gogala
Hi!
db2 => with rec(ename,empno,mgr,job,sal,lev) as
(select ename,empno,mgr,job,sal,0 as lev from emp where ename='KING'
union all select e.ename,e.empno,e.mgr,e.job,e.sal,lev+1 from rec
r,emp e where e.mgr=r.empno)
select * from rec;
db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.)
=>
ENAME EMPNO MGR JOB SAL LEV ----------
----------- ----------- --------- -------- ----------- SQL0347W The
recursive common table expression "SCOTT.REC" may contain an infinite
loop. SQLSTATE=01605
KING 7839 - PRESIDENT 5000.00 0 JONES
7566 7839 MANAGER 2975.00 1 BLAKE
7698 7839 MANAGER 2850.00 1 CLARK
7782 7839 MANAGER 2450.00 1 SCOTT 7788
7566 ANALYST 3000.00 2 FORD 7902
7566 ANALYST 3000.00 2 ALLEN 7499 7698
SALESMAN 1600.00 2 WARD 7521 7698
SALESMAN 1250.00 2 MARTIN 7654 7698
SALESMAN 1250.00 2 TURNER 7844 7698
SALESMAN 1500.00 2 JAMES 7900 7698 CLERK
950.00 2 MILLER 7934 7782 CLERK
1300.00 2 ADAMS 7876 7788 CLERK
1100.00 3 SMITH 7369 7902 CLERK
800.00 3
14 record(s) selected with 1 warning messages printed.
I can supress it by setting +W in DB2OPTIONS, but I wonder how did the
database conclude that there may be a possible infinite loop? The result
is correct, the same as displayed by Oracle. The course of my confusion
is the
SQL0347W The recursive common table expression "SCOTT.REC" may contain
an infinite loop. SQLSTATE=01605
warning. How did the engine conclude that there may be an infinite
recursion?

Please ignore. I figured it out by myself. What the statement needs is a
terminating condition. So, no problems when the query looks like this:

with rec(ename,empno,mgr,job,sal,lev) as
(select ename,empno,mgr,job,sal,0 as lev from emp where ename='KING'
union all
select e.ename,e.empno,e.mgr,e.job,e.sal,lev+1 from rec r,emp e
where e.mgr=r.empno and lev<=7)
select * from rec;

The terminating condition "lev<=7" takes care of the "possible infinite
recursion". I find it rather silly.


[***@db2v11 ~]$ db2 -f /tmp/ttt.sql

ENAME EMPNO MGR JOB SAL LEV
---------- ----------- ----------- --------- -------- -----------
KING 7839 - PRESIDENT 5000.00 0
JONES 7566 7839 MANAGER 2975.00 1
BLAKE 7698 7839 MANAGER 2850.00 1
CLARK 7782 7839 MANAGER 2450.00 1
SCOTT 7788 7566 ANALYST 3000.00 2
FORD 7902 7566 ANALYST 3000.00 2
ALLEN 7499 7698 SALESMAN 1600.00 2
WARD 7521 7698 SALESMAN 1250.00 2
MARTIN 7654 7698 SALESMAN 1250.00 2
TURNER 7844 7698 SALESMAN 1500.00 2
JAMES 7900 7698 CLERK 950.00 2
MILLER 7934 7782 CLERK 1300.00 2
ADAMS 7876 7788 CLERK 1100.00 3
SMITH 7369 7902 CLERK 800.00 3

14 record(s) selected.


[***@db2v11 ~]$

This works without the terminating condition on both Oracle 12.2 and MS
SQL 2017. My version is 11.1:

[***@db2v11 ~]$ db2 connect to sample

Database Connection Information

Database server = DB2/LINUXX8664 11.1.1.1
SQL authorization ID = MGOGALA
Local database alias = SAMPLE

And yes, this is an express edition. No warning suppression:
[***@db2v11 ~]$ echo $DB2OPTIONS
-t