Re: Different order by behaviour depending on where clause?

Hi Jan

It is my understanding that a select query without "order by" has an
undefined order.
Though I'm sure you understand the dangers of using something which is
undefined, I'm going reiterate them here:

In general you should never rely on something which is undefined.
Attempting to investigate undefined behavior is a very bad idea
because it can change without warning - especially with databases and
explain plans.

Even when it appears to work (example a and b) you must NEVER rely on it.
This is clearly being affected by the way postgres plans the query.
Since the plan can change depending on the profile of the data, your
application may order things correctly when you build it but suddenly
stop ordering correctly years down the line when it has been filled
with data. Also when postgres gets upgraded no-one guarantees that
undefined behavior will remain unchanged.

As tedious as this is, you're just going to have to order this in your
select queries.

Regards

On 28 October 2011 06:13, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello
>
> 2011/10/28 Jan Bakuwel <jan.bakuwel@greenpeace.org>:
>> Hi,
>>
>> I have a compound query with some grouping, having and order by's saved
>> as a view, say with name "myview".
>>
>> A) select * from "myview" returns the results as expected in the correct
>> order (the order by is on fields "Category", "Year", "Month" and a few
>> other fields). The results are correctly ordered by these fields as
>> specified in the view.
>>
>> B) select * from "myview" where "Year"=2011 and "Month"=1 also returns
>> the results as expected in the correct order (a subset of A).
>>
>> however
>>
>> C) select * from "myview" where "Year"=2011 and "Month" >= 1 and "Month"
>> <= 1 returns the same resultset as B but the order of the rows is not
>> correct (not even close; haven't been able to see any pattern).
>>
>> Any idea how I can further analyse/diagnose this?
>>
>> regards,
>> Jan
>>
>
> Look on EXPLAIN - these queries will have a different execution plan
>
> http://www.postgresql.org/docs/8.4/static/sql-explain.html
>
> Regards
>
> Pavel Stehule
>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>