Bug #55633 Strange results from stored function
Submitted: 29 Jul 2010 18:08 Modified: 30 Jul 2010 8:19
Reporter: Pawel Parys Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.51a-community, 4.1, 5.0, 5.1, 5.6.99 bzr OS:Any (MS Windows, all)
Assigned to: CPU Architecture:Any
Tags: calculations, INT, stored function

[29 Jul 2010 18:08] Pawel Parys
Description:
I have problem with stored function results which are definitely wrong in certain situation. I create stored function like this:

create function kierowcy.odsetki(od date, do date, baza int unsigned) returns int unsigned deterministic reads sql data
begin
	declare kwota int unsigned default 0;
	select round(sum(if(round((datediff(if(wazny_do>do,do,wazny_do), if(datediff(od, wazny_od)<0, wazny_od, od))+1)*baza*wartosc_float/36500,0)>0, round((datediff(if(wazny_do>do,do,wazny_do), if(datediff(od, wazny_od)<0, wazny_od, od))+1)*baza*wartosc_float/36500,0), 0)), 0) into kwota from kierowcy.konfiguracja where klucz = 'odsetki';
	return kwota;
end;

Then I run it and get very strange result:

mysql> select odsetki('2010-07-27', curdate(), 10000);
+-----------------------------------------+
| odsetki('2010-07-27', curdate(), 10000) |
+-----------------------------------------+
|                              4294967295 |
+-----------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'kwota' at row 13 |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

Now I make a small modification to function's header and it starts to work fine:

create function kierowcy.odsetki(od date, do date, baza decimal(10, 0) unsigned) returns int unsigned deterministic reads sql data
begin
	declare kwota int unsigned default 0;
	select round(sum(if(round((datediff(if(wazny_do>do,do,wazny_do), if(datediff(od, wazny_od)<0, wazny_od, od))+1)*baza*wartosc_float/36500,0)>0, round((datediff(if(wazny_do>do,do,wazny_do), if(datediff(od, wazny_od)<0, wazny_od, od))+1)*baza*wartosc_float/36500,0), 0)), 0) into kwota from kierowcy.konfiguracja where klucz = 'odsetki';
	return kwota;
end;

mysql> select odsetki('2010-07-27', curdate(), 10000);
+-----------------------------------------+
| odsetki('2010-07-27', curdate(), 10000) |
+-----------------------------------------+
|                                      11 |
+-----------------------------------------+
1 row in set (0.01 sec)

How to repeat:
Run statements as above.
[29 Jul 2010 18:15] Pawel Parys
Tried also on version 5.1.41-community-log and it returns:

mysql> select odsetki('2010-07-27', curdate(), 10000);
ERROR 1264 (22003): Out of range value for column 'kwota' at row 13
[29 Jul 2010 19:20] Valeriy Kravchuk
It would be nice to get a dump of that kierowcy.konfiguracja table or at least SHOW CREATE TABLE results for it and data of "magic" row 13 in the table.
[29 Jul 2010 21:08] Pawel Parys
Here it is:

mysql> select * from konfiguracja;
+---------+--------------+--------------+---------------+---------------------+---------------------+
| klucz   | wartosc_text | wartosc_data | wartosc_float | wazny_od            | wazny_do            |
+---------+--------------+--------------+---------------+---------------------+---------------------+
| odsetki | NULL         | NULL         |         13.00 | 2008-12-15 00:00:00 | 2010-12-31 00:00:00 |
| odsetki | NULL         | NULL         |         11.50 | 2005-10-15 00:00:00 | 2008-12-14 00:00:00 |
| odsetki | NULL         | NULL         |         13.50 | 2005-01-10 00:00:00 | 2005-10-14 00:00:00 |
| odsetki | NULL         | NULL         |         12.25 | 2003-09-25 00:00:00 | 2005-01-09 00:00:00 |
| odsetki | NULL         | NULL         |         13.00 | 2003-02-01 00:00:00 | 2003-09-24 00:00:00 |
| odsetki | NULL         | NULL         |         16.00 | 2002-07-25 00:00:00 | 2003-01-31 00:00:00 |
| odsetki | NULL         | NULL         |         20.00 | 2001-12-15 00:00:00 | 2002-07-24 00:00:00 |
| odsetki | NULL         | NULL         |         30.00 | 2000-11-01 00:00:00 | 2001-12-14 00:00:00 |
| odsetki | NULL         | NULL         |         21.00 | 1999-05-15 00:00:00 | 2000-10-31 00:00:00 |
| odsetki | NULL         | NULL         |         24.00 | 1999-02-01 00:00:00 | 1999-05-14 00:00:00 |
| odsetki | NULL         | NULL         |         33.00 | 1998-04-15 00:00:00 | 1999-01-31 00:00:00 |
| odsetki | NULL         | NULL         |         35.00 | 1996-01-01 00:00:00 | 1998-04-14 00:00:00 |
| odsetki | NULL         | NULL         |         46.00 | 1995-12-15 00:00:00 | 1995-12-31 00:00:00 |
+---------+--------------+--------------+---------------+---------------------+---------------------+

mysql> show create table konfiguracja;
+--------------+----------------------------------------
| Table        | Create Table
+--------------+----------------------------------------
| konfiguracja | CREATE TABLE `konfiguracja` (
  `klucz` char(128) default NULL,
  `wartosc_text` char(32) default NULL,
  `wartosc_data` date default NULL,
  `wartosc_float` decimal(6,2) unsigned default '0.00',
  `wazny_od` datetime default NULL,
  `wazny_do` datetime default NULL,
  KEY `klucze` (`klucz`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1250 |
+--------------+----------------------------------------
[30 Jul 2010 8:19] Sveta Smirnova
Thank you for the feedback.

Problem is expression datediff(if(wazny_do>do,do,wazny_do), if(datediff(od, wazny_od)<0, wazny_od, od))+1 for some of rows returns negative values. In this case result can be overflow or negative value depending of what type is used.

Easier test case to repeat the problem:

create table t1(f1 decimal(10,0) unsigned);
create table t2 (f1 int unsigned);
insert into t1 values(10000);
insert into t2 values(10000);
select -589*f1 from t1;
select -589*f1 from t2;

Outputs:

mysql> select -589*f1 from t;
+----------+
| -589*f1  |
+----------+
| -5890000 |
+----------+
1 row in set (0.03 sec)

mysql> select -589*f1 from t2;
+----------------------+
| -589*f1              |
+----------------------+
| 18446744073703661616 |
+----------------------+
1 row in set (0.05 sec)