Your recursive Common Table Expression (CTE) lacks a limit or where
clause. Without a limit or where clause, recursive CTEs generate an
unlimited number of rows. They take SQLiteSpy forever to display.
To remedy this, SQLiteSpy allows to abort any query by pressing the
[ESC] key. The [ESC] key triggers sqlite3_interrupt(), which aborts the
query and returns as early as possible. SQLiteSpy then displays all
records which the SQLite engine has produced so far.
The [ESC] key cancels any kind of queries, not just recursive CTEs. This
makes the [ESC] key ideal for testing, especially queries which generate
lots of output or just take a long time to run.
In production, you might want to make sure you add a limit clause to
your recursive CTE so your application does not stall.
This example takes SQLiteSpy about 30 ms and never produces more than
10000 rows:
WITH RECURSIVE r(i) AS (
values(0)
UNION ALL SELECT i+1 AS x FROM r limit 10000
) SELECT * FROM r
Ralf
On 13.06.2019 12:39, Миронов Леонид wrote:
You may use the following infinitely looping code for testing_______________________________________________
WITH RECURSIVE r(i) AS (
values(0)
UNION ALL SELECT i+1 AS x FROM r
) SELECT * FROM r