Hello, please sign in or register
You are here: Home

Problems with DataType FLOAT use LIKE

Strange thing happened when searching a float field using "=" condition. No results were found. Whilst the same query using the LIKE expression did find the record.

On further investigation this appears to be quite common and it is everything to do with the way FLOAT datatypes are stored.

Here's a heuristic test

-- Create table with values
CREATE TABLE numeric_types (flt FLOAT, flt1  FLOAT(6,2), dbl double(6,2), num NUMERIC(6,2) )
SELECT 123.32 as flt,123.32 as flt1,123.32 as dbl, 123.32 as num;

-- Run tests, this shows the value inserted vs the value stored
SELECT flt, flt+0, flt1, flt1+0,dbl+0,num+0 FROM numeric_types;

-- Which datatype correctly matches are inserted value
SELECT (flt = 123.32), (flt1 = 123.32), (dbl = 123.32), (num = 123.32) FROM numeric_types;

See

mysql5> SELECT (flt = 123.32), (flt1 = 123.32), (flt1 = "123.32"), (dbl = 123.32) FROM numeric_types;
+----------------+-----------------+-------------------+----------------+
| (flt = 123.32) | (flt1 = 123.32) | (flt1 = "123.32") | (dbl = 123.32) |
+----------------+-----------------+-------------------+----------------+
|              0 |               1 |                 0 |              1 |
+----------------+-----------------+-------------------+----------------+
1 row in set (0.01 sec)

Results are different between MySQL 5 and 4.1 where MySQL 5 returns the correct result if the value matched is of the right CAST... i.e. The following will not match in 4.1. where as it did in the above example

mysql4> SELECT (flt = 123.32) FROM numeric_types;
+----------------+
| (flt = 123.32) |
+----------------+
|              0 |
+----------------+
1 row in set (0.01 sec)

For the following query...

SELECT flt AS "FLOAT", flt+0 AS "FLOAT+0", flt1 AS "FLOAT(6,2)", flt1+0 AS "FLOAT(6,2)+0", dbl AS "DOUBLE", dbl+0 AS "DOUBLE+0", num AS "NUMERIC", num+0 AS "NUMERIC+0" FROM numeric_types; 

MySQL version

FLOAT FLOAT+0 FLOAT(6,2) FLOAT(6,2)+0 DOUBLE DOUBLE+0 NUMERIC NUMERIC+0
4.1
123.32 123.31999969482 123.32 123.31999969482 123.32 123.32 123.32 123.32
5 123.32 123.31999969482 123.32 123.32 123.32 123.32 123.32 123.32

Shows the true value that is stored. And for all FLOAT types the value always has a long decimal value, (unless you are uing MySQL 5 and specify the number of decimals). So some kind of polynomial function or algorithm is used to store the float values efficiently.

Obviously the way round this is to use the DOUBLE, NUMERIC type in MySQL 4.1. Which store and return the values as you'd expect.  However neither of these appear as storage efficient as FLOAT's. Certianly not Numeric which prior to MySQL 5.0.3 stored individual digits as characters in a string type.

See http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html about storage solutions.

 

Looks like i'm not alone http://forums.mysql.com/read.php?51,127128,127128

Comments

clibin
Tyrion 5c cases watched her ray ban sunglasses read. His timber...
Created 18/04/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
wanglili
polo ralph lauren outlet online michael kors outlet
Created 21/08/17
meadc
Oakley Sunglasses hermes belt
Created 09/09/17
Title*
Comment

Prove you are not a robot

To prove you are not a robot, please type in the six character code you see in the picture below
Security confirmation codeI can't see this!
Contact
Name*
Email never shown*
Home Page

Author

Andrew Dodson
Since:Feb 2007

Comment | flag

Categories

Bookmark and Share