could not devise a query plan

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