MySQL Bugs: #17948: Decimal arithmetic failure after alter

Bug #17948	Decimal arithmetic failure after alter
Submitted:	6 Mar 2006 3:23	Modified:	5 Jul 2006 6:37
Reporter:	Peter Gulutzan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1.8-beta-debug	OS:	Linux (SUSE 10.0)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I create a table with a DECIMAL(15,5) column.
I insert a value.
I alter the table so the column is DECIMAL(25,5).
I update multiplying the column by 100,000.
I see a warning, and the result is incorrect.

How to repeat:
mysql> create table t12 (s1 decimal(15,5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t12 values (1234567890.12345);
Query OK, 1 row affected (0.00 sec)

mysql> alter table t12 modify s1 decimal(25,5);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update t12 set s1 = s1 * 100000;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 's1' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from t12;
+----------------------------+
| s1                         |
+----------------------------+
| 99999999999999999744.00000 |
+----------------------------+
1 row in set (0.00 sec)

mysql> insert into t12 values (1234567890.12345 * 100000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t12;
+----------------------------+
| s1                         |
+----------------------------+
| 99999999999999999865.00000 |
|      123456789012345.00000 |
+----------------------------+
2 rows in set (0.01 sec)

works fine in 5.0, broken in 5.1 (both bk builds)

See Bug #18014: data loss caused by altering decimal fields.
Fixed in 5.1.12-beta.

A new test case:

mysql> create table t12 (s1 decimal(15,5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t12 values (1234567890.12345);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t12;
+------------------+
| s1               |
+------------------+
| 1234567890.12345 |
+------------------+
1 row in set (0.00 sec)

mysql> alter table t12 modify s1 decimal(25,5);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update t12 set s1 = s1 * 100000;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t12;
+-----------------------+
| s1                    |
+-----------------------+
| 123456789012345.00000 |
+-----------------------+
1 row in set (0.00 sec)

mysql> alter table t12 modify s1 decimal(20,5);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t12;
+------+
| s1   |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------+
| Level | Code | Message                                             |
+-------+------+-----------------------------------------------------+
| Error | 1366 | Incorrect decimal value: '' for column '' at row -1 |
+-------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

Reverified with PeterG's latest test case.

miguel@hegel:~/dbs/mysql-5.1> bk changes | head
ChangeSet@1.2234, 2006-07-03 13:49:25-04:00, cmiller@zippy.(none)
  Additional patch from Bug#16206.  BEGIN/COMMIT behavior changed 5.0 -> 5.1 .

ChangeSet@1.2233, 2006-07-03 11:35:58-04:00, cmiller@zippy.(none)
  Merge zippy.(none):/home/cmiller/work/mysql/merge/mysql-5.0
  into  zippy.(none):/home/cmiller/work/mysql/merge/mysql-5.1

miguel@hegel:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t12 (s1 decimal(15,5));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t12 values (1234567890.12345);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t12;
+------------------+
| s1               |
+------------------+
| 1234567890.12345 | 
+------------------+
1 row in set (0.01 sec)

mysql> alter table t12 modify s1 decimal(25,5);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update t12 set s1 = s1 * 100000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t12;
+-----------------------+
| s1                    |
+-----------------------+
| 123456789012345.00000 | 
+-----------------------+
1 row in set (0.01 sec)

mysql> alter table t12 modify s1 decimal(20,5);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t12;
+------+
| s1   |
+------+
|    0 | 
+------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------+
| Level | Code | Message                                             |
+-------+------+-----------------------------------------------------+
| Error | 1366 | Incorrect decimal value: '' for column '' at row -1 | 
+-------+------+-----------------------------------------------------+
1 row in set (0.00 sec)