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
