> On 2. Mar 2018, at 21:39, Dan Kennedy <danielk1...@gmail.com> wrote: > > On 03/01/2018 05:37 PM, Adrián Medraño Calvo wrote: >> Dear SQLite, >> >> The following SQL script shows a query selecting data from a recursive CTE >> and filtering it. I expected the optimizer to apply the filter to the >> recursive CTE directly, and indeed the documentation of pushDownWhereTerms >> (src/select.c:3833) indicates this possibility when various conditions are >> satisfied. As far as I can see, the conditions are satisfied, but the query >> is nonetheless not optimized. This indicates a misunderstanding on my part, >> or an oversight in SQLite. >> >> -- A table containing some numbers. >> CREATE TABLE t (v INT PRIMARY KEY); >> INSERT INTO t >> VALUES (0), (1), (2), (3), (4), (5); >> >> -- Recursive query relating a number a sequence of numbers from "t" equal or >> -- greater than it. >> EXPLAIN QUERY PLAN >> WITH RECURSIVE >> eqgrseq(initial, next) AS (SELECT v, v >> FROM t >> UNION >> SELECT eqgrseq.initial, t.v >> FROM eqgrseq >> JOIN t >> ON (t.v = eqgrseq.next + 1)) >> SELECT eqgrseq.initial, eqgrseq.next >> FROM eqgrseq >> WHERE eqgrseq.initial = :initial; > > > It's quite a special case really. You can push the WHERE term down only > because it refers to a column that is always copied without modification > from the initial result set into any recursive results. You could not > push down a term like: > > WHERE eqgrseq.next = :next: > > Dan.
Indeed… pushing the WHERE clause would absolutely be wrong, as it would prevent generating part of the results. I was blind to that, thank you for pointing it out. Best regards, Adrián. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
