This is a minimized version of complex recursive query. The query works when columns in recursive member are listed explicitly:
with t (c,p) as (
select 2,1 from dual
), rec (c,p) as (
select c,p from t
union all
select t.c,t.p from rec join t on rec.c = t.p
)
select * from rec
I don't get why error
ORA-01789: query block has incorrect number of result columns is raised when specified t.* instead.
with t (c,p) as (
select 2,1 from dual
), rec (c,p) as (
select c,p from t
union all
select t.* from rec join t on rec.c = t.p
)
select * from rec
Why t.* is not equivalent to t.c,t.p here? Could you please point me to documentation for any reasoning?
Tested on 11g, 18 - db fiddle
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=6b44cda99ae2a3d5486d14b28206f289 and 19 (livesql).
The docs do specify this restriction but perhaps it could be worded more explicitly
"The number of column aliases following WITH query_name and the number of columns in the SELECT lists of the anchor and recursive query blocks must be the same."
We do this check *very* early in the processing *before* expansion.
For example
SQL> with t (c,p) as (
2 select 2,1 from dual
3 ), rec (c,p) as (
4 select c,p from t
5 union all
6 select blahblahblah.* from rec join t on rec.c = t.p
7 )
8 select * from rec
9 /
select blahblahblah.* from rec join t on rec.c = t.p
*
ERROR at line 6:
ORA-01789: query block has incorrect number of result columns