以前发现的一个MYSQL的BUG

该BUG经mysql确认后在Connector 5.1.4版本中修正.

 

[9 Apr 2008 4:52] Kuang Yinong

Description:when i run the follow sql at my VB program, it will give me wrong result of the sum(Cost) = 800000select sum(Cost),sum(Weekly_IMPS),sum(Estimated_Clicks),sum(Estimated_Conversions) fromOnline_Plan_Detail a,Site_SYS b where a.Site_Auto_ID=b.ID and b.Site_Level='StandardExternals' and Plan_ID=1 and Week_Date Between '2008-03-24' and '2008-03-30'i execute the sql use adodb, adodb version is 2.8.table detail as follow:CREATE TABLE online_plan_detail (  ID int(11) NOT NULL auto_increment,  Plan_ID int(11) NOT NULL default '0',  Placement_ID int(11) default NULL,  Site_Auto_ID int(11) NOT NULL,  Creative_Size_ID varchar(10) NOT NULL,  Weekly_IMPS int(11) NOT NULL,  Estimated_Clicks mediumint(9) NOT NULL,  Estimated_Conversions mediumint(9) NOT NULL,  Cost decimal(8,2) NOT NULL,  CPM decimal(8,2) NOT NULL,  RateCard decimal(8,2) NOT NULL,  Week_Date date NOT NULL,  PRIMARY KEY  (ID)) ENGINE=MyISAM  DEFAULT CHARSET=latin1;CREATE TABLE site_sys (  ID int(11) NOT NULL auto_increment,  Site varchar(128) NOT NULL,  Site_ID varchar(10) NOT NULL default '0',  Site_Level varchar(50) default NULL,  PRIMARY KEY  (ID)) ENGINE=InnoDB  DEFAULT CHARSET=latin1;i run the sql at mysql command line, it work ok. the result of sum(cost) is 8000,when i change the field cost to double type, it can get the correct result 8000.it very strange.How to repeat:CREATE TABLE online_plan_detail (  ID int(11) NOT NULL auto_increment,  Plan_ID int(11) NOT NULL default '0',  Placement_ID int(11) default NULL,  Site_Auto_ID int(11) NOT NULL,  Creative_Size_ID varchar(10) NOT NULL,  Weekly_IMPS int(11) NOT NULL,  Estimated_Clicks mediumint(9) NOT NULL,  Estimated_Conversions mediumint(9) NOT NULL,  Cost decimal(8,2) NOT NULL,  CPM decimal(8,2) NOT NULL,  RateCard decimal(8,2) NOT NULL,  Week_Date date NOT NULL,  PRIMARY KEY  (ID)) ENGINE=MyISAM  DEFAULT CHARSET=latin1;CREATE TABLE site_sys (  ID int(11) NOT NULL auto_increment,  Site varchar(128) NOT NULL,  Site_ID varchar(10) NOT NULL default '0',  Site_Level varchar(50) default NULL,  PRIMARY KEY  (ID)) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

[9 Apr 2008 5:00] Kuang Yinong

mysql version is: 5.0.51a-community-ntos: windows 2003 enterprise edition

[9 Apr 2008 5:01] Kuang Yinong

all my data

Attachment: ddd.sql (text/plain), 17.98 KiB.
 

[9 Apr 2008 5:38] Jess Balint

Verified as described. Result received is 8000000.00.

[9 Apr 2008 6:50] Jess Balint

If the decimal point was on the beginning edge of one of the segments, it would beskipped.Index: driver/utility.c===================================================================--- driver/utility.c    (revision 1088)+++ driver/utility.c    (working copy)@@ -2371,7 +2371,7 @@        if we have the decimal point, ignore it by setting it to the        last char (will be ignored by strtoul)     */-    if (decpt && decpt > numstr + i && decpt < numstr + i + usedig)+    if (decpt && decpt >= numstr + i && decpt < numstr + i + usedig)     {       usedig = (int) (decpt - (numstr + i) + 1);       sqlnum->scale= len - (i + usedig);Index: test/my_types.c===================================================================--- test/my_types.c     (revision 1088)+++ test/my_types.c     (working copy)@@ -879,6 +879,9 @@   is(sqlnum_test_from_str(hstmt, num3, 6, -1, 0, NULL, 10123, 0) == OK);   is(sqlnum_test_from_str(hstmt, num3, 5, -1, 0, NULL, 10123, 0) == OK);+  /* Bug#35920 */+  is(sqlnum_test_from_str(hstmt, "8000.00", 30, 2, 1, NULL, 800000, 0) == OK);+  is(sqlnum_test_from_str(hstmt, "1234567.00", 30, 2, 1, NULL, 123456700, 0) == OK);   /* some larger numbers */   {SQLCHAR expdata[SQL_MAX_NUMERIC_LEN]= {0xD5, 0x50, 0x94, 0x49,0,0,0,0,0,0,0,0,0,0,0,0};

[9 Apr 2008 15:29] Kuang Yinong

other field Estimated_Clicks mediumint(9) NOT NULL,Estimated_Conversions mediumint(9) NOT NULL,also will have the problem sometimes, but not always.also change it to double will ok.

[15 Apr 2008 0:02] Lawrin Novitsky

approved

[15 Apr 2008 7:32] Jess Balint

Fix committed in rev 1105, will be released in 5.1.4.

[8 Jul 2008 11:30] Tony Bedford

An entry has been added to the 5.1.4 Changelog:Wrong result obtained when using sum() on a decimal(8,2) field type.