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.
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
