Thread: BUG #5263: Query execution fails with "ERROR: FULL JOIN is only supported with merge-joinable join conditions"
BUG #5263: Query execution fails with "ERROR: FULL JOIN is only supported with merge-joinable join conditions"
From
"Jozsef Szalay"
Date:
The following bug has been logged online: Bug reference: 5263 Logged by: Jozsef Szalay Email address: jszalay@storediq.com PostgreSQL version: 8.4.2 Operating system: Windows, Linux (Fedora/CentOS) Description: Query execution fails with "ERROR: FULL JOIN is only supported with merge-joinable join conditions" Details: Create a table: CREATE TABLE test ( id integer, description character varying, CONSTRAINT test_pkey PRIMARY KEY (id) ); 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); You'll get the "ERROR: FULL JOIN is only supported with merge-joinable join conditions" This used to work with 8.3.7 (and before). I can make the query work by adding an "ORDER BY id, value" clause to each sub-query or if I use the same constant (e.g. "0") as value in both sub-queries.
Re: BUG #5263: Query execution fails with "ERROR: FULL JOIN is only supported with merge-joinable join conditions"
From
Tom Lane
Date:
"Jozsef Szalay" <jszalay@storediq.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
Re: BUG #5263: Query execution fails with "ERROR: FULL JOIN is only supported with merge-joinable join conditions"
From
Jozsef Szalay
Date:
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
Re: BUG #5263: Query execution fails with "ERROR: FULL JOIN is only supported with merge-joinable join conditions"
From
Tom Lane
Date:
Jozsef Szalay <jszalay@storediq.com> writes: > Well, this is a real-world case for us :-). The actual sql is a lot more complicated (and it is machine-generated), butthe bottom line is that we need to project constants as columns, and we need to be able to "combine" the results comingout 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 mostif not all of the cases we had to deal with. So for that reason, and b/c the outer join closely resembles the inner joinssyntactically, we chose to go with the outer join rather than with the union in our query generator. Hmph. The FULL JOIN construct would have forced sorting of each of the inputs, so it's pretty hard to see how it wouldn't lose to a UNION ALL. You're also fortunate to have not run into the problem before, because we've never supported FULL JOIN ON FALSE (until about five minutes ago) and so whether it worked would depend on whether the planner was smart enough to recognize the impossibility of the join condition. I guess with sufficiently stylized output from a query generator maybe you'd never have hit it... Anyway, I've applied a patch to cover this case. regards, tom lane