MySQL Bugs: #35920: get wrong result with decimal(8,2) field type

Bug #35920	get wrong result with decimal(8,2) field type
Submitted:	9 Apr 2008 2:52	Modified:	8 Jul 2008 9:30
Reporter:	Kuang Yinong	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.3	OS:	Windows
Assigned to:	Jess Balint	CPU Architecture:	Any
Tags:	5.1, decimal, double, MyODBC

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;

Verified as described. Result received is 8000000.00.

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};

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.

approved

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

An entry has been added to the 5.1.4 Changelog:

Wrong result obtained when using sum() on a decimal(8,2) field type.