?
Solved

float value

Posted on 2003-10-24
5
Medium Priority
?
2,016 Views
Last Modified: 2012-06-27
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
Comment
Question by:balee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 17

Expert Comment

by:Squeebee
ID: 9615116
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
 
LVL 6

Accepted Solution

by:
andreif earned 500 total points
ID: 9615118
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
 
LVL 3

Expert Comment

by:richardjb
ID: 9617683
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
 
LVL 1

Author Comment

by:balee
ID: 9619549
resertew
0
 
LVL 1

Author Comment

by:balee
ID: 9619555
My browser doesn't show accept buttons ... it took a while I could find it :)

Decimal works, thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question