sql: implement differential testing of physical plans produced by the old and new factories

[yuzefovich]

With the work being tracked by #47473 we're introducing a new exec.Factory implementation that creates processor specs directly and constructs the physical plan without the need to have an intermediate planNode tree. Eventually new factory will replace the old one. However, to get there we need to be certain that we don't introduce any changes to the physical plans (apart from the ones that are deliberate, e.g. having partially distributed plans).

One approach that we could take for that is to use differential testing of the physical plans produced by the new factory on one side and the old factory + distsql physical planner on the other. The goal is to make sure that the plans are "equal deeply", and we probably can reuse some serialized representation of the whole plan for that (likely existing marshaling methods of the specs - which are protobufs - will be of use).

We need to think a bit more on how we would set the testing up. Some ideas:

• extend execbuild tests to include the physical planning step
• extend the logic test harness so that instead of running the query once and checking the expected output, it would run the query with both planning modes and confirm that serialized physical plans are the same (fakedist configs will need to be skipped)
• introduce some testing knob that would control whether the query is executed or a serialized physical plan is returned to the client (maybe we could have special EXPLAIN (EXPERIMENTAL_TEST) that would actually run the query twice with different factories and return ok/fail?) and have a roachtest that uses sqlsmith to generate queries .

Jira issue: CRDB-4112

[yuzefovich]

cc @asubiotto if you have some ideas.

[github-actions[bot]]

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!