• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2031
  • Last Modified:

float value

Does anybody know why I can't get result in the second case?
--------------------------------------------------------------------------------
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1212 to server version: 4.0.14-max-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use dekoprint;
Database changed
mysql> describe proba;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | tinyint(4)  |      | PRI | NULL    | auto_increment |
| k     | float(11,1) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from proba;
+----+------+
| id | k    |
+----+------+
|  1 |  2.1 |
|  2 |  2.5 |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from proba where k=2.5;
+----+------+
| id | k    |
+----+------+
|  2 |  2.5 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from proba where k=2.1;
Empty set (0.00 sec)

mysql>
0
balee
Asked:
balee
1 Solution
 
SqueebeeCommented:
This is a common problem with floating point numbers and rounding issues. Essentially you need to look at an alternative method, I do not have time here are work to go into it, but look at

http://www.mysql.com/doc/en/Problems_with_float.html

For a good explanation of why not to use rounding with floats.

Quick summary: never compare to an absolute number, but take the absolute value of the difference and compare it against a tolerance value.
0
 
andreifCommented:
It's float, so you got not 2.1 there, but something different like 2.099999
Where the data is coming from?
Are you sure you want to use float there, what is your numbers range? may be you should consider using decimal(11,1)

check docs:

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
A small (single-precision) floating-point number. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. If UNSIGNED is specified, negative values are disallowed. The M is the display width and D is the number of decimals. FLOAT without arguments or FLOAT(X) where X <= 24 stands for a single-precision floating-point number.

D is used for displaying only, so it doesn't make sure the number is actually rounded there.

Also check this article:
http://www.mysql.com/doc/en/Problems_with_float.html

Good Luck
0
 
richardjbCommented:
Hi balee,

If you want (or have) to stay with floats, write something like select * from proba where k between 2.099 and 2.101 ;

Regards,  Richard
0
 
baleeAuthor Commented:
resertew
0
 
baleeAuthor Commented:
My browser doesn't show accept buttons ... it took a while I could find it :)

Decimal works, thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now