Re: BUG #5263: Query execution fails with "ERROR: FULL JOIN is only supported with merge-joinable join conditions"

Jozsef Szalay <jszalay@storediq.com> writes:
> Well, this is a real-world case for us :-). The actual sql is a lot more complicated (and it is machine-generated),
butthe bottom line is that we need to project constants as columns, and we need to be able to "combine" the results
comingout of the sub-queries. 
> Years ago (8.1.x), we found that a FULL OUTER JOIN actually performed better or at least as well as UNION [ALL] in
mostif not all of the cases we had to deal with. So for that reason, and b/c the outer join closely resembles the inner
joinssyntactically, we chose to go with the outer join rather than with the union in our query generator. 

Hmph.  The FULL JOIN construct would have forced sorting of each of the
inputs, so it's pretty hard to see how it wouldn't lose to a UNION ALL.

You're also fortunate to have not run into the problem before, because
we've never supported FULL JOIN ON FALSE (until about five minutes ago)
and so whether it worked would depend on whether the planner was smart
enough to recognize the impossibility of the join condition.  I guess
with sufficiently stylized output from a query generator maybe you'd
never have hit it...

Anyway, I've applied a patch to cover this case.

            regards, tom lane