MySQL Bugs: #42796: Updating with "AND" keyword in field list, doesn't throw error from JDBC.

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

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>

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.

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.

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)

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.

Please update me on above

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.