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

Bug #526: Three levels deeply nested SELECT goes wrong

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #526: Three levels deeply nested SELECT goes wrong
Date: 2001-11-30 14:58:29
Message-ID: 200111301458.fAUEwT791104@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Maarten Fokkinga (fokkinga(at)cs(dot)utwente(dot)nl) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Three levels deeply nested SELECT goes wrong

Long Description
A 3 levels deeply nested SELECT with "R(a)" in the top-most FROM part and "R.a=S.a AND XXX" in the middle WHERE part may give different results if in a SELECT within XXX the term "S.a" is replaced by "R.a".

Since the innermost SELECT lies in the "scope" of the conjunct "R.a=S.a", it should not make any difference if "R.a" is used or "S.a".

See the simple example code to reproduce the error.

Version: PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96


Sample Code
create table R(a int);
create table S(a int, b int);
create table T(a int, b int);
insert into R values (1);
insert into R values (2);
insert into S values (2,20);
insert into S values (1,10);
insert into T values (2,20);
insert into T values (1,10);
-- the order of the rows in R, S, T is significant

-- first query:
select a from R where
exists (select b from S where 
        S.a=R.a AND S.b in (select b from T where a=S.a));
-- gives two rows (rows "(1)" and "(2)")

-- second query:
select a from R where
exists (select b from S where 
        S.a=R.a AND S.b in (select b from T where a=R.a));
-- gives one row ("(1)" only)

No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-11-30 15:01:07
Subject: Re: Vá: Va: [BUGS] Va: [BUGS] Bug #519: Bug in order b y clausule
Previous:From: Sandor.VigDate: 2001-11-30 09:54:11
Subject: Vá: Va: [BUGS] Va: [BUGS] Bug #?==?iso-8859-2?Q?519: Bug in order b y clausule

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