Skip site navigation (1) Skip section navigation (2)

Error with union in sub-selects

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 (view raw or flat)
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


Responses

pgsql-bugs by date

Next:From: Martin NeimeierDate: 2000-09-06 13:37:21
Subject: Re: Error with union in sub-selects
Previous:From: Piers ScannellDate: 2000-09-06 09:19:07
Subject: RE: Possible bug in referential integrity system

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group