Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL)
-
None
Description
testcase:
WITH RECURSIVE cte AS ( |
SELECT 1 AS a UNION ALL |
SELECT NULL FROM cte WHERE a IS NOT NULL) |
SELECT * FROM cte; |
it produces an infinite loop, so will get (max_recursive_iterations) strings with 0 (plus 1 sting with 1)
if we use union distinct:
MariaDB [test]> WITH RECURSIVE cte AS (
|
-> SELECT 1 AS a UNION
|
-> SELECT NULL FROM cte WHERE a IS NOT NULL)
|
-> SELECT * FROM cte;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 0 |
|
+---+
|
2 rows in set (0.001 sec)
|
Attachments
Issue Links
- relates to
-
MDEV-16505 NULL is treated as 0 in CTE
-
- Open
-
-
MDEV-15447 Import CTE tests from MySQL 8
-
- Stalled
-
Alice,
The type of CTE is determined only by the type of anchor parts.
In your case the type of the anchor part is INT NOT NULL.
The recursive part returns NULL that is converted to this type.
Please compare with:
MariaDB [test]> create table t1 (a int);
MariaDB [test]> insert into t1 values (1);
MariaDB [test]> WITH RECURSIVE cte AS (SELECT a FROM t1 UNION SELECT NULL FROM cte WHERE a IS NOT NULL) SELECT * FROM cte;
+------+
| a |
+------+
| 1 |
| NULL |
+------+