Consider overlapping scalar subquery and parent query computation

### 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_

Start with ScalarSubqueryExpr and ScalarSubqueryExec, then trace how uncorrelated scalar subqueries and parent queries are evaluated in parallel. Examine how morsel-driven parallelism affects the two proposed strategies. Done requires selecting and implementing a strategy, including the needed synchronization or lazy evaluation behavior, but the issue does not define which alternative to choose.

Written by the indexing model from the issue text.