NestedLoopJoinExec spill fallback evaluates the left input twice #24661

Problem

When NestedLoopJoinExec cannot fully buffer its left input, the spill fallback executes the left child again:

1. The normal path calls left.execute(0) and starts collecting the stream.
2. Collection fails with ResourcesExhausted.
3. The fallback calls left.execute(0) again to create the spill input.

The shared spill state prevents every probe partition from repeating the scan, but the left input is still evaluated twice overall.

This behavior was discussed during review of #21448:

• feat: Add memory-limited execution for NestedLoopJoinExec #21448 (comment)
• feat: Add memory-limited execution for NestedLoopJoinExec #21448 (comment)

This is a focused follow-up to #15760 and #21448.

Why this matters

Repeated evaluation can duplicate expensive scans, network reads, or other work performed by the left subtree. It also increases pressure during an OOM recovery path.

This issue does not propose changing ExecutionPlan::execute semantics. Each call should continue producing an independent stream, as established by #21565. The goal is to avoid the redundant call where possible.

Proposed direction

Refactor the initial left-side collection so that, when its reservation cannot grow, the fallback can retain:

• Already buffered batches.
• The batch that encountered memory pressure.
• The remaining original input stream.

The fallback could then spill or process that state directly instead of executing the left child again.

Alternative designs that guarantee a single left-side evaluation would also address the issue.

Testing

Add a test execution plan that:

• Counts calls to execute().
• Produces an independent stream on every call.
• Is used as the left child of NestedLoopJoinExec.
• Forces the memory-limited spill path.

Assert that:

• The left child is executed exactly once.
• Spill occurs.
• The query returns the expected results.
• Memory reservations and temporary spill files are released after completion or cancellation.

References

• Parent tracking issue: Memory limited nest loop join #15760
• Initial implementation: feat: Add memory-limited execution for NestedLoopJoinExec #21448
• Commit: f9239a197f2c49b69a87efb5ef25fd67b13b7ca1
• Independent stream semantics: fix: LazyMemoryExec should produce independent streams per execute() #21565