Recursive WITH clause join bug

I recently ran into a strange bug where I spent a lot of time banging my head trying to figure out why my recursion wasn’t working, causing ORA-32044: cycle detected while executing recursive WITH query errors to show up.

After simplifying the query down to a single counter column I eventually discovered the problem was with the CROSS JOIN syntax.

WITH
    params AS(SELECT 5 limit FROM DUAL),
    x (n)
    AS
        (SELECT 1 n FROM DUAL
         UNION ALL
         SELECT n + 1 n
           FROM x CROSS JOIN params
          WHERE n < limit )
SELECT *
  FROM x;

While the above syntax appears to be correct, running the query will generate the ORA-32044 mentioned above. Fortunately, there is a simple workaround, replace the “a CROSS JOIN b” syntax with the older “a,b” syntax.

WITH
    params AS(SELECT 5 limit FROM DUAL),
    x (n)
    AS
        (SELECT 1 n FROM DUAL
         UNION ALL
         SELECT n + 1 n
           FROM x, params
          WHERE n < limit)
SELECT *
  FROM x;

         N
----------
         1
         2
         3
         4
         5

Now the query works as expected.

I initially found this in an on-premises 21c database; but I was also able to reproduce it on Oracle’s own Live SQL. As of this writing Live SQL is running version 19.8. I also reproduced it on a 12.2 database. I have submitted a bug report to Oracle.