From: | pgsql-bugs(at)postgresql(dot)org |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Error with union in sub-selects |
Date: | 2000-09-06 13:17:03 |
Message-ID: | 200009061317.e86DH3S58967@hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Martin Neimeier (nei(at)ibn(dot)de) reports a bug with a severity of 1
The lower the number the more severe it is.
Short Description
Error with union in sub-selects
Long Description
Version: PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2
If i try to use unions in a subselect, postgres reports an parser-error.
ERROR: parser: parse error at or near "union"
see Example. (stipped to essentials)
Sample Code
create table product
(
id int4 not null, -- internal id of product
name TEXT not null, -- name of the product
price numeric not null, -- the price of the product
constraint product_pk
primary key (id)
);
create table product_special
(
id int4 not null, -- internal id of product_special
product_id int4 not null, -- reference to product
start_date date not null, -- when does the special starts
end_date date not null, -- when does the special ends
price numeric not null, -- modified price of product in special-period
constraint product_special_pk -- pk
primary key(id),
constraint product_special_fk1 -- fk to product->id
foreign key (product_id)
references product(id)
match full
on delete cascade
on update cascade
deferrable
);
INSERT INTO product
(id,name,price) VALUES
(1,'Merlot 1996','10.00');
INSERT INTO product
(id,name,price) VALUES
(2,'Gaya & Ray 1994','8.00');
INSERT INTO product_special
(id,product_id,start_date,end_date,price) VALUES (1,1,'2000-09-01','2000-11-30','8.00');
INSERT INTO product_special
(id,product_id,start_date,end_date,price) VALUES (2,2,'2000-01-01','2000-12-02','7.00');
SELECT
p.id as id,
p.name as name
from product p
where
p.id in (select id
from product
where
price >= '9.00' and
price <= '50' and
id not in (select product_id
from product_special
where
start_date <= '2000-9-6' and
end_date >= '2000-9-6')
union
select product_id as id
from product_special
where
price >= '9.00' and
price <= '50' and
start_date <= '2000-9-6' and
end_date >= '2000-9-6');
No file was uploaded with this report
From | Date | Subject | |
---|---|---|---|
Next Message | Martin Neimeier | 2000-09-06 13:37:21 | Re: Error with union in sub-selects |
Previous Message | Piers Scannell | 2000-09-06 09:19:07 | RE: Possible bug in referential integrity system |