Environment
Product | Version |
Pivotal HDB | 2.0.0, 2.0.1, 2.1.0, 2.1.1 |
Pivotal HDP | 2.4,2.5 |
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.
Comments