| 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: | |
| 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: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)
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.