ROWNUM→LIMIT optimization breaks Oracle compatibility with aggregation/ORDER BY/DISTINCT/GROUP BY

[rophy]

Summary

The ROWNUM→LIMIT optimization in src/backend/optimizer/plan/planner.c applies too broadly, causing incorrect results when ROWNUM is used with aggregation, ORDER BY, DISTINCT, or GROUP BY clauses. This breaks Oracle compatibility.

Root Cause

PostgreSQL's LIMIT is applied after ORDER BY, DISTINCT, GROUP BY, and aggregation operations, while Oracle's ROWNUM filter is applied before these operations. The current transform_rownum_to_limit() function rewrites ROWNUM <= N to LIMIT N regardless of the query structure, leading to semantic differences.

Test Results

All tests were verified against Oracle Database 23.26 Free:

❌ Test 1: COUNT with ROWNUM

SELECT COUNT(*) FROM test_table WHERE ROWNUM <= 5;

Database	Result	Expected
Oracle 23.26	5	✅ Counts first 5 rows
IvorySQL	10	❌ Counts all rows, LIMIT applied after COUNT

❌ Test 2: ORDER BY with ROWNUM

SELECT id, val FROM test_table WHERE ROWNUM <= 5 ORDER BY val;

Database	Result	Expected
Oracle 23.26	IDs: 2,4,1,5,3 Values: 50,75,100,150,200	✅ First 5 arbitrary rows, then sorted
IvorySQL	IDs: 6,2,10,4,8 Values: 25,50,60,75,90	❌ All rows sorted first, then top 5 (wrong rows!)

❌ Test 3: DISTINCT with ROWNUM

-- Table has: A, A, B, B, C, C (6 rows)
SELECT DISTINCT category FROM test_distinct WHERE ROWNUM <= 3;

Database	Result	Expected
Oracle 23.26	A, B (2 rows)	✅ DISTINCT applied to first 3 rows
IvorySQL	A, B, C (3 rows)	❌ DISTINCT on all rows, then limited

❌ Test 4: GROUP BY with ROWNUM

-- Table has: A=10, A=20, B=30, B=40, C=50, C=60
SELECT category, SUM(amount)
FROM test_group
WHERE ROWNUM <= 4
GROUP BY category;

Database	Result	Expected
Oracle 23.26	A=30, B=70 (2 groups)	✅ Groups first 4 rows only
IvorySQL	A=30, B=70, C=110 (3 groups)	❌ All rows grouped, then 3 groups returned

Suggested Fix

Restrict the ROWNUM→LIMIT optimization to queries with no higher-level relational processing. Only apply the transformation when:

• parse->commandType == CMD_SELECT
• parse->groupClause == NIL
• parse->groupingSets == NIL
• !parse->hasAggs
• parse->distinctClause == NIL && !parse->hasDistinctOn
• parse->sortClause == NIL
• !parse->hasWindowFuncs
• !parse->setOperations
• !parse->hasTargetSRFs

For all other queries, leave the ROWNUM predicate in place and let the executor handle it with proper Oracle semantics.

Impact

This bug affects:

• Oracle migration scenarios using ROWNUM with aggregation
• Top-N queries with ORDER BY (common Oracle pattern)
• Any ROWNUM usage combined with DISTINCT or GROUP BY
• Data correctness in production workloads

Reproduction

Test suite available in commit 7580c70 on branch test/rownum. Run tests with:

# Test against Oracle Database 23.26 Free
docker exec ivorysql-oracle-1 sqlplus / as sysdba < test_coderabbit_claims.sql

# Test against IvorySQL
psql -h localhost -p 1521 -d testdb < test_coderabbit_claims.sql

Related

• Original implementation: PR Add Oracle ROWNUM Pseudocolumn Support #11
• Code location: src/backend/optimizer/plan/planner.c:252 (transform_rownum_to_limit)