Re: Select double value

[Michael Stassen]

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