Solved

float value

Posted on 2003-10-24
5
2,000 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
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 125 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now