Consider overlapping scalar subquery and parent query computation

[neilconway]

Is your feature request related to a problem or challenge?

Right now (if/when #21240 lands), uncorrelated scalar subqueries are evaluated eagerly and in parallel, and then the parent query is evaluated. We could consider some alternative strategies, such as:

1. Continue evaluating scalar subqueries eagerly and in parallel, but also allow parent query computation to be overlapped with subquery computation. This would require a synchronization point between ScalarSubqueryExpr evaluation and the evaluation of the corresponding subquery itself (via ScalarSubqueryExec); if the former happens before the latter, we'll need to arrange to block parent query evaluation until the subquery results are available.
2. Alternatively, we could explore lazily evaluating scalar subqueries. This might exploit parallelism less effectively, but it would avoid wasting work (e.g., in the case that a subquery doesn't ever need to be evaluated, e.g., due to another predicate in the WHERE clause).

Not sure how either of these approaches interacts with morsel-driven parallelism...

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response