MySQL request return results when call from console, but empty when request using ActiveRecord

Question

I have strange problem. Im using Yii2 (latest stable). I have table with some filters for diamond base. One of filters r float (Weight).

When i trying to filter from 1.1 to 1.2 - i see results in array from active record. When im trying to request 1.1 to 1.1 (exact weight) yii returns empty array.

I try to copy request from debug toolbar end execute it in mysql console and mysql returns all results correctly.

Request:

SELECT `Diamonds`.* FROM `Diamonds` 
    LEFT JOIN `DiamondsSpecialGroups` 
        ON `Diamonds`.`specialGroup` = `DiamondsSpecialGroups`.`id` 
        WHERE (((((`endPrice` >= 500) 
        AND (`type` IN (0, 1, 2, 3))) AND ((`active`=1) 
        AND (`internalBlock`=0) 
        AND (`frontendHide`=0))) 
        AND (`Weight`=0.39)) 
        AND (`endPrice` >= '500')) 
        AND (`endPrice` <= '1323231') 
    ORDER BY `endPrice` 
    LIMIT 50

If i remove

(`Weight`=0.39)

All results sowing both in mysql and yii.

Finally question - why?

It is quite hard with `=` and floats. Floats are never 100% accurate I think the issue might be that the translation from ruby to mysql might change the value of your `0.39` the slightest. — Albin, Apr 17 '15 at 09:03
This is a more elaborate answer on the issue. http://stackoverflow.com/questions/2567434/mysql-floating-point-comparison-issues — Albin, Apr 17 '15 at 09:04
Thank you! After changing FLOAT to DECIMAL all seems works good! — Alex Bond, Apr 18 '15 at 21:06

score 1 · Accepted Answer · edited May 23 '17 at 12:07

1

It is quite hard with = and floats.

Floats are never 100% accurate I think the issue might be that two different instances of 0.39 differs a little bit from one another.

In order to avoid this you could use DECIMAL instead of FLOAT as suggested in the answer I posted below.

More elaborate answer.

edited May 23 '17 at 12:07

Community

1
1

answered Apr 20 '15 at 06:29

Albin

2,912
1
21
31

Good note, but how it relates to Ruby? – arogachev Apr 20 '15 at 06:36
I think it is general for all languages. You should avoid equal for floats. – Albin Apr 20 '15 at 06:50
Yes, but I think it's better to remove Ruby mention from answer. – arogachev Apr 20 '15 at 06:55
1

Good point. I updated the answer according to your suggestion. – Albin Apr 20 '15 at 07:43

MySQL request return results when call from console, but empty when request using ActiveRecord

1 Answers1