MDEV-16708: Unsupported commands for prepared statements

Withing this task the following changes were made:
- Added sending of metadata info in prepare phase for the admin related
  command (check table, checksum table, repair, optimize, analyze).

- Refactored implmentation of HELP command to support its execution in
  PS mode

- Added support for execution of LOAD INTO and XA- related statements
  in PS mode

- Modified mysqltest.cc to run statements in PS mode unconditionally
  in case the option --ps-protocol is set. Formerly, only those statements
  were executed using PS protocol that matched the hard-coded regular expression

- Fixed the following issues:
    The statement
      explain select (select 2)
    executed in regular and PS mode produces different results:

    MariaDB [test]> prepare stmt from "explain select (select 2)";
    Query OK, 0 rows affected (0,000 sec)
    Statement prepared
    MariaDB [test]> execute stmt;
    +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
    | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
    +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
    |    1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
    |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
    +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
    2 rows in set (0,000 sec)
    MariaDB [test]> explain select (select 2);
    +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
    | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
    +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
    |    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
    +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
    1 row in set, 1 warning (0,000 sec)

    In case the statement
      CREATE TABLE t1 SELECT * FROM (SELECT 1 AS a, (SELECT a+0)) a
    is run in PS mode it fails with the error
      ERROR 1054 (42S22): Unknown column 'a' in 'field list'.

- Uniform handling of read-only variables both in case the SET var=val
  statement is executed as regular or prepared statememt.

- Fixed assertion firing on handling LOAD DATA statement for temporary tables

- Relaxed assert condition in the function lex_end_stage1() by adding
  the commands SQLCOM_ALTER_EVENT, SQLCOM_CREATE_PACKAGE,
  SQLCOM_CREATE_PACKAGE_BODY to a list of supported command

- Removed raising of the error ER_UNSUPPORTED_PS in the function
  check_prepared_statement() for the ALTER VIEW command

- Added initialization of the data memember st_select_lex_unit::last_procedure
  (assign NULL value) in the constructor

  Without this change the test case main.ctype_utf8 fails with the following
  report in case it is run with the optoin --ps-protocol.
    mysqltest: At line 2278: query 'VALUES (_latin1 0xDF) UNION VALUES(_utf8'a' COLLATE utf8_bin)' failed: 2013: Lost connection

- The following bug reports were fixed:
      MDEV-24460: Multiple rows result set returned from stored
                  routine over prepared statement binary protocol is
                  handled incorrectly
      CONC-519: mariadb client library doesn't handle server_status and
                warnign_count fields received in the packet
                COM_STMT_EXECUTE_RESPONSE.

  Reasons for these bug reports have the same nature and caused by
  missing loop iteration on results sent by server in response to
  COM_STMT_EXECUTE packet.

  Enclosing of statements for processing of COM_STMT_EXECUTE response
  in the construct like
    do
    {
      ...
    } while (!mysql_stmt_next_result());
  fixes the above mentioned bug reports.

1 files changed, 96 insertions, 103 deletions

diff --git a/client/mysqltest.cc b/client/mysqltest.cc
index 8d3046faae0..fb36c4b075d 100644
--- a/client/mysqltest.cc
+++ b/client/mysqltest.cc
@@ -258,7 +258,6 @@ static const char *opt_suite_dir, *opt_overlay_dir;
 static size_t suite_dir_len, overlay_dir_len;
 
 /* Precompiled re's */
-static regex_t ps_re;     /* the query can be run using PS protocol */
 static regex_t sp_re;     /* the query can be run as a SP */
 static regex_t view_re;   /* the query can be run as a view*/
 
@@ -8324,116 +8323,119 @@ void run_query_stmt(struct st_connection *cn, struct st_command *command,
     goto end;
   }
 
-  /*
-    When running in cursor_protocol get the warnings from execute here
-    and keep them in a separate string for later.
-  */
-  if (cursor_protocol_enabled && !disable_warnings)
-    append_warnings(&ds_execute_warnings, mysql);
-
-  /*
-    We instruct that we want to update the "max_length" field in
-    mysql_stmt_store_result(), this is our only way to know how much
-    buffer to allocate for result data
-  */
+  do
   {
-    my_bool one= 1;
-    if (mysql_stmt_attr_set(stmt, STMT_ATTR_UPDATE_MAX_LENGTH, (void*) &one))
-      die("mysql_stmt_attr_set(STMT_ATTR_UPDATE_MAX_LENGTH) failed': %d %s",
-          mysql_stmt_errno(stmt), mysql_stmt_error(stmt));
-  }
+    /*
+      When running in cursor_protocol get the warnings from execute here
+      and keep them in a separate string for later.
+    */
+    if (cursor_protocol_enabled && !disable_warnings)
+      append_warnings(&ds_execute_warnings, mysql);
 
