Description

One feature we want to get in for LOOKUP JOIN is the ability to push down filters passed the join:

| LOOKUP JOIN lookup ON a
| WHERE a > 10
// becomes
| WHERE a > 10
| LOOKUP JOIN lookup ON a

Right now, we're looking only at pushing the filter on the main index (left side) - pushdown on the right side will occur later.
The overall plan is to convert the USING/ON a into an equality expression (resolved in the analyzer since we're not introducing name qualifiers), then combine filters on top of the join, with those in the expression: e.g. (ON a becomes ON leftside.a == rightside.a)

A benefit of the expression combination is creating transient constraints on one join key to the other side (regardless if the name qualifiers are present or not), e.g.:

LOOKUP JOIN lookup ON l.a == r.b  | WHERE a > 10
// becomes
LOOKUP JOIN lookup ON l.a == r.b  AND l.a > 10
// through transitivity
LOOKUP ON lookup ON l.a == r.b AND l.a > 10 AND r.b > 10

The relevant filters (per side) can then be pushed down on their relevant side: in the example above l.a on the left and r.b on the right.
The benefit of this approach is the transitivity rule is generic enough and can be applied in other filters as well.

As such:

• Retrofit JoinConfig into a (bool) condition @costin
  • extract the equijoin fields through an utility to keep the compute layer untouched
• Combine Filter following JOIN with its condition as a conjunction
• Create rule to detect and infer logic transitivity (for starters we can enable it only for join) /cc @bpintea
  (given a filter where a == b and a >1 infer than b > 1. Let's get this working for multiple cases as well:
  a == b AND b == c AND c > 10 and a < 100 determines that a > 10, b > 10 AND b < 100 and c < 100
• Break down the filter into conjunctions islands and push down these based on their references to the relevant side