| From: | "Michael" <horsten(at)aquavital(dot)de> | 
|---|---|
| To: | pgsql-bugs(at)postgresql(dot)org | 
| Subject: | BUG #1700: Error on Subselect with date in where condition | 
| Date: | 2005-06-03 12:47:46 | 
| Message-ID: | 20050603124746.52AE1F0B00@svr2.postgresql.org | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-bugs | 
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Bruno Wolff III | 2005-06-03 13:58:28 | Re: BUG #1698: Different behavior in UNIQUE and DISTINCT | 
| Previous Message | Guillaume Beaudoin | 2005-06-03 11:41:24 | BUG #1699: Invalid timestamps with timezone and interger timestamps |