Bug: ROWNUM in SELECT list of subquery returns 0 instead of actual values

[rophy]

Bug Description

When ROWNUM is used in the SELECT list of a subquery, it incorrectly returns 0 instead of preserving its actual values.

Environment

• IvorySQL Version: 5beta1 (PostgreSQL 18beta1)
• Oracle Version (reference): 23.26.0.0 Free
• Branch: test/rownum
• Commit: 1fdd54b

Reproduction Steps

CREATE TABLE test_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 subquery
SELECT * 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:

1. Debugging queries - capturing original row order before sorting
2. Pagination with sorting - preserving original row numbers
3. 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

[rophy]

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:

SELECT * FROM (
    SELECT ROWNUM as rn, id, name, value
    FROM test_sub
    ORDER BY value DESC
) WHERE ROWNUM <= 3;

The execution plan is:

1. Inner SeqScan on test_sub → assigns ROWNUM 1,2,3,4,5
2. Sort node → reorders by value DESC
3. 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 BY
SELECT * 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 |   300
  5 |  5 | Eve   |   250
  2 |  2 | Bob   |   200

-- Test 2: Nested ROWNUM are independent
SELECT 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 |  1
        2 |        2 |  2
        3 |        3 |  3

-- Test 3: ROWNUM values support arithmetic (proving they're constants)
SELECT inner_rn * 10 as 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 * 10
        40  -- 4 * 10
        30  -- 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:

Implementation Plan

1. Add materialization flag to ProjectionInfo

// src/include/nodes/execnodes.h
typedef struct ProjectionInfo
{
    ...existing fields...
    bool pi_needsMaterialization;  /* NEW: Contains volatile exprs like ROWNUM */
} ProjectionInfo;

2. Detect ROWNUM during expression building

// src/backend/executor/execExpr.c - in ExecBuildProjectionInfo()
// When building projection, check if target list contains ROWNUM
if (expression_contains_rownum(target))
    projection->pi_needsMaterialization = true;

3. Force materialization after projection

// src/include/executor/execScan.h - in ExecScanExtended()
if (projInfo)
{
    TupleTableSlot *result = ExecProject(projInfo);
    
    /* Materialize tuples containing ROWNUM to preserve values */
    if (projInfo->pi_needsMaterialization && !TTS_IS_PHYSICAL(result))
    {
        ExecMaterializeSlot(result);
    }
    
    return result;
}

Why This Works

1. Evaluation Once: ROWNUM is evaluated during projection when the value is correct
2. Storage: ExecMaterializeSlot() converts the virtual tuple to a physical tuple, storing actual values
3. No Re-evaluation: Subsequent reads access stored values, not expression evaluation
4. Nested Queries: Each level's ROWNUM values are independently materialized
5. Oracle Compatibility: Matches Oracle's behavior exactly

Benefits

✅ 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

1. Scan-specific counter (attempted): Added ss_rownum to ScanState, but failed because SubqueryScan creates new context
2. Snapshot in ExprContext: Would fail for nested queries due to context switching
3. Planner-level constant folding: Too invasive, requires deep planner integration

Next Steps

1. Implement the materialization solution
2. 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
3. Verify all 235 oracle-check tests still pass
4. 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.

[rophy]

✅ 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() in src/backend/optimizer/plan/createplan.c to:

1. Detect when ROWNUM appears in a projection above a Sort node
2. Push ROWNUM evaluation down to the scan level (before sort)
3. Convert ROWNUM to Var references in upper levels
4. Materialize ROWNUM values to prevent re-evaluation

Test Results

With the fix applied:

SELECT * FROM (
    SELECT ROWNUM as rn, id, name, value
    FROM test_sub
    ORDER BY value DESC
) WHERE ROWNUM <= 3;

Result:

 rn | id | name  | value 
----|----+-------+-------
  4 |  4 | David |   300  ✅ Correct!
  5 |  5 | Eve   |   250  ✅ Correct!
  2 |  2 | Bob   |   200  ✅ Correct!

Matches Oracle 23.26 behavior exactly!

Files Changed

• src/backend/optimizer/plan/createplan.c - ROWNUM pushdown logic
• src/backend/executor/execExpr.c - ROWNUM detection helpers
• src/backend/executor/nodeResult.c - Materialization support
• src/include/executor/execScan.h - Materialization in scans
• src/include/nodes/execnodes.h - Materialization flag
• src/oracle_test/regress/sql/rownum.sql - Added test cases
• src/oracle_test/regress/expected/rownum.out - Updated expected output

Status

✅ 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.