Bug #118159 recursive CTE with float returns error: Out of range value

• Submitted: 10 May 2:12
• Modified: 13 May 7:37
• Reporter: Chunling Qin
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.4.3
• OS: Any
• CPU Architecture: Any

[10 May 2:12] Chunling Qin

Description:
MySQL [fuzztest]> WITH RECURSIVE numbers (i) AS (
    ->     SELECT 1.5                        
    ->     UNION ALL
    ->     SELECT i + 1.0 FROM numbers WHERE i < 100                       )
    -> SELECT i FROM numbers WHERE i = 1.5;
ERROR 1264 (22003): Out of range value for column 'i' at row 1

Results from postgres:
postgres=# WITH RECURSIVE numbers (i) AS (
    SELECT 1.5                        
    UNION ALL
    SELECT i + 1.0 FROM numbers WHERE i < 100                       )
SELECT i FROM numbers WHERE i = 1.5;
  i  
-----
 1.5
(1 row)

How to repeat:
WITH RECURSIVE numbers (i) AS (
    SELECT 1.5                        
    UNION ALL
    SELECT i + 1.0 FROM numbers WHERE i < 100                       )
SELECT i FROM numbers WHERE i = 1.5;

Suggested fix:
It should return 1.5.

[13 May 7:37] MySQL Verification Team

Hello Chunling Qin,

Thank you for the bug report.
Imho this is duplicate of Bug #116182, please see Bug #116182.

Regards,
Ashwini Patil