pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDifferent order by behaviour depending on where clause?
4+ messages / 3 participants
[nested] [flat]
* Different order by behaviour depending on where clause?
@ 2011-10-28 04:53 Jan Bakuwel <jan.bakuwel@greenpeace.org>
2011-10-28 05:13 ` Re: Different order by behaviour depending on where clause? Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jan Bakuwel @ 2011-10-28 04:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Different order by behaviour depending on where clause?
2011-10-28 04:53 Different order by behaviour depending on where clause? Jan Bakuwel <jan.bakuwel@greenpeace.org>
@ 2011-10-28 05:13 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-10-28 10:25 ` Re: Different order by behaviour depending on where clause? Phil Couling <couling@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Pavel Stehule @ 2011-10-28 05:13 UTC (permalink / raw)
To: Jan Bakuwel <jan.bakuwel@greenpeace.org>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Different order by behaviour depending on where clause?
2011-10-28 04:53 Different order by behaviour depending on where clause? Jan Bakuwel <jan.bakuwel@greenpeace.org>
2011-10-28 05:13 ` Re: Different order by behaviour depending on where clause? Pavel Stehule <pavel.stehule@gmail.com>
@ 2011-10-28 10:25 ` Phil Couling <couling@gmail.com>
2011-10-29 21:16 ` Re: Different order by behaviour depending on where clause? Jan Bakuwel <jan.bakuwel@greenpeace.org>
0 siblings, 1 reply; 4+ messages in thread
From: Phil Couling @ 2011-10-28 10:25 UTC (permalink / raw)
To: Jan Bakuwel <jan.bakuwel@greenpeace.org>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Different order by behaviour depending on where clause?
2011-10-28 04:53 Different order by behaviour depending on where clause? Jan Bakuwel <jan.bakuwel@greenpeace.org>
2011-10-28 05:13 ` Re: Different order by behaviour depending on where clause? Pavel Stehule <pavel.stehule@gmail.com>
2011-10-28 10:25 ` Re: Different order by behaviour depending on where clause? Phil Couling <couling@gmail.com>
@ 2011-10-29 21:16 ` Jan Bakuwel <jan.bakuwel@greenpeace.org>
0 siblings, 0 replies; 4+ messages in thread
From: Jan Bakuwel @ 2011-10-29 21:16 UTC (permalink / raw)
To: Phil Couling <couling@gmail.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-10-29 21:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-28 04:53 Different order by behaviour depending on where clause? Jan Bakuwel <jan.bakuwel@greenpeace.org>
2011-10-28 05:13 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-10-28 10:25 ` Phil Couling <couling@gmail.com>
2011-10-29 21:16 ` Jan Bakuwel <jan.bakuwel@greenpeace.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox