• Infinite loop warning

    From Mladen Gogala@21:1/5 to All on Thu Apr 12 19:01:59 2018
    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
    Database Consultant
    http://mgogala.byethost5.com

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Mladen Gogala@21:1/5 to Mladen Gogala on Thu Apr 12 20:41:38 2018
    On Thu, 12 Apr 2018 19:01:59 +0000, Mladen Gogala wrote:

    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?

    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.


    [mgogala@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.


    [mgogala@db2v11 ~]$

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

    [mgogala@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:
    [mgogala@db2v11 ~]$ echo $DB2OPTIONS
    -t



    --
    Mladen Gogala
    Database Consultant
    http://mgogala.byethost5.com

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)