Description:
Four distinct classes of string-to-integer conversion bug.
(1) Strings with multiple '.' characters are somehow interpreted as if the first '.' (but ONLY the first one) were missing.
(2) Certain strings ending with 'e+' or 'e-' are truncated to 20 digits (silently dropping digits after the 20th) rather than saturating to LLONG_MAX or ULLONG_MAX.
(3) Decimals of the form '0.9...' with more than 19 digits, which happen to lie between 0.9223372036854775807 and 1.0 exclusive, are "rounded downward" to 0 rather than rounded to 1.
(4) When converting a negative number to "unsigned", if the negative number's absolute value is greater than ULLONG_MAX it will be "looped around" to the unsigned type's maximum representable value, rather than saturated to 0.
How to repeat:
set sql_mode = '';
drop database if exists db;
create database db;
use db;
create table foo (i int, iu int unsigned,
b bigint, bu bigint unsigned);
delete from foo;
insert into foo (i,iu,b,bu) values ('1.2.3.4', '1.2.3.4', '1.2.3.4', '1.2.3.4');
select i,iu,b,bu from foo;
+------+------+------+------+
| i | iu | b | bu |
+------+------+------+------+
| 12 | 12 | 12 | 12 |
+------+------+------+------+
delete from foo;
insert into foo (b,bu) values ('92233720368547758000e+', '92233720368547758000e+');
select b,bu from foo;
+---------------------+---------------------+
| b | bu |
+---------------------+---------------------+
| 9223372036854775800 | 9223372036854775800 |
+---------------------+---------------------+
delete from foo;
insert into foo (i,b) values ('0.9223372036854775807', '0.9223372036854775807');
insert into foo (i,b) values ('0.9223372036854775808', '0.9223372036854775808');
select i,b from foo;
+------+------+
| i | b |
+------+------+
| 1 | 1 |
| 0 | 0 |
+------+------+
delete from foo;
insert into foo (iu,bu) values ('-1e19', '-1e19');
insert into foo (iu,bu) values ('-2e19', '-2e19');
insert into foo (iu,bu) values ('-18446744073709551615', '-18446744073709551615');
insert into foo (iu,bu) values ('-18446744073709551616', '-18446744073709551616');
select iu,bu from foo;
+------------+----------------------+
| iu | bu |
+------------+----------------------+
| 0 | 0 |
| 4294967295 | 18446744073709551615 |
| 0 | 0 |
| 4294967295 | 18446744073709551615 |
+------------+----------------------+
Description: Four distinct classes of string-to-integer conversion bug. (1) Strings with multiple '.' characters are somehow interpreted as if the first '.' (but ONLY the first one) were missing. (2) Certain strings ending with 'e+' or 'e-' are truncated to 20 digits (silently dropping digits after the 20th) rather than saturating to LLONG_MAX or ULLONG_MAX. (3) Decimals of the form '0.9...' with more than 19 digits, which happen to lie between 0.9223372036854775807 and 1.0 exclusive, are "rounded downward" to 0 rather than rounded to 1. (4) When converting a negative number to "unsigned", if the negative number's absolute value is greater than ULLONG_MAX it will be "looped around" to the unsigned type's maximum representable value, rather than saturated to 0. How to repeat: set sql_mode = ''; drop database if exists db; create database db; use db; create table foo (i int, iu int unsigned, b bigint, bu bigint unsigned); delete from foo; insert into foo (i,iu,b,bu) values ('1.2.3.4', '1.2.3.4', '1.2.3.4', '1.2.3.4'); select i,iu,b,bu from foo; +------+------+------+------+ | i | iu | b | bu | +------+------+------+------+ | 12 | 12 | 12 | 12 | +------+------+------+------+ delete from foo; insert into foo (b,bu) values ('92233720368547758000e+', '92233720368547758000e+'); select b,bu from foo; +---------------------+---------------------+ | b | bu | +---------------------+---------------------+ | 9223372036854775800 | 9223372036854775800 | +---------------------+---------------------+ delete from foo; insert into foo (i,b) values ('0.9223372036854775807', '0.9223372036854775807'); insert into foo (i,b) values ('0.9223372036854775808', '0.9223372036854775808'); select i,b from foo; +------+------+ | i | b | +------+------+ | 1 | 1 | | 0 | 0 | +------+------+ delete from foo; insert into foo (iu,bu) values ('-1e19', '-1e19'); insert into foo (iu,bu) values ('-2e19', '-2e19'); insert into foo (iu,bu) values ('-18446744073709551615', '-18446744073709551615'); insert into foo (iu,bu) values ('-18446744073709551616', '-18446744073709551616'); select iu,bu from foo; +------------+----------------------+ | iu | bu | +------------+----------------------+ | 0 | 0 | | 4294967295 | 18446744073709551615 | | 0 | 0 | | 4294967295 | 18446744073709551615 | +------------+----------------------+