Message-ID: <05d801c41bd8$daf4f8f0$0403a8c0@fejleszt4>
From: "=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.hu>
To: <pgsql-sql@postgresql.org>
Cc: <TIR@yahoogroups.com>
Subject: could not devise a query plan
Date: Tue, 6 Apr 2004 15:12:46 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: 7bit

Dear Gurus,

I couldn't find the string of my email's subject on the web, except for one
place: the PostgreSQL source :)

So I'm desperate.

--
VERSION

I'm using "PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4"
with the patch for "shown aggregate columns is 0" (if you know what I mean
;) )

Version "PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4" on a
differend machine yields the same results, except as noted below.

Difference may be the version or something else, but there is a recent
mirror of the 7.3.3 db (generated from textual pg_dump) on the 7.4.1 server
that also throws the error.

--
ABSTRACT

#1. Below is a very simplified query that throws this error. The original
query used a view, CASE's, aggregates, function calls and meaningful WHERE
clauses :) The idea is to join the table with itself, but the subselects sum
different rows in field vi_m and sz_m.

Some modifications solve the problem, I show two versions.

#2. One is a single field rename (counts much in NATURAL FULL),
#3. the other is a group by construction.

There is another erroneous query:

#4. Giving an outer WHERE clause to #3, the error is back, BUT ONLY IN 7.4.1

--
DETAILS

are at the end of this email.

--
CONCLUSION

If this is enough to give me a clue, I'd be grateful.
If there is a general discussion about this error, I'd be honoured.
If you'd like to see the original query and corresponding definitions, I
think I can share it with you.
If this is a bug and has been fixed since 7.4.1, I'd take the task to
compile a newer version and see how it fares.

G.
%----------------------- cut here -----------------------%
\end

\d sztgy

             Table "pg_temp_4.sztgy"
       Column        |     Type      | Modifiers
---------------------+---------------+-----------
 az                  | integer       |
 allapot             | integer       |
 megrendelo          | integer       |
 szallito            | integer       |
 keretrendeles_az    | integer       |
 teljesites          | date          |
 szallitolevel_fajta | integer       |
 szallitas           | integer       |
 tetelszam           | integer       |
 cikk                | integer       |
 minoseg             | integer       |
 mennyiseg           | numeric(14,4) |
 fajta               | integer       |
 mennyisegi_egyseg   | integer       |
 hibastatusz         | integer       |

%----------------------- cut here -----------------------%

-- #1: This throws the error:
SELECT * FROM
(SELECT * FROM
 (SELECT
    sum(mennyiseg) as vi_m
  FROM sztgy
 ) szt_having
) AS szt
NATURAL FULL OUTER JOIN
(SELECT * FROM
 (SELECT
    sum(mennyiseg) as sz_m
  FROM sztgy
 ) vsz_having
) AS vsz;

ERROR:  could not devise a query plan for the given query


%----------------------- cut here -----------------------%

-- #2: This works, with a single rename, but useless for me:
SELECT * FROM
(SELECT * FROM
 (SELECT
    sum(mennyiseg) as sz_m
  FROM sztgy
 ) szt_having
) AS szt
NATURAL FULL OUTER JOIN
(SELECT * FROM
 (SELECT
    sum(mennyiseg) as sz_m
  FROM sztgy
 ) vsz_having
) AS vsz;
      sz_m
----------------
 530515336.8900
(1 row)

%----------------------- cut here -----------------------%

-- #3: This works, with group-by
-- the original query has group-by clause, but throws the error (see #4)
-- SELECT'ed count just to show the result. SELECT'ing * also works.
SELECT count(*) FROM
(SELECT * FROM
 (SELECT
    cikk, minoseg,
    sum(mennyiseg) as vi_m
  FROM sztgy
  group by cikk, minoseg
 ) szt_having
) AS szt
NATURAL FULL OUTER JOIN
(SELECT * FROM
 (SELECT
    cikk, minoseg,
    sum(mennyiseg) as sz_m
  FROM sztgy
  group by cikk, minoseg
 ) vsz_having
) AS vsz;

 count
-------
  1590
(1 row)

%----------------------- cut here -----------------------%

-- #4: This works only on server v7.3.3:
SELECT * FROM
(SELECT * FROM
 (SELECT
    cikk, minoseg,
    sum(mennyiseg) as vi_m
  FROM sztgy
  group by cikk, minoseg
 ) szt_having
 where cikk=101917 and minoseg=1
) AS szt
NATURAL FULL OUTER JOIN
(SELECT * FROM
 (SELECT
    cikk, minoseg,
    sum(mennyiseg) as sz_m
  FROM sztgy
  group by cikk, minoseg
 ) vsz_having
 where cikk=101917 and minoseg=1
) AS vsz;

-- 7.3.3:
  cikk  | minoseg |  vi_m   |  sz_m
--------+---------+---------+---------
 101917 |       1 | 20.0000 | 20.0000
(1 row)

-- 7.4.1:
ERROR:  could not devise a query plan for the given query