Bug #3361 mysqldump quotes DECIMAL values

• Submitted: 1 Apr 2004 11:22
• Modified: 6 Apr 2004 2:35
• Reporter: Dean Ellis
• Status: Closed
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S3 (Non-critical)
• Version: 4.0.19 4.1.2
• OS: Any
• CPU Architecture: Any

[1 Apr 2004 11:22] Dean Ellis

Description:
mysqldump quotes DECIMAL values (thought not FLOAT); further it quotes them with " even in ANSI compatibility mode.

How to repeat:
USE test;
CREATE TABLE t1 ( a DECIMAL(10,5), b FLOAT );
INSERT INTO t1 VALUES (1.2345, 2.3456);

mysqldump test t1

mysqldump --compat=ANSI

Suggested fix:
Do not quote DECIMAL/NUMERIC values.

[5 Apr 2004 8:35] Victor Vagin

Actually it's not a bug..
The quotating was introduced as a fix for bug 
Bug #2005 "Long decimal comparion bug. " 
(Look at http://bugs.mysql.com/bug.php?id=2005)

Problem is possible only with using " marks in ansi mode:

----------------------------
mysql> CREATE TABLE t1 (a DECIMAL(10,5), b FLOAT);

INSERT INTO t1 VALUES (1.2345, 2.3456);
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO t1 VALUES (1.2345, 2.3456);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES ('1.2345', 2.3456);
INSERT INTO t1 VALUES ("1.2345", 2.3456);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES ("1.2345", 2.3456);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1.2345, 2.3456);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES ('1.2345', 2.3456);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES ("1.2345", 2.3456);
ERROR 1054 (42S22): Unknown column '1.2345' in 'field list'
mysql> SET SQL_MODE=@OLD_SQL_MODE;
Query OK, 0 rows affected (0.00 sec)
----------------------------

To avoid such situation, i wrote patch that changes " quotas to '

subj:bk commit - 4.0 tree (vva:1.1765) BUG#3361
ChangeSet
  1.1765 04/04/05 23:18:16 [e-mail] +3 -0
  fixed Bug #3361 "mysqldump quotes DECIMAL values"

[6 Apr 2004 2:35] Victor Vagin

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Patch was pushed. 
Fix will be in mysql-4.0.19