Re: Non-trivial condition is only propagated to one side of JOIN

On Sunday, August 25, 2024, Tobias Hoffmann <ldev-list(at)thax(dot)hardliners(dot)org>
wrote:

>
> 3) Problematic example:
>
> # EXPLAIN SELECT * FROM tbl1 JOIN tbl2 ON tbl2.site_id = tbl1.site_id
> WHERE tbl1.site_id = 1 OR tbl1.site_id IS NULL;

The “is null” predicate in this query is doing nothing as your next comment
alludes to; you will produce no rows out of the join with a null site_id
due to the use of the equals operator in the join.

>
> Also, `ON tbl1.site_id IS NOT DISTINCT FROM tbl2.site_id` does not help,
>

Others may correct me but I’m guessing that indeed the optimizer has a gap
here that could be filled in, it’s just it feels like adding code to deal
with broken queries so isn’t overly motivated to work on. Joining using
distinct instead of equality is uncommon, since nearly all models join
primary keys to foreign keys and both of those are almost always non-null.

David J.