Bug #37745 Arithmetic exceeding range fails silently, unless "zerofill"
Submitted: 30 Jun 2008 17:06 Modified: 12 Nov 2008 13:59
Reporter: Joerg Bruehe Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.26 OS:Microsoft Windows (32 + 64 bit)
Assigned to: Sergei Glukhov CPU Architecture:Any

[30 Jun 2008 17:06] Joerg Bruehe
Description:
Detected in the 5.1.26 build.

This happens during both the 32 and 64 bit builds on Windows,
in the 64 bit version there are other issues I will report separate.

--- /PATH/mysql-test/suite/funcs_1/r/innodb_storedproc.result
+++ /PATH/mysql-test/suite/funcs_1/r/innodb_storedproc.reject
@@ -16100,7 +16102,7 @@
 END//
 SELECT fn2(1.84e+19);
 fn2(1.84e+19)
-18400000000000000000
+9223372036854775808
 DROP FUNCTION IF EXISTS fn3;
 CREATE FUNCTION fn3( f1 bigint unsigned zerofill) returns bigint unsigned zerofill
 BEGIN

This is the corresponding part of the "result" file:
16095 DROP FUNCTION IF EXISTS fn2;
16096 CREATE FUNCTION fn2( f1 bigint unsigned) returns bigint unsigned
16097 BEGIN
16098 set f1 = (f1 / 2); set f1 = (f1 * 2); set f1 = (f1 - 10); set f1 = (f1 + 10);
16099 return f1;
16100 END//
16101 SELECT fn2(1.84e+19);
16102 fn2(1.84e+19)
16103 18400000000000000000
16104 DROP FUNCTION IF EXISTS fn3;
16105 CREATE FUNCTION fn3( f1 bigint unsigned zerofill) returns bigint unsigned zerofill
16106 BEGIN
16107 set f1 = (f1 / 2); set f1 = (f1 * 2); set f1 = (f1 - 10); set f1 = (f1 + 10);
16108 return f1;
16109 END//
16110 SELECT fn3(1.84e+17);
16111 fn3(1.84e+17)
16112 184000000000000000

Note that the "zerofill" case does *not* fail.

Later, differences continue:

@@ -17224,7 +17226,7 @@
 END//
 CALL sp2(1.84e+19);
 f1
-18400000000000000000
+9223372036854775808
 DROP PROCEDURE IF EXISTS sp3;
 CREATE PROCEDURE sp3( f1 bigint unsigned zerofill)
 BEGIN
That was just another call of the same procedure (why ?).

This one makes me believe it is the initial construction of the value
(or "just" the output function ?):
@@ -18634,7 +18636,7 @@
 END//
 CALL spexecute07();
 var1   var2
-18400000000000000000   NULL
+9223372036854775808    NULL
 var3   var4
 -9220000000000000000   NULL
 var5   var6

See the "result" file:
18609 DROP PROCEDURE IF EXISTS spexecute07;
18610 CREATE PROCEDURE spexecute07()
18611 BEGIN
18612 declare var1 bigint unsigned;
18613 declare var2 bigint unsigned;
18614 declare var3 bigint;
18615 declare var4 bigint;
18616 declare var5 bigint;
18617 declare var6 bigint;
18618 declare var7 bigint;
18619 declare var8 bigint;
18620 set var1 =  1.84e+19;
18621 set var3 = -9.22e+18;
18622 set var5 = -9.22e+18;
18623 set var7 = -9.22e+18;
18624 SELECT var1, var2;
18625 SELECT var3, var4;
18626 SELECT var5, var6;
18627 SELECT var7, var8;
18628 CALL sp07( var1, var1, var2, var3, var3, var4,
18629 var5, var5, var6, var7, var7, var8 );
18630 SELECT var1, var2;
18631 SELECT var3, var4;
18632 SELECT var5, var6;
18633 SELECT var7, var8;
18634 END//
18635 CALL spexecute07();
18636 var1    var2
18637 18400000000000000000    NULL
18638 var3    var4
18639 -9220000000000000000    NULL
So "var1" was just assigned from a constant, not computed in an expression.

Differences continue:

@@ -18642,7 +18644,7 @@
 var7   var8
 -9220000000000000000   NULL
 f1     f2      f3
-18400000000000000000   18400000000000000000    NULL
+9223372036854775808    9223372036854775808     NULL
 f4     f5      f6
 -9220000000000000000   -9220000000000000000    NULL
 f7     f8      f9
@@ -18650,7 +18652,7 @@
 f10    f11     f12
 -9220000000000000000   -9220000000000000000    NULL
 f1     f2      f3
-18353255926290448384   18353255926290448384    18353255926290448384
+0      0       0
 f4     f5      f6
 -9220000000000000000   6744073709551616        6744073709551616
 f7     f8      f9
@@ -18658,7 +18660,7 @@
 f10    f11     f12
 -9220000000000000000   6744073709551616        6744073709551616
 var1   var2
-18353255926290448384   18353255926290448384
+0      0
 var3   var4
 6744073709551616       6744073709551616
 var5   var6
mysqltest: Result content mismatch

Happens on both Windows platforms (32 + 64 bit) in all configurations.

*If* this is an effect of "zerofill" implying "unsigned" (see bug#27416),
then why does this not happen on any other platform ?
Is the velue range on Windows smaller than on Unix/Linux ?

How to repeat:
Run the "funcs_1" tests.
[12 Nov 2008 13:58] Sergei Glukhov
The bug is duplicate of bug#27483. The patch proposed in 27483 fixes current report problem.