Wrong setup for FLOAT? MySQL keeps changing the number!

[youradds]

Hi,

I've just come across a bit of a weird one with mySQL!

If I run the following:

UPDATE Wiki_Articles SET article_map_latitude = 51.4916460374623 WHERE article_id = 255

... the "latidue" gets set to the below:

51.491645812988281250000000000000

(notice how 5812988281250000000000000 is all wrong ... I don't care about the 0000 bit.. but the actual numbers themselves are wrong!)

This is the field setup, maybe something wrong with that?

`article_map_longitude` float(34,30) DEFAULT NULL,

TIA! (I'm tearing my head out on this one :/)

Andy

 

[sleipnir214]

This from the MySQL online manual: Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies.

You need to store these either in columns of types DECIMAL or even VARCHAR to preserve accuracy.


Want to ask the best questions? Read Eric S. Raymond's essay "How To Ask Questions The Smart Way". TANSTAAFL!

 

[youradds]

Hi,

Ahhh ok - mmm

The problem with Decimal (from what I remember).. is that it doesn't support negative numbers?

For VARCHAR - will that still let me do:

fieldname >= 1233.43545

?

(as we will need to obviously compare numbers based on the location sent)

TIA

Andy

 

[r937]

VARCHAR won't work

try this and let us know what you get...

CREATE TABLE test_negative_decimal
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, foo DECIMAL(27,24)
);
INSERT INTO test_negative_decimal (foo) VALUES
 ( 51.4916460374623 )
,( -51.4916460374623 ) 
;
SELECT * 
  FROM test_negative_decimal
;

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[smays]

Can you change the `article_map_latitude` field to a DOUBLE?

http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

A normal-size (double-precision) floating-point number. Permissible values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.

M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.

UNSIGNED, if specified, disallows negative values.

The FLOAT data type does not sound like it is appropriate at all based on the number of decimal places for which it is supposed to be accurate:

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

A small (single-precision) floating-point number. Permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.

M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.

UNSIGNED, if specified, disallows negative values.

Using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision. See Section C.5.5.7, “Solving Problems with No Matching Rows”.

Everything I do both server side and client side I use DOUBLE, which in all fairness is overkill.

Steve.

 

[r937]

steve, if FLOAT is not appropriate due to conversion errors, why would DOUBLE be any better?

seems like DECIMAL is best for this

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[smays]

There will literally be conversion error with any floating precision approach. If the goal is to execute some kind of query like:

...WHERE article_map_latitude = 51.4916460374623 AND...

then perhaps DECIMAL is better.

I would never try to search for records based on the absolute value of a known analog value. In fact, I use plain integers to represent target floating point values. If an AC source is to supply 120VAC, I would set TargetInputVoltage = 120 and V_In, as measured by a power analyzer, would be set to 120.02 or whatever was actually measured. When searching for data taken at 120VAC for statistical purposes, etc, I would use a statement much like the following:

...GROUP BY TargetInputVoltage, TargetDimLevel,...

Steve.