opt: split disjunction in join conditions in more cases

[rytaft]

Is your feature request related to a problem? Please describe.
When a join condition includes a disjunction (e.g. a OR b), in some cases we can remove the disjunction by splitting the join into a UNION of joins to create a more efficient plan. However, we currently only perform this transformation if one side of the OR predicate contains an equijoin predicate (e.g., t1.col1 = t2.col1). There are other cases where we could improve the plan by splitting the disjunction, but we don't currently do so. For example, consider the following:

CREATE TABLE a (
  a1 INT PRIMARY KEY,
  a2 INT NOT NULL,
  a3 INT NOT NULL,
  a4 INT NULL,
  INDEX (a3) STORING (a2, a4),
  INDEX (a4) STORING (a2, a3)
);

CREATE TABLE b (
  b1 INT PRIMARY KEY,
  b2 INT NULL,
  b3 INT NULL,
  b4 INT NOT NULL,
  INDEX (b2) STORING (b3),
  INDEX (b3) STORING (b2)
);

EXPLAIN SELECT a.*
FROM
  a INNER JOIN b ON b.b3 = a.a2
WHERE
  a.a4 = 30
  OR (a.a3 = 10 AND b.b2 = 20);
                            info
------------------------------------------------------------
  distribution: local
  vectorized: true

  • hash join
  │ equality: (a2) = (b3)
  │ pred: (a4 = 30) OR ((a3 = 10) AND (b2 = 20))
  │
  ├── • scan
  │     missing stats
  │     table: a@a_pkey
  │     spans: FULL SCAN
  │
  └── • scan
        missing stats
        table: b@b_b2_idx
        spans: FULL SCAN

Note that because we do not split the disjunction, we cannot push any predicate below the join, and we perform full table scans of both a and b.

Describe the solution you'd like
We should be able to split the disjunction in this case to allow us to push predicates down and perform constrained scans of a and b. For example, we should be able to produce the following plan for the above query:

EXPLAIN SELECT a.*
FROM
  a INNER JOIN b ON b.b3 = a.a2
WHERE
  a.a4 = 30
  OR (a.a3 = 10 AND b.b2 = 20);
                info
------------------------------------
  distribution: local
  vectorized: true

  • distinct
  │ distinct on: a1, b1
  │
  └── • union all
      │
      ├── • lookup join
      │   │ table: b@b_b3_idx
      │   │ equality: (a2) = (b3)
      │   │
      │   └── • scan
      │         missing stats
      │         table: a@a_a4_idx
      │         spans: [/30 - /30]
      │
      └── • hash join
          │ equality: (a2) = (b3)
          │
          ├── • scan
          │     missing stats
          │     table: a@a_a3_idx
          │     spans: [/10 - /10]
          │
          └── • scan
                missing stats
                table: b@b_b2_idx
                spans: [/20 - /20]

Jira issue: CRDB-24828