pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16425: Possible error in full join
2+ messages / 2 participants
[nested] [flat]
* BUG #16425: Possible error in full join
@ 2020-05-08 15:25 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2020-05-08 15:25 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: c_michal@poczta.onet.pl
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #16425: Possible error in full join
@ 2020-05-08 16:17 Jeff Janes <jeff.janes@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Jeff Janes @ 2020-05-08 16:17 UTC (permalink / raw)
To: c_michal@poczta.onet.pl, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
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);
Cheers,
Jeff
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-05-08 16:17 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-05-08 15:25 BUG #16425: Possible error in full join PG Bug reporting form <noreply@postgresql.org>
2020-05-08 16:17 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox