Mysql Case returns wrong value

Question

On one server I use mysql version 5.0.45 and when running the following query it returns 1 if boolvalue='True' AND 2.2 else, just as it should.

SELECT Q.value
FROM (
SELECT (CASE table.boolvalue='True' WHEN 1 THEN 1 ELSE 2.2 END) AS value FROM table
) Q;

On another server (webhosting) I use mysql version 5.0.22 and when running the same query it always returns 0.9999, why?!

But using this query it returns the result as it should:

SELECT (CASE table.boolvalue='True' WHEN 1 THEN 1 ELSE 2.2 END) AS value FROM table

I need to use the subquery as the query also does some other stuff, just broke it down to show you where the error is. Please can someone explain this to me?

Answer 1

It may be a floating point error. Try sticking ROUND() in front of your number to fix the problem

Answer 2

It might just be a bug. 5.0.22 is very old, after all. By the way, you should be able to simplify your CASE as

SELECT (CASE WHEN table.boolvalue='True' THEN 1 ELSE 2.2 END) AS value FROM table

Or even simply use IF()

SELECT IF(table.boolvalue='True', 1, 2.2) AS value FROM table