MDEV-10709 Expressions as parameters to Dynamic SQL

10 files changed, 692 insertions, 80 deletions

diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 7e7a20ca9e6..8e1c5056b88 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -4093,3 +4093,201 @@ deallocate prepare stmt;
 SET SESSION sql_mode = @save_sql_mode;
 DROP TABLE t1,t2;
 # End of 10.0 tests
+#
+# Start of 10.2 tests
+#
+#
+# MDEV-10709 Expressions as parameters to Dynamic SQL
+#
+#
+# Using a simple expressions as an EXECUTE parameter
+#
+PREPARE stmt FROM 'SELECT ? FROM DUAL';
+EXECUTE stmt USING 10;
+?
+10
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM 'SELECT ? FROM DUAL';
+EXECUTE stmt USING TO_BASE64('xxx');
+?
+eHh4
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM 'SELECT ?+? FROM DUAL';
+EXECUTE stmt USING 10, 10 + 10;
+?+?
+30
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM 'SELECT CONCAT(?,?) FROM DUAL';
+EXECUTE stmt USING 'xxx', CONCAT('yyy','zzz');
+CONCAT(?,?)
+xxxyyyzzz
+DEALLOCATE PREPARE stmt;
+#
+# Testing disallowed expressions in USING
+#
+PREPARE stmt FROM 'SELECT ? FROM DUAL';
+EXECUTE stmt 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
+DEALLOCATE PREPARE stmt;
+CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
+PREPARE stmt FROM 'SELECT ? FROM DUAL';
+EXECUTE stmt USING f1();
+ERROR 42000: EXECUTE..USING does not support subqueries or stored functions
+DEALLOCATE PREPARE stmt;
+DROP FUNCTION f1;
+#
+# Testing erroneous expressions in USING
+#
+PREPARE stmt FROM 'SELECT ?';
+EXECUTE stmt USING _latin1'a'=_latin2'a';
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '='
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM 'SELECT ?';
+EXECUTE stmt USING ROW(1,2);
+ERROR 21000: Operand should contain 1 column(s)
+DEALLOCATE PREPARE stmt;
+#
+# Creating tables from EXECUTE parameters
+#
+PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ? AS c1 FROM DUAL';
+EXECUTE stmt USING 10;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` bigint(21) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+EXECUTE stmt USING 10.123;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` decimal(5,3) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+EXECUTE stmt USING 10.123e0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` double NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_DATE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` date DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIMESTAMP;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` datetime DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIMESTAMP(3);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` datetime(3) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIMESTAMP(6);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` datetime(6) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIME;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` time DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIME(3);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` time(3) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIME(6);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` time(6) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+DEALLOCATE PREPARE stmt;
+#
+# Using a user variable as an EXECUTE..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;
+PREPARE stmt FROM 'CALL p1(?)';
+EXECUTE stmt USING @a;
+SELECT @a;
+@a
+10
+DROP PROCEDURE p1;
+#
+# Using an SP variable as an EXECUTE..USING out parameter
+#
+CREATE PROCEDURE p1 (OUT a INT)
+BEGIN
+SET a=10;
+END;
+/
+CREATE PROCEDURE p2 (OUT a INT)
+BEGIN
+PREPARE stmt FROM 'CALL p1(?)';
+EXECUTE stmt USING a;
+END;
+/
+SET @a= 1;
+CALL p2(@a);
+SELECT @a;
+@a
+10
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+#
+# Testing re-prepare on a table metadata update between PREPARE and EXECUTE
+#
+CREATE TABLE t1 (a INT);
+CREATE PROCEDURE p1(a INT)
+BEGIN
+INSERT INTO t1 VALUES (a);
+END;
+/
+PREPARE stmt FROM 'CALL p1(?)';
+EXECUTE stmt USING 10;
+SELECT * FROM t1;
+a
+10
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=NEW.a+1;
+EXECUTE stmt USING 20;
+SELECT * FROM t1;
+a
+10
+21
+DEALLOCATE PREPARE stmt;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# End of MDEV-10709 Expressions as parameters to Dynamic SQL
+#
+#
+# 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 61aaa934315..a64871ad679 100644
--- a/mysql-test/suite/binlog/r/binlog_stm_ps.result
+++ b/mysql-test/suite/binlog/r/binlog_stm_ps.result
@@ -27,3 +27,79 @@ master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
 master-bin.000001	#	Query	#	#	use `test`; insert into t1 select 100 limit 100
 master-bin.000001	#	Query	#	#	COMMIT
 drop table t1;
