#11 incorrectly displays decimal(10,2) columns

[John Douthat]

on my database, many of the columns are defined as
decimal (10,2) to record money amounts. sometimes the
fields are displayed correctly in cocoamysql, sometimes
they're not. the issue seems to add decimals that do not
belong, and slightly reduce the value of the field

the following are copies of what is outputted from
cocoamysql, the mysql command line tool, and a php script.
each example is running the same query:

SELECT customer_id, SUM(total_charge_amt) as amt FROM
orders GROUP BY customer_id ORDER BY amt DESC limit 14

cocoamysql custom result exported as csv (displays the
same way in the custom query tab):
"customer_id","amt"
"588","57678.72"
"7482","37473.39"
"3123","33124.59"
"510","29540.29"
"504","13411"
"3997","11876.69"
"867","11653.53"
"3544","11080.51"
"512","10790.83"
"9034","9992.74"
"2463","9151.110000000001"
"15442","8805.889999999999"
"2962","8349.5"
"5479","8312.799999999999"

/usr/local/mysql/bin/mysql:
+-------------+----------+
| customer_id | amt |
+-------------+----------+
| 588 | 57678.72 |
| 7482 | 37473.39 |
| 3123 | 33124.59 |
| 510 | 29540.29 |
| 504 | 13411.00 |
| 3997 | 11876.69 |
| 867 | 11653.53 |
| 3544 | 11080.51 |
| 512 | 10790.83 |
| 9034 | 9992.74 |
| 2463 | 9151.11 |
| 15442 | 8805.89 |
| 2962 | 8349.50 |
| 5479 | 8312.80 |

pulling from a php script, just for good measure
#
customer_id
amt

1
588
57678.72

2
7482
37473.39

3
3123
33124.59

4
510
29540.29

5
504
13411.00

6
3997
11876.69

7
867
11653.53

8
3544
11080.51

9
512
10790.83

10
9034
9992.74

11
2463
9151.11

12
15442
8805.89

13
2962
8349.50

14
5479
8312.80

here is the way I defined the fields:

CREATE TABLE `orders` (
`order_id` bigint(20) unsigned NOT NULL auto_increment,
`total_charge_amt` decimal(10,2) default NULL,
PRIMARY KEY (`order_id`),
UNIQUE KEY `order_id` (`order_id`)
) TYPE=MyISAM

[J]

Logged In: YES
user_id=113658

I noticed this too as I use CocoaMySQL in day-to-day work.
In the Content Pane, data showed for DECIMAL values
(be it (9,2) or (10,2) or anything else) is not shown correctly.

This has been verified and is under review.

[John Douthat]

Logged In: YES
user_id=674656

<pre>
Well, it turns out this bug wasn't in CocoaMySQL, but in the
SMySQL framework. I was using the framework in a project and
the same bug creeped up. I debugged it for a couple of hours
and was able to come up with a fix.

(note, I'm using SMySQL framework v2.3.1)

in MCPResult.m, find (around line 298)
case FIELD_TYPE_DECIMAL:
case FIELD_TYPE_FLOAT:
case FIELD_TYPE_DOUBLE:
theCurrentObj = [NSNumber
numberWithDouble:atof(theData)];
break;
replace with:
case FIELD_TYPE_DECIMAL:

theCurrentObj = nil; // If the first
keyword beyond the above colon is a type keyword, you get a
compile error.
// so I put this
useless line here to avoid the compile error.

// Using an NSDecimalNumber fixes the
loss of precision errors
// First we need to make an NSString
from theData
NSString *tmpString = [NSString
stringWithCString:theData];

// Then, convert that NSString to an
NSDecimalNumber
// NSDecimalNumber is a subclass of
NSNumber, so it should work inplace of NSNumber
theCurrentObj = [NSDecimalNumber
decimalNumberWithString:tmpString];

// free the temporary NSString
[tmpString release];
break;
case FIELD_TYPE_DOUBLE:
theCurrentObj = [NSNumber
numberWithDouble:atof(theData)];
break;

CocoaMySQL is linked against a customized older version of
the SMySQL framework, to which I don't have the source to
test against.
If I can get a copy of the sources to your version of the
framework, I could probably fix the problem.
</pre>

[John Douthat]

same text as my comment, without all the spacing issues of html

[Nobody/Anonymous]

Logged In: NO

just my .2c... it also does this with floats.
73.07 came in from an insert and showed as 73.06999999999999 in
CocoaMySQL.