Re: Bug in ordered views?

Tom Lane wrote:
> 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

Classical "pilot error". I recognized the missing order by a few
minutes after sending my message. Sorry for the noise, but it looked
totally reproducible, no matter what kind of where clause I added.

Thanks anyway

Sebastian