Error ORA-00918 not raised as expected when using the FETCH command

oracleoracle-12c

The Oracle 12c documentation about FETCH is telling the below

If the select list contains columns with identical names and you
specify the row_limiting_clause, then an ORA-00918 error occurs. This
error occurs whether the identically named columns are in the same
table or in different tables. You can work around this issue by
specifying unique column aliases for the identically named columns.

But in Oracle Live SQL i am able to run the below Query succesfully.
(there we will have 2 columns returned in the result with same name "department_id")

select *
from hr.employees e
join hr.departments d
on (e.department_id = d.department_id)
fetch next 10 rows with ties
;

Could someone explain me if this is where we should expect the Oracle Error or it the documentation meant another scenario ?

Thanks

SD

Best Answer

Do not believe everything you read in Oracle documentation.

Try the below:

CREATE TABLE a (test NUMBER not null, test1 VARCHAR(6) not null, id VARCHAR(5) not null); 
CREATE TABLE b ( id VARCHAR(5) not null, lang VARCHAR(5) not null, test1 VARCHAR(6) not null, test2 VARCHAR(5) not null );

insert into a values(1, 'AA',10);
insert into a values(2, 'BB',20);
insert into a values(3, 'CC',30);
commit;

insert into b values(10, 'eng','AA','test2');
insert into b values(20, 'tamil','BB','test2');
commit;

Then:

1) SELECT aa.test, aa.test1, aa.id, bb.test1, bb.test2 FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ;
2) SELECT aa.test, aa.test1, aa.id, bb.test1, bb.test2 FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ORDER BY 1 FETCH FIRST 3 ROWS ONLY;
3) SELECT aa.test, aa.test1, aa.id, bb.test1 as t1, bb.test2 FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ORDER BY 1 FETCH FIRST 3 ROWS ONLY;
4) SELECT * FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ORDER BY 1 FETCH FIRST 3 ROWS ONLY;

1), 3) and 4) runs successfully on 12.1.0.2, 2) throws ORA-00918.

On 12.2.0.1, all 4 runs successfully.

The one thing to keep in mind: never rely on implicit behaviour you can not control. Use explicit, unique aliases.

Related Question