REPLACE statement with optimizer hint is resulting syntax error

[ramanich1]

Bug Report

1. Minimal reproduce step (Required)

drop table if exists t1;
CREATE TABLE t1(a INT, KEY(a));
INSERT /*+ SET_VAR(sql_mode='') */ INTO t1 VALUES(2);
REPLACE /*+ SET_VAR(sql_mode='') */ INTO t1 VALUES(2);

2. What did you expect to see? (Required)

mysql> INSERT /*+ SET_VAR(sql_mode='') */ INTO t1 VALUES(2);
Query OK, 1 row affected, 0 warning (0.00 sec)

mysql> REPLACE /*+ SET_VAR(sql_mode='') */ INTO t1 VALUES(2);
Query OK, 1 row affected, 0 warning (0.00 sec)

3. What did you see instead (Required)

mysql> INSERT /*+ SET_VAR(sql_mode='') */ INTO t1 VALUES(2);
Query OK, 1 row affected (0.02 sec)

mysql> REPLACE /*+ SET_VAR(sql_mode='') */ INTO t1 VALUES(2);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 35 near "/*+ SET_VAR(sql_mode='') */ INTO t1 VALUES(2)" 

4. What is your TiDB version? (Required)

| Release Version: v6.0.0
Edition: Community
Git Commit Hash: 36a9810441ca0e496cbd22064af274b3be771081
Git Branch: heads/refs/tags/v6.0.0
UTC Build Time: 2022-03-31 10:33:28
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[YangKeao]

Tested in the latest (2022 10-13) version of TiDB. It seems that this issue has been fixed 🤔 . If you faced this issue again, feel free to reopen it 🍻

[YangKeao]

Tested in the latest (2022 10-13) version of TiDB. It seems that this issue has been fixed 🤔 . If you faced this issue again, feel free to reopen it 🍻

[mcdeea]

The issue still reproduces on v6.5.4 and v7.5.0

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1138753740
Server version: 8.0.11-TiDB-v7.5.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 8.0 compatible

mysql> INSERT /*+ SET_VAR(sql_mode='') */ INTO t1 VALUES(2);
Query OK, 1 row affected (0.02 sec)

mysql> REPLACE /*+ SET_VAR(sql_mode='') */ INTO t1 VALUES(2);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 35 near "/*+ SET_VAR(sql_mode='') */ INTO t1 VALUES(2)"

mysql> REPLACE INTO t1 VALUES(2);
Query OK, 1 row affected (0.00 sec)

[YangKeao]

Thanks for this issue report. Maybe I made some mistake when I was reproducing this issue 🤦 .

Now I submit a PR to fix it #50007

[yibin87]

Need to cherry pick for v6.5, v7.1, v7.5 from customer request @YangKeao