From: SZŰCS Gábor <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
Message-ID: <05d801c41bd8$daf4f8f0$0403a8c0@fejleszt4> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: surrano@mailbox.hu, TIR@yahoogroups.com
Subject: Re: could not devise a query plan
In-Reply-To: <05d801c41bd8$daf4f8f0$0403a8c0@fejleszt4>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox