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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org