SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    525
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Simple number comparison giving false results

    I'm doing a number comparison between values in different tables and MySql is telling me the numbers are not equal when in fact they are.

    As an illustration:
    Code:
    SELECT 
    	(select costPrice from tProducts where productID=164082) AS n1, 
    	(select costPrice from simons_products where productID=164082) AS n2, 
    	(select costPrice from tProducts where productID=164082) = (select costPrice from simons_products where productID=164082) AS is_equal
    This gives the result
    Code:
    n1	n2	is_equal
    13.12	13.12	0
    Both of these fields are defined as double(10,2) and have charset latin1, so I'm a bit stumped as to why it's giving this result.

    I can get around it by applying a 2 dp rounding to each value when doing the equality evaluation, but I'm still unsure as to why this is necessary. Any ideas?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hessodreamy View Post
    Both of these fields are defined as double(10,2)
    there's your problem right there

    floating point numbers are approximations only

    floating point numbers are intended for extremely small numbers (e.g. distance between atoms measured in kilometres) or extremely large numbers (e.g. distance between stars measured in centimetres)

    use DECIMAL(10,2) and your troubles will go away
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    525
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Gah! It would be something as simple as that! Cheers!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •