Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fABJbam92443 for ; Sun, 11 Nov 2001 14:37:37 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fABJbPc21942; Sun, 11 Nov 2001 14:37:25 -0500 (EST) To: nickf@ontko.com cc: pgsql-hackers@postgreSQL.org Subject: Re: [SQL] RIGHT JOIN is only supported with mergejoinable join conditions In-reply-to: References: Comments: In-reply-to "Nick Fankhauser" message dated "Wed, 07 Nov 2001 11:16:38 -0500" Date: Sun, 11 Nov 2001 14:37:25 -0500 Message-ID: <21939.1005507445@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200111/360 X-Sequence-Number: 15216 "Nick Fankhauser" writes: > I'm on version 7.1, and I'm getting this error when attempting to select > from a view: > RIGHT JOIN is only supported with mergejoinable join conditions I have committed a fix for this problem --- of the three routines that can generate mergejoin plans, only two were checking to ensure they'd generated a valid join plan in RIGHT/FULL join cases. I seem to recall having deliberately decided that sort_inner_and_outer didn't need to check, but your example proves that it does. There is still a related problem with FULL JOIN, which is that *all* the possible join plans may get rejected: regression=# create table aa (v1 varchar, v2 varchar); CREATE regression=# create table bb (v1 varchar, v2 varchar, v3 varchar); CREATE regression=# select * from aa a full join bb b on regression-# a.v2 = b.v3 and a.v1 = b.v2 and a.v1 = b.v1 and a.v2 = b.v1; ERROR: Unable to devise a query plan for the given query regression=# This is not exactly fatal, since you can work around it by pushing down the redundant join condition to one of the input relations: regression=# select * from aa a full join bb b on regression-# a.v2 = b.v3 and a.v1 = b.v2 and a.v1 = b.v1 regression-# where a.v2 = a.v1; [ okay ] But it's pretty annoying anyway. I'm trying to figure out how we could implement the query as given... regards, tom lane