Re: BUG #9896: Bug in FULL OUTER JOIN

From: Tom Lane
Subject: Re: BUG #9896: Bug in FULL OUTER JOIN
Date: ,
Msg-id: 28747.1396901878@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: BUG #9896: Bug in FULL OUTER JOIN  (Tom Lane)
Responses: Re: BUG #9896: Bug in FULL OUTER JOIN  (Vik Fearing)
List: pgsql-bugs

Tree view

BUG #9896: Bug in FULL OUTER JOIN  (, )
 Re: BUG #9896: Bug in FULL OUTER JOIN  (Tom Lane, )
  Re: BUG #9896: Bug in FULL OUTER JOIN  (David Johnston, )
  Re: BUG #9896: Bug in FULL OUTER JOIN  (Tom Lane, )
   Re: BUG #9896: Bug in FULL OUTER JOIN  (Vik Fearing, )

I wrote:
>  writes:
>> SELECT *
>> FROM   T_CLIENT_CLI AS C
>> FULL OUTER JOIN T_PROSPECT_PSP AS P
>> ON C.CLI_SIREN = P.PSP_SIREN
>> OR C.CLI_ENSEIGNE = P.PSP_ENSEIGNE;

> Can you show us the query plans used by those systems?

For the archives' sake: the OP sent me a not-too-useful screen shot
in which SQL Server claims it's using a merge join for this query.

I find this less than credible: what linear sort order would bring
together all the potentially joinable rows?  If they're getting the
right answer at all, there must be some secret sauce in there someplace.
Perhaps they're just Doing It The Hard Way with state storage
proportional to the size of the relations?

            regards, tom lane

pgsql-bugs by date:

From:
Date:
Subject: Re: Configuring Standby Server in PostgreSQL 9.3.3
From: Michael Paquier
Date:
Subject: Re: BUG #9895: Duplicate pkey