The following bug has been logged online:

Bug reference: 1700
Logged by: Michael
Email address: horsten (AT) aquavital (DOT) de
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

On Fri, Jun 03, 2005 at 01:47:46PM +0100, Michael wrote:
Are you sure that "Subselect with date in where condition" accurately
describes the conditions that produce the error? Do you get the
error if you don't use a subselect or if you don't use a date in
the WHERE condition?

One possibility is that you're trying to use a cursor outside of the
transaction in which it was declared. "zeoscursor_91bb_610f34f9ac96"
looks automatically generated -- where is it coming from? What
client interface are you using? How are you handling transactions?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

[Please copy the mailing list on replies. I'm sending this copy of
your message to the list so others can see what the problem was.]

On Fri, Jun 03, 2005 at 05:48:19PM +0200, Michael Horsten wrote:
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match