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:08]
Pawel Parys
[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)