Обсуждение: full outer join bug?

Here is a report from a user in Japan. I confirmed it happens in
current.

DROP TABLE t1;
CREATE TABLE t1 ( name TEXT, n INTEGER);
DROP TABLE t2;
CREATE TABLE t2 ( name TEXT, n INTEGER);
DROP TABLE t3;
CREATE TABLE t3 ( name TEXT, n INTEGER);

INSERT INTO t1 VALUES ( 'aa', 11 );
INSERT INTO t2 VALUES ( 'aa', 12 );
INSERT INTO t2 VALUES ( 'bb', 22 );
INSERT INTO t3 VALUES ( 'aa', 13 );
INSERT INTO t3 VALUES ( 'cc', 33 );

SELECT * FROM t1 FULL JOIN t2 USING (name) FULL  JOIN t3 USING (name); -- NG
ERROR:  FULL JOIN is only supported with mergejoinable join conditions
--
Tatsuo Ishii

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

I wrote:
> 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 ...

There probably ought to be something in the master TODO list about
these.  Bruce, would you add something along the lines of:

* Nested FULL OUTER JOINs don't work (Tom)
* Allow merge and hash joins on expressions not just simple variables (Tom)
        regards, tom lane

> I wrote:
> > 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 ...
> 
> There probably ought to be something in the master TODO list about
> these.  Bruce, would you add something along the lines of:
> 
> * Nested FULL OUTER JOINs don't work (Tom)
> * Allow merge and hash joins on expressions not just simple variables (Tom)

Added.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026