PostgreSQL Bugs - BUG #1700: Error on Subselect with date in where condition

Author

BUG #1700: Error on Subselect with date in where condition

Michael

2005-06-03, 9:24 am

The following bug has been logged online:

Bug reference: 1700
Logged by: Michael
Email address: horsten@aquavital.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_610f
34f9ac96" does not exist

normally should look like this:

A01 SALESMAN 5 20

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Michael Fuhr

2005-06-03, 9:24 am

On Fri, Jun 03, 2005 at 01:47:46PM +0100, Michael wrote:
>
> Description: Error on Subselect with date in where condition

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?

> ERROR: cursor " zeoscursor_91bb_610f
34f9ac96" does not exist

One possibility is that you're trying to use a cursor outside of the
transaction in which it was declared. " zeoscursor_91bb_610f
34f9ac96"
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@postgresql
.org

Michael Fuhr

2005-06-03, 11:24 am

[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:
> I'm really sorry,
> it was my fault.
> I only executed the SQL Statement in "EMS SQL Query", not in the pgAdmin III SQL Query.
> After i pasted the Code in the pgAdminIII it worked fine,
> so:
> It is a problem of the EMS-Tool, not of PostgreSQL!!!!
> Sorry for disturbing you!
>
> 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_610f
34f9ac96"
> 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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Sponsored Links