Re: Incorrect result in certain expressions of anti-join - Mailing list pgsql-bugs

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)