BUG #9896: Bug in FULL OUTER JOIN

From: sqlpro@sqlspot.com
Subject: BUG #9896: Bug in FULL OUTER JOIN
Date: ,
Msg-id: 20140407131705.342.75513@wrigleys.postgresql.org
(view: Whole thread, Raw)
Responses: 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, )

The following bug has been logged on the website:

Bug reference:      9896
Logged by:          SQLpro
Email address:      
PostgreSQL version: 9.3.4
Operating system:   Windows
Description:

According to ISO standard SQL there is no limitation in predicat ON for JOIN
operator.
But PostGreSQL is unable to join tables in the FULL OUTER mode when
predicate ON is not sargable.
here is an example :

CREATE TABLE T_CLIENT_CLI --> customers
(CLI_ID       INT PRIMARY KEY,
 CLI_SIREN    CHAR(9) NOT NULL UNIQUE,
 CLI_ENSEIGNE VARCHAR(16) NOT NULL);

CREATE TABLE T_PROSPECT_PSP --> sale perspectives
(PSP_ID       INT PRIMARY KEY,
 PSP_SIREN    CHAR(9) NOT NULL UNIQUE,
 PSP_ENSEIGNE VARCHAR(16) NOT NULL);

We want to join on SIREN (national French firm number) or, in the case of
mistake in this number, on the name (enseigne).

Some datas :

INSERT INTO T_CLIENT_CLI VALUES
(1, '123456789', 'IBM'),
(2, '111111111', 'Microsoft'),
(3, '999555111', 'SAP');

INSERT INTO T_PROSPECT_PSP VALUES
(101, '123456789', 'IBM'),
(102, '555555555', 'Microsoft'),
(103, '777777777', 'Amazon'),
(104, '444444444', 'Google');

This query answer exactly to the question :

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;

The result would be :

CLI_ID      CLI_SIREN CLI_ENSEIGNE     PSP_ID      PSP_SIREN PSP_ENSEIGNE
----------- --------- ---------------- ----------- ---------
----------------
1           123456789 IBM              101         123456789 IBM
2           111111111 Microsoft        102         555555555 Microsoft
3           999555111 SAP              NULL        NULL      NULL
NULL        NULL      NULL             103         777777777 Amazon
NULL        NULL      NULL             104         444444444 Google

But PG throw an exception :

ERREUR:  FULL JOIN is only supported with merge-joinable or hash-joinable
join conditions
État SQL :0A000

This is the case in version 9.1.2 too.
It seems that is the case in any version of PG...

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

Thanks

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