MySQL Bugs: #19398: Incorrect update statement gives NO parse error

Bug #19398	Incorrect update statement gives NO parse error
Submitted:	27 Apr 2006 10:05	Modified:	27 Apr 2006 11:07
Reporter:	Andre Timmer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Solaris (Solaris)
Assigned to:		CPU Architecture:	Any

Description:
Following statement parses and executes:
   update aat5 set col2 = 'a2' and id = 1;

It contains a programmer typo 'and' should be 'where'

How to repeat:
drop table if exists aat5;

create table aat5 (id integer primary key, col2 integer) engine=innodb;

insert into aat5 (id, col2) values (1, 1);
insert into aat5 (id, col2) values (2, 2);
insert into aat5 (id, col2) values (3, 3);

update aat5 set col2 = 'a2' and id = 1;

Suggested fix:
Produce parse error when syntacs is not correct.

Sorry, but it is not a bug. "update aat5 set col2 = 'a2' and id = 1" is interpreted and executed as "update aat5 set col2 = ('a2' and id = 1)". Look:

mysql> create table aat5 (id integer primary key, col2 integer) engine=innodb;
Query OK, 0 rows affected (0.80 sec)

mysql> insert into aat5 (id, col2) values (1, 1);
Query OK, 1 row affected (0.21 sec)

mysql> insert into aat5 (id, col2) values (2, 2);
Query OK, 1 row affected (0.02 sec)

mysql> insert into aat5 (id, col2) values (3, 3);
Query OK, 1 row affected (0.02 sec)

mysql> select 'a2' and 1 = 1;
+----------------+
| 'a2' and 1 = 1 |
+----------------+
|              0 |
+----------------+
1 row in set, 1 warning (0.08 sec)

Example above shows that expressions like yours are really possible, although return 0 (false).

mysql> update aat5 set col2 = 'a2' and id = 1;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'a2'

In my case (5.0.18-nt, on Windows), I have a restricted SQL mode by default, that prevents using such an expression. But on Solrais the defaults are different:

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> update aat5 set col2 = 'a2' and id = 1;
Query OK, 3 rows affected, 3 warnings (0.03 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: 'a2'
*************************** 2. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: 'a2'
*************************** 3. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: 'a2'
3 rows in set (0.00 sec)

mysql> select * from aat5;
+----+------+
| id | col2 |
+----+------+
|  1 |    0 |
|  2 |    0 |
|  3 |    0 |
+----+------+
3 rows in set (0.01 sec)

So, it is not a parsing problem - the exprassion is sintactically valid. Semantix of it can be different, as I shown.

Also see bug #46641