[MDEV-15159] NULL is treated as 0 in CTE Created: 2018-02-01  Updated: 2018-06-18  Resolved: 2018-05-15

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.3
Fix Version/s: 10.3.7

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-16505 NULL is treated as 0 in CTE Open
relates to MDEV-15447 Import CTE tests from MySQL 8 Stalled

 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)



 Comments   
Comment by Igor Babaev [ 2018-02-05 ]

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 |
+------+

Comment by Alice Sherepa [ 2018-02-06 ]

Igor, please reconsider this case.
My point is that it should be nullable or else it can be án easy source of mistakes.
Instead of NULL can be some complicated expression/function, that will return NULL. Then it truncates to 0, and we can get wrong results. If the CTE's recursive part produces wider values for a column than the CTE's nonrecursive part, it is possible to use CAST, but what should one do in the case with nullability. At least it should be warning about it.

Comparing to SQL Server: "All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements." The same behavior is implemented in Mysql 8.0

create table t1 (a int not null);
insert into t1 values (0);
WITH RECURSIVE cte AS (SELECT a FROM t1 where a=0 UNION ALL SELECT NULL FROM cte) 
SELECT * FROM cte;

Comment by Igor Babaev [ 2018-05-15 ]

This bug cannot be reproduced in 10.2 anymore due to a changed rule of type aggregations in recursive units. (BTW this rule is not in line with SQL standard).
As this rule was not applied to 10.3 the bug still can be reproduced there.

Comment by Igor Babaev [ 2018-05-15 ]

A fix for this bug was pushed into 10.3

Generated at Thu Feb 08 08:19:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.