Bug #1050 SQL parser fails to detect syntax error
Submitted: 14 Aug 2003 23:53 Modified: 15 Aug 2003 2:38
Reporter: Miguel Solorzano Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.14 OS:Microsoft Windows (Windows)
Assigned to: CPU Architecture:Any

[14 Aug 2003 23:53] Miguel Solorzano
Description:
From: Ron Savage: win32.mysql.com

This SQL:

update service
set service_pabx_port = '-'
and service_pabx_port_key = '-'
where service_id = 3412

executed, but the syntax and the results are wrong. The first field 
was updated with '0' and the second field was not updated.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.14-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table service (
    -> service_id int,
    -> service_pabx_port varchar(255),
    -> service_pabx_port_key varchar(255));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into service values
    -> (3412,"","");
Query OK, 1 row affected (0.00 sec)

mysql> update service
    -> set service_pabx_port = '-'
    -> and service_pabx_port_key = '-'
    -> where service_id = 3412;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from service;
+------------+-------------------+-----------------------+
| service_id | service_pabx_port | service_pabx_port_key |
+------------+-------------------+-----------------------+
|       3412 | 0                 |                       |
+------------+-------------------+-----------------------+
1 row in set (0.00 sec)
[15 Aug 2003 2:38] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

The syntax and result are, in fact, correct, though not obvious. The query is parsed as

update service
set service_pabx_port = ('-' and service_pabx_port_key = '-')
where service_id = 3412