← Back to team overview

maria-developers team mailing list archive

Re: MDEV-11360 Dynamic SQL: DEFAULT as a bind parameter

 

Hello Sanja,

This is a new version, with some cleanups in "switch" statements
in various Item_param methods, and with a test covering Item_param::eq()
added.

Thanks!


On 11/27/2016 11:00 AM, Alexander Barkov wrote:
Hello Sanja,

Please review MDEV-11360.


Thanks!
commit e8fe39063e26ea3eea163a6391fbef2deb921e87
Author: Alexander Barkov <bar@xxxxxxxxxxx>
Date:   Sun Nov 27 17:05:54 2016 +0400

    MDEV-11360 Dynamic SQL: DEFAULT as a bind parameter
    
    This patch adds DEFAULT as a possible dynamic SQL parameter, e.g.:
      EXECUTE IMMEDIATE 'INSERT INTO t1 (column) VALUES(?)' USING DEFAULT;
      EXECUTE IMMEDIATE 'UPDATE t1 SET column=?' USING DEFAULT;
    and for similar PREPARE..EXECUTE queries.
    
    This is done for symmetry with the STMT_INDICATOR_DEFAULT indicator in
    the client-server PS protocol.
    
    The changes include:
    - Allowing DEFAULT as a possible option in execute USING clause (sql_yacc.yy)
    - Adding "virtual bool Item::save_in_param(THD *thd, Item_param *param)",
      because "normal" items (that have real values) and Item_default_value
      have now different actions when assigning itself as an Item_param value.
    - Fixing switch() statements in a few Item_param methods not to have "default",
      because its easy to forget to add a new "case" when adding a new XXX_VALUE
      value into the enum Item_param::enum_item_param_state.
      This is important, as we'll be adding new values soon, e.g. for MDEV-11359.
      Removing "default" helped to find and report bugs MDEV-11361 and MDEV-11362,
      because DECIMAL_VALUE is obviously not properly handled in some cases.

diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 5150ee6..a9211ee 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -4649,3 +4649,70 @@ DROP TABLE t1;
 #
 # End of 10.2 tests
 #
