Re: [HACKERS] 8.4b1: Query returning results in different order to 8.3
Greg Stark wrote: UNION ALL should still preserve the order of the subqueries. It just returns all the rows of each subquery one after the other with no other work. Although without an ORDER BY that's not well-defined and thus not guaranteed to work in future versions either. I doubt we're going to change that anytime soon, but I wouldn't rely on it in an application. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4b1: Query returning results in different order to 8.3
Ian Barwick wrote: Note I'm not sure whether this is a bug, or whether the assumption made for the original query (that the row order returned by the subquery would be carried over to the main part of the query) is incorrect but just happened to work as expected pre-8.4. The latter. Without an ORDER BY (at the outermost level), the order of the result is not well defined. Before 8.4, UNION was always performed by a Sort + Unique, which explains why the output is always sorted in previous releases. 8.4 knows how to perform it with a Hash Aggregate, which doesn't yield sorted output. You can look at the EXPLAIN output to see the difference. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4b1: Query returning results in different order to 8.3
Ian Barwick wrote: Workaround / solution to produce consistent results is to move the ORDER BY 1 to the main SELECT clause: SELECT 1 AS id , 2 AS tmpl_id WHERE FALSE UNION SELECT * FROM (SELECT 2 AS id, 96 AS tmpl_id UNION SELECT 3 AS id, 101 AS tmpl_id ) tmpl WHERE tmpl_id IS NOT NULL ORDER BY 1 (The full version of this query in its original form is in production on 8.2 and 8.3 versions and I am confident it has always produced consistent results. It is used to select the appropriate template for pages on a website and someone would have noticed long before now if it was serving up the wrong template). Note I'm not sure whether this is a bug, or whether the assumption made for the original query (that the row order returned by the subquery would be carried over to the main part of the query) is incorrect but just happened to work as expected pre-8.4. I don't believe it's a bug - the assumption is one you're not entitled to make. Your workaround is the correct solution, ISTM. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4b1: Query returning results in different order to 8.3
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Ian Barwick wrote: Note I'm not sure whether this is a bug, or whether the assumption made for the original query (that the row order returned by the subquery would be carried over to the main part of the query) is incorrect but just happened to work as expected pre-8.4. The latter. Without an ORDER BY (at the outermost level), the order of the result is not well defined. Before 8.4, UNION was always performed by a Sort + Unique, which explains why the output is always sorted in previous releases. 8.4 knows how to perform it with a Hash Aggregate, which doesn't yield sorted output. This is mentioned in the release notes, but I suppose we'd better promote it to the observe the following incompatibilities list... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4b1: Query returning results in different order to 8.3
On 18 Apr 2009, at 22:22, Tom Lane wrote: This is mentioned in the release notes, but I suppose we'd better promote it to the observe the following incompatibilities list... This is a really funny one, because people naturally expect UNION [ALL] to stay in the same order. Unlike the table, order here cannot change by inserts/updates, etc. I am sure many, even well experienced will stumble upon that one. Me is guessing, that UNION [ALL] performance just had to be improved for CTEs ? Or was it something completely separate. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4b1: Query returning results in different order to 8.3
On Sat, Apr 18, 2009 at 11:19 PM, Grzegorz Jaskiewicz g...@pointblue.com.pl wrote: This is a really funny one, because people naturally expect UNION [ALL] to stay in the same order. Unlike the table, order here cannot change by inserts/updates, etc. I am sure many, even well experienced will stumble upon that one. There is a misunderstanding here. UNION has *never* preserved the order of the subqueries before. In the OP's query it was *not* preserving the order. It was a coincidence that the order the subquery was in was sorted on the first field and since UNION resorted the whole result set by all the fields in order that meant it was in order by the first field. Me is guessing, that UNION [ALL] performance just had to be improved for CTEs ? Or was it something completely separate. Hash aggregates were new relative to set operations which have been around a very long time. They didn't take advantage of the new code but it was always fairly obvious that they should eventually have been changed to. I think it came along with fixing DISTINCT to use hash aggregates which was a similar situation. UNION ALL should still preserve the order of the subqueries. It just returns all the rows of each subquery one after the other with no other work. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4b1: Query returning results in different order to 8.3
2009/4/19 Tom Lane t...@sss.pgh.pa.us Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Ian Barwick wrote: Note I'm not sure whether this is a bug, or whether the assumption made for the original query (that the row order returned by the subquery would be carried over to the main part of the query) is incorrect but just happened to work as expected pre-8.4. The latter. Without an ORDER BY (at the outermost level), the order of the result is not well defined. Before 8.4, UNION was always performed by a Sort + Unique, which explains why the output is always sorted in previous releases. 8.4 knows how to perform it with a Hash Aggregate, which doesn't yield sorted output. This is mentioned in the release notes, but I suppose we'd better promote it to the observe the following incompatibilities list... Thanks for clarifying that. The relevant section in the release notes (which I managed to miss) is this: http://www.postgresql.org/docs/8.4/static/release-8-4.html#AEN93685 It would certainly be worth an explicit mention as I imagine the previous behaviour has been consistent enough for queries to have come to rely on it. Regards Ian Barwick
