Bug: ROWNUM counter not reset in correlated subqueries

[rophy]

Bug Report: ROWNUM Counter Not Reset in Correlated Subqueries

Summary

When ROWNUM is used in a correlated subquery, the es_rownum counter is not reset between invocations, causing incorrect values to be returned. Each correlated execution should start with ROWNUM=1, but instead the counter continues incrementing across all invocations.

Environment

• IvorySQL Version: 5beta1 (PostgreSQL 18beta1)
• Branch: origin/feat/rownum (commit c1ea1b5)
• Compatibility Mode: Oracle

Bug Description

In Oracle, when a correlated subquery contains ROWNUM, the counter resets to 1 for each execution of the subquery. IvorySQL does not reset the counter, causing it to continue incrementing across multiple correlated invocations.

Steps to Reproduce

1. Create Test Data

CREATE TABLE test_data (
    id INT,
    name VARCHAR2(50),
    value NUMBER
);

INSERT INTO test_data VALUES (1, 'Alice', 100);
INSERT INTO test_data VALUES (2, 'Bob', 200);
INSERT INTO test_data VALUES (3, 'Charlie', 150);
INSERT INTO test_data VALUES (4, 'David', 300);
INSERT INTO test_data VALUES (5, 'Eve', 250);

2. Run Correlated Subquery

SELECT 
    id,
    name,
    (SELECT ROWNUM FROM (
        SELECT * FROM test_data t2 
        WHERE t2.id = t1.id
        ORDER BY value DESC
    ) sub) as correlated_rn
FROM test_data t1
ORDER BY id;

Expected Behavior (Oracle 23c)

 ID NAME      CORRELATED_RN
--- --------- -------------
  1 Alice                 1
  2 Bob                   1
  3 Charlie               1
  4 David                 1
  5 Eve                   1

Explanation: Each correlated execution sees ROWNUM starting from 1.

Actual Behavior (IvorySQL)

 id |  name   | correlated_rn 
----|---------|---------------
  1 | Alice   |             3
  2 | Bob     |             6
  3 | Charlie |             9
  4 | David   |            12
  5 | Eve     |            15

Explanation: The counter continues incrementing:

• First execution: ROWNUM goes from 1→3 (3 rows scanned in subquery)
• Second execution: ROWNUM continues from 3→6 (3 more rows)
• Third execution: ROWNUM continues from 6→9 (3 more rows)
• And so on...

Root Cause Analysis

Current Implementation

The ROWNUM counter (es_rownum) is stored in EState and incremented during scan operations:

// src/include/nodes/execnodes.h
typedef struct EState {
    ...
    int64       es_rownum;  /* current ROWNUM value */
    ...
} EState;

// src/include/executor/execScan.h (line ~225)
if (node->ps.state->es_rownum >= 0)
    node->ps.state->es_rownum++;

The Problem

When a correlated subquery executes:

1. The same EState is shared between outer query and subquery
2. The subquery's scans increment es_rownum
3. No reset occurs when the subquery completes
4. Next correlated invocation sees the incremented value

Why This Happens

Correlated subqueries are executed via ExecSubPlan() in src/backend/executor/nodeSubplan.c, but there is no save/restore mechanism for es_rownum around the subquery execution.

Proposed Solution

Option 1: Save/Restore Around Correlated Execution (Recommended)

Modify nodeSubplan.c to save and restore es_rownum:

// src/backend/executor/nodeSubplan.c

Datum
ExecSubPlan(SubPlanState *node, ExprContext *econtext, bool *isNull)
{
    EState     *estate = node->planstate->state;
    int64       saved_rownum;
    Datum       result;

    /* Save current ROWNUM counter */
    saved_rownum = estate->es_rownum;
    
    /* Reset counter for correlated subquery execution */
    estate->es_rownum = 0;
    
    /* Execute the subquery */
    result = /* ... existing subquery execution logic ... */;
    
    /* Restore ROWNUM counter */
    estate->es_rownum = saved_rownum;
    
    return result;
}

