Description:
Certain values of a double data type are not recognised. Values starting from 1.13 to 1.19.
How to repeat:
Create test table with:
-----------------------
CREATE TABLE `yieldtest` (
`YieldID` double(3,2) NOT NULL auto_increment,
PRIMARY KEY (`YieldID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Insert Test data with:
-----------------------
INSERT INTO yieldtest (YieldID)
VALUES
(1.00),
(1.05),
(1.10),
(1.11),
(1.12),
(1.13),
(1.14),
(1.15),
(1.16),
(1.17),
(1.18),
(1.19),
(1.20),
(1.25);
Insert Test data with:
-----------------------
Perform select statement:
Insert Test data with:
-----------------------
select * from yieldtest where yieldid = 1.12;
and the below is returned:
-----------------------
1.12
This works for any other value to my knowledge apart from values, 1.13, 1.14, 1.15, 1.16, 1.17, 1.18, 1.19. Therefore try:
-----------------------
select * from yieldtest where yieldid = 1.13;
select * from yieldtest where yieldid = 1.14;
etc up to 1.19
All statements return:
-----------------------
<NULL>
When clearly these values exist within the table(yieldtest).
Suggested fix:
Not sure of fix as I presume it is DBMS side of which I have little knowledge. An easy work around is not to use the double data format. I actually created a different table with an ID column and then perform the where clause on the ID column. The ID field being an Int as can be seem below:
Create Table (yield)
==============================
CREATE TABLE `yield` (
`YieldID` int(4) NOT NULL auto_increment,
`YieldRate` double(3,2) NOT NULL default '0.00',
PRIMARY KEY (`YieldID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
