pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #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>
  2020-05-08 16:17 ` Re: BUG #16425: Possible error in full join Jeff Janes <jeff.janes@gmail.com>
  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 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>
  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