?
Solved

MySQL  floating point number equality test, how does it work?

Posted on 2007-10-08
10
Medium Priority
?
293 Views
Last Modified: 2010-08-05
See the following, when I tried to list PROD_PRICE = 5.99  it showed an empty set, whereas the list
obviously show it has such entries.

mysql> select * from PRODUCT;
+----------+----------------------+-----------+------------+
| PROD_NUM | PROD_DESCRIPTION     | VEND_CODE | PROD_PRICE |
+----------+----------------------+-----------+------------+
|        1 | Phillip Screw Driver |         1 |       5.99 |
|        2 | Drill  bit #3        |         1 |       9.99 |
|        3 | Phillip Screw Driver |         1 |       5.99 |
+----------+----------------------+-----------+------------+
3 rows in set (0.00 sec)

mysql> select * from PRODUCT where PROD_PRICE = 5.99;
Empty set (0.00 sec)

mysql> select * from PRODUCT where PROD_PRICE = '5.99';
Empty set (0.00 sec)

mysql>

----------------------

Thanks.
0
Comment
Question by:zzhang2006
  • 4
  • 3
  • 3
10 Comments
 
LVL 17

Assisted Solution

by:Aleksandar Bradarić
Aleksandar Bradarić earned 248 total points
ID: 20035184
Could you post the CREATE TABLE statement for table `PRODUCT`? Also which version mf MySQL are you running?
0
 
LVL 28

Accepted Solution

by:
gamebits earned 252 total points
ID: 20035251
select * from PRODUCT WHERE FORMAT( PROD_PRICE, 2 ) = 5.99
0
 
LVL 1

Author Comment

by:zzhang2006
ID: 20035681
I have MySQL Server version of 5.0.27. I guess it is the server's version that matters.
Anyway I am using MySQL Control Center/SQL client and mysql.exe command line
client, both giving the same problem.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 28

Expert Comment

by:gamebits
ID: 20035867
Did you try my solution?
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 20035886
Does gamebits' query work?
0
 
LVL 1

Author Comment

by:zzhang2006
ID: 20035918
Yes, gamebit solution works!  Thanks.  But that make it sound alkward though, why can't it be a simply equal sign?
0
 
LVL 28

Expert Comment

by:gamebits
ID: 20035932
That's a question you need to ask the mysql developpers
0
 
LVL 1

Author Comment

by:zzhang2006
ID: 20035940
This is the statemanet I used to create the PRODUCT table.

CREATE TABLE PRODUCT(PROD_NUM                  INT PRIMARY KEY,
                                                  PROD_DESCRIPTION  CHAR(60),
                                                  VEND_CODE              INT ,
                                                  PROD_PRICE                FLOAT NOT NULL
                                                  );

0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 20035958
> why can't it be a simply equal sign?

The value of 5.99 is probably stored as 5.99000001 or something, so it does not trigger the WHERE condition :( On the other hand, SELECT uses the formatting defined in the CREATE TABLE (e.g. 2 decimals), so it shows 5.99 :(
0
 
LVL 28

Expert Comment

by:gamebits
ID: 20036012
You can change the type to DOUBLE with the length/value set to 7,2 than it will work with your query

select * from PRODUCT where PROD_PRICE = 5.99;
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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…
Suggested Courses
Course of the Month14 days, 14 hours left to enroll

839 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