Bug #6583 Update not returning an error on incorrect syntax
Submitted: 11 Nov 2004 22:01 Modified: 12 Nov 2004 10:20
Reporter: Kenny Mann Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:mysql Ver 12.22 Distrib 4.0.20, for pc- OS:Linux (Gentoo Linux)
Assigned to: Sergei Golubchik CPU Architecture:Any

[11 Nov 2004 22:01] Kenny Mann 
Description:
Running this command:
UPDATE software SET oid=1 AND notes="some notes"  WHERE id=1

will return some results, however will not modify the data correctly. It modified my oid to 1 and left the notes to the previous value -- one would have assumed it would have thrown an error or said not modify anything at all.
Strange, considering I didn't get an error and the correct syntax is:

UPDATE software SET oid=1, notes="some notes"  WHERE id=1

How to repeat:
I can assume that it's a generic issue and running my commands again will cause this problem to happen again, however if it's table config...

The oid was an integer and notes was longtext. Everything else in my system is Gentoo config / defaults.

Suggested fix:
Return an error complaining saying that something is wrong between x and y -- OR -- don't do anything at all and say nothing was modified. I would prefer the former...
[11 Nov 2004 22:53] MySQL Verification Team 
I wasn't to repeat that issue just with your description of the
table. Could you please provide us a more complete test case.

Thanks in advance.
[11 Nov 2004 22:55] MySQL Verification Team 
Sorry I forgot to paste:

mysql> UPDATE software SET oid=1, notes="another notes"  WHERE id=1;
ERROR 1054: Unknown column 'id' in 'where clause'
[11 Nov 2004 23:00] Kenny Mann 
More potentially useful information {

More notes:
I'm using phpmyadmin (latest version) and ran the SQL query inside of it. The query was typed by hand.

software table:

id  	int(10) 	UNSIGNED
title  	varchar(255)
vendor  	varchar(255)
oid  	int(10) 	UNSIGNED
serial_number  	varchar(255)
code_key  	varchar(255)
media_loc  	varchar(255)
notes  	longtext
partnum_sku  	varchar(255)
purchase_date  	date
purchase_cost  	varchar(255)
purchase_person  	varchar(255)
purchase_method  	varchar(50) 
placeofpurchase  	varchar(255)
approval_person  	varchar(255)
entrydate  	datetime

Sample data:
   <software>
        <id>139</id>
        <title>Windows Server CAL 2000</title>
        <vendor>Microsoft</vendor>
        <oid>3</oid>
        <serial_number></serial_number>
        <code_key></code_key>
        <media_loc>N/A</media_loc>
        <notes>Server: dataserver</notes>
        <license>CAL</license>
        <partnum_sku></partnum_sku>
        <upc></upc>
        <purchase_date>2004-03-05</purchase_date>
        <purchase_cost>0.00</purchase_cost>
        <purchase_person></purchase_person>
        <purchase_method></purchase_method>
        <placeofpurchase></placeofpurchase>
        <approval_person></approval_person>
        <entrydate>2004-10-26 13:25:37</entrydate>
    </software>
}
[11 Nov 2004 23:01] Kenny Mann 
Sorry, that looks horrid.

   <software>
        <id>139</id>
        <title>Windows Server CAL 2003</title>
        <vendor>Microsoft</vendor>
        <oid>3</oid>
        <serial_number></serial_number>
        <code_key></code_key>
        <media_loc>N/A</media_loc>
        <notes>Server: dataserver</notes>
        <license>CAL</license>
        <partnum_sku></partnum_sku>
        <upc></upc>
        <purchase_date>2004-03-05</purchase_date>
        <purchase_cost>0.00</purchase_cost>
        <purchase_person></purchase_person>
        <purchase_method></purchase_method>
        <placeofpurchase></placeofpurchase>
        <approval_person></approval_person>
        <entrydate>2004-10-26 13:25:37</entrydate>
    </software>
[12 Nov 2004 10:20] Sergei Golubchik 
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is not a bug.

UPDATE software SET oid=1 AND notes="some notes"  WHERE id=1

is parsed as

UPDATE software SET oid=(1 AND notes="some notes")  WHERE id=1

where (1 AND notes="some notes") is a valid expression.
Thus no error.
[18 Jun 2007 15:27] MySQL Verification Team 
Bug: http://bugs.mysql.com/bug.php?id=29114 was marked as duplicate
of this one.
[23 Mar 2011 20:37] MySQL Verification Team 
Bug http://bugs.mysql.com/bug.php?id=60607 marked as duplicate of this one.
[4 Dec 2015 13:43] MySQL Verification Team 
http://bugs.mysql.com/bug.php?id=79525 marked as duplicate of this one.
[27 Feb 2016 13:29] MySQL Verification Team 
bugs.mysql.com/bug.php?id=80541 marked as duplicateone. of this
[14 Sep 2016 13:45] MySQL Verification Team 
http://bugs.mysql.com/bug.php?id=82990 marked as duplicate of this one.