+#
+# MDEV-10709 Expressions as parameters to Dynamic SQL
+#
+FLUSH LOGS;
+SET TIMESTAMP=UNIX_TIMESTAMP('2001-01-02 10:20:30.123456');
+CREATE TABLE t1 (a DECIMAL(30,8));
+PREPARE stmt FROM 'INSERT INTO t1 VALUES (?)';
+EXECUTE stmt USING 10;
+EXECUTE stmt USING 11e0;
+EXECUTE stmt USING 12.1;
+EXECUTE stmt USING '13';
+EXECUTE stmt USING CURRENT_DATE;
+EXECUTE stmt USING MAKETIME(10,20,30);
+EXECUTE stmt USING CURRENT_TIME;
+EXECUTE stmt USING CURRENT_TIME(3);
+EXECUTE stmt USING CURRENT_TIME(6);
+EXECUTE stmt USING CURRENT_TIMESTAMP;
+EXECUTE stmt USING CURRENT_TIMESTAMP(3);
+EXECUTE stmt USING CURRENT_TIMESTAMP(6);
+SELECT * FROM t1;
+a
+10.00000000
+11.00000000
+12.10000000
+13.00000000
+20010102.00000000
+102030.00000000
+102030.00000000
+102030.12300000
+102030.12345600
+20010102102030.00000000
+20010102102030.12300000
+20010102102030.12345600
+include/show_binlog_events.inc
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000002	#	Binlog_checkpoint	#	#	master-bin.000002
+master-bin.000002	#	Gtid	#	#	GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; CREATE TABLE t1 (a DECIMAL(30,8))
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (10)
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (11)
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (12.1)
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES ('13')
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (DATE'2001-01-02')
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (TIME'10:20:30')
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (TIME'10:20:30')
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (TIME'10:20:30.123')
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (TIME'10:20:30.123456')
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (TIMESTAMP'2001-01-02 10:20:30')
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (TIMESTAMP'2001-01-02 10:20:30.123')
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (TIMESTAMP'2001-01-02 10:20:30.123456')
+master-bin.000002	#	Query	#	#	COMMIT
+DROP TABLE t1;
+SET TIMESTAMP=DEFAULT;
diff --git a/mysql-test/suite/binlog/t/binlog_stm_ps.test b/mysql-test/suite/binlog/t/binlog_stm_ps.test
index e86db980b00..8e52b71d3f0 100644
--- a/mysql-test/suite/binlog/t/binlog_stm_ps.test
+++ b/mysql-test/suite/binlog/t/binlog_stm_ps.test
@@ -28,3 +28,29 @@ prepare s from "insert into t1 select 100 limit ?";
 set @a=100; execute s using @a;
 source include/show_binlog_events.inc;
 drop table t1;
+
+--echo #
+--echo # MDEV-10709 Expressions as parameters to Dynamic SQL
+--echo #
+
+FLUSH LOGS;
+SET TIMESTAMP=UNIX_TIMESTAMP('2001-01-02 10:20:30.123456');
+CREATE TABLE t1 (a DECIMAL(30,8));
+PREPARE stmt FROM 'INSERT INTO t1 VALUES (?)';
+EXECUTE stmt USING 10;
+EXECUTE stmt USING 11e0;
+EXECUTE stmt USING 12.1;
+EXECUTE stmt USING '13';
+EXECUTE stmt USING CURRENT_DATE;
+EXECUTE stmt USING MAKETIME(10,20,30);
+EXECUTE stmt USING CURRENT_TIME;
+EXECUTE stmt USING CURRENT_TIME(3);
+EXECUTE stmt USING CURRENT_TIME(6);
+EXECUTE stmt USING CURRENT_TIMESTAMP;
+EXECUTE stmt USING CURRENT_TIMESTAMP(3);
+EXECUTE stmt USING CURRENT_TIMESTAMP(6);
+SELECT * FROM t1;
+--let $binlog_file = LAST
+source include/show_binlog_events.inc;
+DROP TABLE t1;
+SET TIMESTAMP=DEFAULT;
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index 3881d522bbf..b8f636e35ac 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -3672,3 +3672,174 @@ DROP TABLE t1,t2;
 
 
 --echo # End of 10.0 tests
