support join on or predicates rewrite to Union All

[stephen-shelby]

Enhancement

Take the following SQL as an example:
select * from left_table l join right_table r on l.k1 = r.v1 or l.k1 = r.v2

currently, we will rewrite the join type to nestloop join. This performance is relatively poor.

Actually, we can rewrite this sql to

select * from left_table l join right_table r on l.k1 = r.v1 and l.k1 = r.k2
union all
select * from left_table l join right_table r on l.k1 != r.v1 and l.k1 = r.k2
union all
select * from left_table l join right_table r on l.k1 = r.v1 and l.k1 != r.k2

we want to rewrite join on or predicate to union all. this will have a higher performance than nest loop join.

you could write a rule to rewrite this kind query.

you need to test whether the performance is better than nest loop join in the following two case:

• multiple or predicate
• non-related equal predicate like on l.k1 = r.v1 or l.k2 = r.v2

[KKould]

i think this optimization can also be applied to this case: #52399, using the on predicate of inner join to convert cross join into union all

In what the issue expects (T0 X T1 X T2), they can become sql like this:

select a, null as b, null as c from t0
union all
select null as a, b, null as c from t1
union all
select null as a, null as b, null as c from t2