You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
ROWNUM→LIMIT optimization breaks Oracle compatibility with aggregation/ORDER BY/DISTINCT/GROUP BY #12
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
SELECTCOUNT(*) 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 <=5ORDER 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=60SELECT category, SUM(amount)
FROM test_group
WHERE ROWNUM <=4GROUP 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:
Activity
fix: restrict ROWNUM→LIMIT optimization to prevent incorrect results
test: add regression tests for ROWNUM with ORDER BY/DISTINCT/GROUP BY…
fix: restrict ROWNUM→LIMIT optimization to prevent incorrect results
test: add regression tests for ROWNUM with ORDER BY/DISTINCT/GROUP BY…