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 > > > ?
