Hi The following query is distilled down from a real production query for ease of reproduction:
SELECT 1 AS id , 2 AS tmpl_id WHERE FALSE -- (in production, only rarely will this clause return a row) UNION SELECT * FROM (SELECT 2 AS id, 96 AS tmpl_id UNION SELECT 3 AS id, 101 AS tmpl_id ORDER BY 1 ) tmpl WHERE tmpl_id IS NOT NULL In 8.3 and earlier it consistently produces this result: id | tmpl_id ----+--------- 2 | 96 3 | 101 This is the result I am expecting this query to produce ("expecting" as in this is what I intend it to do, and it seems to work as intended). In 8.4beta1 the result is unpredictable; sometimes the row with id 3 is returned first, e.g.: id | tmpl_id ----+--------- 3 | 101 2 | 96 Changing the values selected for tmpl_id produces different ordering; on both systems tested [*], changing 101 to 102 on the third select produces the expected ordering consistently. 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, 102 AS tmpl_id ORDER BY 1 ) tmpl WHERE tmpl_id IS NOT NULL id | tmpl_id ----+--------- 2 | 96 3 | 102 Using other values produces varying results, I can't see a pattern. Note that removing the first SELECT completely produces the expected ordering consistently: SELECT * FROM (SELECT 2 AS id, 96 AS tmpl_id UNION SELECT 3 AS id, 101 AS tmpl_id ORDER BY 1 ) tmpl WHERE tmpl_id IS NOT NULL id | tmpl_id ----+--------- 2 | 96 3 | 101 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. [*] tested on: - Ubuntu 8.10 running on VIA C7-M - OS X 10.5 running on Intel Core Duo Regards Ian Barwick -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers