On 26 Aug 2015, at 8:00am, J Decker <d3ck0r at gmail.com> wrote:
> select random() order by random() should definately reprocess the function...
Agreed. But I've come to the conclusion that
SELECT random() AS rr ORDER BY rr
should not. Here's a nasty result
SQLite version 3.8.10.2 2015-05-20 18:14:01
Enter ".help" for usage hints.
sqlite> SELECT rr FROM myView ORDER BY rr;
92
28
97
55
-39
I feel that this result must be wrong. Here's how to obtain it:
SQLite version 3.8.10.2 2015-05-20 18:14:01
Enter ".help" for usage hints.
sqlite> CREATE TABLE myTable (a INTEGER);
sqlite> INSERT INTO myTable VALUES (1),(2),(3),(4),(5);
sqlite> CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable;
sqlite> SELECT * FROM myView;
1|0
2|43
3|-33
4|24
5|-81
sqlite> SELECT * FROM myView;
1|0
2|-77
3|84
4|35
5|45
sqlite> SELECT rr FROM myView ORDER BY rr;
92
28
97
55
-39
sqlite>
In my example it's obvious I'm using a VIEW because of what I named it, but in
complicated real-life code this may be concealed and the programmer may think
they're doing a simple SELECT on static values. They shouldn't have to worry
about such a bizarre result in a simple SELECT command.
I'm gonna call this a bug whether the dev team does or not.
Simon.
