Thread: Re: could not devise a query plan
Dear Gurus, Sorry for upping a 13-month-old thread; please tell if I should've opened another one. Here I come again, with another silly join. Please forgive me, but our queries are built from blocks :) VERSION: 7.4.6, 7.4.8, 8.0.0rc4 (sorry, no newer installed right now) 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 bbWHERE a+b = 3; DETAILS: It seems it's something about the redundant WHERE clauses. If I comment (replace with "WHERE true") any of the three WHERE clauses, it works. Session log: See below. TIA, -- G. serv1:tir=> \d cdqp Table "pg_temp_20.cdqp" Column | Type | Modifiers --------+---------+----------- a | integer | b | integer | serv1:tir=> SELECT * FROM serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa serv1:tir-> NATURAL FULL JOIN serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb serv1:tir-> WHERE a+b = 3; ERROR: could not devise a query plan for the given query serv1:tir=> SELECT * FROM serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa serv1:tir-> NATURAL FULL JOIN serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb serv1:tir-> WHERE true; a | b ---+--- 1 | 2 (1 row)
Szűcs Gábor <surrano@gmail.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;