Full Outer Join in Greenplum Database (GPDB) 6

Products

• VMware Tanzu Greenplum

Issue/Introduction

The following GUC does not exist in Greenplum Database (GPDB) 6.

gpadmin=# show optimizer_enable_full_join;                                                                                                                                  optimizer_enable_full_join
----------------------------
 off
(1 row)

Environment

Product Version: 6.7

Resolution

Full Joins are on by default in GPDB 6. Therefore, a query similar to the one below works automatically in GPDB 6 with no parameter change.

gpadmin=# select * from test_piv_1 a full outer join test_piv_2 b on a.a=b.d ;
 a  | b  | c  | d  | e  | f  | g
----+----+----+----+----+----+---
  1 |  1 |  1 |  1 |  1 |  1 |
  1 |  1 |  1 |  1 |  1 |  1 |
  5 |  5 |  5 |  5 |  5 |  5 |
  5 |  5 |  5 |  5 |  5 |  5 |
    |    |    | 11 | 11 | 11 |
    |    |    | 12 | 12 | 12 |
    |    |    | 14 | 14 | 14 |
    |    |    | 15 | 15 | 15 |
  2 |  2 |  2 |  2 |  2 |  2 |
  2 |  2 |  2 |  2 |  2 |  2 |
  3 |  3 |  3 |  3 |  3 |  3 |

Whereas a query like the one below in GPDB 5 errors out because of the distinct statement in the join. 

gpadmin=# select * from dds.source s full outer join dds.source s2 on s.source_id is distinct from s2.source_id;
ERROR:  FULL JOIN is only supported with merge-joinable or hash-joinable join conditions

In this case. you will have to re-write your queries:

select * from dds.source s full outer join dds.source s2 on s.source_id = s2.source_id where s.source_id is distinct from s2.source_id;