X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5BEAD52880; Tue, 24 May 2005 15:12:31 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86331-05; Tue, 24 May 2005 18:12:30 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id C590A528A7; Tue, 24 May 2005 15:12:28 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j4OICP9O004475; Tue, 24 May 2005 14:12:25 -0400 (EDT) To: =?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= Cc: "pgsql-bugs@postgresql.org" , "pgsql-sql@postgresql.org" Subject: Re: [SQL] could not devise a query plan In-reply-to: <42930447.2030801@gmail.com> References: <05d801c41bd8$daf4f8f0$0403a8c0@fejleszt4> <42930447.2030801@gmail.com> Comments: In-reply-to =?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= message dated "Tue, 24 May 2005 12:39:04 +0200" Date: Tue, 24 May 2005 14:12:25 -0400 Message-ID: <4474.1116958345@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/206 X-Sequence-Number: 11910 =?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= writes: > ABSTRACT: The following query fails. > SELECT * FROM > (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa > NATURAL FULL JOIN > (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb > WHERE a+b = 3; Thanks for the report! Seems I overlooked a case when fixing the original report last year. Patch for 8.0 attached (it's the same in 7.4 too). regards, tom lane Index: joinpath.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/optimizer/path/joinpath.c,v retrieving revision 1.91.4.1 diff -c -r1.91.4.1 joinpath.c *** joinpath.c 23 Jan 2005 02:22:27 -0000 1.91.4.1 --- joinpath.c 24 May 2005 17:54:15 -0000 *************** *** 498,512 **** * nestloop path, but since mergejoin is our only join type that * supports FULL JOIN, it's necessary to generate a clauseless * mergejoin path instead. - * - * Unfortunately this can't easily be extended to handle the case - * where there are joinclauses but none of them use mergejoinable - * operators; nodeMergejoin.c can only do a full join correctly if - * all the joinclauses are mergeclauses. */ if (mergeclauses == NIL) { ! if (jointype == JOIN_FULL && restrictlist == NIL) /* okay to try for mergejoin */ ; else continue; --- 498,507 ---- * nestloop path, but since mergejoin is our only join type that * supports FULL JOIN, it's necessary to generate a clauseless * mergejoin path instead. */ if (mergeclauses == NIL) { ! if (jointype == JOIN_FULL) /* okay to try for mergejoin */ ; else continue;