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
