MDEV-29104 Optimize queries to INFORMATION_SCHEMA.PARAMETERS/ROUTINESbb-10.11-MDEV-29104

For queries like
    "SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
      WHERE SPECIFIC_NAME='proc_name'"
and
    "SELECT * FROM INFORMATION_SCHEMA.ROUTINES
      WHERE ROUTINE_NAME='proc_name'"
there is a possibility to avoid loading a stored procedure code and
parsing it to retrieve parameters.
If the name of the procedure/function is specified explicitly then
it is possible to filter out routines that do not match at
an early stage.

5 files changed, 116 insertions, 43 deletions

diff --git a/mysql-test/main/information_schema_parameters.result b/mysql-test/main/information_schema_parameters.result
index 9b212da8e55..ace51258bd4 100644
--- a/mysql-test/main/information_schema_parameters.result
+++ b/mysql-test/main/information_schema_parameters.result
@@ -635,11 +635,11 @@ USE i_s_parameters_test;
 CREATE FUNCTION test_func5 (s CHAR(20)) RETURNS VARCHAR(30)
 RETURN CONCAT('XYZ, ' ,s);
 #
-# We cannot use the index due to CONCAT()
+# We cannot use the index due to missing condition on SPECIFIC_SCHEMA,
+# but we will use SPECIFIC_NAME for filtering records from mysql.proc
 FLUSH STATUS;
 SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
-WHERE CONCAT(SPECIFIC_SCHEMA) = 'i_s_parameters_test'
-  AND SPECIFIC_NAME = 'test_func5';
+WHERE SPECIFIC_NAME = 'test_func5';
 SPECIFIC_CATALOG	def
 SPECIFIC_SCHEMA	i_s_parameters_test
 SPECIFIC_NAME	test_func5
@@ -675,7 +675,19 @@ ROUTINE_TYPE	FUNCTION
 SHOW STATUS LIKE 'handler_read%next';
 Variable_name	Value
 Handler_read_next	54
-Handler_read_rnd_next	97
+Handler_read_rnd_next	3
+#
+# We cannot use the index due to CONCAT(), and filtering by SPECIFIC_NAME
+# does not work either since SPECIFIC_NAME = 'not_existing_proc'. See
+# the difference in counters in comparison to the previous test
+FLUSH STATUS;
+SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
+WHERE CONCAT(SPECIFIC_SCHEMA) = 'i_s_parameters_test'
+  AND SPECIFIC_NAME = 'not_existing_proc';
+SHOW STATUS LIKE 'handler_read%next';
+Variable_name	Value
+Handler_read_next	54
+Handler_read_rnd_next	1
 #
 # Now the index must be used
 FLUSH STATUS;
diff --git a/mysql-test/main/information_schema_parameters.test b/mysql-test/main/information_schema_parameters.test
index c53ecce8b2b..0c9d94e97f6 100644
--- a/mysql-test/main/information_schema_parameters.test
+++ b/mysql-test/main/information_schema_parameters.test
@@ -292,11 +292,21 @@ CREATE FUNCTION test_func5 (s CHAR(20)) RETURNS VARCHAR(30)
   RETURN CONCAT('XYZ, ' ,s);
 
 --echo #
---echo # We cannot use the index due to CONCAT()
+--echo # We cannot use the index due to missing condition on SPECIFIC_SCHEMA,
+--echo # but we will use SPECIFIC_NAME for filtering records from mysql.proc
+FLUSH STATUS;
+query_vertical SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
+  WHERE SPECIFIC_NAME = 'test_func5';
+SHOW STATUS LIKE 'handler_read%next';
+
+--echo #
+--echo # We cannot use the index due to CONCAT(), and filtering by SPECIFIC_NAME
+--echo # does not work either since SPECIFIC_NAME = 'not_existing_proc'. See
+--echo # the difference in counters in comparison to the previous test
 FLUSH STATUS;
 query_vertical SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
   WHERE CONCAT(SPECIFIC_SCHEMA) = 'i_s_parameters_test'
-  AND SPECIFIC_NAME = 'test_func5';
+  AND SPECIFIC_NAME = 'not_existing_proc';
 SHOW STATUS LIKE 'handler_read%next';
 
 --echo #
diff --git a/mysql-test/main/information_schema_routines.result b/mysql-test/main/information_schema_routines.result
index 48c9212506c..fa630227265 100644
--- a/mysql-test/main/information_schema_routines.result
+++ b/mysql-test/main/information_schema_routines.result
@@ -860,11 +860,11 @@ USE i_s_routines_test;
 CREATE FUNCTION test_func5 (s CHAR(20)) RETURNS VARCHAR(30)
 RETURN CONCAT('XYZ, ' ,s);
 #
