Re: BUG #18429: Inconsistent results on similar queries with join lateral

On Sun, Apr 14, 2024 at 6:59 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> So this seems like a bit of a mess.  We can fix the submitted bug with
> the kluge of only testing the nullable_relids condition, as I've done
> in the attached patch for v15.  (As join_clause_is_movable_into says,
> this condition is conservative and might sometimes reject a clause
> that could be evaluated safely, but that's fine for
> get_baserel_parampathinfo's purposes.)


I wondered that we could fix this issue by checking em_nullable_relids
in generate_join_implied_equalities_normal when we determine if an EC
member is computable at the join.  Then I realize that this is not easy
to achieve without knowing the exact join(s) where the nulling would
happen, which is exactly what the nullingrel stuff introduced in v16
does.  So your proposed fix seems the right way to go.

Now that we've learned that join_clause_is_movable_into's heuristic
about physically referencing the target rel can fail for EC-derived
clauses, I'm kind of concerned that we may end up with duplicate clauses
in the final plan, since we do not check EC-derived clauses against
join_clause_is_movable_into in get_baserel_parampathinfo while we do in
create_nestloop_path.  What if we have an EC-derived clause that in
get_baserel_parampathinfo it is put into ppi_clauses while in
create_nestloop_path it does not pass the movability checking?  Is it
possible to occur, or is it just my illusion?

Thanks
Richard