-  /*
-    If we got here the statement succeeded and was expected to do so,
-    get data. Note that this can still give errors found during execution!
-    Store the result of the query if if will return any fields
-  */
-  if (mysql_stmt_field_count(stmt) && mysql_stmt_store_result(stmt))
-  {
-    handle_error(command, mysql_stmt_errno(stmt),
-                 mysql_stmt_error(stmt), mysql_stmt_sqlstate(stmt), ds);
-    goto end;
-  }
+    /*
+      We instruct that we want to update the "max_length" field in
+      mysql_stmt_store_result(), this is our only way to know how much
+      buffer to allocate for result data
+    */
+    {
+      my_bool one= 1;
+      if (mysql_stmt_attr_set(stmt, STMT_ATTR_UPDATE_MAX_LENGTH, (void*) &one))
+        die("mysql_stmt_attr_set(STMT_ATTR_UPDATE_MAX_LENGTH) failed': %d %s",
+            mysql_stmt_errno(stmt), mysql_stmt_error(stmt));
+    }
 
-  /* If we got here the statement was both executed and read successfully */
-  handle_no_error(command);
-  if (!disable_result_log)
-  {
     /*
-      Not all statements creates a result set. If there is one we can
-      now create another normal result set that contains the meta
-      data. This set can be handled almost like any other non prepared
-      statement result set.
+      If we got here the statement succeeded and was expected to do so,
+      get data. Note that this can still give errors found during execution!
+      Store the result of the query if if will return any fields
     */
-    if ((res= mysql_stmt_result_metadata(stmt)) != NULL)
+    if (mysql_stmt_field_count(stmt) && mysql_stmt_store_result(stmt))
     {
-      /* Take the column count from meta info */
-      MYSQL_FIELD *fields= mysql_fetch_fields(res);
-      uint num_fields= mysql_num_fields(res);
+      handle_error(command, mysql_stmt_errno(stmt),
+                   mysql_stmt_error(stmt), mysql_stmt_sqlstate(stmt), ds);
+      goto end;
+    }
 
-      if (display_metadata)
-        append_metadata(ds, fields, num_fields);
+    /* If we got here the statement was both executed and read successfully */
+    handle_no_error(command);
+    if (!disable_result_log)
+    {
+      /*
+        Not all statements creates a result set. If there is one we can
+        now create another normal result set that contains the meta
+        data. This set can be handled almost like any other non prepared
+        statement result set.
+      */
+      if ((res= mysql_stmt_result_metadata(stmt)) != NULL)
+      {
+        /* Take the column count from meta info */
+        MYSQL_FIELD *fields= mysql_fetch_fields(res);
+        uint num_fields= mysql_num_fields(res);
 
-      if (!display_result_vertically)
-        append_table_headings(ds, fields, num_fields);
+        if (display_metadata)
+          append_metadata(ds, fields, num_fields);
 
-      append_stmt_result(ds, stmt, fields, num_fields);
+        if (!display_result_vertically)
+          append_table_headings(ds, fields, num_fields);
 
-      mysql_free_result(res);     /* Free normal result set with meta data */
+        append_stmt_result(ds, stmt, fields, num_fields);
 
-      /*
-        Normally, if there is a result set, we do not show warnings from the
-        prepare phase. This is because some warnings are generated both during
-        prepare and execute; this would generate different warning output
-        between normal and ps-protocol test runs.
+        mysql_free_result(res);     /* Free normal result set with meta data */
 
-        The --enable_prepare_warnings command can be used to change this so
-        that warnings from both the prepare and execute phase are shown.
-      */
-      if (!disable_warnings && !prepare_warnings_enabled)
-        dynstr_set(&ds_prepare_warnings, NULL);
-    }
-    else
-    {
-      /*
-	This is a query without resultset
-      */
-    }
+        /*
+          Normally, if there is a result set, we do not show warnings from the
+          prepare phase. This is because some warnings are generated both during
+          prepare and execute; this would generate different warning output
+          between normal and ps-protocol test runs.
 
-    /*
-      Fetch info before fetching warnings, since it will be reset
-      otherwise.
-    */
-    if (!disable_info)
-      append_info(ds, mysql_stmt_affected_rows(stmt), mysql_info(mysql));
+          The --enable_prepare_warnings command can be used to change this so
+          that warnings from both the prepare and execute phase are shown.
+        */
+        if (!disable_warnings && !prepare_warnings_enabled)
+          dynstr_set(&ds_prepare_warnings, NULL);
+      }
+      else
+      {
+        /*
+          This is a query without resultset
+        */
+      }
 
-    if (display_session_track_info)
-      append_session_track_info(ds, mysql);
+      /*
+        Fetch info before fetching warnings, since it will be reset
+        otherwise.
+      */
+      if (!disable_info)
+        append_info(ds, mysql_stmt_affected_rows(stmt), mysql_info(mysql));
 
+      if (display_session_track_info)
+        append_session_track_info(ds, mysql);
 
-    if (!disable_warnings)
-    {
-      /* Get the warnings from execute */
 
-      /* Append warnings to ds - if there are any */
-      if (append_warnings(&ds_execute_warnings, mysql) ||
-          ds_execute_warnings.length ||
-          ds_prepare_warnings.length ||
-          ds_warnings->length)
+      if (!disable_warnings)
       {
-        dynstr_append_mem(ds, "Warnings:\n", 10);
-        if (ds_warnings->length)
-          dynstr_append_mem(ds, ds_warnings->str,
-                            ds_warnings->length);
-        if (ds_prepare_warnings.length)
-          dynstr_append_mem(ds, ds_prepare_warnings.str,
-                            ds_prepare_warnings.length);
-        if (ds_execute_warnings.length)
-          dynstr_append_mem(ds, ds_execute_warnings.str,
-                            ds_execute_warnings.length);
+        /* Get the warnings from execute */
+
+        /* Append warnings to ds - if there are any */
+        if (append_warnings(&ds_execute_warnings, mysql) ||
+            ds_execute_warnings.length ||
+            ds_prepare_warnings.length ||
+            ds_warnings->length)
+        {
+          dynstr_append_mem(ds, "Warnings:\n", 10);
+          if (ds_warnings->length)
+            dynstr_append_mem(ds, ds_warnings->str,
+                              ds_warnings->length);
+          if (ds_prepare_warnings.length)
+            dynstr_append_mem(ds, ds_prepare_warnings.str,
+                              ds_prepare_warnings.length);
+          if (ds_execute_warnings.length)
+            dynstr_append_mem(ds, ds_execute_warnings.str,
+                              ds_execute_warnings.length);
+        }
       }
     }
-  }
+  } while ( !mysql_stmt_next_result(stmt));
 
 end:
   if (!disable_warnings)