-# We cannot use the index due to CONCAT()
+# We cannot use the index due to missing condition on SPECIFIC_SCHEMA,
+# but we will use ROUTINE_NAME for filtering records from mysql.proc
 FLUSH STATUS;
 SELECT * FROM INFORMATION_SCHEMA.ROUTINES
-WHERE CONCAT(ROUTINE_SCHEMA) = 'i_s_routines_test'
-  AND ROUTINE_NAME = 'test_func5';
+WHERE ROUTINE_NAME = 'test_func5';
 SPECIFIC_NAME	test_func5
 ROUTINE_CATALOG	def
 ROUTINE_SCHEMA	i_s_routines_test
@@ -899,7 +899,19 @@ DATABASE_COLLATION	latin1_swedish_ci
 SHOW STATUS LIKE 'handler_read%next';
 Variable_name	Value
 Handler_read_next	54
-Handler_read_rnd_next	55
+Handler_read_rnd_next	2
+#
+# We cannot use the index due to CONCAT(), and filtering by ROUTINE_NAME
+# does not work either since ROUTINE_NAME = 'not_existing_proc'. See
+# the difference in counters in comparison to the previous test
+FLUSH STATUS;
+SELECT * FROM INFORMATION_SCHEMA.ROUTINES
+WHERE CONCAT(ROUTINE_SCHEMA) = 'i_s_routines_test'
+  AND ROUTINE_NAME = 'not_existing_proc';
+SHOW STATUS LIKE 'handler_read%next';
+Variable_name	Value
+Handler_read_next	54
+Handler_read_rnd_next	1
 #
 # Now the index must be used
 FLUSH STATUS;
diff --git a/mysql-test/main/information_schema_routines.test b/mysql-test/main/information_schema_routines.test
index bf8e4dd7894..ea64c8b0d02 100644
--- a/mysql-test/main/information_schema_routines.test
+++ b/mysql-test/main/information_schema_routines.test
@@ -263,13 +263,24 @@ USE i_s_routines_test;
 CREATE FUNCTION test_func5 (s CHAR(20)) RETURNS VARCHAR(30)
   RETURN CONCAT('XYZ, ' ,s);
 
+
 --echo #
---echo # We cannot use the index due to CONCAT()
+--echo # We cannot use the index due to missing condition on SPECIFIC_SCHEMA,
+--echo # but we will use ROUTINE_NAME for filtering records from mysql.proc
 FLUSH STATUS;
 --replace_column 24 <created> 25 <modified>
 query_vertical SELECT * FROM INFORMATION_SCHEMA.ROUTINES
+  WHERE ROUTINE_NAME = 'test_func5';
+SHOW STATUS LIKE 'handler_read%next';
+
+--echo #
+--echo # We cannot use the index due to CONCAT(), and filtering by ROUTINE_NAME
+--echo # does not work either since ROUTINE_NAME = 'not_existing_proc'. See
+--echo # the difference in counters in comparison to the previous test
+FLUSH STATUS;
+query_vertical SELECT * FROM INFORMATION_SCHEMA.ROUTINES
   WHERE CONCAT(ROUTINE_SCHEMA) = 'i_s_routines_test'
-  AND ROUTINE_NAME = 'test_func5';
+  AND ROUTINE_NAME = 'not_existing_proc';
 SHOW STATUS LIKE 'handler_read%next';
 
 --echo #
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 0026d3ee399..4f1658d2e03 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -6452,40 +6452,63 @@ static inline void copy_field_as_string(Field *to_field, Field *from_field)
   to_field->store(tmp_str.ptr(), tmp_str.length(), system_charset_info);
 }
 
-/**
-  @brief Determine whether mysql.proc table scanning should continue or finish
 
-  @param[in]      proc_table            'mysql.proc' table
-  @param[in]      lookup                values from the WHERE clause which are
-                                        used for the index lookup
-  @param[in]      db                    mysql.proc.db field value of
-                                        the current record
-  @param[in]      name                  mysql.proc.name field value of
-                                        the current record
-
-  @return         Result
-    @retval       true                  scanning must continue
-    @retval       false                 scanning must finish
+/**
+  @brief When scanning mysql.proc check if we should skip this record or even
+         stop the scan
+
+  @param      name_field_charset    mysql.proc.name field charset info
+  @param      lookup                values from the WHERE clause which are
+                                    used for the index lookup
+  @param      db                    mysql.proc.db field value of
+                                    the current record
+  @param      name                  mysql.proc.name field value of
+                                    the current record
+
+  @return  Result
+    @retval  -1                    The record is match (do further processing)
+    @retval   0                    Skip this record, it doesn't match.
+    @retval   HA_ERR_END_OF_FILE   Stop scanning, no further matches possible
 */
