SQL & PL/SQL

Hello everybody,

Oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
OS: Linux Fedora Core 17 (x86_64)

I was practicing on Recursive Subquery Factoring based on oracle examples available in the documentation
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2129904

I was working on an example which prints the hierarchy of each manager with his/her related employees. Here is how I proceed

WITH tmptab(empId, mgrId, lvl) AS
(
    SELECT  employee_id, manager_id, 0 lvl
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT  employee_id, manager_id, lvl+1
    FROM employees, tmptab
    WHERE (manager_id = empId)
)
SEARCH DEPTH FIRST BY mgrId SET order1
SELECT LPAD(' ', lvl * 3, ' ') || empId AS empId
FROM tmptab;

Which gives the desired output

EMPID
---------------------
100
   101
      108
	 109
	 110
	 111
	 112
	 113
      200
      203
      204
      205
	 206
   102
      103
	 104
	 105
	 106
	 107
   114
      115
      116
      117
      118
      119
   120
      125
      126
      127
      128
      180
      181
      182
      183
   121
      129
      130
      131
      132
      184
      185
      186
      187
   122
      133
      134
      135
      136
      188
      189
      190
      191
   123
      137
      138
      139
      140
      192
      193
      194
      195
   124
      141
      142
      143
      144
      196
      197
      198
      199
   145
      150
      151
      152
      153
      154
      155
   146
      156
      157
      158
      159
      160
      161
   147
      162
      163
      164
      165
      166
      167
   148
      168
      169
      170
      171
      172
      173
   149
      174
      175
      176
      177
      178
      179
   201
      202

107 rows selected.

SQL> 

However, by accident, I noticed that if instead of putting a comma between the table names I put CROSS JOIN, the very same query behaves differently.

That is, if instead of writing

. . .
UNION ALL
    SELECT  employee_id, manager_id, lvl+1
    FROM employees, tmptab
    WHERE (manager_id = empId)

I write

. . .
UNION ALL
    SELECT  employee_id, manager_id, lvl+1
    FROM employees CROSS JOIN tmptab
    WHERE (manager_id = empId)

I get the following error message

ERROR at line 4:
ORA-32044: cycle detected while executing recursive WITH query

Any idea?
Correct me if I'm wrong, but as far as I remember, oracle supports both comme notation and CROSS JOIN for Cartesian product (= cross product). For example

SQL> WITH tmptab1 AS
  2  (
  3      SELECT 'a1' AS colval FROM DUAL UNION ALL
  4      SELECT 'a2' AS colval FROM DUAL UNION ALL
  5      SELECT 'a3' AS colval FROM DUAL
  6  ),
  7  tmptab2 AS
  8  (
  9      SELECT 'b1' AS colval FROM DUAL UNION ALL
 10      SELECT 'b2' AS colval FROM DUAL
 11  )
 12  SELECT t1.colval, t2.colval
 13  FROM tmptab1 t1 CROSS JOIN tmptab2 t2;

CO CO
-- --
a1 b1
a2 b1
a3 b1
a1 b2
a2 b2
a3 b2

6 rows selected.

SQL> LIST 13
 13* FROM tmptab1 t1 CROSS JOIN tmptab2 t2
SQL>
SQL>
SQL> CHANGE /CROSS JOIN/,
 13* FROM tmptab1 t1 , tmptab2 t2
SQL> 
SQL>
SQL> LIST
  1  WITH tmptab1 AS
  2  (
  3  SELECT 'a1' AS colval FROM DUAL UNION ALL
  4  SELECT 'a2' AS colval FROM DUAL UNION ALL
  5  SELECT 'a3' AS colval FROM DUAL
  6  ),
  7  tmptab2 AS
  8  (
  9  SELECT 'b1' AS colval FROM DUAL UNION ALL
 10  SELECT 'b2' AS colval FROM DUAL
 11  )
 12  SELECT t1.colval, t2.colval
 13* FROM tmptab1 t1 , tmptab2 t2
SQL> 
SQL> /

CO CO
-- --
a1 b1
a2 b1
a3 b1
a1 b2
a2 b2
a3 b2

6 rows selected.

SQL> 

So if both comma notated and CROSS JOIN have the same semantic, why I get a cycle for the above mentioned recursive subquery factoring whereas the very same query works pretty well with comma between the table names instead of CROSS JOIN? Because if a cycle is detected (ancestor = current element) this means that the product with CROSS JOIN notation is generating some duplicates which are absent in the result of the comma notated Cartesian product.

I would appreciate if you could kindly make some clarification.

Thanks in advance,

Regards,
Dariyoosh