Skip to Main Content
  • Questions

  • Why does ROWID break when used with CONNECT BY, but only in a subquery?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ivan.

Asked: November 13, 2023 - 2:13 pm UTC

Last updated: November 20, 2023 - 1:42 pm UTC

Version: 19.0.0.0.0

Viewed 1000+ times

You Asked

Hi Tom!


ROWID works in a subselect: 
SELECT * FROM(SELECT rowid, d.* FROM DUAL d)


and it also works with connect by: 
SELECT rowid, LEVEL FROM DUAL CONNECT BY LEVEL < 2


But when used with both: 
SELECT * FROM(SELECT rowid, LEVEL FROM DUAL CONNECT BY LEVEL < 2)

then it throws:
ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.


I've stumbled on this, since I used CONNECT BY LEVEL as a substitute for generate_series in a view, which was used in an APEX calendar, which had "select ROWID" ticket by default.

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/voddjhspggfm2lv8rzu313gt

and Chris said...

Thanks for providing a LiveSQL example!

The optimizer doesn't remove the inline view from the subquery in the third query. It does remove it in the first query (without connect by):

set serveroutput off

select * from (select d.* from dual d);

select * 
from   dbms_xplan.display_cursor ( format => 'BASIC' );
/*
----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| DUAL |
----------------------------------
*/

select level from dual connect by level < 2;

select * 
from   dbms_xplan.display_cursor ( format => 'BASIC' );
/*
---------------------------------------------
| Id  | Operation                    | Name |
---------------------------------------------
|   0 | SELECT STATEMENT             |      |
|   1 |  CONNECT BY WITHOUT FILTERING|      |
|   2 |   FAST DUAL                  |      |
---------------------------------------------
*/

select * from ( select level from dual connect by level < 2 );

select * 
from   dbms_xplan.display_cursor ( format => 'BASIC' );
/*
----------------------------------------------
| Id  | Operation                     | Name |
----------------------------------------------
|   0 | SELECT STATEMENT              |      |
|   1 |  VIEW                         |      |
|   2 |   CONNECT BY WITHOUT FILTERING|      |
|   3 |    FAST DUAL                  |      |
----------------------------------------------
*/


This is because - in general - merging the inline connect by with the parent query is unsafe. For example, compare these queries:

select count(*) from ( 
  select * from hr.employees 
  connect by prior manager_id = employee_id 
)
join   departments 
using  ( department_id );
/*
  COUNT(*)
----------
       314
*/       
select count(*) from hr.employees
join   departments 
using  ( department_id )
connect by prior manager_id = employee_id;

--ORA-00918: column ambiguously defined

select count(*) from hr.employees e
join   departments 
using  ( department_id )
connect by prior e.manager_id = employee_id;
/*
  COUNT(*)
----------
       312
*/


Notice they all have different behaviour!

So in your third query, the optimizer preserves the inline view. Although not explicitly called out in the ORA-01446 error, connect by is one of the features you can't combine with rowid in an (inline) view.

Rating

  (3 ratings)

Comments

The real question is why doesn't SELECT * work against those inline views

mathguy, November 18, 2023 - 5:02 pm UTC

The answer explains why ROWID can't be in the SELECT list of an outer query against an inline view with CONNECT BY. But it doesn't answer the question in the OP's post - which is different from the question in his title. Namely, why doesn't SELECT * work against such views.

The OP's query, selecting * from the inline view, "should work" (the quotes around "should work" will become clear later). * should be expanded to the list of columns resulting from the view - it should not resolve any of those names to a pseudocolumn of the outer query. (And, to state the obvious, a trivial workaround is to give an alias like ROWID_ to the ROWID pseudocolumn in the inline view.)

Similar queries using other pseudocolumns, like LEVEL and ROWNUM, are transformed correctly - * is expanded to the column names returned by the inline view, as column names IN DOUBLE-QUOTES (which means they can't be resolved to pseudocolumns, which would take precedence; they must be resolved as column names or aliases from the view).

To understand what I'm saying, consider the following three queries:
select LEVEL from (select deptno as "LEVEL", count(*) as ct from scott.dept group by deptno);
select "LEVEL", ct from (select deptno as "LEVEL", count(*) as ct from scott.dept group by deptno);
select * from (select deptno as "LEVEL", count(*) as ct from scott.dept group by deptno);

The first one fails with "CONNECT BY clause required in this query block", but the second query succeeds, and the third succeeds too - and the 10053 trace shows that the third query is transformed into the second query, not the first. (Essentially; in fact the transformed query will also have an alias for the inline view, etc.)

Change LEVEL to ROWID in all the queries above, in all places - now ALL THREE queries fail. The specific question boils down to this:

select "ROWID" from (select deptno as "ROWID", count(*) as ct from scott.dept group by deptno);


fails with "ORA-01446: cannot select ROWID from ..." Which is odd - we explicitly selected "ROWID" (in double-quotes), not ROWID. Why is this handled differently from the "LEVEL" vs LEVEL example?

The answer is found in the documentation - Note 2 to the "Oracle SQL Reserved Words" section. In general, reserved words can be used as column names, if put in double-quotes; but ROWID is a notable exception:

Note 2: You cannot use the uppercase word ROWID, either quoted or nonquoted, as a column name. However, you can use the uppercase word as a quoted identifier that is not a column name, and you can use the word with one or more lowercase letters (for example, "Rowid" or "rowid") as any quoted identifier, including a column name.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Oracle-SQL-Reserved-Words.html#GUID-55C49D1E-BE08-4C50-A9DD-8593EB925612

The documentation doesn't explain why Oracle needed this exception to the rule about reserved words in double quotes as column names, but at least it is clearly documented.
Chris Saxon
November 20, 2023 - 1:42 pm UTC

I don't know why the exception for ROWID; it's been around "forever" so may relate to historic reasons.

Same exception for ROWID documented in the "Object names" section

mathguy, November 18, 2023 - 5:24 pm UTC

For example:

Nonquoted identifiers cannot be Oracle SQL reserved words. Quoted identifiers can be reserved words, although this is not recommended.

Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words.

Note:

The reserved word ROWID is an exception to this rule. You cannot use the uppercase word ROWID, either quoted or nonquoted, as a column name. However, you can use the uppercase word as a quoted identifier that is not a column name, and you can use the word with one or more lowercase letters (for example, "Rowid" or "rowid") as any quoted identifier, including a column name.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Database-Object-Names-and-Qualifiers.html#GUID-75337742-67FD-4EC0-985F-741C93D918DA
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.