+
+--echo #
+--echo # Start of 10.2 tests
+--echo #
+
+--echo #
+--echo # MDEV-10709 Expressions as parameters to Dynamic SQL
+--echo #
+
+--echo #
+--echo # Using a simple expressions as an EXECUTE parameter
+--echo #
+
+PREPARE stmt FROM 'SELECT ? FROM DUAL';
+EXECUTE stmt USING 10;
+DEALLOCATE PREPARE stmt;
+
+PREPARE stmt FROM 'SELECT ? FROM DUAL';
+EXECUTE stmt USING TO_BASE64('xxx');
+DEALLOCATE PREPARE stmt;
+
+PREPARE stmt FROM 'SELECT ?+? FROM DUAL';
+EXECUTE stmt USING 10, 10 + 10;
+DEALLOCATE PREPARE stmt;
+
+PREPARE stmt FROM 'SELECT CONCAT(?,?) FROM DUAL';
+EXECUTE stmt USING 'xxx', CONCAT('yyy','zzz');
+DEALLOCATE PREPARE stmt;
+
+--echo #
+--echo # Testing disallowed expressions in USING
+--echo #
+
+PREPARE stmt FROM 'SELECT ? FROM DUAL';
+--error ER_PARSE_ERROR
+EXECUTE stmt USING (SELECT 1);
+DEALLOCATE PREPARE stmt;
+
+CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
+PREPARE stmt FROM 'SELECT ? FROM DUAL';
+--error ER_SUBQUERIES_NOT_SUPPORTED
+EXECUTE stmt USING f1();
+DEALLOCATE PREPARE stmt;
+DROP FUNCTION f1;
+
+--echo #
+--echo # Testing erroneous expressions in USING
+--echo #
+
+PREPARE stmt FROM 'SELECT ?';
+--error ER_CANT_AGGREGATE_2COLLATIONS
+EXECUTE stmt USING _latin1'a'=_latin2'a';
+DEALLOCATE PREPARE stmt;
+
+PREPARE stmt FROM 'SELECT ?';
+--error ER_OPERAND_COLUMNS
+EXECUTE stmt USING ROW(1,2);
+DEALLOCATE PREPARE stmt;
+
+--echo #
+--echo # Creating tables from EXECUTE parameters
+--echo #
+
+PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ? AS c1 FROM DUAL';
+EXECUTE stmt USING 10;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING 10.123;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING 10.123e0;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_DATE;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIMESTAMP;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIMESTAMP(3);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIMESTAMP(6);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIME;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIME(3);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIME(6);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+DEALLOCATE PREPARE stmt;
+
+
+--echo #
+--echo # Using a user variable as an EXECUTE..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;
+PREPARE stmt FROM 'CALL p1(?)';
+EXECUTE stmt USING @a;
+SELECT @a;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Using an SP variable as an EXECUTE..USING out parameter
+--echo #
+
+DELIMITER /;
+CREATE PROCEDURE p1 (OUT a INT)
+BEGIN
+  SET a=10;
+END;
+/
+CREATE PROCEDURE p2 (OUT a INT)
+BEGIN
+  PREPARE stmt FROM 'CALL p1(?)';
+  EXECUTE stmt USING a;
+END;
+/
+DELIMITER ;/
+SET @a= 1;
+CALL p2(@a);
+SELECT @a;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Testing re-prepare on a table metadata update between PREPARE and EXECUTE
+--echo #
+
+CREATE TABLE t1 (a INT);
+DELIMITER /;
+CREATE PROCEDURE p1(a INT)
+BEGIN
+  INSERT INTO t1 VALUES (a);
+END;
+/
+DELIMITER ;/
+PREPARE stmt FROM 'CALL p1(?)';
+EXECUTE stmt USING 10;
+SELECT * FROM t1;
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=NEW.a+1;
+EXECUTE stmt USING 20;
+SELECT * FROM t1;
+DEALLOCATE PREPARE stmt;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of MDEV-10709 Expressions as parameters to Dynamic SQL
+--echo #
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
diff --git a/sql/item.cc b/sql/item.cc
index 1b4935def56..448e34b89e3 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -3439,10 +3439,10 @@ bool Item_param::CONVERSION_INFO::convert(THD *thd, String *str)
 
 
 /**
-  Set parameter value from user variable value.
+  Set parameter value from Item.
 
   @param thd   Current thread
-  @param entry User variable structure (NULL means use NULL value)
+  @param item  Item
 
   @retval
     0 OK
@@ -3450,34 +3450,44 @@ bool Item_param::CONVERSION_INFO::convert(THD *thd, String *str)
     1 Out of memory
 */
 
