MySQL Bugs: #16375: Query fails to produce parse error (and runs) on incorrect syntax

Bug #16375	Query fails to produce parse error (and runs) on incorrect syntax
Submitted:	11 Jan 2006 14:27	Modified:	11 Jan 2006 15:00
Reporter:	Charlie Farrow	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
The query Parser fails to produce an error on incorrect update syntax.

It has been tested on 5.0.18 and 5.0.15 and it effects both. 

When Executing an update query in an incorrect format the query is executed and no parse error or syntax error is reported. The problem is able to be worked around (by using a correct query), but i think it needs fixing as it could potentially cause people issues.

See how to repeat for details.

How to repeat:
Create a table as follows:

Table test

A int auto_increment primary key,
B Int,
C int,

Put some data in the columns, the problem will be more clearly illistrated if you sill the columns with numbers 20-99.

Now try to execute the following:

update test set B=11 and C=1 where A=1;

the correct syntax should of course be a , and not an and. However, this query executes ok and produces variable results. The one above should execute and set B=1 (not 11) and C does not usually change. If C is bigger, often B will be set to 0.

Either way it is VERY buggy and it is quite easy to make a silly mistake like this. With no error, it took a while to figure what was going on!

Suggested fix:
Correct query parser to produce an error when this syntax is executed as it is incorrect.

Thank you for a problem report. Sorry, but it is not a bug, although may cause misunderstanding and problems. Look:

mysql> create table test (a int auto_increment primary key, b int, c int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test (b,c) values(20, 20), (21, 21), (22, 22);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |   20 |   20 |
| 2 |   21 |   21 |
| 3 |   22 |   22 |
+---+------+------+
3 rows in set (0.01 sec)

mysql> update test set b=11 and c=1 where a=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    0 |   20 |
| 2 |   21 |   21 |
| 3 |   22 |   22 |
+---+------+------+
3 rows in set (0.00 sec)

mysql> update test set b=11 and c=20 where a=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |   20 |
| 2 |   21 |   21 |
| 3 |   22 |   22 |
+---+------+------+
3 rows in set (0.00 sec)

mysql> select 1=1 and 1=0;
+-------------+
| 1=1 and 1=0 |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> select 1=1 and 2=2;
+-------------+
| 1=1 and 2=2 |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

I hope, you got it? Everything after "set b=" and before "where" (or ",") is the expression to be evaluated and set as a value of b:

update test set b=11 and c=1 where a=1;

is the same as:

update test set b=(11 and c=1) where a=1;

It is impossible for parser to decide is it what you wanted or not. So, the expression is just evaluated.