23

I have a Joomla system and I'm trying to change the search so that it correctly finds floating point values in the database.

So, I have a query that is built at runtime that looks something like this:

select 'column1'
from 'some_table'
where 'some_float_field' <=> '2.18'

This doesn't work, it never matches anything, even though I see records in the db with this value there.

So I tried to just do this instead:

select 'column1'
from 'some_table'
where 'some_float_field' <=> 2.18

No luck, so then I tried casting to a decimal (float doesn't work for some reason), so I tried this:

select 'column1'
from 'some_table'
where 'some_float_field' <=> CAST('2.18' AS DECIMAL(20, 2))

No dice...

Keep in mind that >= or <= returns the proper results, just <=> gives me issues.

How do I get equality to work here?

mskfisher
  • 3,291
  • 4
  • 35
  • 48
Joseph
  • 25,330
  • 8
  • 76
  • 125

5 Answers5

19

Here is an easy way to do this

Just write where some_float_field LIKE 2.18

Hope it will help ;)

Neeraj
  • 652
  • 10
  • 18
17

Usually with these types of questions it's good to provide a small example to replicate your results.

Usually testing for exact float values is a bad idea since floating point precision isn't an exact science. It's much better to use some tolerance.

create table foo1 (col1 float);

insert into foo1 values (2.18);
select * from foo1 where abs(col1-2.18) <= 1e-6
dcp
  • 54,410
  • 22
  • 144
  • 164
  • Yeah in this case I'm actually trying to find a "weight" of something that the user is putting in, but they want to match exact numbers, so I have to meet the requirement. – Joseph Feb 02 '10 at 22:28
  • 3
    Is it a user requirement to have a float datatype in the table? Typically, when the users want exact fractional numbers (e. g. money), the decimal datatype is a much better choice. You'll be getting rounding issues for years to come. Think of it. And if it IS actually money, by any chance, waste no time and redesign for decimal RIGHT NOW. Trust me, I speak from experience here. – Seva Alekseyev Feb 03 '10 at 02:05
  • I do agree with Seva. The first thing I do before I start working on a new database is to run the following query and find how many float columns are used. SELECT DATA_TYPE, COUNT(*) AS mycount FROM information_schema.COLUMNS WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') GROUP BY DATA_TYPE ORDER BY mycount DESC If I find that the number of double, float, bigint and blobs are too high then I assume there are going to be a lot of problems. If the number of decimal, date, datetime, tinyint is too small (or nill) then again there is a problem :) – shantanuo Feb 03 '10 at 06:42
  • Off-by-one-cent errors! On amounts that you cannot predict in advance! And you cannot catch them in testing! Accountants flipping out! And so on, and so forth. – Seva Alekseyev Feb 04 '10 at 00:36
  • @Seva you're totally right, but the problem is that i'm using a CMS, which generates these fields automatically, so i don't have any control over the schema unfortunately. It's very irritating to me that it doesn't support currency as a decimal – Joseph Apr 12 '10 at 19:49
  • @Seva I'm going to mark your answer as the accepted answer even though it doesn't work for what I was trying to do. It does definitely resolve the issue that I put forth in this question, though. – Joseph Apr 12 '10 at 19:50
  • The answer isn't mine. I just tried to scare you into a proper design :) – Seva Alekseyev Apr 13 '10 at 03:01
  • Nice shot, but in my case 1e-5 is ok (1e-6 not working). – userlond Oct 15 '15 at 01:28
5

I found a way to check for what my users view as equality.

I had to convert the field to a character string and test the character sets, so this works fine for them:

select 'column1'
from 'some_table'
where CAST('some_float_field' AS CHAR) <=> '2.18'
Joseph
  • 25,330
  • 8
  • 76
  • 125
3

Consider this:

where abs(some_float_field - 2.18) < 0.001
Seva Alekseyev
  • 59,826
  • 25
  • 160
  • 281
  • I understand the precision issue. In my case I don't care about precision past the 2nd decimal place. I just want to find anything that matches, I'm not looking to do any arithmetic. – Joseph Feb 02 '10 at 22:24
1

I know these is an old post but if you don't want a precise comparison just use LIKE or NOT LIKE :

select 'column1'
from 'some_table'
where 'some_float_field' NOT LIKE '2.18'
Shaolin
  • 415
  • 5
  • 11
  • It's a string comparison! It returns also rows like `12.18` etc. – xmedeko Oct 12 '17 at 06:34
  • @xmedeko the author ask to find a way to get all values not equal to 2.18. Even if it is a string comparison 12.18 is not equal to 2.18 so there is no problem to return 12.18. Also if you look closer to my query you will notice that I don't use the percent sign and this why 12.18 is a correct value to return. – Shaolin Oct 13 '17 at 07:41
  • @xmedeko there is nothing to edit. If you want only floats equals to 2.18 then just use LIKE without % sign and it will retrieve only 2.18. `create table test (price float) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;insert into test values(2.18), (12.18), (32.18);SELECT * FROM test where price LIKE '2.18';` – Shaolin Oct 16 '17 at 08:50
  • Sorry, I've overlooked that, what's the point of using `LIKE` and not equality `=` then? – xmedeko Oct 16 '17 at 10:20
  • @xmedeko Just try by yourself `SELECT * FROM test where price = 2.18` won't give you a result. It is because float type in mysql are approximate numeric data values : https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html – Shaolin Oct 17 '17 at 11:15
  • I see, just because LIKE enforces string comparison and not the [default implicit type conversions](https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html). Maybe better is `'2.18' = cast(price as char)` or `'2.18' = concat(price)`. – xmedeko Oct 17 '17 at 12:48