-bool continue_proc_table_scanning(TABLE *proc_table,
-                                  LOOKUP_FIELD_VALUES *lookup,
-                                  const LEX_CSTRING &db,
-                                  const LEX_CSTRING &name)
+
+int check_proc_record(const CHARSET_INFO *name_field_charset,
+                      const LOOKUP_FIELD_VALUES *lookup,
+                      const LEX_CSTRING &db,
+                      const LEX_CSTRING &name)
 {
-  if (lookup->db_value.str)
+  if (lookup->db_value.str && cmp(lookup->db_value, db))
   {
-    if (cmp(lookup->db_value, db))
-      return false;
-    if (lookup->table_value.str)
+    /*
+      We have the name of target database. If we got a non-matching
+      record, this means we've finished reading matching mysql.proc records
+    */
+    return HA_ERR_END_OF_FILE;
+  }
+
+  if (lookup->table_value.str)
+  {
+    if ((my_ci_strnncoll(name_field_charset,
+                         (const uchar *) lookup->table_value.str,
+                         lookup->table_value.length,
+                         (const uchar *) name.str, name.length, 0)))
     {
-      auto cs= proc_table->field[MYSQL_PROC_FIELD_NAME]->charset();
-      if (my_ci_strnncoll(cs, (const uchar *) lookup->table_value.str,
-                          lookup->table_value.length, (const uchar *) name.str,
-                          name.length, 0))
-        return false;
+      /* Routine name doesn't match. */
+      if (lookup->db_value.str)
+      {
+        /*
+          We're using index lookup. A non-matching record means we've
+          finished reading matches.
+        */
+        return HA_ERR_END_OF_FILE;
+      }
+      else
+      {
+        /* The routine name doesn't match, but we're scanning all databases */
+        return 0; /* Continue scanning */
+      }
     }
   }
-  return true;
+  return -1; /* This is a match */
 }
 
 /**
@@ -6521,6 +6544,7 @@ int store_schema_params(THD *thd, TABLE *table, TABLE *proc_table,
   bool free_sp_head;
   bool error= 0;
   sql_mode_t sql_mode;
+  int rc;
   DBUG_ENTER("store_schema_params");
 
   bzero((char*) &tbl, sizeof(TABLE));
@@ -6530,8 +6554,10 @@ int store_schema_params(THD *thd, TABLE *table, TABLE *proc_table,
   proc_table->field[MYSQL_PROC_FIELD_DB]->val_str_nopad(thd->mem_root, &db);
   proc_table->field[MYSQL_PROC_FIELD_NAME]->val_str_nopad(thd->mem_root, &name);
 
-  if (!continue_proc_table_scanning(proc_table, lookup, db, name))
-    DBUG_RETURN(HA_ERR_END_OF_FILE);
+  CHARSET_INFO *name_cs= proc_table->field[MYSQL_PROC_FIELD_NAME]->charset();
+
+  if ((rc= check_proc_record(name_cs, lookup, db, name)) != -1)
+    DBUG_RETURN(rc); /* either HA_ERR_END_OF_FILE or 0 if name didn't match */
 
   proc_table->field[MYSQL_PROC_FIELD_DEFINER]->val_str_nopad(thd->mem_root, &definer);
   sql_mode= (sql_mode_t) proc_table->field[MYSQL_PROC_FIELD_SQL_MODE]->val_int();
@@ -6645,12 +6671,14 @@ int store_schema_proc(THD *thd, TABLE *table, TABLE *proc_table,
   const Sp_handler *sph;
   LEX_CSTRING db, name, definer, returns= empty_clex_str;
   const char *wild= thd->lex->wild ? thd->lex->wild->ptr() : NullS;
+  int rc;
 
   proc_table->field[MYSQL_PROC_FIELD_DB]->val_str_nopad(thd->mem_root, &db);
   proc_table->field[MYSQL_PROC_FIELD_NAME]->val_str_nopad(thd->mem_root, &name);
 
-  if (!continue_proc_table_scanning(proc_table, lookup, db, name))
-    return HA_ERR_END_OF_FILE;
+  CHARSET_INFO *name_cs= proc_table->field[MYSQL_PROC_FIELD_NAME]->charset();
+  if ((rc= check_proc_record(name_cs, lookup, db, name)) != -1)
+    return rc; /* either HA_ERR_END_OF_FILE or 0 if name didn't match */
 
   proc_table->field[MYSQL_PROC_FIELD_DEFINER]->val_str_nopad(thd->mem_root, &definer);
   sph= Sp_handler::handler_mysql_proc((enum_sp_type)