+#
+# MDEV-11360 Dynamic SQL: DEFAULT as a bind parameter
+#
+CREATE TABLE t1 (a INT DEFAULT 10, b INT DEFAULT NULL);
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?,?)' USING DEFAULT, DEFAULT;
+SELECT * FROM t1;
+a	b
+10	NULL
+UPDATE t1 SET a=20, b=30;
+SELECT * FROM t1;
+a	b
+20	30
+EXECUTE IMMEDIATE 'UPDATE t1 SET a=?,b=?' USING DEFAULT, DEFAULT;
+SELECT * FROM t1;
+a	b
+10	NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a INT DEFAULT 10);
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?+1)' USING DEFAULT;
+ERROR HY000: Default value is not supported for such parameter usage
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (CONCAT(?,?))' USING DEFAULT, 'test';
+ERROR HY000: Default value is not supported for such parameter usage
+DROP TABLE t1;
+CREATE TABLE t1 (a INT DEFAULT 10);
+INSERT INTO t1 VALUES (20);
+EXECUTE IMMEDIATE 'UPDATE t1 SET a=?+1' USING DEFAULT;
+ERROR HY000: Default value is not supported for such parameter usage
+EXECUTE IMMEDIATE 'UPDATE t1 SET a=CONCAT(?,?)' USING DEFAULT, 'test';
+ERROR HY000: Default value is not supported for such parameter usage
+DROP TABLE t1;
+EXECUTE IMMEDIATE 'SELECT CAST(? AS SIGNED)' USING DEFAULT;
+ERROR HY000: Default value is not supported for such parameter usage
+EXECUTE IMMEDIATE 'SELECT CAST(? AS DOUBLE)' USING DEFAULT;
+ERROR HY000: Default value is not supported for such parameter usage
+EXECUTE IMMEDIATE 'SELECT CAST(? AS CHAR)' USING DEFAULT;
+ERROR HY000: Default value is not supported for such parameter usage
+EXECUTE IMMEDIATE 'SELECT CAST(? AS DECIMAL(10,1))' USING DEFAULT;
+ERROR HY000: Default value is not supported for such parameter usage
+EXECUTE IMMEDIATE 'SELECT CAST(? AS TIME)' USING DEFAULT;
+ERROR HY000: Default value is not supported for such parameter usage
+EXECUTE IMMEDIATE 'SELECT CAST(? AS DATE)' USING DEFAULT;
+ERROR HY000: Default value is not supported for such parameter usage
+EXECUTE IMMEDIATE 'SELECT CAST(? AS DATETIME)' USING DEFAULT;
+ERROR HY000: Default value is not supported for such parameter usage
+EXECUTE IMMEDIATE 'SELECT ?+1' USING DEFAULT;
+ERROR HY000: Default value is not supported for such parameter usage
+EXECUTE IMMEDIATE 'SELECT CONCAT(?,?)' USING DEFAULT,'test';
+ERROR HY000: Default value is not supported for such parameter usage
+EXECUTE IMMEDIATE 'SELECT 1 LIMIT ?' USING DEFAULT;
+ERROR HY000: Default value is not supported for such parameter usage
+CREATE TABLE t1 (a INT DEFAULT 10);
+INSERT INTO t1 VALUES (1),(2),(3);
+EXECUTE IMMEDIATE 'SELECT * FROM t1 LIMIT ?' USING DEFAULT;
+ERROR HY000: Default value is not supported for such parameter usage
+DROP TABLE t1;
+# The output of this query in 'Note' is a syntactically incorrect query.
+# But as it's never logged, it's ok. It should be human readable only.
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING DEFAULT;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select default AS `?`
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING DEFAULT,DEFAULT;
+ERROR HY000: Default value is not supported for such parameter usage
+DROP TABLE t1;
diff --git a/mysql-test/suite/binlog/r/binlog_stm_ps.result b/mysql-test/suite/binlog/r/binlog_stm_ps.result
index bca298d..0b7491e 100644
--- a/mysql-test/suite/binlog/r/binlog_stm_ps.result
+++ b/mysql-test/suite/binlog/r/binlog_stm_ps.result
@@ -129,3 +129,59 @@ 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;
+#
+# MDEV-11360 Dynamic SQL: DEFAULT as a bind parameter
+#
+FLUSH LOGS;
+CREATE TABLE t1 (a INT DEFAULT 10);
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (Default)';
+# The output of this query in 'Note' is a syntactically incorrect query.
+# But as it's never logged, it's ok. It should be human readable only.
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING Default;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select default AS `?`
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING Default;
+CREATE PROCEDURE p1 ()
+BEGIN
+INSERT INTO t1 VALUES (Default);
+# EXPLAIN should not be logged
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING Default;
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING Default;
+END;
+$$
+CALL p1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+DROP PROCEDURE p1;
+DROP TABLE t1;
+include/show_binlog_events.inc
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000004	#	Binlog_checkpoint	#	#	master-bin.000004
+master-bin.000004	#	Gtid	#	#	GTID #-#-#
+master-bin.000004	#	Query	#	#	use `test`; CREATE TABLE t1 (a INT DEFAULT 10)
+master-bin.000004	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000004	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (Default)
+master-bin.000004	#	Query	#	#	COMMIT
+master-bin.000004	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000004	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (DEFAULT)
+master-bin.000004	#	Query	#	#	COMMIT
+master-bin.000004	#	Gtid	#	#	GTID #-#-#
+master-bin.000004	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
+BEGIN
+INSERT INTO t1 VALUES (Default);
+# EXPLAIN should not be logged
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING Default;
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING Default;
+END
+master-bin.000004	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000004	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (Default)
+master-bin.000004	#	Query	#	#	COMMIT
+master-bin.000004	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000004	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (DEFAULT)
+master-bin.000004	#	Query	#	#	COMMIT
+master-bin.000004	#	Gtid	#	#	GTID #-#-#
+master-bin.000004	#	Query	#	#	use `test`; DROP PROCEDURE p1
+master-bin.000004	#	Gtid	#	#	GTID #-#-#
+master-bin.000004	#	Query	#	#	use `test`; DROP TABLE `t1` /* generated by server */
diff --git a/mysql-test/suite/binlog/t/binlog_stm_ps.test b/mysql-test/suite/binlog/t/binlog_stm_ps.test
index 38f1350..e6e5498 100644
--- a/mysql-test/suite/binlog/t/binlog_stm_ps.test
+++ b/mysql-test/suite/binlog/t/binlog_stm_ps.test
@@ -70,3 +70,32 @@ EXECUTE IMMEDIATE @stmt USING @a;
 --let $binlog_file = LAST
 source include/show_binlog_events.inc;
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-11360 Dynamic SQL: DEFAULT as a bind parameter
+--echo #
+
+FLUSH LOGS;
+CREATE TABLE t1 (a INT DEFAULT 10);
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (Default)';
+--echo # The output of this query in 'Note' is a syntactically incorrect query.
+--echo # But as it's never logged, it's ok. It should be human readable only.
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING Default;
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING Default;
+
+DELIMITER $$;
+CREATE PROCEDURE p1 ()
+BEGIN
+  INSERT INTO t1 VALUES (Default);
+  # EXPLAIN should not be logged
+  EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING Default;
+  EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING Default;
+END;
+$$
+DELIMITER ;$$
+CALL p1;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+--let $binlog_file = LAST
+source include/show_binlog_events.inc;
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index cd48309..04f0ced 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -4188,3 +4188,80 @@ DROP TABLE t1;
 --echo #
 --echo # End of 10.2 tests
 --echo #
