MDEV-10585 EXECUTE IMMEDIATE statement

19 files changed, 609 insertions, 12 deletions

diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 0a75b11e064..91860cde1db 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -6222,5 +6222,15 @@ DROP TABLE t1;
 #
 SET STORAGE_ENGINE=Default;
 #
+# MDEV-10585 EXECUTE IMMEDIATE statement
+#
+SET character_set_connection=ucs2;
+EXECUTE IMMEDIATE 'SELECT COLLATION("a")';
+COLLATION("a")
+ucs2_general_ci
+SET @stmt='SELECT COLLATION("a")';
+EXECUTE IMMEDIATE @stmt;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/r/keywords.result b/mysql-test/r/keywords.result
index a3588017e97..f5bf600dc40 100644
--- a/mysql-test/r/keywords.result
+++ b/mysql-test/r/keywords.result
@@ -275,3 +275,8 @@ set option=1;
 ERROR HY000: Unknown system variable 'option'
 set option option=1;
 ERROR HY000: Unknown system variable 'option'
+#
+# MDEV-10585 EXECUTE IMMEDIATE statement
+#
+CREATE TABLE immediate (immediate int);
+DROP TABLE immediate;
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index e498caebb4d..862113c224a 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -1374,7 +1374,7 @@ performance-schema-max-rwlock-instances -1
 performance-schema-max-socket-classes 10
 performance-schema-max-socket-instances -1
 performance-schema-max-stage-classes 150
-performance-schema-max-statement-classes 184
+performance-schema-max-statement-classes 185
 performance-schema-max-table-handles -1
 performance-schema-max-table-instances -1
 performance-schema-max-thread-classes 50
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 8e1c5056b88..cf2cbce8f93 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -4289,5 +4289,218 @@ DROP TABLE t1;
 # End of MDEV-10709 Expressions as parameters to Dynamic SQL
 #
 #
