Replies: 4 comments
-
That query is going to take an absurd amount of time for the work it's doing. If you know what you are doing you can always raise it yourself in the query or the profile. |
Beta Was this translation helpful? Give feedback.
0 replies
-
slow and exceeded new value setting localhost.localdomain :) set max_recursive_cte_evaluation_depth=10001;
SET max_recursive_cte_evaluation_depth = 10001
Query id: a142ae58-ea57-47ce-98f9-dfb189b2c9e6
Ok.
0 rows in set. Elapsed: 0.002 sec.
localhost.localdomain :) with recursive t as (select 1 a
union all select a+1 from t where a<10000)
select sum(a) from t;
WITH RECURSIVE t AS
(
SELECT 1 AS a
UNION ALL
SELECT a + 1
FROM t
WHERE a < 10000
)
SELECT sum(a)
FROM t
Query id: 6ee1d5ea-dc5a-40ca-8a68-cbc76521afb6
↘ Progress: 19.97 thousand rows, 19.97 KB (380.83 rows/s., 380.83 B/s.) (0.6 CPU, 12.90 MB RAM)
Elapsed: 52.496 sec. Processed 19.97 thousand rows, 19.97 KB (380.45 rows/s., 380.45 B/s.)
Peak memory usage: 12.30 MiB.
Received exception:
Code: 306. DB::Exception: Maximum recursive CTE evaluation depth (10001) exceeded, during evaluation of t AS (SELECT 1 AS a UNION ALL SELECT a + 1 FROM t WHERE a < 10000). Consider raising max_recursive_cte_evaluation_depth setting.: While executing RecursiveCTESource. (TOO_DEEP_RECURSION) |
Beta Was this translation helpful? Give feedback.
0 replies
-
compare to duckdb and sqlite root@localhost:/data/data/com.termux/files/home/103# ./duckdb
v0.10.3 70fd6a8a24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D .timer on
D with recursive t as (select 1 a
union all select a+1 from t where a<10000)
select sum(a) from t;
100% ▕████████████████████████████████████████████████████████████▏
┌──────────┐
│ sum(a) │
│ int128 │
├──────────┤
│ 50005000 │
└──────────┘
Run Time (s): real 7.088 user 5.257192 sys 7.087834
D .exit
root@localhost:/data/data/com.termux/files/home/103
root@localhost:/data/data/com.termux/files/home/103# sqlite3
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
sqlite> with recursive t as (select 1 a
union all select a+1 from t where a<10000)
select sum(a) from t;
50005000
Run Time: real 0.007 user 0.006631 sys 0.000000 |
Beta Was this translation helpful? Give feedback.
0 replies
-
it is a bug of data type overflows. workaround is cast 1 to a bigger type localhost.localdomain :) with recursive t as (select cast(1 as Int32) a
union all select a+1 from t where a<10000)
select sum(a) from t;
WITH RECURSIVE t AS
(
SELECT CAST(1, 'Int32') AS a
UNION ALL
SELECT a + 1
FROM t
WHERE a < 10000
)
SELECT sum(a)
FROM t
Query id: 04b88e09-8c53-4d25-a9e5-961c7ad7de54
┌───sum(a)─┐
1. │ 50005000 │ -- 50.01 million
└──────────┘
1 row in set. Elapsed: 52.480 sec. Processed 19.99 thousand rows, 79.96 KB (380.91 rows/s., 1.52 KB/s.)
Peak memory usage: 12.50 MiB. |
Beta Was this translation helpful? Give feedback.
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
when run
it rasie an error
Beta Was this translation helpful? Give feedback.
All reactions