Paul McNeil wrote:
Good morning to all.
I have a problem with a workaround but I wanted to know if others have run into this.
Are you sure the problem is with mysql? If so, which version do you have? I have 4.0.20, and I get different (expected) results.
Table DATA Column strength [double]
CREATE TABLE data (strength DOUBLE); INSERT INTO data VALUES (3.256498), (0), (2.71828), (NULL), (0.00000);
When I select strength from DATA and the result is a non zero amount it returns correctly
3.256498
however if it is a 0 amount I get
0.00000000
mysql> SELECT strength FROM data; +----------+ | strength | +----------+ | 3.256498 | | 0 | | 2.71828 | | NULL | | 0 | +----------+ 5 rows in set (0.18 sec)
The problem is that in my java.sql.ResultSet.getDouble("strength") a zero amount throws a number format exception. SO, I placed a conditional....
SELECT CASE WHEN strength IS NULL OR strength = 0 THEN 0 ELSE strength END
This, however seems to truncate the result so that a zero return results in
0 BUT a return of 3.1236564 results in 3.
mysql> SELECT CASE WHEN strength IS NULL OR strength = 0 -> THEN 0 ELSE strength END AS strength -> FROM data; +----------+ | strength | +----------+ | 3.256498 | | 0 | | 2.71828 | | 0 | | 0 | +----------+ 5 rows in set (0.27 sec)
Bummer. Finally I had to restructure my conditional...
SELECT CASE WHEN strength IS NOT NULL AND strength != 0 THEN strength ELSE '0' END
You shouldn't need to quote 0, and "IS NOT NULL" is redundant here, as strength != 0 will evaluate to FALSE when strength is NULL.
mysql> SELECT CASE WHEN strength != 0 THEN strength ELSE 0 END strength
-> FROM data;
+----------+
| strength |
+----------+
| 3.256498 |
| 0 |
| 2.71828 |
| 0 |
| 0 |
+----------+
5 rows in set (0.27 sec)I feel that this type of data manipulation shouldn't need to be done. Is
Agreed.
this a bug or normal for a return type of double?
For me, it is neither. Are you using an older version? Otherwise, I suspect the problem lies elsewhere.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
