Re: Bug in ordered views?

Nis Jorgensen <nis(at)superlativ(dot)dk> writes:
> 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.

More specifically, look at this:

select t.test_id,d.projekt_id,d.datum,t.datum, t.id, t.approved,
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;
test_id | projekt_id | datum | datum | id | approved | test_text
---------+------------+------------+------------+----+----------+-----------
2 | 2 | 2006-05-16 | 2006-05-16 | 4 | f | new
2 | 2 | 2006-05-16 | 2006-05-15 | 2 | t | old
2 | 2 | 2006-05-15 | 2006-05-15 | 2 | t | old
2 | 1 | 2006-05-16 | 2006-05-15 | 2 | t | old
2 | 1 | 2006-05-15 | 2006-05-15 | 2 | t | old
1 | 2 | 2006-05-16 | 2006-05-15 | 1 | t | old
1 | 2 | 2006-05-16 | 2006-05-15 | 3 | f | new
* 1 | 2 | 2006-05-15 | 2006-05-15 | 3 | f | new
* 1 | 2 | 2006-05-15 | 2006-05-15 | 1 | t | old
1 | 1 | 2006-05-16 | 2006-05-15 | 1 | t | old
1 | 1 | 2006-05-15 | 2006-05-15 | 1 | t | old
(11 rows)

The two rows I've marked with * are identical in all the columns that
are used in the DISTINCT ON and ORDER BY clauses, which means it's
unspecified which one you get out of the DISTINCT ON. I'm not entirely
sure why adding the test_id condition changes the results, but it may be
an artifact of qsort() behavior. Anyway you need to constrain the ORDER
BY some more to ensure you get well-defined results from the DISTINCT ON.

regards, tom lane