MySQL Bugs: #58743: UPDATE statement is accepted but produces wrong results when AND is used in SET

Bug #58743	UPDATE statement is accepted but produces wrong results when AND is used in SET
Submitted:	5 Dec 2010 17:36	Modified:	5 Dec 2010 19:50
Reporter:	Nikos Sarkas	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.45	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The correct use of the UPDATE statement is as follows:
UPDATE table SET field1=value1,field2=value2,... WHERE ...

However, the following statement syntax is also accepted, but gives wrong results:
UPDATE table SET field1=value1 AND field2=value2 AND... WHERE ...

Not all of the fields are properly updated.

How to repeat:
create table test(a int,b int, c int);
insert into test values(1,1,1);
select * from test; //tuple=(1,1,1)
update test set b=2 and c=2 where a=1;
select * from test; //tuple=(1,0,1)

Suggested fix:
Unfortunately I am not familiar with the MySQL source code to make a concrete suggestion or contribute a fix. But I guess that either that syntax should throw an exception or work as expected.

Provided a more informative synopsos

Obviously it is interpreted like

update test set b=(2 and c=2) where a=1;

.. and since (2 and c=2) is not valid it truncates to ZERO.

Peter
(not a MySQL person)

I think there should at least be a warning.

DROP TABLE IF EXISTS test;
CREATE TABLE test(a INT,b INT, c INT);
INSERT INTO test VALUES(1,1,1);
SELECT * FROM test;
UPDATE test SET b=2 AND c=2 WHERE a=1;
SHOW WARNINGS; -- empty result set. I'd expect "data truncated for column 'b'"

not a bug.  nothing is truncated either hence no warnings to be returned.
(c=2) == 0
(2 and 0) == 0

0 is a perfectly valid int.

.. and besides I was not able to find an SQL_MODE where an error is returned (I expected that with 'traditional' mode it would - but it doesn't)