Solved

float value

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

821 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