Bug #6103 Multi-table UPDATE sets unexpected values

Submitted: 14 Oct 2004 16:47 Modified: 16 Oct 2004 17:16
Reporter: Victoria Reznichenko Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:all OS:Linux (Linux)
Assigned to: 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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.