I have just spotted a couple of typos in my email below. The first two common table expressions should have been as follows -
with recursive cnt(x) as (select 1 union all select x+1 from cnt limit 3) select * from cnt; with recursive cnt(x) as (select 1 union all select x+1 from cnt) select * from cnt limit 3; On 3 January 2018 at 23:24, Shane Dev <devshan...@gmail.com> wrote: > Hi, > > This simple recursive common table expression returns all integers from 1 > to 3 as expected - > > sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt > limit 3) select * from cnt where x; > x > 1 > 2 > 3 > sqlite> > > If the LIMIT constraint is moved from the compound SELECT to the > subsequent SELECT, it works the same - > > sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt) > select * from cnt where x limit 3; > x > 1 > 2 > 3 > sqlite> > > If the LIMIT constraint is replaced with a WHERE constraint in the > compound SELECT, it still works the same - > > sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt > where x < 3) select * from cnt; > x > 1 > 2 > 3 > sqlite> > > However if the WHERE constraint is moved from the compound SELECT to the > subsequent SELECT and adjusted slightly, it selects correct results but > then hangs indefinitely - > > sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt) > select * from cnt where x <= 3; > x > 1 > 2 > 3 > [no sqlite> prompt, CPU utilization 25%] > > I assume sqlite is recursively adding rows to the queue without > considering that the subsequent SELECT only needs the first 3 of them. > > Can we conclude the query planner is unable to optimize the compound > SELECT (the part in brackets) based on the WHERE constraint of the > subsequent SELECT statement? > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
