Bug #66115 updating more than one varchar column produces warnings

• Submitted: 31 Jul 2012 13:00
• Modified: 31 Jul 2012 19:02
• Reporter: Chetan Suttraway
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.5.25a
• OS: Windows (windows 7)
• CPU Architecture: Any
• Tags: two fields, UPDATE, varchar, warning

[31 Jul 2012 13:00] Chetan Suttraway

Description:
if a table has more then one varchar field, and if you try to update both of such columns, then the column values are not correctly assigned. The column is assinged 0 and we get "Truncated incorrect double value" warning.
The remaining varchar columns referred in set clause are assigned with NULL values.

If the update is on only one varchar field, then it succeeds.

How to repeat:
/*mysql version details */
mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.8                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.25a                      |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win32                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

CREATE TABLE `t` (
  `firstname` char(60) DEFAULT NULL,
  `secondname` char(60) DEFAULT NULL,
  `pid` int(11) DEFAULT NULL
) ENGINE=InnoDB

mysql> INSERT INTO t VALUES(NULL,NULL, 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t VALUES('firsts','second', 2);
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE t SET firstname='fasty' AND secondname='tour' WHERE pid=1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'fasty' |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t;
+-----------+------------+------+
| firstname | secondname | pid  |
+-----------+------------+------+
| 0         | NULL       |    1 |
| firsts    | second     |    2 |
+-----------+------------+------+
2 rows in set (0.00 sec)

mysql>

/* for single varchar field update */
mysql> UPDATE t SET firstname='thisworks' WHERE pid=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t;
+-----------+------------+------+
| firstname | secondname | pid  |
+-----------+------------+------+
| 0         | NULL       |    1 |
| thisworks | second     |    2 |
+-----------+------------+------+
2 rows in set (0.00 sec)

mysql>

[31 Jul 2012 13:45] Valeriy Kravchuk

This is not a bug, but a classical mistake... AND is a logical operator and has higher priority than assignment. You should use

UPDATE t SET firstname='fasty', secondname='tour' WHERE pid=1;

instead of

UPDATE t SET firstname='fasty' AND secondname='tour' WHERE pid=1;

[31 Jul 2012 19:02] Chetan Suttraway

Thanks for sharing the correct syntax.