MySQL Bugs: #56935: insert ... on duplicate key update .. update content may not contain ";"

Bug #56935	insert ... on duplicate key update .. update content may not contain ";"
Submitted:	22 Sep 2010 14:50	Modified:	23 Sep 2010 5:24
Reporter:	robert hofer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.021	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	insert, ON DUPLICATE KEY

Description:
the update clause in the insert on duplicate key-statement may not contain semicolons.

How to repeat:
drop table if exists `test` ;
CREATE TABLE `test` (`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,`content` VARCHAR( 150 ) NOT NULL ,PRIMARY KEY ( `id` ) ) ENGINE = MYISAM ;
INSERT INTO `test` (`id`, `content`) VALUES (NULL, 'a;b;c');
UPDATE `test` SET `content` = 'x;y;z' WHERE `test`.`id` =1 ;
## insert `test` SET `id`="1" ,`content` = "x;y;z" WHERE `test`.`id` =1 on duplicate key update `content`="a;b;c";##<-- error as long content contains ";"

There is no WHERE clause in INSERT statement. Read http://dev.mysql.com/doc/refman/5.0/en/insert.html.

So, you do get error about the wrong syntax:

macbook-pro:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.92-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists `test` ;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE `test` (`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,`content` VARCHAR( 150 ) NOT
    -> NULL ,PRIMARY KEY ( `id` ) ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `test` (`id`, `content`) VALUES (NULL, 'a;b;c');
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE `test` SET `content` = 'x;y;z' WHERE `test`.`id` =1 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>  insert `test` SET `id`="1" ,`content` = "x;y;z" WHERE `test`.`id` =1 on duplicate key
    -> update `content`="a;b;c";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `test`.`id` =1 on duplicate key
update `content`="a;b;c"' at line 1

But correct syntax works:

mysql> insert `test` SET `id`="1" ,`content` = "x;y;z" on duplicate key update `content`="a;b;c";
Query OK, 2 rows affected (0.00 sec)

Well this fails too:

INSERT `test` SET `id`="1" ,`content` = "x;y;z" WHERE `test`.`id` = 1 ON DUPLICATE KEY UPDATE `content`="a,b,c";
as well as 
INSERT `test` SET `id`="1" ,`content` = "x;y;z" WHERE `test`.`id` = 1 ON DUPLICATE KEY UPDATE `content`="abc";

It is not semicolons that matter! But after reading
http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html
.. I cannot identify the problem right away.

Peter
(not a MySQL person)

i am sorry; in my example i made a syntax error: of course theres is no where clause in  insert ... on duplicate key update  statement. But originally, the issue is present: 
i found the reason: the statement
insert test set id=1,content="pi;pa;po" on duplicate key update content="pi;pa;po"
works only, if the it is followed by any character (blank, ; #... whatever)