Re: BUG #9896: Bug in FULL OUTER JOIN

From: Tom Lane
Subject: Re: BUG #9896: Bug in FULL OUTER JOIN
Date: ,
Msg-id: 19141.1396884331@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: BUG #9896: Bug in FULL OUTER JOIN  ()
Responses: Re: BUG #9896: Bug in FULL OUTER JOIN  (David Johnston)
Re: BUG #9896: Bug in FULL OUTER JOIN  (Tom Lane)
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, )

 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;

> But PG throw an exception :

> ERREUR:  FULL JOIN is only supported with merge-joinable or hash-joinable
> join conditions

Yeah.  The problem is the OR condition, which doesn't provide anything
that will work with either of those methods.

> By comparizon, this query guive the exact answer in :
> - MS SQL Server since version 2000 to 2014 (2005, 2008/2008R2, 2012).
> - Oracle 11G

Interesting.  I wonder where they keep the per-row match status?

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

In any case, don't hold your breath waiting for a fix; I'm just asking
to gather information for possible future work.  This is unlikely to
be simple to fix, and it's not going to be very high on anyone's priority
list either, given that few people use FULL JOIN as far as I've heard.

            regards, tom lane

pgsql-bugs by date:

From: David Johnston
Date:
Subject: Re: BUG #9896: Bug in FULL OUTER JOIN
From: jeff@jefftrout.com
Date:
Subject: BUG #9898: WindowAgg's causing horrific plans