MySQL Bugs: #32823: Incompatible change of decimal column in 5.0. -0.00 no longer supported.

Bug #32823	Incompatible change of decimal column in 5.0. -0.00 no longer supported.
Submitted:	28 Nov 2007 17:07	Modified:	3 Dec 2007 17:12
Reporter:	Morgan Tocker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1, 5.0.50	OS:	Any
Assigned to:	Alexey Botchkov	CPU Architecture:	Any
Tags:	regression

Description:
See how to repeat.

How to repeat:
#!/bin/bash

mysqladmin -S /tmp/mysql50.sock shutdown
mysqladmin -S /tmp/mysql41.sock shutdown

cd ~/bin/mysql41
rm -rf data/*
./bin/mysqld --no-defaults --basedir=$PWD --skip-grant --skip-networking --socket=/tmp/mysql41.sock &

cd ~/bin/mysql50
rm -rf data/*
./bin/mysqld --no-defaults --basedir=$PWD --skip-grant --skip-networking --socket=/tmp/mysql50.sock &

mysql -S /tmp/mysql41.sock -e 'CREATE DATABASE d1';
mysql -S /tmp/mysql41.sock -e 'CREATE TABLE d1.decimaltest (id INT NOT NULL, d decimal (6,2))';

mysql -S /tmp/mysql41.sock -e "INSERT INTO d1.decimaltest (id, d) VALUES (1, '0.00'), (2, '-0.00'), (3, '0.00')";

# export to a text file
mysqldump -S /tmp/mysql41.sock d1 > /tmp/file_from_4.1.sql

# import into 5.0
mysql -S /tmp/mysql50.sock -e 'CREATE DATABASE d1';
mysql -S /tmp/mysql50.sock d1 < /tmp/file_from_4.1.sql

# export again
mysqldump -S /tmp/mysql50.sock d1 > /tmp/file_from_5.0.sql

# import into 4.1 and dump again
mysql -S /tmp/mysql41.sock d1 < /tmp/file_from_5.0.sql
mysqldump -S /tmp/mysql41.sock d1 > /tmp/file_returned_from_5.0.sql

# compare file_from_4.1.sql to file_returned_from_5.0.sql (should be the same, but it's not)

diff /tmp/file_from_4.1.sql /tmp/file_returned_from_5.0.sql

# output is:
# 32c32
# < INSERT INTO `decimaltest` VALUES (1,'0.00'),(2,'-0.00'),(3,'0.00');
# ---
# > INSERT INTO `decimaltest` VALUES (1,'0.00'),(2,'0.00'),(3,'0.00');

Suggested fix:
Don't know!
See: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html

It's probably covered under:
"Incompatible change: MySQL 5.0.3 and up uses precision math when calculating with DECIMAL and integer columns (64 decimal digits) and for rounding exact-value numbers. Rounding behavior is well-defined, not dependent on the implementation of the underlying C library. However, this might result in incompatibilities for applications that rely on the old behavior. (For example, inserting .5 into an INT column results in 1 as of MySQL 5.0.3, but might be 0 in older versions.) For more information about rounding behavior, see Section 22.4, “Rounding Behavior”, and Section 22.5, “Precision Math Examples”."

So no mysqldump and reload will help you.  If it's intended that we don't support -0.00, should we document it somewhere that we used to.  It might effect a smaller number of people hoping to switch.  They will have permanently lost this information if they try to switch back.

The behavior observed is to be expected as described here:

http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html

"As a result of the change from string to numeric format for DECIMAL storage, DECIMAL columns no longer store a leading + character or leading 0 digits. Before MySQL 5.0.3, if you inserted +0003.1 into a DECIMAL(5,1) column, it was stored as +0003.1. As of MySQL 5.0.3, it is stored as 3.1. Applications that rely on the older behavior must be modified to account for this change."

I will add modify that passage to note that leading '-' characters are not stored as such, either.
I'll also add a cross reference to this section from the upgrade notes.

Closing report because the change in behavior is documented as previously noted.