Bug in ordered views? - Mailing list pgsql-general

Hello all,

I think I found a little but annoying bug in views when ordering is
involved. First, my version of Postgres:

PostgreSQL 8.1.3 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC)
3.4.4 [FreeBSD] 20050518

Please try the following:

CREATE TABLE datum (
   projekt_id INTEGER NOT NULL,
   datum DATE NOT NULL,
   UNIQUE (projekt_id, datum)
) WITHOUT OIDS;

CREATE TABLE test (
   id SERIAL PRIMARY KEY,
   projekt_id INTEGER NOT NULL,
   datum DATE NOT NULL,
   approved BOOLEAN NOT NULL DEFAULT FALSE,
   test_id INTEGER,
   test_text TEXT
) WITHOUT OIDS;

CREATE OR REPLACE VIEW bug AS
SELECT DISTINCT ON (test_id,projekt_id,datum)
   t.id, d.projekt_id, d.datum, t.approved,
   t.test_id, t.test_text
   FROM datum d
   JOIN test t ON
     (t.projekt_id = d.projekt_id OR t.approved IS TRUE) AND
     t.datum <= d.datum
   ORDER BY t.test_id DESC, d.projekt_id DESC, d.datum DESC, t.datum DESC;

INSERT INTO datum (projekt_id,datum) VALUES (1,now());
INSERT INTO datum (projekt_id,datum) VALUES (1,now()+'1d'::interval);
INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES
(1,now(),1,'old');
INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES
(1,now(),2,'old');

UPDATE test SET approved = TRUE WHERE projekt_id = 1;

INSERT INTO datum (projekt_id,datum) VALUES (2,now());
INSERT INTO datum (projekt_id,datum) VALUES (2,now()+'1d'::interval);
INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES
(2,now(),1,'new');
INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES
(2,now()+'1d'::interval,2,'new');

Now do a simple select:

SELECT * FROM bug;

  id | projekt_id |   datum    | approved | test_id | test_text
----+------------+------------+----------+---------+-----------
   4 |          2 | 16.05.2006 | f        |       2 | new
   2 |          2 | 15.05.2006 | t        |       2 | old
   2 |          1 | 16.05.2006 | t        |       2 | old
   2 |          1 | 15.05.2006 | t        |       2 | old
   3 |          2 | 16.05.2006 | f        |       1 | new
   1 |          2 | 15.05.2006 | t        |       1 | old
   1 |          1 | 16.05.2006 | t        |       1 | old
   1 |          1 | 15.05.2006 | t        |       1 | old

And now constrain the above select:

SELECT * FROM bug WHERE test_id = 1;

  id | projekt_id |   datum    | approved | test_id | test_text
----+------------+------------+----------+---------+-----------
   1 |          2 | 16.05.2006 | t        |       1 | old
   1 |          2 | 15.05.2006 | t        |       1 | old
   1 |          1 | 16.05.2006 | t        |       1 | old
   1 |          1 | 15.05.2006 | t        |       1 | old

Notice that the should be 1 line with test_text showing "new"!

Did I miss anything or is it a bug?

Sebastian