opt: TryRemapJoinOuterColsRight prevents optimal plan

[mgartner]

The TryRemapJoinOuterColsRight rule, which is newly introduced in v23.1, is preventing an optimal query plan from being produced.

Here's an example test case showing a suboptimal query plan first, and an optimal query plan when TryRemapJoinOuterColsRight is disabled:

exec-ddl
CREATE TABLE parent (
  id INT PRIMARY KEY
)
----

exec-ddl
CREATE TABLE child (
  id INT PRIMARY KEY,
  parent_id INT NOT NULL REFERENCES parent(id)
)
----

opt
SELECT
  id,
  (SELECT child.parent_id FROM parent WHERE id = child.parent_id)
FROM child
----
project
 ├── columns: id:1!null parent_id:9!null
 ├── key: (1)
 ├── fd: (1)-->(9)
 ├── inner-join (hash)
 │    ├── columns: child.id:1!null child.parent_id:2!null parent.id:5!null parent_id:8!null
 │    ├── multiplicity: left-rows(exactly-one), right-rows(zero-or-more)
 │    ├── key: (1)
 │    ├── fd: (1)-->(2), (5)==(2,8), (8)==(2,5), (2)==(5,8)
 │    ├── scan child
 │    │    ├── columns: child.id:1!null child.parent_id:2!null
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2)
 │    ├── project
 │    │    ├── columns: parent_id:8!null parent.id:5!null
 │    │    ├── key: (5)
 │    │    ├── fd: (5)==(8), (8)==(5)
 │    │    ├── scan parent
 │    │    │    ├── columns: parent.id:5!null
 │    │    │    └── key: (5)
 │    │    └── projections
 │    │         └── parent.id:5 [as=parent_id:8, outer=(5)]
 │    └── filters
 │         └── parent.id:5 = child.parent_id:2 [outer=(2,5), constraints=(/2: (/NULL - ]; /5: (/NULL - ]), fd=(2)==(5), (5)==(2)]
 └── projections
      └── parent_id:8 [as=parent_id:9, outer=(8)]

opt disable=(TryRemapJoinOuterColsRight)
SELECT
  id,
  (SELECT child.parent_id FROM parent WHERE id = child.parent_id)
FROM child
----
project
 ├── columns: id:1!null parent_id:9!null
 ├── key: (1)
 ├── fd: (1)-->(9)
 ├── scan child
 │    ├── columns: child.id:1!null child.parent_id:2!null
 │    ├── key: (1)
 │    └── fd: (1)-->(2)
 └── projections
      └── child.parent_id:2 [as=parent_id:9, outer=(2)]

cc @DrewKimball

Jira issue: CRDB-27561

[mgartner]

This is a tricky case. EliminateJoinUnderProjectLeft doesn't fire because the parent.id:5 column on the RHS of the join used in the top-level project, so the RHS of the join is needed.

[rytaft]

From triage meeting:

• Should this be an exploration rule rather than normalization?
• We should probably make the join elimination rules more powerful
• We shouldn't spend too much time on this. If it's too hard, can move this issue to Backlog