MySQL Bugs: #73410: UPDATE IGNORE fails on overflow

Bug #73410	UPDATE IGNORE fails on overflow
Submitted:	28 Jul 2014 11:21	Modified:	28 Jul 2014 12:05
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5, 5.6, 5.7, 5.6.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Initially reported as https://mariadb.atlassian.net/browse/MDEV-6494

The provided test case causes ER_DATA_OUT_OF_RANGE on the last UPDATE IGNORE. 
Documentation (http://dev.mysql.com/doc/refman/5.5/en/update.html) does not indicate any exceptions for the IGNORE modifier. 

How to repeat:
create table x (x bigint unsigned);
insert into x values (37000);
update ignore x set x=x*x;
update ignore x set x=x*x;
update ignore x set x=x*x;

Hello Elena,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

// 5.6.21

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.21                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.21-enterprise-commercial-advanced-log               |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> create table x (x bigint unsigned);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into x values (37000);
Query OK, 1 row affected (0.00 sec)

mysql> update ignore x set x=x*x;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> update ignore x set x=x*x;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> update ignore x set x=x*x;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`x`.`x` * `test`.`x`.`x`)'

As far as I can see, it is the same with and without IGNORE

DROP TABLE IF EXISTS X;
CREATE TABLE X (X BIGINT UNSIGNED);
INSERT INTO X VALUES (37000);
UPDATE X SET X=X*X;
UPDATE X SET X=X*X;
UPDATE X SET X=X*X;
-- Error Code: 1690
-- BIGINT UNSIGNED value is out of range in '(`test`.`x`.`x` * `test`.`x`.`x`)'

Docs at http://dev.mysql.com/doc/refman/5.5/en/update.html state:

With the IGNORE keyword, the update statement does not abort even if errors occur during the update. Rows for which duplicate-key conflicts occur are not updated. Rows for which columns are updated to values that would cause data conversion errors are updated to the closest valid values instead.

Should the last calculated UPDATE then have the result that table is updated with the largest possible value for the datatype?  Or should docs list that statement resulting in an integer overflow will still be aborted and IGNORE does not change that?

-- Peter
-- not a MySQL/Oracle person.

As far as I can see, it is the same with and without IGNORE

DROP TABLE IF EXISTS X;
CREATE TABLE X (X BIGINT UNSIGNED);
INSERT INTO X VALUES (37000);
UPDATE X SET X=X*X;
UPDATE X SET X=X*X;
UPDATE X SET X=X*X;
-- Error Code: 1690
-- BIGINT UNSIGNED value is out of range in '(`test`.`x`.`x` * `test`.`x`.`x`)'

Docs at http://dev.mysql.com/doc/refman/5.5/en/update.html state:

With the IGNORE keyword, the update statement does not abort even if errors occur during the update. Rows for which duplicate-key conflicts occur are not updated. Rows for which columns are updated to values that would cause data conversion errors are updated to the closest valid values instead.

Should the last calculated UPDATE then have the result that table is updated with the largest possible value for the datatype?  Or should docs list that statement resulting in an integer overflow will still be aborted and IGNORE does not change that?

-- Peter
-- not a MySQL/Oracle person.