Duplicate columns in Oracle query using row limiting clause

Question

Since Oracle 12c, we can finally use the SQL standard row limiting clause like this:

SELECT * FROM t FETCH FIRST 10 ROWS ONLY

Now, in Oracle 12.1, there was a limitation that is quite annoying when joining tables. It's not possible to have two columns of the same name in the SELECT clause, when using the row limiting clause. E.g. this raises ORA-00918 in Oracle 12.1

SELECT t.id, u.id FROM t, u FETCH FIRST 10 ROWS ONLY

This is a restriction is documented in the manual for all versions 12.1, 12.2, 18.0:

The workaround is obviously to alias the columns

SELECT t.id AS t_id, u.id AS u_id FROM t, u FETCH FIRST 10 ROWS ONLY

Or to resort to "classic" pagination using ROWNUM or window functions.

Curiously, though, the original query with ambiguous ID columns runs just fine from Oracle 12.2 onwards. Is this a documentation bug, or an undocumented feature?

Answer 1

Seems in this case when you are using the row limiting clause, Oracle internally calling the ROW_NUMBER() function where it using the column name in OVER clause Like ROW_NUMBER OVER(ORDER BY ID). because of this you are getting the ORA-00918 error.