BUG #1528: Rows returned that should be excluded by WHERE clause

The following bug has been logged online:

Bug reference: 1528
Logged by: Peter Wright
Email address: pete(at)flooble(dot)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(at)serf [07/Mar 6:28:50] pts/10 !19 ~ $ createdb test1

CREATE DATABASE

pete(at)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=#