H2 Database Recursive query result incorrect

Question

I am trying H2 database. It has many features over Derby Database, to mention one is Recursion. But when I tried the ubiquitous scott/tiger's EMP table, the results are not quite as expected. Please take a look:

with e1(empno, ename, mgr, job, hiredate, l) as ( 
select empno, ename, mgr, job, hiredate, 0 
from   emp 
where  mgr is null 
union all 
select e2.empno, e2.ename, e2.mgr, e2.job, e2.hiredate, l+1 
from   emp e2, e1 
where  e2.mgr = e1.empno) 
select * from e1;

The result:

EMPNO  ENAME  MGR  JOB  HIREDATE  L  
7839 KING null PRESIDENT 1981-11-17 0 
7566 JONES 7839 MANAGER 1981-04-02 1 
7698 BLAKE 7839 MANAGER 1981-05-01 1 
7782 CLARK 7839 MANAGER 1981-06-09 1 
7499 ALLEN 7698 SALESMAN 1981-02-20 2 
7521 WARD 7698 SALESMAN 1981-02-22 2 
7654 MARTIN 7698 SALESMAN 1981-09-28 2 
7788 SCOTT 7566 ANALYST 1987-07-13 2 
7844 TURNER 7698 SALESMAN 1981-09-08 2 
7900 JAMES 7698 CLERK 1981-12-03 2 
7902 FORD 7566 ANALYST 1981-12-03 2 
7934 MILLER 7782 CLERK 1982-01-23 2 
7369 SMITH 7902 CLERK 1980-12-17 3 
7876 ADAMS 7788 CLERK 1987-07-13 3 
(14 rows, 0 ms)

All the MANAGERs are put together and then CLERKs etc. I want result to be different.

For the same query I have done the following in Postgres:

with recursive emp_tree(empno, ename, deptno, job, sal, mgr, l, tree) as (
select empno,
       ename,
       deptno,
       job,
       sal,
       mgr,
       0 as l,
       Array[empno] || '{}'
from emp
where  mgr is null
union all
select e2.empno,
       e2.ename,
       e2.deptno,
       e2.job,
       e2.sal,
       e2.mgr,
       emp_tree.l + 1,
       Array_append(tree, e2.empno)
from emp e2,
       emp_tree
where  e2.mgr = emp_tree.empno)
select * from emp_tree
order by tree

And the results are quite perfect:

"empno";"ename";"deptno";"job";"sal";"mgr";"l";"tree"
7839;"KING";10;"PRESIDENT";5000.00;;0;"{7839}"
7566;"JONES";20;"MANAGER";2975.00;7839;1;"{7839,7566}"
7788;"SCOTT";20;"ANALYST";3000.00;7566;2;"{7839,7566,7788}"
7876;"ADAMS";20;"CLERK";1100.00;7788;3;"{7839,7566,7788,7876}"
7902;"FORD";20;"ANALYST";3000.00;7566;2;"{7839,7566,7902}"
7369;"SMITH";20;"CLERK";800.00;7902;3;"{7839,7566,7902,7369}"
7698;"BLAKE";30;"MANAGER";2850.00;7839;1;"{7839,7698}"
7499;"ALLEN";30;"SALESMAN";1600.00;7698;2;"{7839,7698,7499}"
7521;"WARD";30;"SALESMAN";1250.00;7698;2;"{7839,7698,7521}"
7654;"MARTIN";30;"SALESMAN";1250.00;7698;2;"{7839,7698,7654}"
7844;"TURNER";30;"SALESMAN";1500.00;7698;2;"{7839,7698,7844}"
7900;"JAMES";30;"CLERK";950.00;7698;2;"{7839,7698,7900}"
7782;"CLARK";10;"MANAGER";2450.00;7839;1;"{7839,7782}"
7934;"MILLER";10;"CLERK";1300.00;7782;2;"{7839,7782,7934}"

Please note that in hierarchical query I cannot order the result on empno or join date because SMITH (Clerk) joined before KING (President). So, SMITH's empno is before KING's empno.

Please suggest me a work around for doing the same in H2.

thanks and regards

BB23850

Answer 1

The result you get in H2 is "correct" - you have not specified any sort order, so the database is free to choose any order it wants.

You can simulate the array you use in Postgres in H2 using string concatenation:

with e1 (empno, ename, mgr, job, hiredate, path, lvl) as 
( 
  select empno, ename, mgr, job, hiredate, '/'||lpad(empno, 6, '0'), 0
  from emp 
  where mgr is null 
  union all 
  select child.empno, child.ename, child.mgr, child.job, child.hiredate, 
         parent.path||'/'||lpad(child.empno, 6, '0'), 
         parent.lvl + 1
  from emp child
    join e1 parent on child.mgr = parent.empno
) 
select *
from e1
order by path;

The lpad() is necessary because the final order by is done on a string and there '10' would be sorted before '2'. Padding the number with zeros works around that problem with string comparison.