The following bug has been logged online:
Bug reference: 1528
Logged by: Peter Wright
Email address: pete@flooble.net
PostgreSQL version: 7.4.7, 8.0.1
Operating system: Debian Linux (unstable)
Description: Rows returned that should be excluded by WHERE clause
Details:
Hopefully this example SQL will paste correctly -
I think this demonstrates the problem much better than I could explain in
words. The bug is shown in the two
SELECT queries with a WHERE clause. Very bizarre.
The same bug crops up on 7.4.6, 7.4.7 and 8.0.1.
pete@serf [07/Mar 6:28:50] pts/10 !19 ~ $ createdb test1
CREATE DATABASE
pete@serf [07/Mar 6:28:59] pts/10 !20 ~ $ psql test1
Welcome to psql 7.4.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test1=# create table t1 ( a smallint primary key, b smallint ) ;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for
table "t1"
CREATE TABLE
test1=# create table t2 ( a smallint primary key, b smallint ) ;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for
table "t2"
CREATE TABLE
test1=# insert into t1 values (1, 1);
INSERT 118413888 1
test1=# insert into t1 values (2, 2);
INSERT 118413889 1
test1=# insert into t2 values (1, 4);
INSERT 118413890 1
test1=# insert into t2 values (2, 8);
INSERT 118413891 1
test1=# select id, min(b) from ( select 1 as id, max(b) as b from t1 union
select 2 as id, max(b) from t2 ) as q1 group by id ;
id | min
----+-----
1 | 2
2 | 8
(2 rows)
test1=# create view qry1 as select id, min(b) from ( select 1 as id, max(b)
as b from t1 union select 2 as id, max(b) from t2 ) as q1 group by id ;
CREATE VIEW
test1=# select * from qry1 where id = 1;
id | min
----+-----
1 | 2
2 |
(2 rows)
test1=# select * from qry1 where id = 2;
id | min
----+-----
1 |
2 | 8
(2 rows)
test1=# select * from qry1;
id | min
----+-----
1 | 2
2 | 8
(2 rows)
test1=#
