rounding/truncation of decimal fields

[Matt Loschert]

I have quick question about rounding and/or truncation of a decimal table
field.  I ran into this unexpectedly and found it surprising.

Given the following input:


  DROP TABLE IF EXISTS test1;
  CREATE TABLE test1 (id tinyint, truncated decimal(12,2));
  INSERT INTO test1 VALUES (1, '0.004');
  INSERT INTO test1 VALUES (2,  0.004 );
  INSERT INTO test1 VALUES (3, '0.005');
  INSERT INTO test1 VALUES (4,  0.005 );
  SELECT * FROM test1;
  SELECT VERSION();


I received this output.  Is this to be expected since I used a stringified
form for some of the numbers?


mysql> CREATE TABLE test1 (id tinyint, truncated decimal(12,2));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (1, '0.004');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (2,  0.004 );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (3, '0.005');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (4,  0.005 );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test1;
+------+-----------+
| id   | truncated |
+------+-----------+
|    1 |      0.00 |
|    2 |      0.00 |
|    3 |      0.00 |
|    4 |      0.01 |
+------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 3.23.49   |
+-----------+
1 row in set (0.00 sec)


- Matt

--
Matt Loschert - Software Engineer       | email: loschert@stripped    |
ServInt Internet Services               | web:   http://www.servint.net/ |
McLean, Virginia USA                    | phone: [phone]          |