Bug #41091 UPDATE Syntax error is not captured and displayed

• Submitted: 28 Nov 2008 12:35
• Modified: 28 Nov 2008 14:30
• Reporter: Nidhin Asokan
• Status: Not a Bug
• Category: MySQL Server: Errors
• Severity: S3 (Non-critical)
• Version: 5.0.51a
• OS: Any
• CPU Architecture: Any

[28 Nov 2008 12:35] Nidhin Asokan

Description:
There is a problem in UPDATE statement syntax
We shall not get an error message 
if we use an query eventhough it doesnt update the mysql table

UPDATE data_set SET description='files size' 
AND name='fsize' where id='0'

correct syntax is 
UPDATE data_set SET description='files size' 
,name='fsize' where id='0'

How to repeat:
UPDATE data_set SET description='files size' 
AND name='fsize' where id='0'

Suggested fix:
need to add a syntax check on the above condition

[28 Nov 2008 14:30] Valeriy Kravchuk

Sorry, but this is not a bug. This statement

UPDATE data_set SET description='files size' 
AND name='fsize' where id='0';

is interpreted like

UPDATE data_set SET description=('files size' AND name='fsize') where id='0';

So, the result of AND with character arguments (they are implicitly casted to DOUBLE), 0 in non-strict SQL mode, is assigned to description column.  In strcit mode you will get erro message like this:

mysql> update tt set c1 = 'a' AND c2='b';
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'a'

All this is expected and documented behaviour.