On Sunday, August 25, 2024, Tobias Hoffmann <
ldev-list@thax.hardliners.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.
