Incorrect result in certain expressions of anti-join

From: Torbjörn Gannholm <torbjorn(dot)gannholm(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Incorrect result in certain expressions of anti-join
Date: 2025-01-07 17:51:20
Message-ID: CAOW_tKjRWjghZ0+TKw7VkDV0Uh104VfjAowy37CKm67JuK5BBw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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)

/tobe

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Thomas Munro 2025-01-07 21:57:20 Re: BUG #18146: Rows reappearing in Tables after Auto-Vacuum Failure in PostgreSQL on Windows
Previous Message Philippe BEAUDOIN 2025-01-07 15:54:07 Re: BUG #18767: Inconsistency in result of a Plpgsql GET DIAGNOSTICS PG_CONTEXT instruction.