@@ -8719,8 +8721,7 @@ void run_query(struct st_connection *cn, struct st_command *command, int flags)
     statement already and we can't do it twice
   */
   if (ps_protocol_enabled &&
-      complete_query &&
-      match_re(&ps_re, query))
+      complete_query)
     run_query_stmt(cn, command, query, query_len, ds, &ds_warnings);
   else
     run_query_normal(cn, command, flags, query, query_len,
@@ -8795,9 +8796,9 @@ void init_re(void)
 {
   /*
     Filter for queries that can be run using the
-    MySQL Prepared Statements C API
+    Stored procedures
   */
-  const char *ps_re_str =
+  const char *sp_re_str =
     "^("
     "[[:space:]]*ALTER[[:space:]]+SEQUENCE[[:space:]]|"
     "[[:space:]]*ALTER[[:space:]]+TABLE[[:space:]]|"
@@ -8851,19 +8852,12 @@ void init_re(void)
     ")";
 
   /*
-    Filter for queries that can be run using the
-    Stored procedures
-  */
-  const char *sp_re_str =ps_re_str;
-
-  /*
     Filter for queries that can be run as views
   */
   const char *view_re_str =
     "^("
     "[[:space:]]*SELECT[[:space:]])";
 
-  init_re_comp(&ps_re, ps_re_str);
   init_re_comp(&sp_re, sp_re_str);
   init_re_comp(&view_re, view_re_str);
 }
@@ -8899,7 +8893,6 @@ int match_re(regex_t *re, char *str)
 
 void free_re(void)
 {
-  regfree(&ps_re);
   regfree(&sp_re);
   regfree(&view_re);
 }