Thread

1. Error with union in sub-selects

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-09-06T13:17:03Z

   Martin Neimeier (nei@ibn.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
   
   
   

2. Re: Error with union in sub-selects

   Martin Neimeier <nei@ibn.de> — 2000-09-06T13:37:21Z

   Hello,
   some additional informations:
   
   - if i execute the subselect alone, it works fine !.
   - The same select statement works with sybase and oracle, so i think its a legal statement.
   - After reading in the sql2-standard, i have found nothing which restricts unions in sub-selects.
   
   (I don't want to use another rdbms ... i want to use PostgreSQL :-)))))
   
   If somebody has a workaround, then i am the happiest person for the day.
   
   cu
   Martin
   
   -- 
   Martin Neimeier
   Ingenieur-Buero Neimeier
   Schwarzach / Germany
   mailto:nei@ibn.de  / http://www.ibn.de (under heavy reconstruction)
   Tel:+49(6262)912344 / Fax:+49(6262)912347