+
+
+--echo #
+--echo # MDEV-11360 Dynamic SQL: DEFAULT as a bind parameter
+--echo #
+
+# Correct usage
+CREATE TABLE t1 (a INT DEFAULT 10, b INT DEFAULT NULL);
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?,?)' USING DEFAULT, DEFAULT;
+SELECT * FROM t1;
+UPDATE t1 SET a=20, b=30;
+SELECT * FROM t1;
+EXECUTE IMMEDIATE 'UPDATE t1 SET a=?,b=?' USING DEFAULT, DEFAULT;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+# Incorrect usage in a expression in INSERT..VALUES
+CREATE TABLE t1 (a INT DEFAULT 10);
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?+1)' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (CONCAT(?,?))' USING DEFAULT, 'test';
+DROP TABLE t1;
+
+# Incorrect usage in UPDATE..SET
+CREATE TABLE t1 (a INT DEFAULT 10);
+INSERT INTO t1 VALUES (20);
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'UPDATE t1 SET a=?+1' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'UPDATE t1 SET a=CONCAT(?,?)' USING DEFAULT, 'test';
+DROP TABLE t1;
+
+
+# Incorrect usage in not an UPDATE/INSERT query at all
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CAST(? AS SIGNED)' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CAST(? AS DOUBLE)' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CAST(? AS CHAR)' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CAST(? AS DECIMAL(10,1))' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CAST(? AS TIME)' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CAST(? AS DATE)' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CAST(? AS DATETIME)' USING DEFAULT;
+
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT ?+1' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CONCAT(?,?)' USING DEFAULT,'test';
+
+
+# Incorrect usage in the LIMIT clause
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT 1 LIMIT ?' USING DEFAULT;
+CREATE TABLE t1 (a INT DEFAULT 10);
+INSERT INTO t1 VALUES (1),(2),(3);
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT * FROM t1 LIMIT ?' USING DEFAULT;
+DROP TABLE t1;
+
+
+--echo # The output of this query in 'Note' is a syntactically incorrect query.
+--echo # But as it's never logged, it's ok. It should be human readable only.
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING DEFAULT;
+
+
+# This tests Item_param::eq() for DEFAULT as a bound value
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING DEFAULT,DEFAULT;
+DROP TABLE t1;
diff --git a/sql/item.cc b/sql/item.cc
index 70b7383..24b877b 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -43,6 +43,7 @@
 #include "sql_expression_cache.h"
 
 const String my_null_string("NULL", 4, default_charset_info);
+const String my_default_string("DEFAULT", 7, default_charset_info);
 
 static int save_field_in_field(Field *, bool *, Field *, bool);
 
