MySQL Bugs: #107109: Optimizer hints doesn't work if client charset is the same with charset of syste

Bug #107109	Optimizer hints doesn't work if client charset is the same with charset of syste
Submitted:	24 Apr 2022 9:07	Modified:	6 Jun 2022 7:12
Reporter:	Brian Yue (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	8.0.27, 5.7.37, 8.0.32	OS:	Any (rhel-7.4)
Assigned to:		CPU Architecture:	Any (x86-64)
Tags:	charset, Contribution, Optimizer hints

Description:
Hello,
  Recently we find a case that with different charset of client, optimizer hints not always works well. When character_set_client is the same with character_set_system, optimizer hints don't woek.
  Please reference to `How to repeat`.

  Reported by GoldenDB team.

How to repeat:
[yxx_findb@localhost ~]$
[yxx_findb@localhost ~]$ mysql -uroot -p'db10$ZTE' -S bin/mysql1.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.27 Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show variables like '%char%';
+--------------------------+---------------------------------+
| Variable_name            | Value                           |
+--------------------------+---------------------------------+
| character_set_client     | utf8mb4                         |
| character_set_connection | utf8mb4                         |
| character_set_database   | utf8mb4                         |
| character_set_filesystem | binary                          |
| character_set_results    | utf8mb4                         |
| character_set_server     | utf8mb4                         |
| character_set_system     | utf8mb3                         |
| character_sets_dir       | /home/yxx_findb/share/charsets/ |
+--------------------------+---------------------------------+
8 rows in set (0.01 sec)

# `semijoin=off` is got from query result, it's correct.

mysql> set names utf8mb4; select /*+ SET_VAR(optimizer_switch='semijoin=off') */ @@optimizer_switch\G
Query OK, 0 rows affected (0.00 sec)

*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=off,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.00 sec)

# the hint doesn't work, `semijoin=on` is got
 
mysql> set names utf8mb3; select /*+ SET_VAR(optimizer_switch='semijoin=off') */ @@optimizer_switch\G
Query OK, 0 rows affected, 1 warning (0.00 sec)

*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set, 1 warning (0.00 sec)

mysql>

Suggested fix:
the parser handles optimizer hints incorrectly.

Hello Brian Yue,

Thank you for the report and feedback.

regards,
Umesh

this bug is fixed like this (basing on version MySQL8.0.25):

diff --git a/include/typelib.h b/include/typelib.h
index c979dc1..e26c15c 100644
--- a/include/typelib.h
+++ b/include/typelib.h
@@ -52,7 +52,11 @@ extern int find_type_or_exit(const char *x, TYPELIB *typelib,
 /** makes @c find_type() treat ',' as terminator */
 #define FIND_TYPE_COMMA_TERM (1 << 3)
 
-extern int find_type(const char *x, const TYPELIB *typelib, unsigned int flags);
+extern int find_type(const char *x, const TYPELIB *typelib, unsigned int flags
+                     , const char *end = nullptr
+);
 extern const char *get_type(TYPELIB *typelib, unsigned int nr);
 extern TYPELIB *copy_typelib(MEM_ROOT *root, TYPELIB *from);

diff --git a/mysys/typelib.cc b/mysys/typelib.cc
index ae0b8e0..c8fdc48 100644
--- a/mysys/typelib.cc
+++ b/mysys/typelib.cc
@@ -83,7 +83,11 @@ int find_type_or_exit(const char *x, TYPELIB *typelib, const char *option) {
     >0  Offset+1 in typelib for matched string
 */
 
-int find_type(const char *x, const TYPELIB *typelib, uint flags) {
+int find_type(const char *x, const TYPELIB *typelib, uint flags
+              , const char *end
+) {
   int find, pos;
   int findpos = 0; /* guarded by find */
   const char *i;
@@ -107,7 +111,11 @@ int find_type(const char *x, const TYPELIB *typelib, uint flags) {
       ;
     if (!*j) {
       while (*i == ' ') i++; /* skip_end_space */
-      if (!*i || ((flags & FIND_TYPE_COMMA_TERM) && is_field_separator(*i)))
+      if (!*i || ((flags & FIND_TYPE_COMMA_TERM) && is_field_separator(*i))
+          || i == end
+      )
         return pos + 1;
     }
     if ((!*i && (!(flags & FIND_TYPE_COMMA_TERM) || !is_field_separator(*i))) &&
@@ -252,7 +260,11 @@ static TYPELIB on_off_default_typelib = {
 static int parse_name(const TYPELIB *lib, const char **strpos,
                       const char *end) {
   const char *pos = *strpos;
-  int find = find_type(pos, lib, FIND_TYPE_COMMA_TERM);
+  int find = find_type(pos, lib, FIND_TYPE_COMMA_TERM
+                       , end
+  );
   for (; pos != end && *pos != '=' && *pos != ','; pos++)
     ;
   *strpos = pos;

basing on MySQL8025 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug-fix_107109.txt (text/plain), 2.07 KiB.

Thank you for your contribution.