"column ambiguously defined error" even when the column is prefixed?

Question

I have the following sql and it works fine (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production).

with n as (
    select ntile(cnt) over ( partition by cnt order by Id ) NT, Id
    from t
)
select  n.NT, high.Id
from    (select distinct NT from n) n 
        outer apply (
           select * 
           from n x where x.NT = n.NT and rownum = 1 order by Id
        ) low
        outer apply (
           select * 
           from (select * from n x where x.NT = n.NT order by Id desc) d where rownum = 1
        ) high

Now I changed it to the following,

with n as (
    select ntile(cnt) over ( partition by cnt order by Id ) NT, Id
    from t
)
select  n.NT, phigh.Id -- Error - phigh.Id
from    (select distinct NT from n) n 
        outer apply (
            select * 
            from n x where x.NT = n.NT and rownum = 1 order by Id
        ) low
        outer apply (
            select * 
            from (select * from n x where x.NT = n.NT order by Id desc) d where rownum = 1
        ) high
        outer apply (
            select * 
            from (select * from n x where x.NT = n.NT-1 order by Id desc) d where rownum = 1
        ) phigh

Now it gets the following error on phigh.Id:

ORA-00918: column ambiguously defined

918. 00000 - "column ambiguously defined"

And the second query works if I change phigh.Id to phigh.*? Changing phigh.Id to high.Id works too.

Update:

Tried to use the different alias from the CTE. Still got the same error

with n as (
    select ntile(cnt) over ( partition by cnt order by Id ) NT, Id
    from t
)
select  n.NT, phigh.Id -- Error - phigh.Id
from    (select distinct NT from n) nt 
        outer apply (
            select * 
            from n x where x.NT = nt.NT and rownum = 1 order by Id
        ) low
        outer apply (
            select * 
            from (select * from n x where x.NT = nt.NT order by Id desc) d where rownum = 1
        ) high
        outer apply (
            select * 
            from (select * from n x where x.NT = nt.NT-1 order by Id desc) d where rownum = 1
        ) phigh

Update2:

The following testable query got the error.

with t as (
  select 10 as cnt, 1 as Id from dual
), 
x as (
    select ntile(cnt) over ( partition by cnt order by Id ) NT, Id
    from t
)
select  n.NT, 
        high.Id a, 
        phigh.Id
from    (select distinct NT from x) n 
        outer apply (select * from x where x.NT = n.NT and rownum = 1 order by Id) low
        outer apply (select * from (select * from x where x.NT = n.NT order by Id desc) d where rownum = 1) high
        outer apply (select * from (select * from x where x.NT = n.NT - 1 order by Id desc) d where rownum = 1) phigh

The following code works.

with t as (
  select 10 as cnt, 1 as Id from dual
), 
x as (
    select ntile(cnt) over ( partition by cnt order by Id ) NT, Id
    from t
)
select  n.NT, 
        --high.Id a, 
        phigh.Id
from    (select distinct NT from x) n 
        outer apply (select * from x where x.NT = n.NT and rownum = 1 order by Id) low
        --outer apply (select * from (select * from x where x.NT = n.NT order by Id desc) d where rownum = 1) high
        outer apply (select * from (select * from x where x.NT = n.NT - 1 order by Id desc) d where rownum = 1) phigh

Answer 1

You should use different aliases for main query and cte and now they are the same n:

with n_cte as (
    select /*+ materialize*/ ntile(cnt) over ( partition by cnt order by Id ) NT, Id
    from tab
)
select  n.NT, phigh.Id 
from    (select distinct NT from n_cte) n 
outer apply (
    select * 
    from n_cte where n.NT = n_cte.NT and rownum = 1 order by Id
) low
outer apply (
    select * 
    from (select * from n_cte where n_cte.NT = n.NT order by Id desc) d where rownum = 1
) high
outer apply (
    select * 
    from (select * from n_cte where n_cte.NT = n.NT-1 order by Id desc) d where rownum = 1
) phigh

db<>fiddle demo

Another option could be adding SELECT /*+ materialize*/ ... hint inside cte part.

Answer 2

I found renaming the column names shared in high and 'phigh` works.

with t as (
  select 10 as cnt, 1 as Id from dual
), 
x as (
    select ntile(cnt) over ( partition by cnt order by Id ) NT, Id
    from t
)
select  n.NT, 
        high.Id a, 
        phigh.Id
from    (select distinct NT from x) n 
        outer apply (select * from x where x.NT = n.NT and rownum = 1 order by Id) low
        outer apply (select Id z from (select * from x where x.NT = n.NT order by Id desc) d where rownum = 1) high
        outer apply (select * from (select * from x where x.NT = n.NT - 1 order by Id desc) d where rownum = 1) phigh

But it's really not a solution for me (the question owner). The code will be dynamically generated and there will be many columns.

It seems Oracle has an issue when multiple subqueries return columns with the same name and these columns are in the select clause.

Solution 2:

Changing outer apply (select * from x where x.NT = n.NT and rownum = 1 order by Id) low to outer apply (select * from x where x.NT = 1 and rownum = 1 order by Id) low will also get rid of the error.