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
