DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=bArvi/T+DbsuV5lHUgKEzFnvL2z5E3jFWh4VLAsQ7k2uNSt2J22zGWhIjSuO85yS+j
	vUtZTujUD5tEr13rWjbmM5Z2yzA+vqzY1soxGPIOkYD8J1lMRo4oVzuWVuDL42RzvUhG
	cWtyaUyi/yt4B773l+353jYHq9zY7VOgnPw+M=
MIME-Version: 1.0
In-Reply-To: <BANLkTinQE8iZXmSvnx4GaXbDbmyK7vC+-w@mail.gmail.com>
References: <BANLkTinQE8iZXmSvnx4GaXbDbmyK7vC+-w@mail.gmail.com>
Date: Mon, 13 Jun 2011 12:07:36 -0400
Message-ID: <BANLkTik2bSEyHpE9QKzL_t5w_L4OV-v9CA@mail.gmail.com>
Subject: Re: 7.2. Table Expressions FULL join is only supported with
	merge-joinable join conditions
From: Robert Haas <robertmhaas@gmail.com>
To: Grzegorz Szpetkowski <gszpetkowski@gmail.com>
Cc: pgsql-docs@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Mon, May 16, 2011 at 7:32 PM, Grzegorz Szpetkowski
<gszpetkowski@gmail.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:
>
> =3D> SELECT * FROM t1 LEFT JOIN t2 ON t1.num =3D t2.num AND t2.value =3D =
'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 =3D t2.num AND t2.value =3D 'xxx'=
;
> ERROR: =A0FULL 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=3D# create table t1 (num int, value text);
CREATE TABLE
rhaas=3D# create table t2 (num int, value text);
CREATE TABLE
rhaas=3D# insert into t1 values (1, 'yyy'), (2, 'yyy');
INSERT 0 2
rhaas=3D# insert into t2 values (2, 'yyy'), (3, 'yyy');
INSERT 0 2
rhaas=3D# select * from t1 full join t2 on t1.num =3D t2.num and t2.value =
=3D '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 followi=
ng:

select * from t1 full join (select * from t2 where t2.value =3D 'xxx')
t2 on t1.num =3D t2.num;
select * from t1 full join t2 on t1.num =3D t2.num WHERE t2.value =3D '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.

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