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