-bool Item_param::set_from_user_var(THD *thd, const user_var_entry *entry)
+bool Item_param::set_from_item(THD *thd, Item *item)
 {
-  DBUG_ENTER("Item_param::set_from_user_var");
-  if (entry && entry->value)
+  DBUG_ENTER("Item_param::set_from_item");
+  if (limit_clause_param)
   {
-    unsigned_flag= entry->unsigned_flag;
-    if (limit_clause_param)
+    longlong val= item->val_int();
+    if (item->null_value)
+    {
+      set_null();
+      DBUG_RETURN(false);
+    }
+    else
     {
-      bool unused;
-      set_int(entry->val_int(&unused), MY_INT64_NUM_DECIMAL_DIGITS);
+      unsigned_flag= item->unsigned_flag;
+      set_int(val, MY_INT64_NUM_DECIMAL_DIGITS);
       item_type= Item::INT_ITEM;
-      set_handler_by_result_type(entry->type);
+      set_handler_by_result_type(item->result_type());
       DBUG_RETURN(!unsigned_flag && value.integer < 0 ? 1 : 0);
     }
-    switch (entry->type) {
+  }
+  struct st_value tmp;
+  if (!item->store(&tmp, 0))
+  {
+    unsigned_flag= item->unsigned_flag;
+    switch (item->cmp_type()) {
     case REAL_RESULT:
-      set_double(*(double*)entry->value);
+      set_double(tmp.value.m_double);
       item_type= Item::REAL_ITEM;
       set_handler_by_field_type(MYSQL_TYPE_DOUBLE);
       break;
     case INT_RESULT:
-      set_int(*(longlong*)entry->value, MY_INT64_NUM_DECIMAL_DIGITS);
+      set_int(tmp.value.m_longlong, MY_INT64_NUM_DECIMAL_DIGITS);
       item_type= Item::INT_ITEM;
       set_handler_by_field_type(MYSQL_TYPE_LONGLONG);
       break;
     case STRING_RESULT:
     {
-      value.cs_info.set(thd, entry->charset());
+      value.cs_info.set(thd, item->collation.collation);
       /*
         Exact value of max_length is not known unless data is converted to
         charset of connection, so we have to set it later.
@@ -3485,13 +3495,13 @@ bool Item_param::set_from_user_var(THD *thd, const user_var_entry *entry)
       item_type= Item::STRING_ITEM;
       set_handler_by_field_type(MYSQL_TYPE_VARCHAR);
 
-      if (set_str((const char *)entry->value, entry->length))
+      if (set_str(tmp.m_string.ptr(), tmp.m_string.length()))
         DBUG_RETURN(1);
       break;
     }
     case DECIMAL_RESULT:
     {
-      const my_decimal *ent_value= (const my_decimal *)entry->value;
+      const my_decimal *ent_value= &tmp.m_decimal;
       my_decimal2decimal(ent_value, &decimal_value);
       state= DECIMAL_VALUE;
       decimals= ent_value->frac;
@@ -3502,8 +3512,17 @@ bool Item_param::set_from_user_var(THD *thd, const user_var_entry *entry)
       set_handler_by_field_type(MYSQL_TYPE_NEWDECIMAL);
       break;
     }
-    case ROW_RESULT:
     case TIME_RESULT:
+    {
+      value.time= tmp.value.m_time;
+      state= TIME_VALUE;
+      max_length= item->max_length;
+      decimals= item->decimals;
+      item_type= Item::DATE_ITEM;
+      set_handler(item->type_handler());
+      break;
+    }
+    case ROW_RESULT:
       DBUG_ASSERT(0);
       set_null();
     }
@@ -3742,18 +3761,34 @@ const String *Item_param::query_val_str(THD *thd, String* str) const
     break;
   case TIME_VALUE:
     {
+      static const uint32 typelen= 9; // "TIMESTAMP" is the longest type name
       char *buf, *ptr;
       str->length(0);
       /*
         TODO: in case of error we need to notify replication
         that binary log contains wrong statement 
       */
-      if (str->reserve(MAX_DATE_STRING_REP_LENGTH+3))
+      if (str->reserve(MAX_DATE_STRING_REP_LENGTH + 3 + typelen))
         break; 
 
       /* Create date string inplace */
+      switch (value.time.time_type) {
+      case MYSQL_TIMESTAMP_DATE:
+        str->append(C_STRING_WITH_LEN("DATE"));
+        break;
+      case MYSQL_TIMESTAMP_TIME:
+        str->append(C_STRING_WITH_LEN("TIME"));
+        break;
+      case MYSQL_TIMESTAMP_DATETIME:
+        str->append(C_STRING_WITH_LEN("TIMESTAMP"));
+        break;
+      case MYSQL_TIMESTAMP_ERROR:
+      case MYSQL_TIMESTAMP_NONE:
+        break;
+      }
+      DBUG_ASSERT(str->length() <= typelen);
       buf= str->c_ptr_quick();
-      ptr= buf;
+      ptr= buf + str->length();
       *ptr++= '\'';
       ptr+= (uint) my_TIME_to_str(&value.time, ptr, decimals);
       *ptr++= '\'';
diff --git a/sql/item.h b/sql/item.h
index a9381ba1517..28a14454c0b 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -30,6 +30,29 @@
 
 C_MODE_START
 #include <ma_dyncol.h>
+
+/*
+  A prototype for a C-compatible structure to store a value of any data type.
+  Currently it has to stay in /sql, as it depends on String and my_decimal.
+  We'll do the following changes:
+  1. add pure C "struct st_string" and "struct st_my_decimal"
+  2. change type of m_string to struct st_string and move inside the union
+  3. change type of m_decmal to struct st_my_decimal and move inside the union
+  4. move the definition to some file in /include
+*/
+struct st_value
+{
+  enum enum_dynamic_column_type m_type;
+  union
+  {
+    longlong m_longlong;
+    double m_double;
+    MYSQL_TIME m_time;
+  } value;
+  String m_string;
+  my_decimal m_decimal;
+};
+
 C_MODE_END
 
 const char *dbug_print_item(Item *item);
@@ -742,6 +765,57 @@ public:
     supposed to be applied recursively.  
   */
   virtual inline void quick_fix_field() { fixed= 1; }
+
+  bool store(struct st_value *value, ulonglong fuzzydate)
+  {
+    switch (cmp_type()) {
+    case INT_RESULT:
+    {
+      value->m_type= unsigned_flag ? DYN_COL_UINT : DYN_COL_INT;
+      value->value.m_longlong= val_int();
+      break;
+    }
+    case REAL_RESULT:
+    {
+      value->m_type= DYN_COL_DOUBLE;
+      value->value.m_double= val_real();
+      break;
+    }
+    case DECIMAL_RESULT:
+    {
+      value->m_type= DYN_COL_DECIMAL;
+      my_decimal *dec= val_decimal(&value->m_decimal);
+      if (dec != &value->m_decimal && !null_value)
+        my_decimal2decimal(dec, &value->m_decimal);
+      break;
+    }
+    case STRING_RESULT:
+    {
+      value->m_type= DYN_COL_STRING;
+      String *str= val_str(&value->m_string);
+      if (str != &value->m_string && !null_value)
+        value->m_string.set(str->ptr(), str->length(), str->charset());
+      break;
+    }
+    case TIME_RESULT:
+    {
+      value->m_type= DYN_COL_DATETIME;
+      get_date(&value->value.m_time, fuzzydate);
+      break;
+    }
+    case ROW_RESULT:
+      DBUG_ASSERT(false);
+      null_value= true;
+      break;
+    }
+    if (null_value)
+    {
+      value->m_type= DYN_COL_NULL;
+      return true;
+    }
+    return false;
+  }
+
   /* Function returns 1 on overflow and -1 on fatal errors */
   int save_in_field_no_warnings(Field *field, bool no_conversions);
   virtual int save_in_field(Field *field, bool no_conversions);
@@ -2816,7 +2890,7 @@ public:
   bool set_str(const char *str, ulong length);
   bool set_longdata(const char *str, ulong length);
   void set_time(MYSQL_TIME *tm, timestamp_type type, uint32 max_length_arg);
-  bool set_from_user_var(THD *thd, const user_var_entry *entry);
+  bool set_from_item(THD *thd, Item *item);
   void reset();
   /*
     Assign placeholder value from bind data.
diff --git a/sql/protocol.cc b/sql/protocol.cc
index be73c94c9b2..a3085c69f17 100644
--- a/sql/protocol.cc
+++ b/sql/protocol.cc
@@ -1306,33 +1306,27 @@ bool Protocol_text::send_out_parameters(List<Item_param> *sp_params)
               thd->lex->prepared_stmt_params.elements);
 
   List_iterator_fast<Item_param> item_param_it(*sp_params);
-  List_iterator_fast<LEX_STRING> user_var_name_it(thd->lex->prepared_stmt_params);
+  List_iterator_fast<Item> param_it(thd->lex->prepared_stmt_params);
 
   while (true)
   {
     Item_param *item_param= item_param_it++;
-    LEX_STRING *user_var_name= user_var_name_it++;
+    Item *param= param_it++;
+    Settable_routine_parameter *sparam;
 
-    if (!item_param || !user_var_name)
+    if (!item_param || !param)
       break;
 
     if (!item_param->get_out_param_info())
       continue; // It's an IN-parameter.
 
-    Item_func_set_user_var *suv=
-      new (thd->mem_root) Item_func_set_user_var(thd, *user_var_name, item_param);
-    /*
-      Item_func_set_user_var is not fixed after construction, call
-      fix_fields().
-    */
-    if (suv->fix_fields(thd, NULL))
-      return TRUE;
-
-    if (suv->check(FALSE))
-      return TRUE;
+    if (!(sparam= param->get_settable_routine_parameter()))
+    {
+      DBUG_ASSERT(0);
+      continue;
+    }
 
-    if (suv->update())
-      return TRUE;
+    sparam->set_value(thd, thd->spcont, reinterpret_cast<Item **>(&item_param));
   }
 
   return FALSE;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 5310e2a018f..cb081faa75b 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -2728,7 +2728,7 @@ public:
   /* If true, prepared_stmt_code is a name of variable that holds the query */
   bool prepared_stmt_code_is_varref;
   /* Names of user variables holding parameters (in EXECUTE) */
-  List<LEX_STRING> prepared_stmt_params;
+  List<Item> prepared_stmt_params;
   sp_head *sphead;
   sp_name *spname;
   bool sp_lex_in_use;	/* Keep track on lex usage in SPs for error handling */
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index df15a629802..54a0021daeb 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -186,9 +186,9 @@ public:
 #else
   bool (*set_params_data)(Prepared_statement *st, String *expanded_query);
 #endif
-  bool (*set_params_from_vars)(Prepared_statement *stmt,
-                               List<LEX_STRING>& varnames,
-                               String *expanded_query);
+  bool (*set_params_from_actual_params)(Prepared_statement *stmt,
+                                        List<Item> &list,
+                                        String *expanded_query);
 public:
   Prepared_statement(THD *thd_arg);
   virtual ~Prepared_statement();
@@ -1141,31 +1141,27 @@ swap_parameter_array(Item_param **param_array_dst,
   Assign prepared statement parameters from user variables.
 
   @param stmt      Statement
-  @param varnames  List of variables. Caller must ensure that number
-                   of variables in the list is equal to number of statement
+  @param params    A list of parameters. Caller must ensure that number
+                   of parameters in the list is equal to number of statement
                    parameters
   @param query     Ignored
 */
 
-static bool insert_params_from_vars(Prepared_statement *stmt,
-                                    List<LEX_STRING>& varnames,
-                                    String *query __attribute__((unused)))
+static bool
+insert_params_from_actual_params(Prepared_statement *stmt,
+                                 List<Item> &params,
+                                 String *query __attribute__((unused)))
 {
   Item_param **begin= stmt->param_array;
   Item_param **end= begin + stmt->param_count;
-  user_var_entry *entry;
-  LEX_STRING *varname;
-  List_iterator<LEX_STRING> var_it(varnames);
-  DBUG_ENTER("insert_params_from_vars");
+  List_iterator<Item> param_it(params);
+  DBUG_ENTER("insert_params_from_actual_params");
 
   for (Item_param **it= begin; it < end; ++it)
   {
     Item_param *param= *it;
-    varname= var_it++;
-    entry= (user_var_entry*)my_hash_search(&stmt->thd->user_vars,
-                                           (uchar*) varname->str,
-                                           varname->length);
-    if (param->set_from_user_var(stmt->thd, entry) ||
+    Item *ps_param= param_it++;
+    if (param->set_from_item(stmt->thd, ps_param) ||
         param->convert_str_value(stmt->thd))
       DBUG_RETURN(1);
   }
@@ -1174,45 +1170,41 @@ static bool insert_params_from_vars(Prepared_statement *stmt,
 
 
 /**
-  Do the same as insert_params_from_vars but also construct query text for
-  binary log.
+  Do the same as insert_params_from_actual_params
+  but also construct query text for binary log.
 
   @param stmt      Prepared statement
-  @param varnames  List of variables. Caller must ensure that number of
-                   variables in the list is equal to number of statement
+  @param params    A list of parameters. Caller must ensure that number of
+                   parameters in the list is equal to number of statement
                    parameters
   @param query     The query with parameter markers replaced with corresponding
                    user variables that were used to execute the query.
 */
 
-static bool insert_params_from_vars_with_log(Prepared_statement *stmt,
-                                             List<LEX_STRING>& varnames,
-                                             String *query)
+static bool
+insert_params_from_actual_params_with_log(Prepared_statement *stmt,
+                                          List<Item> &params,
+                                          String *query)
 {
   Item_param **begin= stmt->param_array;
   Item_param **end= begin + stmt->param_count;
-  user_var_entry *entry;
-  LEX_STRING *varname;
-  List_iterator<LEX_STRING> var_it(varnames);
+  List_iterator<Item> param_it(params);
   THD *thd= stmt->thd;
   Copy_query_with_rewrite acc(thd, stmt->query(), stmt->query_length(), query);
 
-  DBUG_ENTER("insert_params_from_vars_with_log");
+  DBUG_ENTER("insert_params_from_actual_params_with_log");
 
   for (Item_param **it= begin; it < end; ++it)
   {
     Item_param *param= *it;
-    varname= var_it++;
-
-    entry= (user_var_entry *) my_hash_search(&thd->user_vars, (uchar*)
-                                             varname->str, varname->length);
+    Item *ps_param= param_it++;
     /*
       We have to call the setup_one_conversion_function() here to set
       the parameter's members that might be needed further
       (e.g. value.cs_info.character_set_client is used in the query_val_str()).
     */
     setup_one_conversion_function(thd, param, param->field_type());
-    if (param->set_from_user_var(thd, entry))
+    if (param->set_from_item(thd, ps_param))
       DBUG_RETURN(1);
 
     if (acc.append(param))
@@ -3042,7 +3034,49 @@ 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;
+  }
+
+  /*
+    thd->free_list can already have some Items.
+
+    Example queries:
+      - SET STATEMENT var=expr FOR EXECUTE stmt;
+      - EXECUTE stmt USING expr;
+
+    E.g. for a query like this:
+      PREPARE stmt FROM 'INSERT INTO t1 VALUES (@@max_sort_length)';
+      SET STATEMENT max_sort_length=2048 FOR EXECUTE stmt;
+    thd->free_list contains a pointer to Item_int corresponding to 2048.
+
+    If Prepared_statement::execute() notices that the table metadata for "t1"
+    has changed since PREPARE, it returns an error asking the calling
+    Prepared_statement::execute_loop() to re-prepare the statement.
+    Before returning the error, Prepared_statement::execute()
+    calls Prepared_statement::cleanup_stmt(),
+    which calls thd->cleanup_after_query(),
+    which calls Query_arena::free_items().
+
+    We hide "external" Items, e.g. those created while parsing the
+    "SET STATEMENT" or "USING" parts of the query,
+    so they don't get freed in case of re-prepare.
+    See MDEV-10702 Crash in SET STATEMENT FOR EXECUTE
+  */
+  Item *free_list_backup= thd->free_list;
+  thd->free_list= NULL; // Hide the external (e.g. "SET STATEMENT") Items
   (void) stmt->execute_loop(&expanded_query, FALSE, NULL, NULL);
+  thd->free_items();    // Free items created by execute_loop()
+  /*
+    Now restore the "external" (e.g. "SET STATEMENT") Item list.
+    It will be freed normaly in THD::cleanup_after_query().
+  */
+  thd->free_list= free_list_backup;
+
   stmt->lex->restore_set_statement_var();
   DBUG_VOID_RETURN;
 }
@@ -3490,7 +3524,7 @@ void Prepared_statement::setup_set_params()
 
   if (replace_params_with_values)
   {
-    set_params_from_vars= insert_params_from_vars_with_log;
+    set_params_from_actual_params= insert_params_from_actual_params_with_log;
 #ifndef EMBEDDED_LIBRARY
     set_params= insert_params_with_log;
 #else
@@ -3499,7 +3533,7 @@ void Prepared_statement::setup_set_params()
   }
   else
   {
-    set_params_from_vars= insert_params_from_vars;
+    set_params_from_actual_params= insert_params_from_actual_params;
 #ifndef EMBEDDED_LIBRARY
     set_params= insert_params;
 #else
@@ -3809,8 +3843,8 @@ Prepared_statement::set_parameters(String *expanded_query,
   if (is_sql_ps)
   {
     /* SQL prepared statement */
-    res= set_params_from_vars(this, thd->lex->prepared_stmt_params,
-                              expanded_query);
+    res= set_params_from_actual_params(this, thd->lex->prepared_stmt_params,
+                                       expanded_query);
   }
   else if (param_count)
   {
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 4a88c1f2ddf..0da0b442946 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -2255,7 +2255,14 @@ execute:
 
 execute_using:
           /* nothing */
-        | USING execute_var_list
+        | USING            { Lex->expr_allows_subselect= false; }
+          execute_var_list
+          {
+            if (Lex->table_or_sp_used())
+              my_yyabort_error((ER_SUBQUERIES_NOT_SUPPORTED, MYF(0),
+                               "EXECUTE..USING"));
+            Lex->expr_allows_subselect= true;
+          }
         ;
 
 execute_var_list:
@@ -2264,12 +2271,9 @@ execute_var_list:
         ;
 
 execute_var_ident:
-          '@' ident_or_text
+          expr
           {
-            LEX *lex=Lex;
-            LEX_STRING *lexstr= (LEX_STRING*)thd->memdup(&$2, sizeof(LEX_STRING));
-            if (!lexstr || lex->prepared_stmt_params.push_back(lexstr,
-                                                               thd->mem_root))
+            if (Lex->prepared_stmt_params.push_back($1, thd->mem_root))
               MYSQL_YYABORT;
           }
         ;