Bug #116182 recursive CTE handling of decimal is unexpected
Submitted: 20 Sep 13:39 Modified: 20 Sep 14:08
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:9.0.1, 8.4.2, 8.0.39 OS:Any
Assigned to: CPU Architecture:Any

[20 Sep 13:39] Daniël van Eeden 
Description:
The setup part of a recursive CTE implicitly sets the type of the result. Using 1.0 instead of 1 for example forces a decimal/float result instead of a integer. However it also seems to affect the precision of the value, which is unexpected.

How to repeat:
mysql-9.0.1> WITH RECURSIVE cte(n) AS (SELECT .0 AS n UNION ALL SELECT n+1 FROM cte WHERE n<5) SELECT n FROM cte;
ERROR 1264 (22003): Out of range value for column 'n' at row 1
mysql-9.0.1> WITH RECURSIVE cte(n) AS (SELECT 0.0 AS n UNION ALL SELECT n+1 FROM cte WHERE n<5) SELECT n FROM cte;
+------+
| n    |
+------+
|  0.0 |
|  1.0 |
|  2.0 |
|  3.0 |
|  4.0 |
|  5.0 |
+------+
6 rows in set (0.00 sec)

mysql-9.0.1> WITH RECURSIVE cte(n) AS (SELECT 1.0 AS n UNION ALL SELECT n+1 FROM cte WHERE n<10) SELECT n FROM cte;
ERROR 1264 (22003): Out of range value for column 'n' at row 1
mysql-9.0.1> WITH RECURSIVE cte(n) AS (SELECT 01.0 AS n UNION ALL SELECT n+1 FROM cte WHERE n<10) SELECT n FROM cte;
+------+
| n    |
+------+
|  1.0 |
|  2.0 |
|  3.0 |
|  4.0 |
|  5.0 |
|  6.0 |
|  7.0 |
|  8.0 |
|  9.0 |
| 10.0 |
+------+
10 rows in set (0.00 sec)

Suggested fix:
Don't limit the decimal precision for CTEs
[20 Sep 13:42] Daniël van Eeden 
mysql-9.0.1> WITH RECURSIVE cte(n) AS (SELECT 1.0 AS n UNION ALL SELECT n+(1./9) FROM cte WHERE n<2) SELECT n FROM cte;
+------+
| n    |
+------+
|  1.0 |
|  1.1 |
|  1.2 |
|  1.3 |
|  1.4 |
|  1.5 |
|  1.6 |
|  1.7 |
|  1.8 |
|  1.9 |
|  2.0 |
+------+
11 rows in set, 10 warnings (0.00 sec)

Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
mysql-9.0.1> WITH RECURSIVE cte(n) AS (SELECT 1.00000000000 AS n UNION ALL SELECT n+(1./9) FROM cte WHERE n<2) SELECT n FROM cte;
+---------------+
| n             |
+---------------+
| 1.00000000000 |
| 1.11111111100 |
| 1.22222222200 |
| 1.33333333300 |
| 1.44444444400 |
| 1.55555555500 |
| 1.66666666600 |
| 1.77777777700 |
| 1.88888888800 |
| 1.99999999900 |
| 2.11111111000 |
+---------------+
11 rows in set (0.00 sec)
[20 Sep 13:52] Daniël van Eeden 
mysql-9.0.1> WITH RECURSIVE cte(n) AS (SELECT 1/3 AS n UNION ALL SELECT n+(1./3) FROM cte WHERE n<2) SELECT n FROM cte;
+--------+
| n      |
+--------+
| 0.3333 |
| 0.6666 |
| 0.9999 |
| 1.3332 |
| 1.6665 |
| 1.9998 |
| 2.3331 |
+--------+
7 rows in set, 7 warnings (0.00 sec)

Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
Note (Code 1265): Data truncated for column 'n' at row 1
mysql-9.0.1> WITH RECURSIVE cte(n) AS (SELECT 1.00000000000000000000000000000000000000000000000000000/3 AS n UNION ALL SELECT n+(1./3) FROM cte WHERE n<2) SELECT n FROM cte;
+----------------------------------+
| n                                |
+----------------------------------+
| 0.333333333333333333333333333333 |
| 0.666666666333333333333333333333 |
| 0.999999999333333333333333333333 |
| 1.333333332333333333333333333333 |
| 1.666666665333333333333333333333 |
| 1.999999998333333333333333333333 |
| 2.333333331333333333333333333333 |
+----------------------------------+
7 rows in set, 1 warning (0.00 sec)

Note (Code 1265): Data truncated for column 'n' at row 1

Not sure why it only returns one warning, looks like all rows are truncated.
[20 Sep 14:08] MySQL Verification Team 
Hello Daniël,

Thank you for the report and feedback.

regards,
Umesh