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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Non-trivial condition is only propagated to one side of JOIN
Дата
Msg-id CAKFQuwZ3jMCfy=cMS5BrzVR7M-ZN2N_2Ocgh+6Rj89f6=an-ZA@mail.gmail.com
обсуждение исходный текст
Ответ на Non-trivial condition is only propagated to one side of JOIN  (Tobias Hoffmann <ldev-list@thax.hardliners.org>)
Ответы Re: Non-trivial condition is only propagated to one side of JOIN
Re: Non-trivial condition is only propagated to one side of JOIN
Список pgsql-hackers
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.

В списке pgsql-hackers по дате отправления: