CODEWITHSUNDEEP
mysqlselectsubquery
Mats
Mats

Reputation:

Mysql Case returns wrong value

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?

Upvotes: 1

Views: 368

Answers (2)

Josh Davis
Josh Davis

Reputation: 28730

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

Upvotes: 0

Justin Giboney
Justin Giboney

Reputation: 3301

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

Upvotes: 1

Related Questions