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]