Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
Description
The CTE below is intentionally endless. It's controlled by max_recursive_iterations, which works just fine. But the execution time is radically different, depending on whether we use a VARCHAR column, or a TEXT column of the same length.
DROP TABLE IF EXISTS t; |
CREATE TABLE t (c1 VARCHAR(255), c2 TINYTEXT); |
INSERT INTO t VALUES ('a','a'),('b','b'),('c','c'),('d','d'); |
|
|
SET max_recursive_iterations = 8; |
|
|
WITH RECURSIVE cte(f) AS ( |
SELECT c2 FROM t |
UNION |
SELECT c2 FROM t, cte |
) SELECT COUNT(*) FROM cte; |
|
|
WITH RECURSIVE cte(f) AS ( |
SELECT c1 FROM t |
UNION |
SELECT c1 FROM t, cte |
) SELECT COUNT(*) FROM cte; |
|
Result with TEXT column (15.23 sec) |
MariaDB [test]> WITH RECURSIVE cte(f) AS ( |
-> SELECT c2 FROM t |
-> UNION |
-> SELECT c2 FROM t, cte |
-> ) SELECT COUNT(*) FROM cte; |
+----------+ |
| COUNT(*) | |
+----------+ |
| 4 |
|
+----------+ |
1 row in set (15.23 sec) |
|
Result with VARCHAR column (0.01 sec) |
MariaDB [test]> WITH RECURSIVE cte(f) AS ( |
-> SELECT c1 FROM t |
-> UNION |
-> SELECT c1 FROM t, cte |
-> ) SELECT COUNT(*) FROM cte; |
+----------+ |
| COUNT(*) | |
+----------+ |
| 4 |
|
+----------+ |
1 row in set (0.01 sec) |
If I understand the idea, with UNION the temporary table should never grow above 4 rows, so there is no reason for such long execution time.
If I replace UNION with UNION ALL, execution time naturally grows, and it becomes approximately the same for VARCHAR and TEXT column. The result (final count) is the same, which shows that the number of iterations is the same too.
MariaDB [test]> WITH RECURSIVE cte(f) AS ( |
-> SELECT c2 FROM t |
-> UNION ALL |
-> SELECT c2 FROM t, cte |
-> ) SELECT COUNT(*) FROM cte; |
+----------+ |
| COUNT(*) | |
+----------+ |
| 349524 |
|
+----------+ |
1 row in set (21.04 sec) |
MariaDB [test]> WITH RECURSIVE cte(f) AS ( |
-> SELECT c1 FROM t |
-> UNION ALL |
-> SELECT c1 FROM t, cte |
-> ) SELECT COUNT(*) FROM cte; |
+----------+ |
| COUNT(*) | |
+----------+ |
| 349524 |
|
+----------+ |
1 row in set (19.43 sec) |
Attachments
Issue Links
- relates to
-
-
- Closed
-