Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tomáš.

Asked: October 07, 2020 - 8:43 pm UTC

Answered by: Connor McDonald - Last updated: October 09, 2020 - 3:03 am UTC

Category: SQL - Version: 11.2.0.4.0, 18.0.0.0.0, 19.0.0.0.0

Viewed 100+ times

You Asked

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).


with LiveSQL Test Case:

and we said...

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

and you rated our response

  (2 ratings)

Reviews

thanks + link

October 08, 2020 - 6:33 am UTC

Reviewer: Tomáš Záluský from CZ

Thanks for clarification, Connor. I was aware of sentence you cited from https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/SELECT.html#d436015e2085 , however I was not aware of time of expansion so I didn't treat star expression as single column. Good to know.

Also thanks for quick response in comparison with SO. Anyway I'm doublelinking SO question for better reachability: https://stackoverflow.com/q/64177477/653539 .

Connor McDonald

Followup  

October 09, 2020 - 3:03 am UTC

Glad we could help

October 12, 2020 - 3:45 am UTC

Reviewer: A reader


More to Explore

SQL

The Oracle documentation contains a complete SQL reference.