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

[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users