Bug #6103 Multi-table UPDATE sets unexpected values

• Submitted: 14 Oct 2004 16:47
• Modified: 16 Oct 2004 17:16
• Reporter: Victoria Reznichenko
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: all
• OS: Linux (Linux)
• CPU Architecture: Any

[14 Oct 2004 16:47] Victoria Reznichenko

Description:
Multi-table UPDATE sets unexpected value if LEFT JOIN is used and one table is empty.

mysql> SELECT * FROM t2;
+------+------+
| id   | str  |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

mysql> UPDATE t2 LEFT JOIN t1 USING(id) SET t2.str='b' AND t1.str='c';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t2;
+------+------+
| id   | str  |
+------+------+
|    1 | 0    |
+------+------+
1 row in set (0.00 sec)

How to repeat:
CREATE TABLE t1(id int, str varchar(10));
CREATE TABLE t2(id int, str varchar(10));
INSERT INTO t2 VALUES (1,'a');
SELECT * FROM t2;
UPDATE t2 LEFT JOIN t1 USING(id) SET t2.str='b' AND t1.str='c';
SELECT * FROM t2;

[16 Oct 2004 17:16] MySQL Verification Team

This is expected behaviour.

The error is in the SET clause, where AND transforms the result to boolean 0 or 1.