And while "SELECT random() AS rr ORDER BY rr" is slightly contrived, the
example from J Decker:
select ItemName,SoldDate, date('now','-1 month') as z from
SoldItemDetails order by
SoldDate<z desc,SoldDate asc;
if it were to show the same behaviour (I haven't tested it) might break if
executed "around midnight" and might more legitimately be regarded as a bug.
My 2 cents...
Graham.
Sent from Samsung Mobile
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.
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