Option 2: Per-Query Context Counter

Create a separate ROWNUM context for each query level, similar to how Oracle manages its pseudo-column state. This would require more extensive changes to the executor.

Recommended Approach

Option 1 is simpler and aligns with Oracle's behavior. The save/restore pattern is already used in PostgreSQL for other execution state (e.g., snapshot handling).

Test Case

Add to src/oracle_test/regress/sql/rownum.sql:

-- Test ROWNUM in correlated subquery (should reset for each execution)
SELECT 
    id,
    name,
    (SELECT ROWNUM FROM (
        SELECT * FROM test_data t2 
        WHERE t2.id = t1.id
        ORDER BY value DESC
    ) sub) as correlated_rn
FROM test_data t1
ORDER BY id;

-- Expected: All correlated_rn values should be 1

Expected output:

 id |  name   | correlated_rn 
----|---------|---------------
  1 | Alice   |             1
  2 | Bob     |             1
  3 | Charlie |             1
  4 | David   |             1
  5 | Eve     |             1

Impact

Severity: HIGH

This bug affects any query using ROWNUM in correlated subqueries, which is a common Oracle pattern for:

• Row numbering within groups
• Finding the Nth item per category
• Pagination within correlated contexts

Workaround

None available. The correlated subquery pattern cannot be reliably used with ROWNUM in current IvorySQL.

Additional Context

Related Code Locations

1. Counter Management:

   • src/include/nodes/execnodes.h - EState definition
   • src/backend/executor/execUtils.c - EState initialization
   • src/include/executor/execScan.h - Counter increment

2. Subquery Execution:

   • src/backend/executor/nodeSubplan.c - Subplan execution
   • ExecInitSubPlan() - Subplan initialization
   • ExecSubPlan() - Subplan execution (WHERE FIX NEEDED)

3. Related Functions:

   • ExecReScanSubPlan() - Subplan rescanning
   • ExecEndSubPlan() - Subplan cleanup

Oracle Documentation Reference

From Oracle Database SQL Language Reference:

"For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on."

Key point: "for each query" - implying reset behavior for subqueries.

Verification in Oracle

Tested in Oracle Database 23c Free (container-registry.oracle.com/database/free:23.26.0.0-lite):

SQL> SELECT id, (SELECT ROWNUM FROM (SELECT * FROM test WHERE id = t.id)) as rn
     FROM test t;

ID         RN
---------- ----------
1          1
2          1
3          1

Confirms Oracle resets ROWNUM for each correlated execution.

Related Issues

• This bug is independent of the ROWNUM+ORDER BY bug (fixed in PR #XXX)
• The ROWNUM+ORDER BY fix operates at planner level
• This bug requires executor-level changes

Proposed Patch

A minimal patch would modify ExecSubPlan() in src/backend/executor/nodeSubplan.c:

@@ -XXX,YY +XXX,ZZ @@ ExecSubPlan(SubPlanState *node, ...)
 {
     PlanState  *planstate = node->planstate;
     SubLinkType subLinkType = node->subLinkType;
+    EState     *estate = planstate->state;
+    int64       saved_rownum = -1;
     ...
     
+    /* Save and reset ROWNUM for correlated subquery */
+    if (estate && estate->es_rownum >= 0)
+    {
+        saved_rownum = estate->es_rownum;
+        estate->es_rownum = 0;
+    }
+    
     /* Execute subquery */
     ...
     
+    /* Restore ROWNUM counter */
+    if (saved_rownum >= 0)
+        estate->es_rownum = saved_rownum;
+    
     return result;
 }

Priority

HIGH - This is a critical Oracle compatibility issue affecting a commonly used pattern.

Labels

• bug
• oracle-compatibility
• rownum
• executor
• correlated-subquery

---

Reported by: Testing during ROWNUM ORDER BY bug fix implementation
Date: 2025-11-29
IvorySQL Version: 5beta1 (commit c1ea1b5)