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:Microsoft Windows
Assigned to: Jess Balint
Tags: 5.1, decimal, double, MyODBC
Triage: D2 (Serious)

[9 Apr 2008 2: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 3:38] Jess Balint
Verified as described. Result received is 8000000.00.
[9 Apr 2008 4: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 13: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.
[14 Apr 2008 22:02] Lawrenty Novitsky
approved
[15 Apr 2008 5:32] Jess Balint
Fix committed in rev 1105, will be released in 5.1.4.
[8 Jul 2008 9: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.