Re: [SQL] could not devise a query plan

=?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= <surrano(at)gmail(dot)com> 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;