pingcap
Syntax error in optimizer hints should not invalidate the entire hint
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)
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.
- yes this means
- Error 1064 on TiDB.
Error 1064 on TiDB.
I can not reproduce this error.
TiDB 4.0.4:
TiDB([email protected]:test) > SELECT 1 /*+ stuff */;
+----------------+
| 1 /*+ stuff */ |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
TiDB([email protected]: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([email protected]:test) > SELECT 1 /*+ stuff */;
+----------------+
| 1 /*+ stuff */ |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
TiDB([email protected]: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 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.
@kolbe it is a different bug (the error is the unexpected "hint token" /*+ …. */, the content xyz is irrelevant).