Обсуждение: Different order by behaviour depending on where clause?

Поиск
Список
Период
Сортировка

Different order by behaviour depending on where clause?

От
Jan Bakuwel
Дата:
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



Re: Different order by behaviour depending on where clause?

От
Pavel Stehule
Дата:
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
>


Re: Different order by behaviour depending on where clause?

От
Phil Couling
Дата:
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
>


Re: Different order by behaviour depending on where clause?

От
Jan Bakuwel
Дата:
Hi Phil,

Thanks for your reply.


On 28/10/11 23:25, Phil Couling wrote:
> 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:

I think you might have misread my email, I'm quoting:

"I have a compound query with some grouping, having and order by's saved
as a view, say with name "myview".

So... I am using explicit order by's. The problem (now solved) was that
I somewhere subtle "hidden" in one of my subqueries, type casted a row
(varchar to int) but omitted to do the same one or two "levels up". This
messed up my sorting. Once I applied the type cast in every single bit
of statement or all (sub)selects, all was OK.

kind regards,
Jan