Environment

Symptom

Query with a "full outer join" in HAWQ fails and error "FULL JOIN is only supported with merge-joinable join conditions" is seen. 

Error Message:

gpadmin=# select * from test1 full outer join test2 on test1.col1=1 or test2.col1=1;
ERROR:  FULL JOIN is only supported with merge-joinable join conditions
gpadmin=#

Steps to Reproduce the Error:

gpadmin=# create table test1 (col1 text);
CREATE TABLE
gpadmin=# create table test2 (col1 text);
CREATE TABLE
gpadmin=# select * from test1 full outer join test2 on test1.col1=1 or test2.col1=1;
ERROR:  FULL JOIN is only supported with merge-joinable join conditions

Cause 

This is a software defect reported by internal defect ID: GPSQL-3334.

Resolution

Workaround:

Modify the SQL to change the query so that it does not use FULL OUTER JOIN. In case of the above example:

gpadmin=# select * from test1,test2 where test1.col1 = '1' or test2.col1='1';
 col1 | col1
------+------
 1    | 1
 1    | 2
 1    | 3
 2    | 1
 3    | 1
(5 rows)

Permanent Solution:

There are plans to fix this issue in a future release of HAWQ. To confirm if this is fixed, check the most recent release notes for HAWQ.