MDEV-16708: Unsupported commands for prepared statementsbb-10.6-MDEV-16708-3

Within 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 implementation 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 to enable any statements except PREPARE FROM,
    EXECUTE, EXECUTE IMMEDIATE, DEALLOCATE to be run in PS mode.

  - 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 statement.

  - 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 initialisation of the data member 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 option --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
                  warning_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.

 - Some tests has been disabled by the reason that they contains
   multi-statements that not supported in PS mode. Unfortunately,
   some of these multi-statements can't be converted to a sequence of
   single-statements by the reason that multi-statementness is
   a requirement for tests. Therefore the whole test file will be
   skipped in case it is run with --ps-protocol

1 files changed, 12 insertions, 1 deletions

diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
index 805afedc8ae..74f80919333 100644
--- a/mysql-test/main/view.test
+++ b/mysql-test/main/view.test
@@ -2292,6 +2292,7 @@ CREATE VIEW v1 AS SELECT 42 AS Meaning;
 DROP FUNCTION IF EXISTS f1;
 --enable_warnings
 DELIMITER //;
+--enable_prepare_warnings
 CREATE FUNCTION f1() RETURNS INTEGER
 BEGIN
   DECLARE retn INTEGER;
@@ -2300,6 +2301,7 @@ BEGIN
 END
 //
 DELIMITER ;//
+--disable_prepare_warnings
 CREATE VIEW v2 AS SELECT f1();
 select * from v2;
 drop view v2,v1;
@@ -2476,6 +2478,7 @@ drop view t1, v1;
 # using SP function
 create table t1 (a int);
 delimiter //;
+--enable_prepare_warnings
 create function f1() returns int
 begin
   declare mx int;
@@ -2483,6 +2486,7 @@ begin
   return mx;
 end//
 delimiter ;//
+--disable_prepare_warnings
 create view v1 as select f1() as a;
 create view v2 as select * from v1;
 drop table t1;
@@ -3081,7 +3085,9 @@ create view v1 as select * from t1 order by f2;
 select * from v1;
 explain extended select * from v1;
 select * from v1 order by f1;
+--enable_prepare_warnings
 explain extended select * from v1 order by f1;
+--disable_prepare_warnings
 drop view v1;
 drop table t1;
 
@@ -3115,12 +3121,13 @@ DROP VIEW IF EXISTS v1;
 
 let $query = SELECT * FROM (SELECT 1) AS t into @w;
 
+--enable_prepare_warnings
 eval $query;
 --error ER_PARSE_ERROR
 eval CREATE VIEW v1 AS $query;
 --echo # Previously the following would fail.
 eval $query;
-
+--disable_prepare_warnings
 
 #
 # Bug#24532 The return data type of IS TRUE is different from similar operations
@@ -3883,11 +3890,13 @@ CREATE TABLE t1 (a INT);
 CREATE TABLE t2 (a INT);
 
 delimiter //;
+--enable_prepare_warnings
 CREATE FUNCTION f1() RETURNS INT
 BEGIN
   SELECT a FROM v2 INTO @a;
   RETURN @a;
 END//
+--disable_prepare_warnings
 delimiter ;//
 
 --echo # Trigger pre-locking when opening v2.
@@ -4421,7 +4430,9 @@ CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT f1 FROM t1;
 CREATE ALGORITHM=MERGE VIEW v2 AS SELECT f1 FROM v1 ORDER BY f1;
 
 SELECT * FROM v2 AS a1, v2 AS a2;
+--enable_prepare_warnings
 EXPLAIN EXTENDED SELECT * FROM v2 AS a1, v2 AS a2;
+--disable_prepare_warnings
 
 DROP VIEW v1, v2;
 DROP TABLE t1;