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
