PostgreSQL Library
Hey, we just uncovered a conversion bug, going from Oracle to PG.
BEWARE... The difference between ROWNUM and LIMIT...
Oracle use a WHERE condition: "ROWNUM <= 10" to limit the number of rows.
PG uses "LIMIT 10" to do the same thing.
The difference is that Oracle operates on the INCOMING ROWS (so as the rows are being selected, it applies this logic to stop early).
Whereas PG is NOT using this as a "predicate" for choosing rows... It uses it AFTER the query has been run/sorted.
So in Oracle:
SELECT max(some_val) from table1 where ROWNUM <= 10; — Will tell you the max of the first 10 rows it finds.
PG:
SELECT max(some_val) from table1 LIMIT 10; — Will find the TABLE1 max value, and then apply the LIMIT to that row
This means the ORDER OF OPERATION is different, and the LIMIT clause should be (likely) applied to a sub-query:
SELECT max(some_val) from (SELECT some_val from table1 LIMIT 10) vbsq;
Now, that takes the first 10 records it finds. Creates a set. Then the max is applied to that set.
Again, the simplest way to understand it is that Oracle operates on INCOMING values, and PG operates on OUTGOING value.
The UPSIDE to the PG approach is that the LIMIT is applied AFTER the sort (ORDER BY).
4 · 2.3K ·