MySQL Bugs: #26405: incorrect update syntax using "and" did not throw a warning

Bug #26405	incorrect update syntax using "and" did not throw a warning
Submitted:	15 Feb 2007 14:26	Modified:	15 Feb 2007 15:40
Reporter:	Paul Orrock	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.26	OS:	Linux (Debian)
Assigned to:		CPU Architecture:	Any
Tags:	and, syntax, UPDATE, warning

Description:

When incorrectly using "and" instead of "," in the set part of an update command no warnings are thrown. 

How to repeat:
mysql> create table blah ( x int(11),y int(11),z int(11) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into blah values (1,2,3);
Query OK, 1 row affected (0.00 sec)

mysql> update blah set x = 2 and y = 2 where z = 3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Suggested fix:
Throw a warning at the invalid syntax, or allow use of "and" ;-)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Thank you for the report, but '2 and y = 2' is correct boolean expression.

I'm sorry but I don't understand what you mean by :

'2 and y = 2' is correct boolean expression

You get the same result even if you quote it and run

update blah set x = '2' and y = '2' where z = 3;

Also if it is interpreting it as a boolean expression, then it should either  throw a warning becuase I am trying to update an int column with a boolean or it should apply the resulting value of the boolean (1 since it is 'true') to the column.

It does neither, it leaves the value unchanged and gives no warning.

regards,

Paul

Please read about Operator Precedence at http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html

I have read the operator precedence link you sent and I cannot see how this affects what I have said. It is not setting the value to be anything or throwing a warning, even if it were interpretting "and" to be a concatentation which I think you are suggesting. 

Can you please explain some more or at least tell me why the operator precedence is relevant when "and" is not valid in that update syntax. If it is valid can you give me an example of when it would be valid and what you would expect to get as the result ?

Thanks 

Paul

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

But I think I can place example here:

mysql> create table blah ( x int(11),y int(11),z int(11) );
Query OK, 0 rows affected (0.14 sec)

mysql> insert into blah values (1,2,3);
Query OK, 1 row affected (0.08 sec)

mysql> insert into blah values (2,4,6);
Query OK, 1 row affected (0.00 sec)

mysql> update blah set x = z = 3 and y = 2;
Query OK, 1 row affected (0.16 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql> select * from blah;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|    1 |    2 |    3 | 
|    0 |    4 |    6 | 
+------+------+------+
2 rows in set (0.08 sec)

mysql>

Sveta's responses do not address the open question ... how to construct an UPDATE query that updates more than one field.  The answer to the question should have been a reference to a URL that includes a multi-field UPDATE example, or simply stating that

UPDATE blah SET x=7, y=8 WHERE z=6;

would perform a multi-field update for all records satisfying the WHERE clause (in her example, just the second record).

- Mike <http://pnmx.com/>

Bug http://bugs.mysql.com/bug.php?id=49257 marked as duplicate of this one.