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)
UNIONSELECT* 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 UNIONSELECT * 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
WHEREtmpl_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 UNIONSELECT * 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
