Bug #42796 Updating with "AND" keyword in field list, doesn't throw error from JDBC.
Submitted: 12 Feb 2009 15:18 Modified: 12 Feb 2009 17:01
Reporter: Dinesh M.S Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.0.22 OS:Any
Assigned to: CPU Architecture:Any
Tags: query, update statement

[12 Feb 2009 15:18] Dinesh M.S
Description:
While executing a query with "AND" Keyword in field list through JDBC connector error is not throwed.

Ex:

UPdate <tblname> SET field1='value1' AND field2='Value2' AND field3=value3 WHERE <Conditions>

While executing the query with above statement through JDBC and directly in Mysql server the row is not getting updated. But it does not through any error.

How to repeat:
Execute the below query

UPdate <tblname> SET field1='value1' AND field2='Value2' AND field3=value3 WHERE <Conditions>
[12 Feb 2009 15:24] Tonci Grgin
Hi Dinesh and thanks for your report.

I do not understand you but here's my guess:
  You have problem that when you issue UPDATE query with multiple fields to be updated from c/J you do not get any values updated, right?

If so, please add *complete* java test case including connect string, version of c/J, JDK version and all other data you can gather so I can try to reproduce.
[12 Feb 2009 15:41] Dinesh M.S
Even if you execute the query in mysql server directly you will find that the query will not update corresponding row but the result of the execution will be success.
[12 Feb 2009 16:19] Tonci Grgin
Dinesh, you are telling server to do logical *AND* with your values! This is not what you wanted. Please replace AND with "," as in my example:
mysql> UPDATE bug42796 SET IntFld1 = 11,  VCFld1 = "Nesto", DecFld1 = 20.20WHERE
 Id = 2;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM bug42796;
+----+---------+--------+---------+
| Id | IntFld1 | VCFld1 | DecFld1 |
+----+---------+--------+---------+
|  1 |       1 | Prvi   |  1.0010 |
|  2 |      11 | Nesto  | 20.2000 |
|  3 |       3 | Treci  |  3.0030 |
+----+---------+--------+---------+
3 rows in set (0.00 sec)
[12 Feb 2009 16:42] Dinesh M.S
Ya if I give field List separated with comma its working, there is no issue in it.

But if I give field List separated with "AND" then it is replacing 0 to a filed which already has NULL value in it.

If any updating field has any value it is not changed.

If it is throwing an error instead of Query Ok,x row(s) affected. It will be better.
[12 Feb 2009 16:48] Dinesh M.S
Please update me on above
[12 Feb 2009 17:01] Tonci Grgin
Dinesh, server can not throw error! It is doing *exactly* what you told it to, *AND's* your data to a value in first field in update list.