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);