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