Обсуждение: BUG #1700: Error on Subselect with date in where condition

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_610f34f9ac96" does not exist


normally should look like this:

A01 SALESMAN 5 20

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_610f34f9ac96" 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_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/

[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:
> >
> > 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_610f34f9ac96" 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_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/