Re: 7.2. Table Expressions FULL join is only supported with merge-joinable join conditions

On Mon, May 16, 2011 at 7:32 PM, Grzegorz Szpetkowski
<gszpetkowski(at)gmail(dot)com> wrote:
> http://www.postgresql.org/docs/9.0/static/queries-table-expressions.html
>
> "The join condition specified with ON can also contain conditions that
> do not relate directly to the join. This can prove useful for some
> queries but needs to be thought out carefully. For example:
>
> => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';"
>
> Can you add information about FULL JOIN in doc there ? I am trying to
> run such query:
>
> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
> ERROR:  FULL JOIN is only supported with merge-joinable join conditions
>
> I mean add something like: "Note that you can't use such conditions
> with FULL JOIN, only equality of selected columns is supported with
> this type".

This works in 9.1. A possibly relevant point is that the semantics
are not what you might think:

rhaas=# create table t1 (num int, value text);
CREATE TABLE
rhaas=# create table t2 (num int, value text);
CREATE TABLE
rhaas=# insert into t1 values (1, 'yyy'), (2, 'yyy');
INSERT 0 2
rhaas=# insert into t2 values (2, 'yyy'), (3, 'yyy');
INSERT 0 2
rhaas=# select * from t1 full join t2 on t1.num = t2.num and t2.value = 'xxx';
num | value | num | value
-----+-------+-----+-------
1 | yyy | |
2 | yyy | |
| | 2 | yyy
| | 3 | yyy
(4 rows)

It's very possible that a user who is writing this meant one of the following:

select * from t1 full join (select * from t2 where t2.value = 'xxx')
t2 on t1.num = t2.num;
select * from t1 full join t2 on t1.num = t2.num WHERE t2.value = 'xxx';

...which are not equivalent to each other, or to the original query.
It'd be nice to document this better, but I don't have a clear feeling
for exactly what is needed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company