@@ -3585,6 +3586,11 @@ int Item_param::save_in_field(Field *field, bool no_conversions)
 {
   field->set_notnull();
 
+  /*
+    There's no "default" intentionally, to make compiler complain
+    when adding a new XXX_VALUE value.
+    Garbage (e.g. in case of a memory overrun) is handled after the switch.
+  */
   switch (state) {
   case INT_VALUE:
     return field->store(value.integer, unsigned_flag);
@@ -3606,13 +3612,21 @@ int Item_param::save_in_field(Field *field, bool no_conversions)
                                               top_table() !=
                                               field->table->pos_in_table_list);
   case NO_VALUE:
-  default:
-    DBUG_ASSERT(0);
+    DBUG_ASSERT(0); // Should not be possible
+    return true;
   }
+  DBUG_ASSERT(0); // Garbage
   return 1;
 }
 
 
+void Item_param::invalid_default_param() const
+{
+  my_message(ER_INVALID_DEFAULT_PARAM,
+             ER_THD(current_thd, ER_INVALID_DEFAULT_PARAM), MYF(0));
+}
+
+
 bool Item_param::get_date(MYSQL_TIME *res, ulonglong fuzzydate)
 {
   if (state == TIME_VALUE)
@@ -3626,6 +3640,7 @@ bool Item_param::get_date(MYSQL_TIME *res, ulonglong fuzzydate)
 
 double Item_param::val_real()
 {
+  // There's no "default". See comments in Item_param::save_in_field().
   switch (state) {
   case REAL_VALUE:
     return value.real;
@@ -3648,20 +3663,23 @@ double Item_param::val_real()
       time value for the placeholder.
     */
     return TIME_to_double(&value.time);
+  case DEFAULT_VALUE:
+    invalid_default_param();
+    // fall through
   case NULL_VALUE:
     return 0.0;
-  case DEFAULT_VALUE:
-    my_message(ER_INVALID_DEFAULT_PARAM,
-               ER_THD(current_thd, ER_INVALID_DEFAULT_PARAM), MYF(0));
-  default:
-    DBUG_ASSERT(0);
+  case NO_VALUE:
+    DBUG_ASSERT(0); // Should not be possible
+    return 0.0;
   }
+  DBUG_ASSERT(0); // Garbage
   return 0.0;
 } 
 
 
 longlong Item_param::val_int() 
 { 
+  // There's no "default". See comments in Item_param::save_in_field().
   switch (state) {
   case REAL_VALUE:
     return (longlong) rint(value.real);
@@ -3681,19 +3699,22 @@ longlong Item_param::val_int()
   case TIME_VALUE:
     return (longlong) TIME_to_ulonglong(&value.time);
   case DEFAULT_VALUE:
-    my_message(ER_INVALID_DEFAULT_PARAM,
-               ER_THD(current_thd, ER_INVALID_DEFAULT_PARAM), MYF(0));
+    invalid_default_param();
+    // fall through
   case NULL_VALUE:
     return 0; 
-  default:
-    DBUG_ASSERT(0);
+  case NO_VALUE:
+    DBUG_ASSERT(0); // Should not be possible
+    return 0;
   }
+  DBUG_ASSERT(0); // Garbage
   return 0;
 }
 
 
 my_decimal *Item_param::val_decimal(my_decimal *dec)
 {
+  // There's no "default". See comments in Item_param::save_in_field().
   switch (state) {
   case DECIMAL_VALUE:
     return &decimal_value;
@@ -3711,19 +3732,22 @@ my_decimal *Item_param::val_decimal(my_decimal *dec)
     return TIME_to_my_decimal(&value.time, dec);
   }
   case DEFAULT_VALUE:
-    my_message(ER_INVALID_DEFAULT_PARAM,
-               ER_THD(current_thd, ER_INVALID_DEFAULT_PARAM), MYF(0));
+    invalid_default_param();
+    // fall through
   case NULL_VALUE:
-    return 0; 
-  default:
-    DBUG_ASSERT(0);
+    return 0;
+  case NO_VALUE:
+    DBUG_ASSERT(0); // Should not be possible
+    return 0;
   }
+  DBUG_ASSERT(0); // Gabrage
   return 0;
 }
 
 
 String *Item_param::val_str(String* str) 
 { 
+  // There's no "default". See comments in Item_param::save_in_field().
   switch (state) {
   case STRING_VALUE:
   case LONG_DATA_VALUE:
@@ -3749,14 +3773,16 @@ String *Item_param::val_str(String* str)
     return str;
   }
   case DEFAULT_VALUE:
-    my_message(ER_INVALID_DEFAULT_PARAM,
-               ER_THD(current_thd, ER_INVALID_DEFAULT_PARAM), MYF(0));
+    invalid_default_param();
+    // fall through
   case NULL_VALUE:
     return NULL; 
-  default:
-    DBUG_ASSERT(0);
+  case NO_VALUE:
+    DBUG_ASSERT(0); // Should not be possible
+    return NULL;
   }
