Re: Bug in ordered views?

Tom Lane wrote:
> Nis Jorgensen <nis@superlativ.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