Обсуждение: FULL JOIN is only supported with merge-joinable join conditions

Hi guys,

I have a question about outer join. For example as follow (pg 8.4.1):
------------------------------
create table t_1(a int);
create table t_3(a int);
insert into t_1 values(1);
insert into t_1 values(2);
insert into t_3 values(1);
insert into t_3 values(3);

postgres=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 8.4.1, compiled by Visual C++ build 1400, 32-bit
(1 row)
postgres=# select * from t_1 full outer join t_3 on t_1.a=1;
ERROR:  FULL JOIN is only supported with merge-joinable join conditions
--------------------------

My question is: why on clause restrict "t_1.a=1"?

I test it in Oracle, it support to such as "t_1.a=1":
------------------------

SQL> SELECT * FROM TAB1;
         A          B          C
---------- ---------- ----------
         2
         3
SQL> SELECT * FROM TAB2;
         A
----------
         1
         2
SQL> select * from tab1 full outer join tab2 on tab1.a=2;
         A          B          C          A
---------- ---------- ---------- ----------
         2                                1
         2                                2
         3
SQL> select * from tab1 left outer join tab2 on tab1.a=2;
         A          B          C          A
---------- ---------- ---------- ----------
         2                                1
         2                                2
         3
SQL> select * from tab1 right outer join tab2 on tab1.a=2;
         A          B          C          A
---------- ---------- ---------- ----------
         2                                1
         2                                2

SQL> select * from tab1 right outer join tab2 on 1=1;
         A          B          C          A
---------- ---------- ---------- ----------
         2                                1
         3                                1
         2                                2
         3                                2
SQL> select * from tab1 right outer join tab2 on tab2.a=2;
         A          B          C          A
---------- ---------- ---------- ----------
                                          1
         2                                2
         3                                2

"hx.li" <fly2nn@126.com> writes:
> ERROR:  FULL JOIN is only supported with merge-joinable join conditions
> My question is: why on clause restrict "t_1.a=1"?

It's an implementation restriction.  If the clauses aren't mergejoinable
there's no very practical way to keep track of which inner-side rows
have had a match.

> I test it in Oracle, it support to such as "t_1.a=1":

I'd be interested to know how whatever they're doing scales to very
large joins.

            regards, tom lane

> It's an implementation restriction.  If the clauses aren't mergejoinable
> there's no very practical way to keep track of which inner-side rows
> have had a match.


If we could consider it is equivalent transformation as follow?

select * from t_1 full outer join t_3 on t_1.a=1;
and
select * from t_1 full outer join t_3 on true where t_1.a=1;

If we could transform RestrictInfo into the where-clause, maybe it right.

TEST=# select * from t_1 full outer join t_3 on true where t_1.a=1;
 A | A
---+---
 1 | 1
 1 | 3
(2 rows)

"hx.li" <fly2nn@126.com> writes:
> If we could consider it is equivalent transformation as follow?

> select * from t_1 full outer join t_3 on t_1.a=1;
> and
> select * from t_1 full outer join t_3 on true where t_1.a=1;

Those are not equivalent.

            regards, tom lane