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
Bug: ROWNUM in SELECT list of subquery returns 0 instead of actual values #13
CREATETABLEtest_sub (
id INT,
name TEXT,
value NUMERIC
);
INSERT INTO test_sub VALUES
(1, 'Alice', 100),
(2, 'Bob', 200),
(3, 'Charlie', 150),
(4, 'David', 300),
(5, 'Eve', 250);
-- Test: ROWNUM in SELECT list of subquerySELECT*FROM (
SELECT ROWNUM as rn, id, name, value
FROM test_sub
ORDER BY value DESC
) WHERE ROWNUM <=3;
Expected Behavior (Oracle 23.26)
RN | ID | NAME | VALUE
----|----+-------+-------
4 | 4 | David | 300
5 | 5 | Eve | 250
2 | 2 | Bob | 200
Explanation: ROWNUM is assigned to each row BEFORE the ORDER BY is applied. So David (id=4) gets ROWNUM=4, Eve (id=5) gets ROWNUM=5, Bob (id=2) gets ROWNUM=2, etc. After sorting by value DESC, these original ROWNUM values are preserved in the output.
Actual Behavior (IvorySQL)
rn | id | name | value
----+----+-------+-------
0 | 4 | David | 300
0 | 5 | Eve | 250
0 | 2 | Bob | 200
Bug: All rn values are 0 instead of the actual ROWNUM values (4, 5, 2).
Impact
This bug affects queries that need to capture and return the original ROWNUM value from before a sort operation. Common use cases include:
Debugging queries - capturing original row order before sorting
Pagination with sorting - preserving original row numbers
Data provenance - tracking which rows were selected from the original dataset
Root Cause Analysis
The ROWNUM pseudocolumn appears to not preserve its value when used as a column in the SELECT list of a subquery. When the subquery result is consumed by the outer query, ROWNUM has lost its original value and defaults to 0.
Test Case Location
This bug was discovered in:
Test:src/pl/plisql/src/sql/plisql_rownum.sql - Test 11
Test Report:test_report_rownum.md - Section 10, Test 11
Related Work
This issue was found during comprehensive ROWNUM testing comparing IvorySQL against Oracle Database 23.26 Free. Out of 89 total tests (76 SQL + 13 PL/iSQL), this is the only known incompatibility.
Workaround
None currently available. Users needing to capture ROWNUM values before sorting cannot use this pattern and would need to restructure their queries.
Additional Context
All other ROWNUM functionality works correctly (98.9% compatibility)
ROWNUM in WHERE clauses works correctly
ROWNUM with ORDER BY in outer query works correctly
The issue only appears when ROWNUM is in the SELECT list of a subquery
I've completed an in-depth investigation into this bug, including comparative testing against Oracle Database 23.26 and analysis of PostgreSQL's executor architecture.
Root Cause Identified
The bug occurs because ROWNUM expressions in SELECT lists are being re-evaluated instead of materialized as constants.
Execution Flow
When processing this query:
SELECT*FROM (
SELECT ROWNUM as rn, id, name, value
FROM test_sub
ORDER BY value DESC
) WHERE ROWNUM <=3;
The execution plan is:
Inner SeqScan on test_sub → assigns ROWNUM 1,2,3,4,5
Sort node → reorders by value DESC
Outer SubqueryScan → reads sorted results
The Problem:
PostgreSQL/IvorySQL uses "virtual tuples" for projection results
Virtual tuples don't store actual values—they store references/pointers to expression evaluation
When the SubqueryScan reads the rn column, the ROWNUM expression gets re-evaluated in the SubqueryScan's context
The SubqueryScan has its own ROWNUM counter (starting at 1), so all rn values become 1
Why Current Implementation Returns 0
The original implementation evaluates ROWNUM from estate->es_rownum, which is 0 in the SubqueryScan context before any rows are processed.
Oracle's Actual Behavior (Verified)
I tested Oracle Database 23.26 extensively to understand the correct semantics:
Test Results:
-- Test 1: ROWNUM persists through ORDER BYSELECT*FROM (
SELECT ROWNUM as rn, id, name, value
FROM test_rownum
ORDER BY value DESC
) WHERE ROWNUM <=3;
-- Oracle Result:
RN | ID | NAME | VALUE
----|----+-------+-------4 | 4 | David | 3005 | 5 | Eve | 2502 | 2 | Bob | 200-- Test 2: Nested ROWNUM are independentSELECT ROWNUM as outer_rn, inner_rn, id FROM (
SELECT ROWNUM as inner_rn, id FROM test_rownum
) WHERE ROWNUM <=3;
-- Oracle Result:
OUTER_RN | INNER_RN | ID
----------|----------|----1 | 1 | 12 | 2 | 23 | 3 | 3-- Test 3: ROWNUM values support arithmetic (proving they're constants)SELECT inner_rn *10as times_ten FROM (
SELECT ROWNUM as inner_rn, id FROM test_rownum ORDER BY id DESC
) WHERE ROWNUM <=3;
-- Oracle Result:
TIMES_TEN
-----------50-- 5 * 1040-- 4 * 1030-- 3 * 10
Key Finding: Oracle materializes ROWNUM values when they appear in a SELECT list. The value becomes a stored constant that persists through sorts, joins, and nested queries.
Proposed Solution
Implement forced materialization of tuples containing ROWNUM expressions:
// src/backend/executor/execExpr.c - in ExecBuildProjectionInfo()// When building projection, check if target list contains ROWNUMif (expression_contains_rownum(target))
projection->pi_needsMaterialization= true;
✅ Correct Semantics: Matches Oracle's materialization behavior
✅ Nested Query Support: Works correctly for arbitrarily deep nesting
✅ Minimal Overhead: Only materializes when necessary (ROWNUM in target list)
✅ Localized Changes: Limited to executor, no planner changes needed
✅ Future-Proof: Handles other volatile expressions that may need similar treatment
Alternative Approaches Considered
Scan-specific counter (attempted): Added ss_rownum to ScanState, but failed because SubqueryScan creates new context
Snapshot in ExprContext: Would fail for nested queries due to context switching
Planner-level constant folding: Too invasive, requires deep planner integration
Next Steps
Implement the materialization solution
Test with comprehensive test suite including:
Single-level subqueries with ORDER BY
Multi-level nested subqueries
ROWNUM in both inner and outer queries
Arithmetic operations on stored ROWNUM values
Verify all 235 oracle-check tests still pass
Performance testing to ensure materialization overhead is acceptable
Files to Modify
src/include/nodes/execnodes.h - Add pi_needsMaterialization flag
src/backend/executor/execExpr.c - Detect ROWNUM in target lists
src/include/executor/execScan.h - Force materialization
src/backend/executor/execExprInterp.c - Helper to detect ROWNUM expressions
Estimated implementation time: 4-6 hours including testing.
✅ Fix implemented and tested
✅ All regression tests pass
✅ Ready for review
Related Issues
During testing, discovered a separate bug: #14 - ROWNUM counter not reset in correlated subqueries. This is an independent issue that requires a different fix at the executor level.
Activity
rophy commentedon Nov 29, 2025
Investigation Update
I've completed an in-depth investigation into this bug, including comparative testing against Oracle Database 23.26 and analysis of PostgreSQL's executor architecture.
Root Cause Identified
The bug occurs because ROWNUM expressions in SELECT lists are being re-evaluated instead of materialized as constants.
Execution Flow
When processing this query:
The execution plan is:
The Problem:
rncolumn, the ROWNUM expression gets re-evaluated in the SubqueryScan's contextrnvalues become 1Why Current Implementation Returns 0
The original implementation evaluates ROWNUM from
estate->es_rownum, which is 0 in the SubqueryScan context before any rows are processed.Oracle's Actual Behavior (Verified)
I tested Oracle Database 23.26 extensively to understand the correct semantics:
Test Results:
Key Finding: Oracle materializes ROWNUM values when they appear in a SELECT list. The value becomes a stored constant that persists through sorts, joins, and nested queries.
Proposed Solution
Implement forced materialization of tuples containing ROWNUM expressions:
Implementation Plan
1. Add materialization flag to ProjectionInfo
2. Detect ROWNUM during expression building
3. Force materialization after projection
Why This Works
ExecMaterializeSlot()converts the virtual tuple to a physical tuple, storing actual valuesBenefits
✅ Correct Semantics: Matches Oracle's materialization behavior
✅ Nested Query Support: Works correctly for arbitrarily deep nesting
✅ Minimal Overhead: Only materializes when necessary (ROWNUM in target list)
✅ Localized Changes: Limited to executor, no planner changes needed
✅ Future-Proof: Handles other volatile expressions that may need similar treatment
Alternative Approaches Considered
ss_rownumto ScanState, but failed because SubqueryScan creates new contextNext Steps
Files to Modify
src/include/nodes/execnodes.h- Addpi_needsMaterializationflagsrc/backend/executor/execExpr.c- Detect ROWNUM in target listssrc/include/executor/execScan.h- Force materializationsrc/backend/executor/execExprInterp.c- Helper to detect ROWNUM expressionsEstimated implementation time: 4-6 hours including testing.
rophy commentedon Nov 29, 2025
✅ Bug Fixed
This issue has been fixed! The fix implements ROWNUM pushdown at the planner level to ensure ROWNUM is evaluated before the ORDER BY sort.
Solution Implemented
The fix modifies
create_projection_plan()insrc/backend/optimizer/plan/createplan.cto:Test Results
With the fix applied:
Result:
Matches Oracle 23.26 behavior exactly!
Files Changed
src/backend/optimizer/plan/createplan.c- ROWNUM pushdown logicsrc/backend/executor/execExpr.c- ROWNUM detection helperssrc/backend/executor/nodeResult.c- Materialization supportsrc/include/executor/execScan.h- Materialization in scanssrc/include/nodes/execnodes.h- Materialization flagsrc/oracle_test/regress/sql/rownum.sql- Added test casessrc/oracle_test/regress/expected/rownum.out- Updated expected outputStatus
✅ Fix implemented and tested
✅ All regression tests pass
✅ Ready for review
Related Issues
During testing, discovered a separate bug: #14 - ROWNUM counter not reset in correlated subqueries. This is an independent issue that requires a different fix at the executor level.
fix: push ROWNUM evaluation before Sort in subqueries
fix: push ROWNUM evaluation before Sort in subqueries
fix: push ROWNUM evaluation before Sort in subqueries
fix: push ROWNUM evaluation before Sort in subqueries
fix: push ROWNUM evaluation before Sort in subqueries
fix: push ROWNUM evaluation before Sort in subqueries