Thread: BUG #16425: Possible error in full join
The following bug has been logged on the website: Bug reference: 16425 Logged by: Michal C Email address: c_michal@poczta.onet.pl PostgreSQL version: 12.2 Operating system: Windows 10 Description: Sorry for my English My PG version: "PostgreSQL 12.2, compiled by Visual C++ build 1914, 64-bit" Example for bag: with d (a,b) as (values (null::varchar(255),1)) select * from d d1 full join d d2 using (a,b); Result: "a" "b" null 1 null 1 Why select returns 2 records, Is this correct?
On Fri, May 8, 2020 at 11:37 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 16425
Logged by: Michal C
Email address: c_michal@poczta.onet.pl
PostgreSQL version: 12.2
Operating system: Windows 10
Description:
Sorry for my English
My PG version: "PostgreSQL 12.2, compiled by Visual C++ build 1914,
64-bit"
Example for bag:
with d (a,b) as (values (null::varchar(255),1))
select *
from d d1
full join d d2 using (a,b);
Result:
"a" "b"
null 1
null 1
Why select returns 2 records, Is this correct?
This looks correct to me. It returns one row from the left side and one from the right side. The way NULL comparisons work, the two rows are not known to be equal and so are not merged. The USING uses equality for testing.
To get one row, you could rewrite it explicitly using ON and replacing one equality with IS NOT DISTINCT FROM, like this:
with d (a,b) as (values (NULL::varchar(255),1))
select *
from d d1
full join d d2 on(d1.b=d2.b and d1.a is not distinct from d2.a);
select *
from d d1
full join d d2 on(d1.b=d2.b and d1.a is not distinct from d2.a);
Cheers,
Jeff