Re: Order by behaviour

Carlos Benkendorf <carlosbenkendorf(at)yahoo(dot)com(dot)br> writes:
> For some implementation reason in 8.0.3 the query is returning the rows in the correct order even without the order by but in 8.1.1 probably the implementation changed and the rows are not returning in the correct order.

It was pure luck that prior versions gave you the result you wanted ---
as other people already noted, the ordering of results is never
guaranteed unless you say ORDER BY. The way you phrased the query
gave rise (before 8.1) to several independent index scans that just
happened to yield non-overlapping, individually sorted, segments of
the desired output, and so as long as the system executed those scans
in the right order, you got your sorted result without explicitly asking
for it. But the system wasn't aware that it was giving you any such
thing, and certainly wasn't going out of its way to do so.

In 8.1 we no longer generate that kind of plan --- OR'd index scans are
handled via bitmap-scan plans now, which are generally a lot faster,
but don't yield sorted output.

You could probably kluge around it by switching to a UNION ALL query:

SELECT * FROM iparq.ARRIPT where
(ANOCALC = 2005
and CADASTRO = 19
and CODVENCTO = 00
and PARCELA >= 00 )
UNION ALL
SELECT * FROM iparq.ARRIPT where
(ANOCALC = 2005
and CADASTRO = 19
and CODVENCTO > 00 )
UNION ALL
SELECT * FROM iparq.ARRIPT where
(ANOCALC = 2005
and CADASTRO > 19 )
UNION ALL
SELECT * FROM iparq.ARRIPT where
(ANOCALC > 2005 );

Again, the system has no idea that it's giving you data in any
useful overall order, so this technique might also break someday,
but it's good for the time being.

Of course, all of these are ugly, klugy solutions. The correct way
to solve your problem would be with a row comparison:

SELECT * FROM iparq.ARRIPT
where
(ANOCALC, CADASTRO, CODVENCTO, PARCELA) >= (2005, 19, 00, 00)
ORDER BY ANOCALC, CADASTRO, CODVENCTO, PARCELA;

Postgres doesn't currently support this (we take the syntax but don't
implement it per SQL spec, and don't understand the connection to an
index anyway :-() ... but sooner or later it'll get fixed.

regards, tom lane