Syntax error in optimizer hints should not invalidate the entire hint

[kennytm]

On MySQL 8, all optimizer hints parsed before a syntax error will be accepted:

$ echo 'SELECT SLEEP(1);' | mysql -u root
SLEEP(1)
0

$ echo 'SELECT /*+ max_execution_time(2) */ SLEEP(1);' | mysql -u root
SLEEP(1)
1

$ echo 'SELECT /*+ max_execution_time(2) (error) */ SLEEP(1);' | mysql -u root
SLEEP(1)
1

$ echo 'SELECT /*+ (error) max_execution_time(2) */ SLEEP(1);' | mysql -u root
SLEEP(1)
0

(returning 0 means hint is ignored, returning 1 means hint is used)

On TiDB Parser, however, once an error is encountered the entire list of hints are thrown out, different from MySQL's behavior.

(TiDB Parser version = 89ae120)

[kennytm]

Several issues are wrongly duped into here. So for record, a /*+ … */ appearing in the wrong place currently causes syntax error which should not happen:

SELECT 1 /*+ stuff */;

• No error no warning on MariaDB, since MariaDB simply does not understand optimizer hints
• No error no warning on MySQL, since MySQL only recognize optimizer hints after the SELECT/INSERT/REPLACE/UPDATE/DELETE keywords
  • yes this means select … for update /*+ (error) */; would generate a warning even if the hint is ignored.
• Error 1064 on TiDB.

[zz-jason]

Error 1064 on TiDB.

I can not reproduce this error.

TiDB 4.0.4:

TiDB(root@127.0.0.1:test) > SELECT 1 /*+ stuff */;
+----------------+
| 1 /*+ stuff */ |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

TiDB(root@127.0.0.1:test) > select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.4
Edition: Community
Git Commit Hash: 33b0f4c8855b8b7af51ba46b3b5075fc49eb3d32
Git Branch: HEAD
UTC Build Time: 2020-08-20 12:14:00
GoVersion: go1.13.12
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

TiDB on the master branch:

TiDB(root@127.0.0.1:test) > SELECT 1 /*+ stuff */;
+----------------+
| 1 /*+ stuff */ |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

TiDB(root@127.0.0.1:test) > select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-972-g04b7492c7
Edition: Community
Git Commit Hash: 04b7492c73b72d273984690678fa0aba182c4209
Git Branch: master
UTC Build Time: 2020-08-25 00:47:40
GoVersion: go1.13.12
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[kennytm]

this should be fixed in #711.

[kolbe]

@kennytm do you think SELECT id FROM tbl WHERE id = 0 FOR UPDATE /*+ xyz */; should be covered by this task, or is it unrelated? That executes without problem in MySQL but gives 1064 in TiDB.

[kennytm]

@kolbe it is a different bug (the error is the unexpected "hint token" /*+ …. */, the content xyz is irrelevant).

[kolbe]

OK, opened #987 for that issue.