Solved

float value

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

737 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