Description:
When character_set_client with character_set_system.
SQL hint with optimizer_switch parse error.
set @@character_set_client=@@character_set_system;
Warnings:
Warning 1287 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead
explain select /*+ set_var(optimizer_switch='prefer_ordering_index=off') */ * from t;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
Warnings:
Warning 1231 Variable 'optimizer_switch' can't be set to the value of 'prefer_ordering_index=off'
Note 1003 /* select#1 */ select `test`.`t`.`c1` AS `c1` from `test`.`t`
How to repeat:
err_switch_hint.test
```
CREATE TABLE t(c1 int);
ANALYZE TABLE t;
explain select /*+ set_var(optimizer_switch='prefer_ordering_index=off') */ * from t;
set @@character_set_client=@@character_set_system;
explain select /*+ set_var(optimizer_switch='prefer_ordering_index=off') */ * from t;
DROP TABLE t;
```
result
```
CREATE TABLE t(c1 int);
ANALYZE TABLE t;
Table Op Msg_type Msg_text
test.t analyze status OK
explain select /*+ set_var(optimizer_switch='prefer_ordering_index=off') */ * from t;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select /*+ SET_VAR(optimizer_switch='prefer_ordering_index=off') */ `test`.`t`.`c1` AS `c1` from `test`.`t`
set @@character_set_client=@@character_set_system;
Warnings:
Warning 1287 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead
explain select /*+ set_var(optimizer_switch='prefer_ordering_index=off') */ * from t;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
Warnings:
Warning 1231 Variable 'optimizer_switch' can't be set to the value of 'prefer_ordering_index=off'
Note 1003 /* select#1 */ select `test`.`t`.`c1` AS `c1` from `test`.`t`
DROP TABLE t;
```
Suggested fix:
This is because when thd->charset_is_system_charset, parser will not convert string.
```
201 if (thd->charset_is_system_charset && double_separators == 0)
202 return ret;
203
204 LEX_STRING s;
205 if (!thd->charset_is_system_charset) {
206 if (thd->convert_string(&s, system_charset_info, yytext, yyleng,
207 thd->charset()))
208 return HINT_ERROR; // OOM etc
```
When find_set_from_flags->find_type, we see str below.
```
prefer_ordering_index=off') */ * from t
```
When !thd->charset_is_system_charset, after convert_string, we will see str below.
```
prefer_ordering_index=off
```
To fix this:
find_type should check the end of str.
Description: When character_set_client with character_set_system. SQL hint with optimizer_switch parse error. set @@character_set_client=@@character_set_system; Warnings: Warning 1287 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead explain select /*+ set_var(optimizer_switch='prefer_ordering_index=off') */ * from t; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL Warnings: Warning 1231 Variable 'optimizer_switch' can't be set to the value of 'prefer_ordering_index=off' Note 1003 /* select#1 */ select `test`.`t`.`c1` AS `c1` from `test`.`t` How to repeat: err_switch_hint.test ``` CREATE TABLE t(c1 int); ANALYZE TABLE t; explain select /*+ set_var(optimizer_switch='prefer_ordering_index=off') */ * from t; set @@character_set_client=@@character_set_system; explain select /*+ set_var(optimizer_switch='prefer_ordering_index=off') */ * from t; DROP TABLE t; ``` result ``` CREATE TABLE t(c1 int); ANALYZE TABLE t; Table Op Msg_type Msg_text test.t analyze status OK explain select /*+ set_var(optimizer_switch='prefer_ordering_index=off') */ * from t; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL Warnings: Note 1003 /* select#1 */ select /*+ SET_VAR(optimizer_switch='prefer_ordering_index=off') */ `test`.`t`.`c1` AS `c1` from `test`.`t` set @@character_set_client=@@character_set_system; Warnings: Warning 1287 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead explain select /*+ set_var(optimizer_switch='prefer_ordering_index=off') */ * from t; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL Warnings: Warning 1231 Variable 'optimizer_switch' can't be set to the value of 'prefer_ordering_index=off' Note 1003 /* select#1 */ select `test`.`t`.`c1` AS `c1` from `test`.`t` DROP TABLE t; ``` Suggested fix: This is because when thd->charset_is_system_charset, parser will not convert string. ``` 201 if (thd->charset_is_system_charset && double_separators == 0) 202 return ret; 203 204 LEX_STRING s; 205 if (!thd->charset_is_system_charset) { 206 if (thd->convert_string(&s, system_charset_info, yytext, yyleng, 207 thd->charset())) 208 return HINT_ERROR; // OOM etc ``` When find_set_from_flags->find_type, we see str below. ``` prefer_ordering_index=off') */ * from t ``` When !thd->charset_is_system_charset, after convert_string, we will see str below. ``` prefer_ordering_index=off ``` To fix this: find_type should check the end of str.