MySQL Math Error?

[naelurec]

Hi everyone .. I am coming up with some strange math when dealing with large numbers. Hopefully someone can point out my problem.

I am multiplying a float and bigint(20) together.

Here are some of the results (fyi: close = float, shares = bigint(20) and marketshare = close * shares)

Close: 27.27 Shares: 1870000000
MySQL returns Marketshare as 50994900856.0181 instead of 50994900000

Close: 12.55 Shares: 979000000
MySQL returns Marketshare as 12286450186.7294 instead of 12286450000

On smaller values, MySQL returns the correct number -- IE:

Close: 27.25 Shares: 3330000000 Marketshare (correct): 90742500000

Any ideas/suggestions would be greatly appreciated!

FYI: here is the Query:
SELECT close * shares as marketshare, close, shares
FROM stocks WHERE date = '2002-11-01'

[tomhath]

Read up on the difference between range and precision. You have exceeded the precision of the floating point type. Probably need to go to decimal or bigint * 100.

[naelurec]

Originally posted by tomhath
Read up on the difference between range and precision. You have exceeded the precision of the floating point type. Probably need to go to decimal or bigint * 100.

Thanks for the info.. After thinking about it for a while, I thought it might have something to do with data type, so I read up on it .. sure enough .. ended up using decimal and it is working great.