-  return str;
+  DBUG_ASSERT(0); // Garbage
+  return NULL;
 }
 
 /**
@@ -3772,18 +3798,19 @@ String *Item_param::val_str(String* str)
 
 const String *Item_param::query_val_str(THD *thd, String* str) const
 {
+  // There's no "default". See comments in Item_param::save_in_field().
   switch (state) {
   case INT_VALUE:
     str->set_int(value.integer, unsigned_flag, &my_charset_bin);
-    break;
+    return str;
   case REAL_VALUE:
     str->set_real(value.real, NOT_FIXED_DEC, &my_charset_bin);
-    break;
+    return str;
   case DECIMAL_VALUE:
     if (my_decimal2string(E_DEC_FATAL_ERROR, &decimal_value,
                           0, 0, 0, str) > 1)
       return &my_null_string;
-    break;
+    return str;
   case TIME_VALUE:
     {
       static const uint32 typelen= 9; // "TIMESTAMP" is the longest type name
@@ -3818,7 +3845,7 @@ const String *Item_param::query_val_str(THD *thd, String* str) const
       ptr+= (uint) my_TIME_to_str(&value.time, ptr, decimals);
       *ptr++= '\'';
       str->length((uint32) (ptr - buf));
-      break;
+      return str;
     }
   case STRING_VALUE:
   case LONG_DATA_VALUE:
@@ -3827,17 +3854,18 @@ const String *Item_param::query_val_str(THD *thd, String* str) const
       append_query_string(value.cs_info.character_set_client, str,
                           str_value.ptr(), str_value.length(),
                           thd->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES);
-      break;
+      return str;
     }
   case DEFAULT_VALUE:
-    my_message(ER_INVALID_DEFAULT_PARAM,
-               ER_THD(current_thd, ER_INVALID_DEFAULT_PARAM), MYF(0));
+    return &my_default_string;
   case NULL_VALUE:
     return &my_null_string;
-  default:
-    DBUG_ASSERT(0);
+  case NO_VALUE:
+    DBUG_ASSERT(0); // Should not be possible
+    return NULL;
   }
-  return str;
+  DBUG_ASSERT(0); // Garbage
+  return NULL;
 }
 
 
@@ -3881,10 +3909,11 @@ Item *
 Item_param::clone_item(THD *thd)
 {
   MEM_ROOT *mem_root= thd->mem_root;
+  // There's no "default". See comments in Item_param::save_in_field().
   switch (state) {
   case DEFAULT_VALUE:
-    my_message(ER_INVALID_DEFAULT_PARAM,
-               ER_THD(current_thd, ER_INVALID_DEFAULT_PARAM), MYF(0));
+    invalid_default_param();
+    // fall through
   case NULL_VALUE:
     return new (mem_root) Item_null(thd, name);
   case INT_VALUE:
@@ -3894,6 +3923,8 @@ Item_param::clone_item(THD *thd)
   case REAL_VALUE:
     return new (mem_root) Item_float(thd, name, value.real, decimals,
                                      max_length);
+  case DECIMAL_VALUE:
+    return 0; // Should create Item_decimal. See MDEV-11361.
   case STRING_VALUE:
   case LONG_DATA_VALUE:
     return new (mem_root) Item_string(thd, name, str_value.c_ptr_quick(),
@@ -3901,11 +3932,11 @@ Item_param::clone_item(THD *thd)
                                       collation.derivation,
                                       collation.repertoire);
   case TIME_VALUE:
-    break;
+    return 0;
   case NO_VALUE:
-  default:
-    DBUG_ASSERT(0);
-  };
+    return 0;
+  }
+  DBUG_ASSERT(0);  // Garbage
   return 0;
 }
 
@@ -3916,10 +3947,11 @@ Item_param::eq(const Item *item, bool binary_cmp) const
   if (!basic_const_item())
     return FALSE;
 
+  // There's no "default". See comments in Item_param::save_in_field().
   switch (state) {
   case DEFAULT_VALUE:
-    my_message(ER_INVALID_DEFAULT_PARAM,
-               ER_THD(current_thd, ER_INVALID_DEFAULT_PARAM), MYF(0));
+    invalid_default_param();
+    return false;
   case NULL_VALUE:
     return null_eq(item);
   case INT_VALUE:
@@ -3929,9 +3961,12 @@ Item_param::eq(const Item *item, bool binary_cmp) const
   case STRING_VALUE:
   case LONG_DATA_VALUE:
     return str_eq(&str_value, item, binary_cmp);
-  default:
-    break;
+  case DECIMAL_VALUE:
+  case TIME_VALUE:
+  case NO_VALUE:
+    return false;
   }
+  DBUG_ASSERT(0); // Garbage
   return FALSE;
 }
 
@@ -4001,6 +4036,15 @@ Item_param::set_param_type_and_swap_value(Item_param *src)
 void Item_param::set_default()
 {
   state= DEFAULT_VALUE;
+  /*
+    When Item_param is set to DEFAULT_VALUE:
+    - its val_str() and val_decimal() return NULL
+    - get_date() returns true
+    It's important also to have null_value==true for DEFAULT_VALUE.
+    Otherwise the callers of val_xxx() and get_date(), e.g. Item::send(),
+    can misbehave (e.g. crash on asserts).
+  */
+  null_value= true;
 }
 
 /**
@@ -6198,6 +6242,12 @@ int Item::save_in_field(Field *field, bool no_conversions)
 }
 
 
+bool Item::save_in_param(THD *thd, Item_param *param)
+{
+  return param->set_from_item(thd, this);
+}
+
+
 int Item_string::save_in_field(Field *field, bool no_conversions)
 {
   String *result;
diff --git a/sql/item.h b/sql/item.h
index 7844b67..6d2445b 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -61,6 +61,7 @@ class Protocol;
 struct TABLE_LIST;
 void item_init(void);			/* Init item functions */
 class Item_field;
