could not devise a query plan - Mailing list pgsql-sql

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_havingwhere
cikk=101917and 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_havingwhere
cikk=101917and 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