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 complicated (and it is machine-generated), but the bottom line is that we need 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 better 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 than with the union in our query generator.

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

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

Regards,
Jozsef Szalay

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

"Jozsef Szalay" <jszalay(at)storediq(dot)com> writes:
> Execute the following query:

> SELECT *
> FROM (SELECT id, 0 AS value
> FROM test
> WHERE description = 'abc'
> ) t1
> FULL OUTER JOIN
> (SELECT id, 1 AS value
> FROM test
> WHERE description = '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