BTW, MySQL also seems to do it 'correctly'. Plus, it apparently recognizes if the random() expression in the ORDER BY is the same as the SELECT one and again sort correctly (without re-evaluating) and without needing an alias.
-----Original Message----- From: Domingo Alvarez Duarte Sent: Wednesday, August 26, 2015 12:23 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] order by not working in combination with random() I tested this on postgresql and I get a correctly ordered list for "SELECT rr FROM myView ORDER BY rr;" So I also will say that the sqlite behavior of reevaluate columns function calls on "order by" is a bug. Cheers ! > Wed Aug 26 2015 9:50:48 am CEST CEST from "Simon Slavin" ><slavins at bigfraud.org> Subject: Re: [sqlite] order by not working in >combination with random() > > 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 > > > _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
