The following bug has been logged online:
Bug reference: 1700
Logged by: Michael
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.3
Operating system: Windows 2000 Pro
Description: Error on Subselect with date in where condition
Details:
Hello,
we're porting our application to postgresql, and i just crashed on that
bug:
Tabledefinitions:
CREATE TABLE ms_vertreter
(
vertreternummer varchar(3) NOT NULL,
name varchar(30),
direktion varchar(3),
CONSTRAINT pk_msvertreter PRIMARY KEY (vertreternummer)
)
CREATE TABLE zugriffe
(
vertreternummer varchar(3) NOT NULL,
kdnr varchar(6) NOT NULL,
zugriff timestamp(0) NOT NULL,
vorlage timestamp(0),
notiz text NOT NULL,
id int4 NOT NULL DEFAULT nextval('public.zugriffe_seq'::text),
CONSTRAINT pk_zugriffe PRIMARY KEY (id)
)
SQL- Statement:
/*
this statement normally shows howmany costomer-contacts an employee had in a
specified period and overall.
for making it easier to understand i reduced taht query to its main
Problem:
Table "ms_vertreter" conatains a list of employees
Table "zugriffe" is a logging table for customer-contacts / empolyee
*/
select
b.vertreternummer,
b.name,
b.entries,
n.total
from
(
select
zugriffe.vertreternummer,
ms_vertreter.name,
5 as entries
from
zugriffe
inner join
ms_vertreter
on
zugriffe.vertreternummer = ms_vertreter.vertreternummer
where zugriffe.zugriff < timestamp '01.01.1997'
) as b
inner join
(
select
t.vertreternummer,
20 as total
from
zugriffe as t
) as n
on
b.vertreternummer = n.vertreternummer
Result:
ERROR: cursor "zeoscursor_91bb_610f34f9ac96" does not exist
normally should look like this:
A01 SALESMAN 5 20
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
