Subject: [BUGS] subquery results bypassed
From: pgsql-bugs@postgresql.org
To: pgsql-bugs@postgresql.org
Date: Mon, 30 Jul 2001 22:57:20 -0400 (EDT)
Reply-to: woody+postgresqlbugs@switchonline.com.au, pgsql-bugs@postgresql.org

Anthony Wood (woody+postgresqlbugs@switchonline.com.au) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
subquery results bypassed

Long Description
The second query in the example code should return:

 a | b | c 
---+---+---
(0 rows)

but actually returns

 a | b | c 
---+---+---
 1 | 3 | 2
(1 row)


which is wrong, because this tuple is not in the subquery

Sample Code
CREATE TABLE "bug" ("a" TEXT, "b" TEXT, "c" TEXT);
INSERT INTO "bug" VALUES ('1','2','1');
INSERT INTO "bug" VALUES ('1','3','2');
SELECT DISTINCT ON ("a") * FROM "bug" ORDER BY "a","c";
SELECT * FROM (SELECT DISTINCT ON ("a") * FROM "bug" ORDER BY "a","c") bug WHERE "b"='3';
DROP TABLE "bug";


No file was uploaded with this report


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly