Re: BUG #16425: Possible error in full join

On Fri, May 8, 2020 at 11:37 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 16425
> Logged by: Michal C
> Email address: c_michal(at)poczta(dot)onet(dot)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);

Cheers,

Jeff