Re: full outer join bug?

Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> SELECT * FROM t1 FULL JOIN t2 USING (name) FULL  JOIN t3 USING (name);
> ERROR:  FULL JOIN is only supported with mergejoinable join conditions

I think we're kinda stuck with that in the near term.  A possible
workaround is

SELECT * FROM t1 FULL JOIN t2 on t1.name=t2.name
FULL JOIN t3 on t1.name=t3.name;

or similarly

SELECT * FROM t1 FULL JOIN t2 on t1.name=t2.name
FULL JOIN t3 on t2.name=t3.name;

each of which is slightly different from the semantics of the original
query, but might be close enough for your purposes.

The problem is that "name" coming out of the t1/t2 full join is not a
simple variable: it's actually a "COALESCE(t1.name,t2.name)" construct.
And the mergejoin code doesn't support mergejoining on anything but
simple variables.  And our other join methods don't support FULL JOIN.
So there's no way to build a working plan.

I have plans to revise the handling of join variables at some point
in the future, probably as part of the fabled querytree redesign.
And mergejoining on expressions should be allowed too, sooner or later.
Neither one is going to happen for 7.2 though ...
        regards, tom lane