Does the same thing happen if you select from Psalms, or Thessalonians, instead of Proverbs? Sorry. Little religion humor. Too early in the morning and I haven't had my coffee, yet.
On Fri, Nov 11, 2016 at 4:34 AM, Cecil Westerhof <[email protected]> wrote: > 2016-11-11 11:22 GMT+01:00 Clemens Ladisch <[email protected]>: > > Cecil Westerhof wrote: > >> When I execute the following query: > >> SELECT *, randomiser * 1000 > >> FROM (SELECT abs(random()) / 10000000000000000 AS randomiser); > >> > >> I get normal results: > >> 61|61000 > >> > >> But when I change it to: > >> SELECT *, randomiser * 1000 > >> FROM (SELECT abs(random()) / 10000000000000000 AS randomiser > >> FROM proverbs) > >> LIMIT 5; > >> > >> I get very strange results: > >> 382|606000 > >> 172|148000 > >> 144|592000 > >> 181|136000 > >> 123|469000 > >> > >> What could be happening here? > > > > In the first query, the subquery is computed with a temporary table > > (actually, a coroutine) before the values of the outer query are > > computed. > > > > The second query is flattened, so it ends up like this: > > > > SELECT abs(random()) / 10000000000000000, > > abs(random()) / 10000000000000000 * 1000 > > FROM proverbs > > LIMIT 5; > > > > "randomiser" is not a table column, it is just an _alias_ for the > > expression, so this is an allowed transformation. > > > > To prevent this, violate one of the rules that the optimizer currently > > uses to determine whether flattening is possible: > > <http://www.sqlite.org/optoverview.html#flattening> > > For example, put a LIMIT clause also into the subquery. > > I need all the records, so I use: > SELECT * > , randomiser * 1000 > , randomiser * 1000 > , randomiser * 1000 > , randomiser * 1000 > , randomiser * 1000 > FROM ( > SELECT abs(random()) / 10000000000000000 AS randomiser > FROM proverbs > LIMIT (SELECT COUNT(*) FROM proverbs) > ) > LIMIT 5; > > Not very clean, but it works. > > Maybe a good idea to have an option to disable flattening? > > -- > Cecil Westerhof > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
