[MDEV-16506] CTE with overflow of integer type returns wrong results Created: 2018-06-18  Updated: 2022-12-06  Resolved: 2022-12-06

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

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

Issue Links:
Relates
relates to MDEV-15447 Import CTE tests from MySQL 8 Stalled

 Description   

in 10.3:

MariaDB [test]> WITH RECURSIVE qn AS (SELECT 1 AS a FROM dual UNION SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT count(*) FROM qn;
 
+----------+
 
| count(*) |
 
+----------+
 
|        4 |
 
+----------+
 
1 row in set (0.002 sec)
 
 
 
MariaDB [test]> select version();
 
+----------------------+
 
| version()            |
 
+----------------------+
 
| 10.3.8-MariaDB-debug |
 
+----------------------+
 
1 row in set (0.000 sec)

Expected to return error, as in 10.2:

MariaDB [test]> WITH RECURSIVE qn AS (SELECT 1 AS a FROM dual UNION SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT count(*) FROM qn;
 
ERROR 1690 (22003): BIGINT value is out of range in '`qn`.`a` * 2000'
 
 
 
MariaDB [test]> select version();
 
+-----------------------+
 
| version()             |
 
+-----------------------+
 
| 10.2.16-MariaDB-debug |
 
+-----------------------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Oleksandr Byelkin [ 2018-12-20 ]

Why only recursive CTE are special to break on type errors?

Comment by Alice Sherepa [ 2022-12-06 ]

currently on 10.3-10.11: 

query 'WITH RECURSIVE qn AS (SELECT 1 AS a FROM dual UNION SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT count(*) FROM qn' failed: 1264: Out of range value for column 'a' at row 4

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