+# MDEV-10585 EXECUTE IMMEDIATE statement
+#
+EXECUTE IMMEDIATE 'SELECT 1 AS a';
+a
+1
+SET @a=10;
+EXECUTE IMMEDIATE 'SELECT ? AS a' USING @a;
+a
+10
+EXECUTE IMMEDIATE 'SELECT ? AS a' USING 20;
+a
+20
+#
+# Erroneous queries
+#
+EXECUTE IMMEDIATE 'xxx';
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'xxx' at line 1
+EXECUTE IMMEDIATE 'SELECT 1' USING @a;
+ERROR HY000: Incorrect arguments to EXECUTE
+EXECUTE IMMEDIATE 'SELECT ?';
+ERROR HY000: Incorrect arguments to EXECUTE
+EXECUTE IMMEDIATE 'EXECUTE IMMEDIATE "SELECT 1"';
+ERROR HY000: This command is not supported in the prepared statement protocol yet
+EXECUTE IMMEDIATE 'PREPARE stmt FROM "SELECT 1"';
+ERROR HY000: This command is not supported in the prepared statement protocol yet
+EXECUTE IMMEDIATE 'EXECUTE stmt';
+ERROR HY000: This command is not supported in the prepared statement protocol yet
+EXECUTE IMMEDIATE 'DEALLOCATE PREPARE stmt';
+ERROR HY000: This command is not supported in the prepared statement protocol yet
+EXECUTE IMMEDIATE 'SELECT ?' USING _latin1'a'=_latin2'a';
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '='
+EXECUTE IMMEDIATE 'SELECT ?' USING ROW(1,2);
+ERROR 21000: Operand should contain 1 column(s)
+#
+# Testing disallowed expressions in USING
+#
+EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING (SELECT 1);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 1)' at line 1
+CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
+EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING f1();
+ERROR 42000: EXECUTE..USING does not support subqueries or stored functions
+DROP FUNCTION f1;
+#
+# DDL
+#
+EXECUTE IMMEDIATE 'CREATE TABLE t1 (a INT)';
+EXECUTE IMMEDIATE 'SHOW CREATE TABLE t1';
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+EXECUTE IMMEDIATE 'DROP TABLE t1';
+SET @stmt= 'CREATE TABLE t1 (a INT)';
+EXECUTE IMMEDIATE @stmt;
+SET @stmt= 'SHOW CREATE TABLE t1';
+EXECUTE IMMEDIATE @stmt;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SET @stmt= 'DROP TABLE t1';
+EXECUTE IMMEDIATE @stmt;
+#
+# DDL with parameters
+#
+SET @a= 10, @b= 10.1, @c= 10e0, @d='str';
+EXECUTE IMMEDIATE
+'CREATE TABLE t1 AS SELECT ? AS a,? AS b,? AS c,? AS d'
+  USING @a,@b,@c,@d;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` bigint(21) NOT NULL,
+  `b` decimal(3,1) DEFAULT NULL,
+  `c` double NOT NULL,
+  `d` varchar(3) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+EXECUTE IMMEDIATE
+'CREATE TABLE t1 AS SELECT ? AS a,? AS b,? AS c,? AS d'
+  USING 10, 10.1, 10e0, 'str';
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` bigint(21) NOT NULL,
+  `b` decimal(3,1) DEFAULT NULL,
+  `c` double NOT NULL,
+  `d` varchar(3) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+EXECUTE IMMEDIATE
+'CREATE TABLE t1 AS SELECT ? AS t1,? AS t2, ? AS d1,? AS dt1, ? AS dt2'
+  USING TIME'10:20:30',
+TIME'10:20:30.123',
+DATE'2001-01-01',
+TIMESTAMP'2001-01-01 10:20:30',
+TIMESTAMP'2001-01-01 10:20:30.123';
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t1` time DEFAULT NULL,
+  `t2` time(3) DEFAULT NULL,
+  `d1` date DEFAULT NULL,
+  `dt1` datetime DEFAULT NULL,
+  `dt2` datetime(3) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
+# Using a user variable as an EXECUTE IMMEDIATE..USING out parameter
+#
+CREATE PROCEDURE p1(OUT a INT)
+BEGIN
+SET a:= 10;
+END;
+/
+SET @a=1;
+CALL p1(@a);
+SELECT @a;
+@a
+10
+SET @a=2;
+EXECUTE IMMEDIATE 'CALL p1(?)' USING @a;
+SELECT @a;
+@a
+10
+DROP PROCEDURE p1;
+#
+# Using an SP variable as an EXECUTE IMMEDIATE..USING out parameter
+#
+CREATE PROCEDURE p1 (OUT a INT)
+BEGIN
+SET a=10;
+END;
+/
+CREATE PROCEDURE p2 (OUT a INT)
+BEGIN
+EXECUTE IMMEDIATE 'CALL p1(?)' USING a;
+END;
+/
+SET @a= 1;
+CALL p2(@a);
+SELECT @a;
+@a
+10
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+#
+# Changing user variables
+#
+SET @a=10;
+EXECUTE IMMEDIATE 'SET @a=@a+1';
+SELECT @a;
+@a
+11
+#
+# SET STATEMENT
+#
+SET @@max_sort_length=1024;
+EXECUTE IMMEDIATE 'SET STATEMENT max_sort_length=1025 FOR SELECT @@max_sort_length';
+@@max_sort_length
+1025
+SELECT @@max_sort_length;
+@@max_sort_length
+1024
+SET @@max_sort_length=DEFAULT;
+#
+# Similar to prepared EXECUTE, IMMEDIATE is not allowed in stored functions
+#
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+EXECUTE IMMEDIATE 'DO 1';
+RETURN 1;
+END;
+$$
+ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
+#
+# Status variables
+#
+CREATE FUNCTION get_status_var(name TEXT) RETURNS INT
+RETURN (SELECT CAST(VARIABLE_VALUE AS INT)
+FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME=name);
+CREATE PROCEDURE test_status_var(name TEXT)
+BEGIN
+SET @cnt0=get_status_var(name);
+EXECUTE IMMEDIATE 'DO 1';
+SET @cnt1=get_status_var(name);
+SELECT @cnt1-@cnt0 AS increment;
+END;
+$$
+# Note, EXECUTE IMMEDIATE does not increment COM_EXECUTE_SQL
+# It increments COM_EXECUTE_IMMEDIATE instead.
+CALL test_status_var('COM_EXECUTE_SQL');
+increment
+0
+CALL test_status_var('COM_EXECUTE_IMMEDIATE');
+increment
+1
+CALL test_status_var('COM_STMT_PREPARE');
+increment
+1
+CALL test_status_var('COM_STMT_EXECUTE');
+increment
+1
+CALL test_status_var('COM_STMT_CLOSE');
+increment
+1
+DROP PROCEDURE test_status_var;
+DROP FUNCTION get_status_var;
+#
+# End of MDEV-10585 EXECUTE IMMEDIATE statement
+#
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/suite/binlog/r/binlog_stm_ps.result b/mysql-test/suite/binlog/r/binlog_stm_ps.result
index a64871ad679..bca298d029d 100644
--- a/mysql-test/suite/binlog/r/binlog_stm_ps.result
+++ b/mysql-test/suite/binlog/r/binlog_stm_ps.result
@@ -103,3 +103,29 @@ master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (TIMESTAMP'2001-
 master-bin.000002	#	Query	#	#	COMMIT
 DROP TABLE t1;
 SET TIMESTAMP=DEFAULT;
+#
+# MDEV-10585 EXECUTE IMMEDIATE statement
+#
+FLUSH LOGS;
+CREATE TABLE t1 (a INT);
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (101)';
+SET @a=102;
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING @a;
+SET @a=103;
+SET @stmt='INSERT INTO t1 VALUES (?)';
+EXECUTE IMMEDIATE @stmt USING @a;
+include/show_binlog_events.inc
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000003	#	Binlog_checkpoint	#	#	master-bin.000003
+master-bin.000003	#	Gtid	#	#	GTID #-#-#
+master-bin.000003	#	Query	#	#	use `test`; CREATE TABLE t1 (a INT)
+master-bin.000003	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000003	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (101)
+master-bin.000003	#	Query	#	#	COMMIT
+master-bin.000003	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000003	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (102)
+master-bin.000003	#	Query	#	#	COMMIT
+master-bin.000003	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000003	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (103)
+master-bin.000003	#	Query	#	#	COMMIT
+DROP TABLE t1;
diff --git a/mysql-test/suite/binlog/t/binlog_stm_ps.test b/mysql-test/suite/binlog/t/binlog_stm_ps.test
index 8e52b71d3f0..38f13507cf1 100644
--- a/mysql-test/suite/binlog/t/binlog_stm_ps.test
+++ b/mysql-test/suite/binlog/t/binlog_stm_ps.test
@@ -54,3 +54,19 @@ SELECT * FROM t1;
 source include/show_binlog_events.inc;
 DROP TABLE t1;
 SET TIMESTAMP=DEFAULT;
+
+--echo #
+--echo # MDEV-10585 EXECUTE IMMEDIATE statement
+--echo #
+
+FLUSH LOGS;
+CREATE TABLE t1 (a INT);
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (101)';
+SET @a=102;
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING @a;
+SET @a=103;
+SET @stmt='INSERT INTO t1 VALUES (?)';
+EXECUTE IMMEDIATE @stmt USING @a;
+--let $binlog_file = LAST
+source include/show_binlog_events.inc;
+DROP TABLE t1;
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index 9b31ae23613..2a6c24653e1 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -3021,9 +3021,9 @@ READ_ONLY	YES
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	PERFORMANCE_SCHEMA_MAX_STATEMENT_CLASSES
 SESSION_VALUE	NULL
-GLOBAL_VALUE	184
+GLOBAL_VALUE	185
 GLOBAL_VALUE_ORIGIN	COMPILE-TIME
-DEFAULT_VALUE	184
+DEFAULT_VALUE	185
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BIGINT UNSIGNED
 VARIABLE_COMMENT	Maximum number of statement instruments.
diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test
index aa5838e7c6d..2090f35fa70 100644
--- a/mysql-test/t/ctype_ucs.test
+++ b/mysql-test/t/ctype_ucs.test
@@ -975,5 +975,17 @@ let $coll_pad='ucs2_bin';
 --source include/ctype_pad_all_engines.inc
 
 --echo #
+--echo # MDEV-10585 EXECUTE IMMEDIATE statement
+--echo #
+SET character_set_connection=ucs2;
+EXECUTE IMMEDIATE 'SELECT COLLATION("a")';
+
+# We don't support character sets with mbminlen>1 in the parser yet
+# Returning "syntax error" is fine
+SET @stmt='SELECT COLLATION("a")';
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE @stmt;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
diff --git a/mysql-test/t/keywords.test b/mysql-test/t/keywords.test
index 54052e65014..40beee9e3c1 100644
--- a/mysql-test/t/keywords.test
+++ b/mysql-test/t/keywords.test
@@ -173,3 +173,10 @@ drop table option;
 set option=1;
 --error 1193
 set option option=1;
+
+--echo #
+--echo # MDEV-10585 EXECUTE IMMEDIATE statement
+--echo #
+
+CREATE TABLE immediate (immediate int);
+DROP TABLE immediate;
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index b8f636e35ac..89db0a91bd0 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -3841,5 +3841,210 @@ DROP TABLE t1;
 --echo #
 
 --echo #
+--echo # MDEV-10585 EXECUTE IMMEDIATE statement
+--echo #
+
+EXECUTE IMMEDIATE 'SELECT 1 AS a';
+SET @a=10;
+EXECUTE IMMEDIATE 'SELECT ? AS a' USING @a;
+EXECUTE IMMEDIATE 'SELECT ? AS a' USING 20;
+
+
+--echo #
+--echo # Erroneous queries
+--echo #
+
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE 'xxx';
+
+--error ER_WRONG_ARGUMENTS
+EXECUTE IMMEDIATE 'SELECT 1' USING @a;
+
+--error ER_WRONG_ARGUMENTS
+EXECUTE IMMEDIATE 'SELECT ?';
+
+--error ER_UNSUPPORTED_PS
+EXECUTE IMMEDIATE 'EXECUTE IMMEDIATE "SELECT 1"';
+
+--error ER_UNSUPPORTED_PS
+EXECUTE IMMEDIATE 'PREPARE stmt FROM "SELECT 1"';
+
+--error ER_UNSUPPORTED_PS
+EXECUTE IMMEDIATE 'EXECUTE stmt';
+
+--error ER_UNSUPPORTED_PS
+EXECUTE IMMEDIATE 'DEALLOCATE PREPARE stmt';
+
+--error ER_CANT_AGGREGATE_2COLLATIONS
+EXECUTE IMMEDIATE 'SELECT ?' USING _latin1'a'=_latin2'a';
+
+--error ER_OPERAND_COLUMNS
+EXECUTE IMMEDIATE 'SELECT ?' USING ROW(1,2);
+
+--echo #
+--echo # Testing disallowed expressions in USING
+--echo #
+
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING (SELECT 1);
+
+CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
+--error ER_SUBQUERIES_NOT_SUPPORTED
+EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING f1();
+DROP FUNCTION f1;
+
+--echo #
+--echo # DDL
+--echo #
+
+EXECUTE IMMEDIATE 'CREATE TABLE t1 (a INT)';
+EXECUTE IMMEDIATE 'SHOW CREATE TABLE t1';
+EXECUTE IMMEDIATE 'DROP TABLE t1';
+
+SET @stmt= 'CREATE TABLE t1 (a INT)';
+EXECUTE IMMEDIATE @stmt;
+SET @stmt= 'SHOW CREATE TABLE t1';
+EXECUTE IMMEDIATE @stmt;
+SET @stmt= 'DROP TABLE t1';
+EXECUTE IMMEDIATE @stmt;
+
+
+--echo #
+--echo # DDL with parameters
+--echo #
+
+SET @a= 10, @b= 10.1, @c= 10e0, @d='str';
+EXECUTE IMMEDIATE
+  'CREATE TABLE t1 AS SELECT ? AS a,? AS b,? AS c,? AS d'
+  USING @a,@b,@c,@d;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+EXECUTE IMMEDIATE
+  'CREATE TABLE t1 AS SELECT ? AS a,? AS b,? AS c,? AS d'
+  USING 10, 10.1, 10e0, 'str';
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+EXECUTE IMMEDIATE
+  'CREATE TABLE t1 AS SELECT ? AS t1,? AS t2, ? AS d1,? AS dt1, ? AS dt2'
+  USING TIME'10:20:30',
+        TIME'10:20:30.123',
+        DATE'2001-01-01',
+        TIMESTAMP'2001-01-01 10:20:30',
+        TIMESTAMP'2001-01-01 10:20:30.123';
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Using a user variable as an EXECUTE IMMEDIATE..USING out parameter
+--echo #
+
+DELIMITER /;
+CREATE PROCEDURE p1(OUT a INT)
+BEGIN
+  SET a:= 10;
+END;
+/
+DELIMITER ;/
+SET @a=1;
+CALL p1(@a);
+SELECT @a;
+SET @a=2;
+EXECUTE IMMEDIATE 'CALL p1(?)' USING @a;
+SELECT @a;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Using an SP variable as an EXECUTE IMMEDIATE..USING out parameter
+--echo #
+
+DELIMITER /;
+CREATE PROCEDURE p1 (OUT a INT)
+BEGIN
+  SET a=10;
+END;
+/
+CREATE PROCEDURE p2 (OUT a INT)
+BEGIN
+  EXECUTE IMMEDIATE 'CALL p1(?)' USING a;
+END;
+/
+DELIMITER ;/
+SET @a= 1;
+CALL p2(@a);
+SELECT @a;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Changing user variables
+--echo #
+
+SET @a=10;
+EXECUTE IMMEDIATE 'SET @a=@a+1';
+SELECT @a;
+
+
+--echo #
+--echo # SET STATEMENT
+--echo #
+
+SET @@max_sort_length=1024;
+EXECUTE IMMEDIATE 'SET STATEMENT max_sort_length=1025 FOR SELECT @@max_sort_length';
+SELECT @@max_sort_length;
+SET @@max_sort_length=DEFAULT;
+
+
+--echo #
+--echo # Similar to prepared EXECUTE, IMMEDIATE is not allowed in stored functions
+--echo #
+DELIMITER $$;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+  EXECUTE IMMEDIATE 'DO 1';
+  RETURN 1;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # Status variables
+--echo #
+CREATE FUNCTION get_status_var(name TEXT) RETURNS INT
+       RETURN (SELECT CAST(VARIABLE_VALUE AS INT)
+           FROM INFORMATION_SCHEMA.SESSION_STATUS
+           WHERE VARIABLE_NAME=name);
+DELIMITER $$;
+CREATE PROCEDURE test_status_var(name TEXT)
+BEGIN
+  SET @cnt0=get_status_var(name);
+  EXECUTE IMMEDIATE 'DO 1';
+  SET @cnt1=get_status_var(name);
+  SELECT @cnt1-@cnt0 AS increment;
+END;
+$$
+DELIMITER ;$$
+--echo # Note, EXECUTE IMMEDIATE does not increment COM_EXECUTE_SQL
+--echo # It increments COM_EXECUTE_IMMEDIATE instead.
+CALL test_status_var('COM_EXECUTE_SQL');
+CALL test_status_var('COM_EXECUTE_IMMEDIATE');
+CALL test_status_var('COM_STMT_PREPARE');
+CALL test_status_var('COM_STMT_EXECUTE');
+CALL test_status_var('COM_STMT_CLOSE');
+
+DROP PROCEDURE test_status_var;
+DROP FUNCTION get_status_var;
+
+--echo #
+--echo # End of MDEV-10585 EXECUTE IMMEDIATE statement
+--echo #
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
diff --git a/sql/lex.h b/sql/lex.h
index d82dcf4e94a..dfb874e3463 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -278,6 +278,7 @@ static SYMBOL symbols[] = {
   { "IGNORE",		SYM(IGNORE_SYM)},
   { "IGNORE_DOMAIN_IDS", SYM(IGNORE_DOMAIN_IDS_SYM)},
   { "IGNORE_SERVER_IDS", SYM(IGNORE_SERVER_IDS_SYM)},
+  { "IMMEDIATE",	SYM(IMMEDIATE_SYM)},
   { "IMPORT",		SYM(IMPORT)},
   { "IN",		SYM(IN_SYM)},
   { "INDEX",		SYM(INDEX_SYM)},
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 0c742a5c484..310ccb047c4 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -3886,6 +3886,7 @@ SHOW_VAR com_status_vars[]= {
   {"drop_user",            STMT_STATUS(SQLCOM_DROP_USER)},
   {"drop_view",            STMT_STATUS(SQLCOM_DROP_VIEW)},
   {"empty_query",          STMT_STATUS(SQLCOM_EMPTY_QUERY)},
+  {"execute_immediate",    STMT_STATUS(SQLCOM_EXECUTE_IMMEDIATE)},
   {"execute_sql",          STMT_STATUS(SQLCOM_EXECUTE)},
   {"flush",                STMT_STATUS(SQLCOM_FLUSH)},
   {"get_diagnostics",      STMT_STATUS(SQLCOM_GET_DIAGNOSTICS)},
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index e2610fd950c..37e449cd666 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -253,6 +253,7 @@ sp_get_flags_for_command(LEX *lex)
     statement within an IF condition.
   */
   case SQLCOM_EXECUTE:
+  case SQLCOM_EXECUTE_IMMEDIATE:
     flags= sp_head::MULTI_RESULTS | sp_head::CONTAINS_DYNAMIC_SQL;
     break;
   case SQLCOM_PREPARE:
diff --git a/sql/sql_cmd.h b/sql/sql_cmd.h
index 92b74bb88ab..e33f8e443dc 100644
--- a/sql/sql_cmd.h
+++ b/sql/sql_cmd.h
@@ -95,6 +95,7 @@ enum enum_sql_command {
   SQLCOM_SHOW_GENERIC,
   SQLCOM_ALTER_USER,
   SQLCOM_SHOW_CREATE_USER,
+  SQLCOM_EXECUTE_IMMEDIATE,
 
   /*
     When a command is added here, be sure it's also added in mysqld.cc
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index cb081faa75b..7cfe6448756 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -3006,6 +3006,19 @@ public:
   void init_last_field(Column_definition *field, const char *name, CHARSET_INFO *cs);
   void set_last_field_type(const Lex_field_type_st &type);
   bool set_bincmp(CHARSET_INFO *cs, bool bin);
+
+  bool prepared_stmt_params_fix_fields(THD *thd)
+  {
+    // Fix Items in the EXECUTE..USING list
+    List_iterator_fast<Item> param_it(prepared_stmt_params);
+    while (Item *param= param_it++)
+    {
+      if (param->fix_fields(thd, 0) || param->check_cols(1))
+        return true;
+    }
+    return false;
+  }
+
   // Check if "KEY IF NOT EXISTS name" used outside of ALTER context
   bool check_add_key(DDL_options_st ddl)
   {
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index ac00b21c837..69637f87b8e 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3377,6 +3377,11 @@ mysql_execute_command(THD *thd)
 
     break;
   }
+  case SQLCOM_EXECUTE_IMMEDIATE:
+  {
+    mysql_sql_stmt_execute_immediate(thd);
+    break;
+  }
   case SQLCOM_PREPARE:
   {
     mysql_sql_stmt_prepare(thd);
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 54a0021daeb..99000573fb8 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -207,6 +207,7 @@ public:
   bool execute_server_runnable(Server_runnable *server_runnable);
   /* Destroy this statement */
   void deallocate();
+  bool execute_immediate(const char *query, uint query_length);
 private:
   /**
     The memory root to allocate parsed tree elements (instances of Item,
@@ -218,6 +219,7 @@ private:
   bool set_parameters(String *expanded_query,
                       uchar *packet, uchar *packet_end);
   bool execute(String *expanded_query, bool open_cursor);
+  void deallocate_immediate();
   bool reprepare();
   bool validate_metadata(Prepared_statement  *copy);
   void swap_prepared_statement(Prepared_statement *copy);
@@ -2764,6 +2766,39 @@ void mysql_sql_stmt_prepare(THD *thd)
   DBUG_VOID_RETURN;
 }
 
+
+void mysql_sql_stmt_execute_immediate(THD *thd)
+{
+  LEX *lex= thd->lex;
+  Prepared_statement *stmt;
+  const char *query;
+  uint query_len= 0;
+  DBUG_ENTER("mysql_sql_stmt_execute_immediate");
+
+  if (lex->prepared_stmt_params_fix_fields(thd))
+    DBUG_VOID_RETURN;
+
+  /*
+    Prepared_statement is quite large,
+    let's allocate it on the heap rather than on the stack.
+  */
+  if (!(query= get_dynamic_sql_string(lex, &query_len)) ||
+      !(stmt= new Prepared_statement(thd)))
+    DBUG_VOID_RETURN;                           // out of memory
+
+  // See comments on thd->free_list in mysql_sql_stmt_execute()
+  Item *free_list_backup= thd->free_list;
+  thd->free_list= NULL;
+  (void) stmt->execute_immediate(query, query_len);
+  thd->free_items();
+  thd->free_list= free_list_backup;
+
+  stmt->lex->restore_set_statement_var();
+  delete stmt;
+  DBUG_VOID_RETURN;
+}
+
+
 /**
   Reinit prepared statement/stored procedure before execution.
 
@@ -3034,13 +3069,8 @@ void mysql_sql_stmt_execute(THD *thd)
 
   DBUG_PRINT("info",("stmt: 0x%lx", (long) stmt));
 
-  // Fix all Items in the USING list
-  List_iterator_fast<Item> param_it(lex->prepared_stmt_params);
-  while (Item *param= param_it++)
-  {
-    if (param->fix_fields(thd, 0) || param->check_cols(1))
-      DBUG_VOID_RETURN;
-  }
+  if (lex->prepared_stmt_params_fix_fields(thd))
+    DBUG_VOID_RETURN;
 
   /*
     thd->free_list can already have some Items.
@@ -4401,16 +4431,58 @@ error:
 }
 
 
-/** Common part of DEALLOCATE PREPARE and mysqld_stmt_close. */
+/**
+  Prepare, execute and clean-up a statement.
+  @param query  - query text
+  @param length - query text length
+  @retval true  - the query was not executed (parse error, wrong parameters)
+  @retval false - the query was prepared and executed
 
-void Prepared_statement::deallocate()
+  Note, if some error happened during execution, it still returns "false".
+*/
+bool Prepared_statement::execute_immediate(const char *query, uint query_len)
+{
+  DBUG_ENTER("Prepared_statement::execute_immediate");
+  String expanded_query;
+  static LEX_STRING execute_immediate_stmt_name=
+    {(char*) STRING_WITH_LEN("(immediate)") };
+
+  set_sql_prepare();
+  name= execute_immediate_stmt_name;      // for DBUG_PRINT etc
+  if (prepare(query, query_len))
+    DBUG_RETURN(true);
+
+  if (param_count != thd->lex->prepared_stmt_params.elements)
+  {
+    my_error(ER_WRONG_ARGUMENTS, MYF(0), "EXECUTE");
+    deallocate_immediate();
+    DBUG_RETURN(true);
+  }
+
+  (void) execute_loop(&expanded_query, FALSE, NULL, NULL);
+  deallocate_immediate();
+  DBUG_RETURN(false);
+}
+
+
+/**
+  Common part of DEALLOCATE PREPARE, EXECUTE IMMEDIATE, mysqld_stmt_close.
+*/
+void Prepared_statement::deallocate_immediate()
 {
   /* We account deallocate in the same manner as mysqld_stmt_close */
   status_var_increment(thd->status_var.com_stmt_close);
 
   /* It should now be safe to reset CHANGE MASTER parameters */
   lex_end_stage2(lex);
+}
 
+
+/** Common part of DEALLOCATE PREPARE and mysqld_stmt_close. */
+
+void Prepared_statement::deallocate()
+{
+  deallocate_immediate();
   /* Statement map calls delete stmt on erase */
   thd->stmt_map.erase(this);
 }
diff --git a/sql/sql_prepare.h b/sql/sql_prepare.h
index aec4ac40036..4a8780c4a02 100644
--- a/sql/sql_prepare.h
+++ b/sql/sql_prepare.h
@@ -75,6 +75,7 @@ void mysqld_stmt_execute(THD *thd, char *packet, uint packet_length);
 void mysqld_stmt_close(THD *thd, char *packet);
 void mysql_sql_stmt_prepare(THD *thd);
 void mysql_sql_stmt_execute(THD *thd);
+void mysql_sql_stmt_execute_immediate(THD *thd);
 void mysql_sql_stmt_close(THD *thd);
 void mysqld_stmt_fetch(THD *thd, char *packet, uint packet_length);
 void mysqld_stmt_reset(THD *thd, char *packet);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 0da0b442946..2b0e722a2b1 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1300,6 +1300,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 %token  IGNORE_DOMAIN_IDS_SYM
 %token  IGNORE_SYM
 %token  IGNORE_SERVER_IDS_SYM
+%token  IMMEDIATE_SYM                 /* SQL-2003-R */
 %token  IMPORT
 %token  INDEXES
 %token  INDEX_SYM
@@ -2251,6 +2252,12 @@ execute:
           }
           execute_using
           {}
+        | EXECUTE_SYM IMMEDIATE_SYM prepare_src
+          {
+            Lex->sql_command= SQLCOM_EXECUTE_IMMEDIATE;
+          }
+          execute_using
+          {}
         ;
 
 execute_using:
@@ -14705,6 +14712,7 @@ keyword_sp:
         | ID_SYM                   {}
         | IDENTIFIED_SYM           {}
         | IGNORE_SERVER_IDS_SYM    {}
+        | IMMEDIATE_SYM            {} /* SQL-2003-R */
         | INVOKER_SYM              {}
         | IMPORT                   {}
         | INDEXES                  {}