Problem with Doubles/Decimals

[Fiendy]

Hello,

Can anyone explain what's going on here? I want a field of 20,10 (IE 10 digits before the decimal, 10 after), & as I need exact precision, I though that a Decimal data type would be best. However, when I create the field & insert a value in it, the last 3 digits of the decimal are corrupted, UNLESS I surround the number with quotes (I know Decimals are stored as strings, but still shouldn't need the quotes should I?).
Eg.

create table float_eg1 (fld1 double(20,10),fld2 decimal(20,10));
insert into float_eg1 values ("1234567890.1234567890","1234567890.1234567890");
insert into float_eg1 values ("1234567890.1234567890",1234567890.1234567890);
select * from float_eg1;

+-----------------------+-----------------------+
| fld1 | fld2 |
+-----------------------+-----------------------+
| 1234567890.1234567165 | 1234567890.1234567890 |
| 1234567890.1234567165 | 1234567890.1234567165 |
+-----------------------+-----------------------+
2 rows in set (0.00 sec)

I don't understand why the double data type corrupts either!

Any help would be appreciated!

Thanks,

Mark.

 

[sleipnir214]

It may be that unless you use the quotes, MySQL attempts to convert the string values into floating-point equivalents then to the string-type storage for DECIMAL columns, with a loss of precision along the way.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Fiendy]

I had a feeling it might be something like that.

I'm wondering if it's a bug in MySQL. I tried the same thing in postgres & it worked fine, even with 4 byte doubles.

I suppose I'll just have to remember to put quotes around my DECIMALS!

Thanks for your reply anyway.

Cheers,

Mark.