ROWNUM: LATERAL subquery doesn't reset counter for each outer row (differs from Oracle)

[rophy]

Summary

When using ROWNUM inside a LATERAL subquery, IvorySQL continues the ROWNUM counter across outer rows. Oracle resets ROWNUM to 1 for each new outer row.

Oracle Behavior

CREATE TABLE lat_test (id int);
INSERT INTO lat_test VALUES (1), (2), (3);

SELECT t.id as outer_id, sub.rn
FROM lat_test t
CROSS APPLY (SELECT ROWNUM as rn FROM lat_test lt WHERE lt.id <= t.id) sub
ORDER BY t.id, sub.rn;

-- Oracle Result:
-- outer_id | rn
-- ---------+----
--        1 |  1
--        2 |  1
--        2 |  2
--        3 |  1
--        3 |  2
--        3 |  3

ROWNUM resets to 1 for each outer row (id=1, id=2, id=3).

IvorySQL Behavior

SELECT t.id as outer_id, sub.rn
FROM lat_test t,
LATERAL (SELECT ROWNUM as rn FROM lat_test lt WHERE lt.id <= t.id) sub
ORDER BY t.id, sub.rn;

-- IvorySQL Result:
-- outer_id | rn
-- ---------+----
--        1 |  1
--        2 |  2
--        2 |  3
--        3 |  4
--        3 |  5
--        3 |  6

The ROWNUM counter continues across outer rows instead of resetting.

Technical Details

LATERAL subqueries are executed via SubqueryScan nodes. While SubqueryScan already has rownum_reset logic for correlated subqueries (where the subplan is re-evaluated for each outer row), LATERAL subqueries may take a different code path.

The fix would require ensuring that es_rownum is reset each time the LATERAL subquery is re-executed for a new outer row.

Related

• Issue Bug: ROWNUM counter not reset in correlated subqueries #14: ROWNUM counter reset in correlated subqueries (fixed)
• The existing SubqueryScan reset logic may need to be extended to cover LATERAL joins