Re: [BUGS] could not devise a query plan

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: [BUGS] could not devise a query plan

Gábor SZŰCS
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 bb
        WHERE 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)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: [BUGS] [SQL] could not devise a query plan

Tom Lane-2
=?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= <[hidden email]> 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;

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org