Query fails with "ERROR""FULL JOIN is only supported with merge-joinable join conditions"

Products

• VMware Tanzu Greenplum

Issue/Introduction

When performing queries using a Full Outer Join we get the following error:

gpadmin-# FULL JOIN public.to_drop_b_nst ON public.to_drop_a_nst.value IS NOT DISTINCT FROM public.to_drop_b_nst.value;
ERROR:  FULL JOIN is only supported with merge-joinable join conditions

Note: This behavior does not exist prior to Greenplum Database (GPDB) 4.3.31.x.

Environment

Product Version: 5.21

Resolution

The way Optimizer handles a Full Outer Join, it makes a union between Left Outer Join and Left Anti Semi Join.

This is not as efficient as a native merge join so they disabled it by default from GPDB 4.3.32.1 onwards.

It can be re-enabled by setting optimizer_enable_full_join = on by default at the database level or at session level:

gpadmin=# set optimizer_enable_full_join = on;

Note: Full Joins are set to on by default in Greenplum 6.x.