Re: varchar(10) to decimal

Michael Stassen Wed, 18 May 2005 11:34:02 -0700

4.1.11 truncates as well, and not just for ALTER TABLE. My tests show mysql handles input to a DECIMAL(M,D) column inconsistently: It rounds if the input is numeric, but it truncates if the input is a string. I'm guessing that because mysql 4.x stores DECIMALs as strings, it handles the string case with a simple string copy, hence the truncation. I think that's a bug, so I reported it as such <http://bugs.mysql.com/10719>. We'll see what the developers think.

The different behavior in 5.0.4 is no doubt a result of the change made to DECIMAL in 5.0.3, <http://dev.mysql.com/doc/mysql/en/precision-math-decimal-changes.html>, though this particular aspect of the change doesn't seem to be documented. Starting with 5.0.3, DECIMAL is no longer a string, so I'd bet the input string is converted to a number first, which means it gets rounded before it is assigned, as is the case in mysql 4.x

Michael

Gordon wrote:

I was all set to tell you why 16.125 became 16.12 when I ran the test on our
production box. It looks like some where between 4.0.20 and 5.0.4 the ALTER
TABLE to a decimal data type changed from truncation to rounding.

Redhat MySQL 4.0.20 truncates all Windows XP MySQL 5.0.4 Rounds with Windows algorithm I suspect a MySQL 5.0.4 on Linux would round with Linux alorithm but I don't have one to test.

See following:

The decimal(6,2) tells MySQL to "round" all values to 2 places and store the results in a decimal field. MySQL uses the Round routines of the host. On a Windows box 16.125 = 16.13 16.135 = 16.14

On a Linux/Unix box
        16.125 = 16.12
        16.135 = 16.14

Here it is on windows
____________________________________________________________________________
mysql> select * from dcml;
+--------+
| a      |
+--------+
| 16.00  |
| 16.25  |
| 16.125 |
| 16.135 |
+--------+
4 rows in set (0.00 sec)

mysql> alter table dcml modify a decimal(6,2);
Query OK, 4 rows affected, 2 warnings (0.28 sec)
Records: 4  Duplicates: 0  Warnings: 2

mysql> show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'a' at row 3 |
| Note  | 1265 | Data truncated for column 'a' at row 4 |
+-------+------+----------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from dcml;
+-------+
| a     |
+-------+
| 16.00 |
| 16.25 |
| 16.13 |
| 16.14 |
+-------+
4 rows in set (0.00 sec)

_________________________________________________________________________

And on Linux
_________________________________________________________________________

mysql> create table dcml (a varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dcml values('16.00'),('16.25'),('16.125'),('16.135');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dcml;
+--------+
| a      |
+--------+
| 16.00  |
| 16.25  |
| 16.125 |
| 16.135 |
+--------+
4 rows in set (0.00 sec)

mysql> alter table dcml modify a decimal(6,2);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 2

mysql> show warnings;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that
corre

Our production system is on 4.0.20 which does not support SHOW WARNINGS
and apparently the ALTER truncates always

mysql> select * from dcml;
+-------+
| a     |
+-------+
| 16.00 |
| 16.25 |
| 16.12 |
| 16.13 |
+-------+
4 rows in set (0.00 sec)
_________________________________________________________________________

Linux rule paraphrased: If the value to the right of the rounding column is
a 5 then if the rounding column is even round down if the rounding column is
odd round up.

-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 18, 2005 10:00 AM To: Jerry Swanson Cc: mysql@lists.mysql.com Subject: Re: varchar(10) to decimal

Hi,
if varchar represents decimal(6,x) where x>2, it's truncated. Else, it's
converted :


mysql> create table dcml (a varchar(10));
Query OK, 0 rows affected (0.24 sec)

mysql> insert into dcml values('16.00'),('16.25'),('16.125'),('16.135');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from dcml;
+--------+
| a      |
+--------+
| 16.00  |
| 16.25  |
| 16.125 |
+--------+
3 rows in set (0.03 sec)

mysql> alter table dcml modify a decimal(6,2);
Query OK, 3 rows affected, 1 warning (0.24 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 3 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from dcml;
+-------+
| a     |
+-------+
| 16.00 |
| 16.25 |
| 16.12 |
+-------+
3 rows in set (0.00 sec)

Here, only row 3 is truncated !

Mathias

Selon Jerry Swanson <[EMAIL PROTECTED]>:

decimal(6,2)

On 5/18/05, Philippe Poelvoorde <[EMAIL PROTECTED]> wrote:

Jerry Swanson wrote:

I need to change format from varchar(10) to decimal.
When I alter the table the data is trimmed.

What I'm doing wrrong?

TH


ALTER TABLE your_table MODIFY your_column double NOT NULL DEFAULT '0.0';
should normally work. What is the command you are doing and have you
example results ?
How did you declare your decimal column ?

--
Philippe Poelvoorde
COS Trading Ltd.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: varchar(10) to decimal

Reply via email to