CODEWITHSUNDEEP
mysqlcastingfloating-point
Sebas
Sebas

Reputation: 21542

Disturbing mysql behaviour around DECIMAL datatype conversion

So, here is the structure:

mysql> describe tier;
+---------------------+----------------+------+-----+---------+----------------+

| Field               | Type           | Null | Key | Default | Extra          |

+---------------------+----------------+------+-----+---------+----------------+

| ID                  | int(10)        | NO   | PRI | NULL    | auto_increment |

| UP_TO               | decimal(21,10) | YES  |     | NULL    |                |

+---------------------+----------------+------+-----+---------+----------------+

2 rows in set (0.01 sec)

Then the datas:

mysql> select id, up_to from tier;
+----+-----------------+
| id | up_to           |
+----+-----------------+
|  1 | 1000.0000000000 |
|  2 | 2000.0000000000 |
|  3 | 3000.0000000000 |
|  4 |  500.0000000000 |
|  5 | 1000.0000000000 |
|  6 | 1500.0000000000 |
|  7 |  100.0000000000 |
|  8 |  200.0000000000 |
|  9 | 1000.0000000000 |
| 10 | 2000.0000000000 |
| 11 |  100.0000000000 |
| 12 |  200.0000000000 |
+----+-----------------+
12 rows in set (0.00 sec)

Then there's a little transformation:

mysql> SELECT id, TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM CAST(up_to AS CH
AR) )) as converted from tier;
+----+-----------+
| id | converted |
+----+-----------+
|  1 | 1000      |
|  2 | 2000      |
|  3 | 3000      |
|  4 | 500       |
|  5 | 1000      |
|  6 | 1500      |
|  7 | 100       |
|  8 | 200       |
|  9 | 1000      |
| 10 | 2000      |
| 11 | 100       |
| 12 | 200       |
+----+-----------+
12 rows in set (0.00 sec)

Fine.

Let's put that in a stored function to be handy!

DELIMITER //
CREATE FUNCTION strip_trailing_zero(I_DEC DECIMAL(10,7)) RETURNS VARCHAR(20) DETERMINISTIC
BEGIN
DECLARE strBuff VARCHAR(20);
DECLARE cnt  NUMERIC(2);
DECLARE tString VARCHAR(20);
    SELECT CAST(I_DEC AS CHAR) INTO tString;
    SELECT LOCATE('.',tString) INTO cnt;

    IF cnt > 0 THEN
        SELECT TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM tString)) INTO strBuff;
    ELSE
        SET strBuff = tString;
    END IF;

    RETURN strBuff;
END//

DELIMITER ;

Cool.

GRANT EXECUTE ON FUNCTION mysql.strip_trailing_zero TO 'whatever'@'localhost';

At last, trying out my new toy now... :

mysql> select id, mysql.strip_trailing_zero(`up_to`) as converted_2 from tier;
+----+-------------+
| id | converted_2 |
+----+-------------+
|  1 | 999.9999999 |
|  2 | 999.9999999 |
|  3 | 999.9999999 |
|  4 | 500         |
|  5 | 999.9999999 |
|  6 | 999.9999999 |
|  7 | 100         |
|  8 | 200         |
|  9 | 999.9999999 |
| 10 | 999.9999999 |
| 11 | 100         |
| 12 | 200         |
+----+-------------+
12 rows in set, 7 warnings (0.02 sec)

Well, then, f*ck you too mysql!

No seriously, that's a silly joke. I'm convinced I did something wrong and there's a floating point conversion in the middle but I just can't figure it out!

Help welcome! Thanks. S.

edit: result after changing the input parameter type to DECIMAL(21,10):

mysql> select id, mysql.strip_trailing_zero(`up_to`) as converted_2 from tier;
+----+-------------+
| id | converted_2 |
+----+-------------+
|  1 | 1000        |
|  2 | 2000        |
|  3 | 3000        |
|  4 | 500         |
|  5 | 1000        |
|  6 | 1500        |
|  7 | 100         |
|  8 | 200         |
|  9 | 1000        |
| 10 | 2000        |
| 11 | 100         |
| 12 | 200         |
+----+-------------+
12 rows in set (0.02 sec)

Problem solved... Great! thank you!

Upvotes: 1

Views: 86

Answers (1)

sgeddes
sgeddes

Reputation: 62851

Your UP_TO field in your table is defined as decimal(21,10), but your function takes in a decimal(10,7). I imagine that is stripping your value.

Try changing your function to accept decimal(21,10) instead.

Upvotes: 2

Related Questions