ROWNUM: INTERSECT/EXCEPT shares counter across both sides (differs from Oracle)

[rophy]

Summary

When using ROWNUM with INTERSECT or EXCEPT set operations, IvorySQL shares the ROWNUM counter across both sides of the operation. Oracle resets ROWNUM independently for each side.

Oracle Behavior

SELECT ROWNUM as rn, id FROM (SELECT 1 as id FROM dual UNION ALL SELECT 2 FROM dual UNION ALL SELECT 3 FROM dual)
INTERSECT
SELECT ROWNUM as rn, id FROM (SELECT 1 as id FROM dual UNION ALL SELECT 2 FROM dual UNION ALL SELECT 3 FROM dual);

-- Oracle Result (3 rows):
-- rn | id
-- ---+---
--  1 |  1
--  2 |  2
--  3 |  3

Each side produces (1,1), (2,2), (3,3) independently, so the intersection returns all 3 rows.

IvorySQL Behavior

-- IvorySQL Result (0 rows):
-- rn | id
-- ---+---
-- (0 rows)

The left side produces (1,1), (2,2), (3,3), but the right side continues the counter and produces (4,1), (5,2), (6,3). Since there's no overlap in ROWNUM values, the intersection returns 0 rows.

Technical Details

INTERSECT and EXCEPT use HashSetOp or sorted SetOp nodes, which have two child paths. The ROWNUM counter (es_rownum) is shared across both children during execution.

The fix would require resetting es_rownum before executing the second child of the SetOp node.

Related

This is similar to the UNION ROWNUM fix (commit b6c483c), but requires changes to the SetOp executor rather than the Append/MergeAppend executors.