Thread: Different order by behaviour depending on where clause?
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
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 >
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 >
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