以前发现的一个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) = 800000

select sum(Cost),sum(Weekly_IMPS),sum(Estimated_Clicks),sum(Estimated_Conversions) from
Online_Plan_Detail a,Site_SYS b where a.Site_Auto_ID=b.ID and b.Site_Level='Standard
Externals' 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-nt
os: 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 be
skipped.

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.