Thanks both that is really helpful! I hadn't come across the query flattening optimisation before. The documentation is helpful as is prepending EXPLAIN.
Thanks again. Kind Regards Keith On 11 April 2017 at 09:55, Clemens Ladisch <clem...@ladisch.de> wrote: > Keith Maxwell wrote: > > Can anyone please explain the results of the query below? > > > > sqlite> WITH > > ...> t1(X) AS ( > > ...> SELECT 1 > > ...> UNION ALL > > ...> SELECT X+1 FROM t1 > > ...> LIMIT 9 > > ...> ) > > ...> ,t2(Y) AS ( > > ...> SELECT abs(random() % 10) FROM t1 > > ...> ) > > ...> SELECT Y, Y <= 5, Y BETWEEN 0 AND 5, Y < 6 FROM t2; > > 7|1|1|0 > > 3|1|0|0 > > 2|1|1|1 > > 2|0|0|1 > > 0|1|1|1 > > 3|1|1|0 > > 5|1|1|0 > > 6|1|1|0 > > 0|1|1|1 > > You have four separate calls to random() in each row. > > This is because of subquery flattening. > http://www.sqlite.org/optoverview.html#flattening > > You can force the database to create a temporary result for t2 > by violating at least one of these rules, e.g., add "LIMIT 9" to > t2 and "WHERE 1" to the final SELECT. > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
