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)
/tobe
