Re: Bug in ordered views?

Sebastian Böck wrote:
> 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"!

Try removing the DISTINCT ON from your view - that should make things
clearer to you. When t.approved is true, the row is joined to all rows
of the datum table satisfying the criteria. The sort order you specify
does not guarantee a unique ordering of the rows, which explains the
inconsistency between the two cases.

/Nis