Bug #61591 Update statement using AND in place of comma causes no warnings or errors

• Submitted: 21 Jun 2011 18:20
• Modified: 21 Jun 2011 18:36
• Reporter: Benjamin Bearman
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.1.46
• OS: Solaris (SunOS 5.10)
• CPU Architecture: Any
• Tags: and, comma, no error, no warning, UPDATE

[21 Jun 2011 18:20] Benjamin Bearman

Description:
When writing an update statement if you replace the comma values with and you get unexpected results without any warnings or errors. There may be some reason this does not throw a SQL exception but I am not sure what that would be. Seems that when parsing the SQL a JOIN or WHERE clause should be required if this will not update as expected. See example below.

How to repeat:
CREATE TABLE `test_table` (
  `pk_1` char(8) NOT NULL,
  `pk_2` char(8) NOT NULL,
  `data1` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`pk_1`,`pk_2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

insert into test_table3 values('L0000001','L0000002','test1');
Query OK, 1 row affected (0.00 sec)

select * from test_table3;
+----------+----------+-------+
| pk_1     | pk_2     | data1 |
+----------+----------+-------+
| L0000001 | L0000002 | test1 |
+----------+----------+-------+

update test_table3 set pk_1 = 'L0000003' AND pk_2 = 'L00000004' where pk_1='L0000001' and pk_2 = 'L0000002';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

select * from test_table3;
+------+----------+-------+
| pk_1 | pk_2     | data1 |
+------+----------+-------+
| 0    | L0000002 | test1 |
+------+----------+-------+

Suggested fix:
Throw a parsing error for SQL syntax or allow the alternate and to be treated as a comma in the set portion of the SQL statement.

[21 Jun 2011 18:21] Benjamin Bearman

Whoops, test_table3 needs to be test_table or vice versa.

[21 Jun 2011 18:36] Valeriy Kravchuk

This is not a bug. Your statement is interpreted as:

update test_table3 set pk_1 = ('L0000003' AND pk_2 = 'L00000004') 
where pk_1='L0000001' and pk_2 = 'L0000002';

then strings are converted to numbers for AND operation, both end up as 0 (because they start with letter L), and (0 AND 0) gives 0 as a result, then is converted back to string, '0'. All these steps are documented.

Hint: execute 

SHOW WARNINGS\G

next time when you'll see that statement generated warnings. This will help to find out what was wrong with the statement executed.