Fibonacci series with 12c recursive subquery factoring

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 26 00:04:38 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> with fib(e,f) as (select 1, 1 from dual union all select e+f,e from fib where e <= 55) select f from fib;

         F
----------
         1
         1
         2
         3
         5
         8
        13
        21
        34
        55

10 rows selected.

Here are a few interesting error messages relating to 12c recursive subquery factoring:

SQL> with tmp as (select 1 id from dual union all select id + 1 from tmp where tmp.id + 1 <= 10) select id from tmp;
with tmp as (select 1 id from dual union all select id + 1 from tmp where tmp.id + 1 <= 10) select id from tmp
                                                                *
ERROR at line 1:
ORA-32039: recursive WITH clause must have column alias list

SQL> with tmp(id) as (select 1 id, 1 l from dual union all select id + 1, id from tmp where tmp.id < 10) select id from tmp;
with tmp(id) as (select 1 id, 1 l from dual union all select id + 1, id from tmp where tmp.id < 10) select id from tmp
                                                                             *
ERROR at line 1:
ORA-32038: number of WITH clause column names does not match number of elements in select list


SQL> with fib(f,g) as (select 1 f, 1 g from dual union select f+g,f from fib where f < 55) select f,g from fib;
with fib(f,g) as (select 1 f, 1 g from dual union select f+g,f from fib where f < 55) select f,g from fib
                                                                    *
ERROR at line 1:
ORA-32040: recursive WITH clause must use a UNION ALL operation



SQL> with fib(f,g) as (select 1 f, 0 g from dual union all select f+g,f from fib where f < 55) select f,g from fib;
ERROR:
ORA-32044: cycle detected while executing recursive WITH query



no rows selected