Re: BUG #5263: Query execution fails with "ERROR: FULL JOIN is only supported with merge-joinable join conditions"

Well, this is a real-world case for us :-). The actual sql is a lot more co=
mplicated (and it is machine-generated), but the bottom line is that we nee=
d to project constants as columns, and we need to be able to "combine" the =
results coming out of the sub-queries.

Years ago (8.1.x), we found that a FULL OUTER JOIN actually performed bette=
r or at least as well as UNION [ALL] in most if not all of the cases we had=
 to deal with. So for that reason, and b/c the outer join closely resembles=
 the inner joins syntactically, we chose to go with the outer join rather t=
han with the union in our query generator.

While changing our query engine is certainly a possibility, it's a time-con=
suming process that we can't afford, and it presents a risk that we can't f=
ace at the present time.

As I mentioned, this query works in 8.3, so I was hoping 8.4 would handle i=
t out-of-the-box. Without it, we will not be able to upgrade to 8.4 for a w=
hile, which we desperately want for the many improvements and features it o=
ffers.

Regards,
Jozsef Szalay



-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Tuesday, January 05, 2010 3:17 PM
To: Jozsef Szalay
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #5263: Query execution fails with "ERROR: FULL JOIN=
 is only supported with merge-joinable join conditions"=20

"Jozsef Szalay" <jszalay@storediq.com> writes:
> Execute the following query:

> SELECT *
> FROM (SELECT id, 0 AS value
>       FROM test
>       WHERE description =3D 'abc'
>      ) t1
>      FULL OUTER JOIN
>      (SELECT id, 1 AS value
>       FROM test
>       WHERE description =3D 'def'
>      ) t2 USING (id, value);

Hm.  It's reducing the join condition to constant FALSE (since 0<>1) and
then deciding it doesn't know how to join in that case.  While this is
certainly undesirable, I have to wonder about the purpose of the query.
It seems like this is just a remarkably inefficient way of performing
UNION ALL.  Do you have a more real-world case where it happens?

            regards, tom lane