Bug #37746 Arithmetic range ("int") is smaller than expected
Submitted: 30 Jun 2008 17:42 Modified: 7 Jul 2009 14:39
Reporter: Joerg Bruehe Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Tests Severity:S3 (Non-critical)
Version:5.1.26 OS:Windows (64 bit only)
Assigned to: CPU Architecture:Any

[30 Jun 2008 17:42] Joerg Bruehe 
Description:
This one is specific to 64 bit Windows, 
it does not happen on 32 bit Windows or any other platform.

Detected during the build of 5.1.26:

--- /PATH/mysql-test/suite/funcs_1/r/innodb_storedproc.result
+++ /PATH/mysql-test/suite/funcs_1/r/innodb_storedproc.reject
@@ -16606,7 +16608,7 @@
 END//
 SELECT fn50(4.29e+09);
 fn50(4.29e+09)
-4290000000
+2147483648
 DROP FUNCTION IF EXISTS fn51;
 CREATE FUNCTION fn51( f1 int unsigned zerofill) returns int unsigned zerofill
 BEGIN
@@ -16615,7 +16617,7 @@
 END//
 SELECT fn51(4.29e+09);
 fn51(4.29e+09)
-4290000000
+2147483648
 DROP FUNCTION IF EXISTS fn52;
 CREATE FUNCTION fn52( f1 int zerofill) returns int zerofill
 BEGIN
@@ -17751,7 +17753,7 @@
 END//
 CALL sp50(4.29e+09);
 f1
-4290000000
+2147483648
 DROP PROCEDURE IF EXISTS sp51;
 CREATE PROCEDURE sp51( f1 int unsigned zerofill)
 BEGIN
@@ -17760,7 +17762,7 @@
 END//
 CALL sp51(4.29e+09);
 f1
-4290000000
+2147483648
 DROP PROCEDURE IF EXISTS sp52;
 CREATE PROCEDURE sp52( f1 int zerofill)
 BEGIN

Difference continue with these values, but I expect these are just further occurrences of the same problem.

What surprises me most is that the reported value is 1 more than the maximum value of a signed 32 bit int.

Here are the corresponding parts of the result file:

16601 DROP FUNCTION IF EXISTS fn50;
16602 CREATE FUNCTION fn50( f1 int unsigned) returns int unsigned
16603 BEGIN
16604 set f1 = (f1 / 2); set f1 = (f1 * 2); set f1 = (f1 - 10); set f1 = (f1 + 10);
16605 return f1;
16606 END//
16607 SELECT fn50(4.29e+09);
16608 fn50(4.29e+09)
16609 4290000000
16610 DROP FUNCTION IF EXISTS fn51;
16611 CREATE FUNCTION fn51( f1 int unsigned zerofill) returns int unsigned zerofill
16612 BEGIN
16613 set f1 = (f1 / 2); set f1 = (f1 * 2); set f1 = (f1 - 10); set f1 = (f1 + 10);
16614 return f1;
16615 END//
16616 SELECT fn51(4.29e+09);
16617 fn51(4.29e+09)
16618 4290000000

.....

17746 DROP PROCEDURE IF EXISTS sp50;
17747 CREATE PROCEDURE sp50( f1 int unsigned)
17748 BEGIN
17749 set f1 = (f1 / 2); set f1 = (f1 * 2); set f1 = (f1 - 10); set f1 = (f1 + 10);
17750 SELECT f1;
17751 END//
17752 CALL sp50(4.29e+09);
17753 f1
17754 4290000000
17755 DROP PROCEDURE IF EXISTS sp51;
17756 CREATE PROCEDURE sp51( f1 int unsigned zerofill)
17757 BEGIN
17758 set f1 = (f1 / 2); set f1 = (f1 * 2); set f1 = (f1 - 10); set f1 = (f1 + 10);
17759 SELECT f1;
17760 END//
17761 CALL sp51(4.29e+09);
17762 f1
17763 4290000000

How to repeat:
Run the funcs_1 suite.
[23 Jun 2009 12:17] Bernt Marius Johnsen 
This problem is also reported in Bug#45293
[7 Jul 2009 14:20] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/78143

3007 Patrick Crews	2009-07-07
      Bug#37746: Arithmetic range ("int") is smaller than expected
      
      Added code to the .test file to skip this test on Win64 for PB2 stability.
      Please remove this code when the bug is fixed.
[7 Jul 2009 14:39] Patrick Crews 
Pushed a patch to disable this test on Win64 platforms (code added to the .test file)

Please remove this upon fixing this bug.
[8 Jul 2009 13:30] Bugs System 
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:joro@sun.com-20090707144419-dcz527rmbjekaki8) (merge vers: 5.1.37) (pib:11)
[9 Jul 2009 7:38] Bugs System 
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:joro@sun.com-20090707144419-dcz527rmbjekaki8) (merge vers: 5.1.37) (pib:11)
[10 Jul 2009 11:20] Bugs System 
Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:patrick.crews@sun.com-20090707142105-57zmmek6ww5cw6vt) (merge vers: 5.4.4-alpha) (pib:11)
[26 Aug 2009 13:46] Bugs System 
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System 
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System 
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:32] Bugs System 
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[30 Jun 2013 12:46] Hartmut Holzgraefe 
With all these pushes ... shouldn't this already be fixed in 5.1.x for years?
[21 Oct 2015 22:20] Emmanuel ROY 
and about and int(11), can we fix the problem ?
int(8) is about 64bits integer, 
what is about an 88bits integer which is more that the age of the universe in seconds ...