Re: BUG #9896: Bug in FULL OUTER JOIN

sqlpro(at)sqlspot(dot)com 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