+class Item_param;
 class user_var_entry;
 class JOIN;
 struct KEY_FIELD;
@@ -819,6 +820,7 @@ class Item: public Value_source,
   /* 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);
+  virtual bool save_in_param(THD *thd, Item_param *param);
   virtual void save_org_in_field(Field *field,
                                  fast_field_copier data
                                  __attribute__ ((__unused__)))
@@ -2959,6 +2961,8 @@ class Item_param :public Item_basic_value,
   Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; }
 
 private:
+  void invalid_default_param() const;
+
   virtual bool set_value(THD *thd, sp_rcontext *ctx, Item **it);
 
   virtual void set_out_param_info(Send_field *info);
@@ -5135,6 +5139,11 @@ class Item_default_value : public Item_field
   bool get_date(MYSQL_TIME *ltime,ulonglong fuzzydate);
   bool send(Protocol *protocol, String *buffer);
   int save_in_field(Field *field_arg, bool no_conversions);
+  bool save_in_param(THD *thd, Item_param *param)
+  {
+    param->set_default();
+    return false;
+  }
   table_map used_tables() const { return (table_map)0L; }
   Item_field *field_for_view_update() { return 0; }
 
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 97a33a5..1e943ca 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1230,7 +1230,7 @@ insert_params_from_actual_params(Prepared_statement *stmt,
   {
     Item_param *param= *it;
     Item *ps_param= param_it++;
-    if (param->set_from_item(stmt->thd, ps_param) ||
+    if (ps_param->save_in_param(stmt->thd, param) ||
         param->convert_str_value(stmt->thd))
       DBUG_RETURN(1);
   }
@@ -1273,7 +1273,7 @@ insert_params_from_actual_params_with_log(Prepared_statement *stmt,
       (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_item(thd, ps_param))
+    if (ps_param->save_in_param(thd, param))
       DBUG_RETURN(1);
 
     if (acc.append(param))
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 163322d..e0e0f6e 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -2280,7 +2280,7 @@ execute_var_list:
         ;
 
 execute_var_ident:
-          expr
+          expr_or_default
           {
             if (Lex->prepared_stmt_params.push_back($1, thd->mem_root))
               MYSQL_YYABORT;

Follow ups

References