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:
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__BABHFGAA "Restrictions on the row_limiting_clause [...] If the select list contains columns with identical names and you specify the row_limiting_clause, then an ORA-00918 error occurs. This error orccurs 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.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?
Notice, I've asked this question also on Stack Overflow:
https://stackoverflow.com/q/51768880/521799
I will ask around internally, but I thought this bug only impact 12.1.0.1. I cannot reproduce on any version above that, eg this from 12.1.0.2
SQL> create table t as select rownum id from dual connect by level <= 10;
Table created.
SQL> create table u as select rownum id from dual connect by level <= 10;
Table created.
SQL>
SQL>
SQL> SELECT t.id, u.id FROM t, u FETCH FIRST 10 ROWS ONLY;
ID ID
---------- ----------
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
10 rows selected.
SQL> select id, id from t FETCH FIRST 10 ROWS ONLY;
ID ID
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
10 rows selected.
SQL> select id, id from t order by 1 FETCH FIRST 10 ROWS ONLY;
ID ID
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
10 rows selected.