Re: Incorrect result in certain expressions of anti-join

On Tuesday, January 7, 2025, Torbjörn Gannholm <torbjorn(dot)gannholm(at)gmail(dot)com>
wrote:

> To reproduce:
>
> create table foo (a text);
> Insert into foo values ('foo');
> create table bar (a text);
> insert into bar values ('foo'), ('bar');
>
> Now the following expression of anti-join gives the correct result:
> select * from bar LEFT JOIN foo ON foo.a = bar.a WHERE foo.a IS NULL;
> a | a
> -----+---
> bar |
> (1 row)
>
> However, a slight change and the anti-condition is simply ignored and the
> unexpected (IMO incorrect) result is returned. I would expect it either to
> work or to cause an error:
> select * from bar LEFT JOIN foo ON foo.a = bar.a AND foo.a IS NULL;
> a | a
> -----+---
> foo |
> bar |
> (2 rows)
>
>
The result is correct. The two queries are not equivalent. The join
condition on the second query evaluates to false for all combinations as
foo.a has no null values.