Thread: Error with union in sub-selects

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

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

Martin Neimeier wrote:

> 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.
>

Create a temp table (I did it this way):

instead of
select x from table where x in (select A union select B);

create temp table t1 (x type_of_x; );
insert into t1 select A union select B;
select x from table where exists (select t1.x from t1 where t1.x=table.x);

Using exists instead of in circumvents another restriction of PostgreSQL.

Tom Lane said, these bugs would be addressed during the query tree reorganization (7.2, in 2001)

>
> (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.

Could be ... if you can live with this ...

    Christof