sql: figure out how to handle subqueries by distSQLSpecExecFactory

[yuzefovich]

• how do we handle subqueries?
  This question comes from the fact that when we're serializing an expression of the main tree, it might be referencing the results of subqueries' evaluation, but we don't have those during execbuilding with current "single-path over memo tree" approach. I see two solutions:
  • 1. perform some kind of lazy serialization for expressions (this would mean that physical plan tree that comes out from the builder would be incomplete) - this would allow us to keep the single traversal approach to building, but probably will come with other concerns
  • 2. change the builder to perform several traversals, in the first one we would build the subqueries, then we would probably execute them, and then we would run the second memo traversal to build the remaining parts of the full plan given that we now have results of subqueries' evaluation. Not sure how hard and how breaking of a change this would be.
• how do we connect appropriate planNodes in the physical plan?
  This question came up when I looked into implementing ConstructBuffer and ConstructScanBuffer - it seems like we need to have some way of identifying a planNode that is wrapped into the physical plan so that we can instantiate another planNode-to-be-wrapped, possibly in a different "tree" within the whole physical plan. This needs more investigation.

Jira issue: CRDB-4072

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!