I guess that a query likeSELECT random() as x FROM some_non_empty_table ORDER
BY xis indeed transformed intoSELECT random() as x FROM some_non_empty_table
ORDER BY random()prior to its execution.
Question is why the implementation does that. Both from an efficiency point
(don't calculate something twice) as from a "what is the intention of the
query"-point-of-view, it seems odd. In my opinion both queries are different.
The first asks to sort on a specific field which is present in the result, the
second asks to randomly shuffle the result (of random values).
And what about other functions than random(), say avg(), sum(), etc.? Consider
the query:SELECT x, AVG(y) as z FROM t GROUP BY x HAVING z>10 ORDER BY z does
that mean that the implementation is calculating the average three times? It
seems rather a waste of CPU power.
> To: sqlite-users at mailinglists.sqlite.org
> From: clemens at ladisch.de
> Date: Mon, 17 Aug 2015 12:01:58 +0200
> Subject: Re: [sqlite] order by not working in combination with random()
> The ORDER BY clause _is_ working, but it sorts the results
> of _another_ call to random().
>
> Just because the ORDER BY clause refers to a column of the
> SELECT clause does not mean that the value is not computed
> a second time.
