Merge remote-tracking branch 'origin/bb-10.2-ext' into 10.3

16 files changed, 298 insertions, 164 deletions

diff --git a/sql/field.h b/sql/field.h
index 7ad47df3909..061232ea1ef 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -4244,6 +4244,10 @@ public:
     }
     return 0;
   }
+  bool adjust_formal_params_to_actual_params(THD *thd, List<Item> *args);
+  bool adjust_formal_params_to_actual_params(THD *thd,
+                                             Item **args, uint arg_count);
+  bool resolve_type_refs(THD *);
 };
 
 
diff --git a/sql/item.cc b/sql/item.cc
index 92459bd6f7f..54426c761c8 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1541,6 +1541,45 @@ bool mark_unsupported_function(const char *w1, const char *w2,
   return mark_unsupported_function(ptr, store, result);
 }
 
+
+Query_fragment::Query_fragment(THD *thd, sp_head *sphead,
+                               const char *start, const char *end)
+{
+  DBUG_ASSERT(start <= end);
+  if (sphead)
+  {
+    if (sphead->m_tmp_query)
+    {
+      // Normal SP statement
+      DBUG_ASSERT(sphead->m_tmp_query <= start);
+      set(start - sphead->m_tmp_query, end - start);
+    }
+    else
+    {
+      /*
+        We're in the "if" expression of a compound query:
+          if (expr)
+            do_something;
+          end if;
+        sphead->m_tmp_query is not set yet at this point, because
+        the "if" part of such statements is never put into the binary log.
+        Values of Rewritable_query_parameter::pos_in_query and
+        Rewritable_query_parameter:len_in_query will not be important,
+        so setting both to 0 should be fine.
+      */
+      set(0, 0);
+    }
+  }
+  else
+  {
+    // Non-SP statement
+    DBUG_ASSERT(thd->query() <= start);
+    DBUG_ASSERT(end <= thd->query_end());
+    set(start - thd->query(), end - start);
+  }
+}
+
+
 /*****************************************************************************
   Item_sp_variable methods
 *****************************************************************************/
diff --git a/sql/item.h b/sql/item.h
index e823d327441..ddaf44770ab 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -89,6 +89,7 @@ public:
 
 const char *dbug_print_item(Item *item);
 
+class sp_head;
 class Protocol;
 struct TABLE_LIST;
 void item_init(void);			/* Init item functions */
@@ -391,6 +392,31 @@ public:
   { return NULL; }
 };
 
+
+/*
+  A helper class to calculate offset and length of a query fragment
+  - outside of SP
+  - inside an SP
+  - inside a compound block
+*/
+class Query_fragment
+{
+  uint m_pos;
+  uint m_length;
+  void set(size_t pos, size_t length)
+  {
+    DBUG_ASSERT(pos < UINT_MAX32);
+    DBUG_ASSERT(length < UINT_MAX32);
+    m_pos= (uint) pos;
+    m_length= (uint) length;
+  }
+public:
+  Query_fragment(THD *thd, sp_head *sphead, const char *start, const char *end);
+  uint pos() const { return m_pos; }
+  uint length() const { return m_length; }
+};
+
+
 /**
   This is used for items in the query that needs to be rewritten
   before binlogging
@@ -2100,7 +2126,6 @@ public:
   Field_enumerator() {}                       /* Remove gcc warning */
 };
 
-class sp_head;
 class Item_string;
 
 
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index e7240ad2cce..e1cdee8298f 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -4432,21 +4432,6 @@ static int init_common_variables()
     SYSVAR_AUTOSIZE(threadpool_size, my_getncpus());
 #endif
 
-  /* Fix host_cache_size. */
-  if (IS_SYSVAR_AUTOSIZE(&host_cache_size))
-  {
-    if (max_connections <= 628 - 128)
-      SYSVAR_AUTOSIZE(host_cache_size, 128 + max_connections);
-    else if (max_connections <= ((ulong)(2000 - 628)) * 20 + 500)
-      SYSVAR_AUTOSIZE(host_cache_size, 628 + ((max_connections - 500) / 20));
-    else
-      SYSVAR_AUTOSIZE(host_cache_size, 2000);
-  }
-
-  /* Fix back_log (back_log == 0 added for MySQL compatibility) */
-  if (back_log == 0 || IS_SYSVAR_AUTOSIZE(&back_log))
-    SYSVAR_AUTOSIZE(back_log, MY_MIN(900, (50 + max_connections / 5)));
-
   /* connections and databases needs lots of files */
   {
     uint files, wanted_files, max_open_files;
@@ -4471,7 +4456,7 @@ static int init_common_variables()
 
     if (files < wanted_files)
     {
-      if (!open_files_limit)
+      if (!open_files_limit || IS_SYSVAR_AUTOSIZE(&open_files_limit))
       {
         /*
           If we have requested too much file handles than we bring
@@ -4500,6 +4485,36 @@ static int init_common_variables()
     }
     SYSVAR_AUTOSIZE(open_files_limit, files);
   }
+
+  /*
+    Max_connections is now set.
+    Now we can fix other variables depending on this variable.
+  */
+
+  /* Fix host_cache_size */
+  if (IS_SYSVAR_AUTOSIZE(&host_cache_size))
+  {
+    /*
+      The default value is 128.
+      The autoset value is 128, plus 1 for a value of max_connections
+      up to 500, plus 1 for every increment of 20 over 500 in the
+      max_connections value, capped at 2000.
+    */
+    uint size= (HOST_CACHE_SIZE + MY_MIN(max_connections, 500) +
+                MY_MAX(((long) max_connections)-500,0)/20);
+    SYSVAR_AUTOSIZE(host_cache_size, size);
+  }
+
+  /* Fix back_log (back_log == 0 added for MySQL compatibility) */
+  if (back_log == 0 || IS_SYSVAR_AUTOSIZE(&back_log))
+  {
+    /*
+      The default value is 150.
+      The autoset value is 50 + max_connections / 5 capped at 900
+    */
+    SYSVAR_AUTOSIZE(back_log, MY_MIN(900, (50 + max_connections / 5)));
+  }
+
   unireg_init(opt_specialflag); /* Set up extern variabels */
   if (!(my_default_lc_messages=
         my_locale_by_name(lc_messages)))
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 5bd4ff95f9c..ebfee30ecd3 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -545,6 +545,7 @@ sp_head::sp_head(const Sp_handler *sph)
    Database_qualified_name(&null_clex_str, &null_clex_str),
    m_handler(sph),
    m_flags(0),
+   m_tmp_query(NULL),
    m_explicit_name(false),
    /*
      FIXME: the only use case when name is NULL is events, and it should
@@ -1426,7 +1427,7 @@ bool sp_head::check_execute_access(THD *thd) const
 
 
 /**
-  Create rcontext using the routine security.
+  Create rcontext optionally using the routine security.
   This is important for sql_mode=ORACLE to make sure that the invoker has
   access to the tables mentioned in the %TYPE references.
 
@@ -1438,25 +1439,52 @@ bool sp_head::check_execute_access(THD *thd) const
   @retval           NULL - error (access denided or EOM)
   @retval          !NULL - success (the invoker has rights to all %TYPE tables)
 */
-sp_rcontext *sp_head::rcontext_create(THD *thd, Field *ret_value)
+sp_rcontext *sp_head::rcontext_create(THD *thd, Field *ret_value,
+                                      Row_definition_list *defs,
+                                      bool switch_security_ctx)
 {
-  bool has_column_type_refs= m_flags & HAS_COLUMN_TYPE_REFS;
+  if (!(m_flags & HAS_COLUMN_TYPE_REFS))
+    return sp_rcontext::create(thd, m_pcont, ret_value, *defs);
+  sp_rcontext *res= NULL;
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   Security_context *save_security_ctx;
-  if (has_column_type_refs &&
+  if (switch_security_ctx &&
       set_routine_security_ctx(thd, this, &save_security_ctx))
     return NULL;
 #endif
-  sp_rcontext *res= sp_rcontext::create(thd, m_pcont, ret_value,
-                                        has_column_type_refs);
+  if (!defs->resolve_type_refs(thd))
+    res= sp_rcontext::create(thd, m_pcont, ret_value, *defs);
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
-  if (has_column_type_refs)
+  if (switch_security_ctx)
     m_security_ctx.restore_security_context(thd, save_security_ctx);
 #endif
   return res;
 }
 
 
+sp_rcontext *sp_head::rcontext_create(THD *thd, Field *ret_value,
+                                      List<Item> *args)
+{
+  DBUG_ASSERT(args);
+  Row_definition_list defs;
+  m_pcont->retrieve_field_definitions(&defs);
+  if (defs.adjust_formal_params_to_actual_params(thd, args))
+    return NULL;
+  return rcontext_create(thd, ret_value, &defs, true);
+}
+
+
+sp_rcontext *sp_head::rcontext_create(THD *thd, Field *ret_value,
+                                      Item **args, uint arg_count)
+{
+  Row_definition_list defs;
+  m_pcont->retrieve_field_definitions(&defs);
+  if (defs.adjust_formal_params_to_actual_params(thd, args, arg_count))
+    return NULL;
+  return rcontext_create(thd, ret_value, &defs, true);
+}
+
+
 /**
   Execute trigger stored program.
 
@@ -1554,8 +1582,9 @@ sp_head::execute_trigger(THD *thd,
   init_sql_alloc(&call_mem_root, MEM_ROOT_BLOCK_SIZE, 0, MYF(0));
   thd->set_n_backup_active_arena(&call_arena, &backup_arena);
 
-  if (!(nctx= sp_rcontext::create(thd, m_pcont, NULL,
-                                  m_flags & HAS_COLUMN_TYPE_REFS)))
+  Row_definition_list defs;
+  m_pcont->retrieve_field_definitions(&defs);
+  if (!(nctx= rcontext_create(thd, NULL, &defs, false)))
   {
     err_status= TRUE;
     goto err_with_cleanup;
@@ -1669,7 +1698,7 @@ sp_head::execute_function(THD *thd, Item **argp, uint argcount,
   init_sql_alloc(&call_mem_root, MEM_ROOT_BLOCK_SIZE, 0, MYF(0));
   thd->set_n_backup_active_arena(&call_arena, &backup_arena);
 
-  if (!(nctx= rcontext_create(thd, return_value_fld)))
+  if (!(nctx= rcontext_create(thd, return_value_fld, argp, argcount)))
   {
     thd->restore_active_arena(&call_arena, &backup_arena);
     err_status= TRUE;
@@ -1883,7 +1912,7 @@ sp_head::execute_procedure(THD *thd, List<Item> *args)
   if (! octx)
   {
     /* Create a temporary old context. */
-    if (!(octx= rcontext_create(thd, NULL)))
+    if (!(octx= rcontext_create(thd, NULL, args)))
     {
       DBUG_PRINT("error", ("Could not create octx"));
       DBUG_RETURN(TRUE);
@@ -1896,7 +1925,7 @@ sp_head::execute_procedure(THD *thd, List<Item> *args)
     thd->spcont->callers_arena= thd;
   }
 
-  if (!(nctx= rcontext_create(thd, NULL)))
+  if (!(nctx= rcontext_create(thd, NULL, args)))
   {
     delete nctx; /* Delete nctx if it was init() that failed. */
     thd->spcont= save_spcont;
diff --git a/sql/sp_head.h b/sql/sp_head.h
index 977e20a07b9..c3ace46a789 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -214,8 +214,12 @@ public:
     m_sp_cache_version= version_arg;
   }
 
-  sp_rcontext *rcontext_create(THD *thd, Field *retval);
-
+  sp_rcontext *rcontext_create(THD *thd, Field *retval, List<Item> *args);
+  sp_rcontext *rcontext_create(THD *thd, Field *retval,
+                               Item **args, uint arg_count);
+  sp_rcontext *rcontext_create(THD *thd, Field *retval,
+                               Row_definition_list *list,
+                               bool switch_security_ctx);
 private:
   /**
     Version of the stored routine cache at the moment when the
diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc
index 6b418e9e387..b96ded8cf80 100644
--- a/sql/sp_rcontext.cc
+++ b/sql/sp_rcontext.cc
@@ -63,20 +63,15 @@ sp_rcontext::~sp_rcontext()
 sp_rcontext *sp_rcontext::create(THD *thd,
                                  const sp_pcontext *root_parsing_ctx,
                                  Field *return_value_fld,
-                                 bool resolve_type_refs)
+                                 Row_definition_list &field_def_lst)
 {
   sp_rcontext *ctx= new (thd->mem_root) sp_rcontext(root_parsing_ctx,
                                                     return_value_fld,
                                                     thd->in_sub_stmt);
-
   if (!ctx)
     return NULL;
 
-  List<Spvar_definition> field_def_lst;
-  ctx->m_root_parsing_ctx->retrieve_field_definitions(&field_def_lst);
-
   if (ctx->alloc_arrays(thd) ||
-      (resolve_type_refs && ctx->resolve_type_refs(thd, field_def_lst)) ||
       ctx->init_var_table(thd, field_def_lst) ||
       ctx->init_var_items(thd, field_def_lst))
   {
@@ -88,6 +83,39 @@ sp_rcontext *sp_rcontext::create(THD *thd,
 }
 
 
+bool Row_definition_list::
+       adjust_formal_params_to_actual_params(THD *thd, List<Item> *args)
+{
+  List_iterator<Spvar_definition> it(*this);
+  List_iterator<Item> it_args(*args);
+  DBUG_ASSERT(elements >= args->elements );
+  Spvar_definition *def;
+  Item *arg;
+  while ((def= it++) && (arg= it_args++))
+  {
+    if (def->type_handler()->adjust_spparam_type(def, arg))
+      return true;
+  }
+  return false;
+}
+
+
+bool Row_definition_list::
+       adjust_formal_params_to_actual_params(THD *thd,
+                                             Item **args, uint arg_count)
+{
+  List_iterator<Spvar_definition> it(*this);
+  DBUG_ASSERT(elements >= arg_count );
+  Spvar_definition *def;
+  for (uint i= 0; (def= it++) && (i < arg_count) ; i++)
+  {
+    if (def->type_handler()->adjust_spparam_type(def, args[i]))
+      return true;
+  }
+  return false;
+}
+
+
 bool sp_rcontext::alloc_arrays(THD *thd)
 {
   {
@@ -146,8 +174,7 @@ check_column_grant_for_type_ref(THD *thd, TABLE_LIST *table_list,
 /**
   This method implementation is very close to fill_schema_table_by_open().
 */
-bool sp_rcontext::resolve_type_ref(THD *thd, Column_definition *def,
-                                   Qualified_column_ident *ref)
+bool Qualified_column_ident::resolve_type_ref(THD *thd, Column_definition *def)
 {
   Open_tables_backup open_tables_state_backup;
   thd->reset_n_backup_open_tables_state(&open_tables_state_backup);
@@ -164,18 +191,18 @@ bool sp_rcontext::resolve_type_ref(THD *thd, Column_definition *def,
   // Make %TYPE variables see temporary tables that shadow permanent tables
   thd->temporary_tables= open_tables_state_backup.temporary_tables;
 
-  if ((table_list= lex.select_lex.add_table_to_list(thd, ref, NULL, 0,
+  if ((table_list= lex.select_lex.add_table_to_list(thd, this, NULL, 0,
                                                     TL_READ_NO_INSERT,
                                                     MDL_SHARED_READ)) &&
       !check_table_access(thd, SELECT_ACL, table_list, TRUE, UINT_MAX, FALSE) &&
       !open_tables_only_view_structure(thd, table_list,
                                        thd->mdl_context.has_locks()))
   {
-    if ((src= lex.query_tables->table->find_field_by_name(&ref->m_column)))
+    if ((src= lex.query_tables->table->find_field_by_name(&m_column)))
     {
       if (!(rc= check_column_grant_for_type_ref(thd, table_list,
-                                                ref->m_column.str,
-                                                ref->m_column.length)))
+                                                m_column.str,
+                                                m_column.length)))
       {
         *def= Column_definition(thd, src, NULL/*No defaults,no constraints*/);
         def->flags&= (uint) ~NOT_NULL_FLAG;
@@ -183,7 +210,7 @@ bool sp_rcontext::resolve_type_ref(THD *thd, Column_definition *def,
       }
     }
     else
-      my_error(ER_BAD_FIELD_ERROR, MYF(0), ref->m_column.str, ref->table.str);
+      my_error(ER_BAD_FIELD_ERROR, MYF(0), m_column.str, table.str);
   }
 
   lex.unit.cleanup();
@@ -200,9 +227,8 @@ bool sp_rcontext::resolve_type_ref(THD *thd, Column_definition *def,
      rec t1%ROWTYPE;
   It opens the table "t1" and copies its structure to %ROWTYPE variable.
 */
-bool sp_rcontext::resolve_table_rowtype_ref(THD *thd,
-                                            Row_definition_list &defs,
-                                            Table_ident *ref)
+bool Table_ident::resolve_table_rowtype_ref(THD *thd,
+                                            Row_definition_list &defs)
 {
   Open_tables_backup open_tables_state_backup;
   thd->reset_n_backup_open_tables_state(&open_tables_state_backup);
@@ -223,7 +249,7 @@ bool sp_rcontext::resolve_table_rowtype_ref(THD *thd,
   // Make %ROWTYPE variables see temporary tables that shadow permanent tables
   thd->temporary_tables= open_tables_state_backup.temporary_tables;
 
-  if ((table_list= lex.select_lex.add_table_to_list(thd, ref, NULL, 0,
+  if ((table_list= lex.select_lex.add_table_to_list(thd, this, NULL, 0,
                                                     TL_READ_NO_INSERT,
                                                     MDL_SHARED_READ)) &&
       !check_table_access(thd, SELECT_ACL, table_list, TRUE, UINT_MAX, FALSE) &&
@@ -261,14 +287,14 @@ bool sp_rcontext::resolve_table_rowtype_ref(THD *thd,
 }
 
 
-bool sp_rcontext::resolve_type_refs(THD *thd, List<Spvar_definition> &defs)
+bool Row_definition_list::resolve_type_refs(THD *thd)
 {
-  List_iterator<Spvar_definition> it(defs);
+  List_iterator<Spvar_definition> it(*this);
   Spvar_definition *def;
   while ((def= it++))
   {
     if (def->is_column_type_ref() &&
-        resolve_type_ref(thd, def, def->column_type_ref()))
+        def->column_type_ref()->resolve_type_ref(thd, def))
       return true;
   }
   return false;
@@ -300,7 +326,7 @@ bool sp_rcontext::init_var_items(THD *thd,
       Row_definition_list defs;
       Item_field_row *item= new (thd->mem_root) Item_field_row(thd, field);
       if (!(m_var_items[idx]= item) ||
-          resolve_table_rowtype_ref(thd, defs, def->table_rowtype_ref()) ||
+          def->table_rowtype_ref()->resolve_table_rowtype_ref(thd, defs) ||
           item->row_create_items(thd, &defs))
         return true;
     }
diff --git a/sql/sp_rcontext.h b/sql/sp_rcontext.h
index c4c80ac5a1f..1b1329c17f0 100644
--- a/sql/sp_rcontext.h
+++ b/sql/sp_rcontext.h
@@ -71,7 +71,7 @@ public:
   static sp_rcontext *create(THD *thd,
                              const sp_pcontext *root_parsing_ctx,
                              Field *return_value_fld,
-                             bool resolve_type_refs);
+                             Row_definition_list &defs);
 
   ~sp_rcontext();
 
@@ -337,12 +337,6 @@ private:
   /// @retval true on error.
   bool init_var_table(THD *thd, List<Spvar_definition> &defs);
 
-  bool resolve_type_refs(THD *, List<Spvar_definition> &defs);
-  bool resolve_type_ref(THD *thd, Column_definition *def,
-                                  Qualified_column_ident *ref);
-  bool resolve_table_rowtype_ref(THD *thd, Row_definition_list &defs,
-                                           Table_ident *ref);
-
   /// Create and initialize an Item-adapter (Item_field) for each SP-var field.
   ///
   /// param thd Thread handle.
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 2bbcf3cd436..7d4a34b6613 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -1081,6 +1081,10 @@ public:
   {
     return static_cast<uint32>(query_string.length());
   }
+  inline char *query_end() const
+  {
+    return query_string.str() + query_string.length();
+  }
   CHARSET_INFO *query_charset() const { return query_string.charset(); }
   void set_query_inner(const CSET_STRING &string_arg)
   {
@@ -5587,6 +5591,7 @@ public:
   {
     db= *db_name;
   }
+  bool resolve_table_rowtype_ref(THD *thd, Row_definition_list &defs);
 };
 
 
@@ -5610,6 +5615,7 @@ public:
    :Table_ident(thd, db, table, false),
     m_column(*column)
   { }
+  bool resolve_type_ref(THD *thd, Column_definition *def);
 };
 
 
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 16995f215e8..3bd72314714 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -6261,15 +6261,17 @@ Item *LEX::create_and_link_Item_trigger_field(THD *thd,
 
 
 Item_param *LEX::add_placeholder(THD *thd, const LEX_CSTRING *name,
-                                 uint pos_in_query, uint len_in_query)
+                                 const char *start, const char *end)
 {
   if (!parsing_options.allows_variable)
   {
     my_error(ER_VIEW_SELECT_VARIABLE, MYF(0));
     return NULL;
   }
+
+  Query_fragment pos(thd, sphead, start, end);
   Item_param *item= new (thd->mem_root) Item_param(thd, name,
-                                                   pos_in_query, len_in_query);
+                                                   pos.pos(), pos.length());
   if (!item || param_list.push_back(item, thd->mem_root))
   {
     my_error(ER_OUT_OF_RESOURCES, MYF(0));
@@ -6279,12 +6281,6 @@ Item_param *LEX::add_placeholder(THD *thd, const LEX_CSTRING *name,
 }
 
 
-const char *LEX::substatement_query(THD *thd) const
-{
-  return sphead ? sphead->m_tmp_query : thd->query();
-}
-
-
 bool LEX::add_signal_statement(THD *thd, const sp_condition_value *v)
 {
   Yacc_state *state= &thd->m_parser_state->m_yacc;
@@ -6338,8 +6334,8 @@ Item_splocal *LEX::create_item_spvar_row_field(THD *thd,
                                                const LEX_CSTRING *a,
                                                const LEX_CSTRING *b,
                                                sp_variable *spv,
-                                               uint pos_in_q,
-                                               uint length_in_q)
+                                               const char *start,
+                                               const char *end)
 {
   if (!parsing_options.allows_variable)
   {
@@ -6347,6 +6343,7 @@ Item_splocal *LEX::create_item_spvar_row_field(THD *thd,
     return NULL;
   }
 
+  Query_fragment pos(thd, sphead, start, end);
   Item_splocal *item;
   if (spv->field_def.is_table_rowtype_ref() ||
       spv->field_def.is_cursor_rowtype_ref())
@@ -6354,7 +6351,7 @@ Item_splocal *LEX::create_item_spvar_row_field(THD *thd,
     if (!(item= new (thd->mem_root)
                 Item_splocal_row_field_by_name(thd, a, b, spv->offset,
                                                MYSQL_TYPE_NULL,
-                                               pos_in_q, length_in_q)))
+                                               pos.pos(), pos.length())))
       return NULL;
   }
   else
@@ -6368,7 +6365,7 @@ Item_splocal *LEX::create_item_spvar_row_field(THD *thd,
                 Item_splocal_row_field(thd, a, b,
                                        spv->offset, row_field_offset,
                                        def->real_field_type(),
-                                       pos_in_q, length_in_q)))
+                                       pos.pos(), pos.length())))
       return NULL;
   }
 #ifdef DBUG_ASSERT_EXISTS
@@ -6463,14 +6460,14 @@ Item *LEX::create_item_func_setval(THD *thd, Table_ident *table_ident,
 Item *LEX::create_item_ident(THD *thd,
                              const LEX_CSTRING *a,
                              const LEX_CSTRING *b,
-                             uint pos_in_q, uint length_in_q)
+                             const char *start, const char *end)
 {
   sp_variable *spv;
   if (spcont && (spv= spcont->find_variable(a, false)) &&
       (spv->field_def.is_row() ||
        spv->field_def.is_table_rowtype_ref() ||
        spv->field_def.is_cursor_rowtype_ref()))
-    return create_item_spvar_row_field(thd, a, b, spv, pos_in_q, length_in_q);
+    return create_item_spvar_row_field(thd, a, b, spv, start, end);
 
   if ((thd->variables.sql_mode & MODE_ORACLE) && b->length == 7)
   {
@@ -6524,7 +6521,7 @@ Item *LEX::create_item_ident(THD *thd,
 
 Item *LEX::create_item_limit(THD *thd,
                              const LEX_CSTRING *a,
-                             uint pos_in_q, uint length_in_q)
+                             const char *start, const char *end)
 {
   sp_variable *spv;
   if (!spcont || !(spv= spcont->find_variable(a, false)))
@@ -6533,10 +6530,11 @@ Item *LEX::create_item_limit(THD *thd,
     return NULL;
   }
 
+  Query_fragment pos(thd, sphead, start, end);
   Item_splocal *item;
   if (!(item= new (thd->mem_root) Item_splocal(thd, a,
                                                spv->offset, spv->sql_type(),
-                                               pos_in_q, length_in_q)))
+                                               pos.pos(), pos.length())))
     return NULL;
 #ifdef DBUG_ASSERT_EXISTS
   item->m_sp= sphead;
@@ -6556,7 +6554,7 @@ Item *LEX::create_item_limit(THD *thd,
 Item *LEX::create_item_limit(THD *thd,
                              const LEX_CSTRING *a,
                              const LEX_CSTRING *b,
-                             uint pos_in_q, uint length_in_q)
+                             const char *start, const char *end)
 {
   sp_variable *spv;
   if (!spcont || !(spv= spcont->find_variable(a, false)))
@@ -6567,8 +6565,7 @@ Item *LEX::create_item_limit(THD *thd,
   // Qualified %TYPE variables are not possible
   DBUG_ASSERT(!spv->field_def.column_type_ref());
   Item_splocal *item;
-  if (!(item= create_item_spvar_row_field(thd, a, b, spv,
-                                          pos_in_q, length_in_q)))
+  if (!(item= create_item_spvar_row_field(thd, a, b, spv, start, end)))
     return NULL;
   if (item->type() != Item::INT_ITEM)
   {
@@ -6634,11 +6631,12 @@ Item *LEX::create_item_ident_nosp(THD *thd, LEX_CSTRING *name)
 
 
 Item *LEX::create_item_ident_sp(THD *thd, LEX_CSTRING *name,
-                                uint start_in_q,
-                                uint length_in_q)
+                                const char *start,
+                                const char *end)
 {
   sp_variable *spv;
   DBUG_ASSERT(spcont);
+  DBUG_ASSERT(sphead);
   if ((spv= spcont->find_variable(name, false)))
   {
     /* We're compiling a stored procedure and found a variable */
@@ -6648,17 +6646,18 @@ Item *LEX::create_item_ident_sp(THD *thd, LEX_CSTRING *name,
       return NULL;
     }
 
+    Query_fragment pos(thd, sphead, start, end);
     Item_splocal *splocal= spv->field_def.is_column_type_ref() ?
       new (thd->mem_root) Item_splocal_with_delayed_data_type(thd, name,
                                                               spv->offset,
-                                                              start_in_q,
-                                                              length_in_q) :
+                                                              pos.pos(),
+                                                              pos.length()) :
       spv->field_def.is_row() || spv->field_def.is_table_rowtype_ref() ?
       new (thd->mem_root) Item_splocal_row(thd, name, spv->offset,
-                                           start_in_q, length_in_q) :
+                                           pos.pos(), pos.length()) :
       new (thd->mem_root) Item_splocal(thd, name,
                                        spv->offset, spv->sql_type(),
-                                       start_in_q, length_in_q);
+                                       pos.pos(), pos.length());
     if (splocal == NULL)
       return NULL;
 #ifdef DBUG_ASSERT_EXISTS
@@ -6679,16 +6678,6 @@ Item *LEX::create_item_ident_sp(THD *thd, LEX_CSTRING *name,
 }
 
 
-Item *LEX::create_item_ident_sp(THD *thd, LEX_CSTRING *name,
-                                const char *start_in_q,
-                                const char *end_in_q)
-{
-  DBUG_ASSERT(sphead);
-  return create_item_ident_sp(thd, name, start_in_q - sphead->m_tmp_query,
-                                         end_in_q - start_in_q);
-}
-
-
 /**
   Generate instructions for:
     SET x.y= expr;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 245e995bc2d..5f1f4a397d4 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -2975,8 +2975,6 @@ public:
 
   void start(THD *thd);
 
-  const char *substatement_query(THD *thd) const;
-
   inline bool is_ps_or_view_context_analysis()
   {
     return (context_analysis_only &
@@ -3207,22 +3205,16 @@ public:
   bool sp_open_cursor(THD *thd, const LEX_CSTRING *name,
                       List<sp_assignment_lex> *parameters);
   Item_splocal *create_item_for_sp_var(LEX_CSTRING *name, sp_variable *spvar,
-                                       const char *start_in_q,
-                                       const char *end_in_q);
+                                       const char *start, const char *end);
 
   Item *create_item_ident_nosp(THD *thd, LEX_CSTRING *name);
   Item *create_item_ident_sp(THD *thd, LEX_CSTRING *name,
-                             uint start_in_q,
-                             uint length_in_q);
-  Item *create_item_ident_sp(THD *thd, LEX_CSTRING *name,
-                             const char *start_in_q,
-                             const char *end_in_q);
+                             const char *start, const char *end);
   Item *create_item_ident(THD *thd, LEX_CSTRING *name,
-                          const char *start_in_q,
-                          const char *end_in_q)
+                          const char *start, const char *end)
   {
     return sphead ?
-           create_item_ident_sp(thd, name, start_in_q, end_in_q) :
+           create_item_ident_sp(thd, name, start, end) :
            create_item_ident_nosp(thd, name);
   }
 
@@ -3249,15 +3241,15 @@ public:
       @param field      - the ROW variable field name
       @param spvar      - the variable that was previously found by name
                           using "var_name".
-      @pos_in_q         - position in the query (for binary log)
-      @length_in_q      - length in the query (for binary log)
+      @param start      - position in the query (for binary log)
+      @param end        - end in the query (for binary log)
   */
   Item_splocal *create_item_spvar_row_field(THD *thd,
                                             const LEX_CSTRING *var,
                                             const LEX_CSTRING *field,
                                             sp_variable *spvar,
-                                            uint pos_in_q,
-                                            uint length_in_q);
+                                            const char *start,
+                                            const char *end);
   /*
     Create an item from its qualified name.
     Depending on context, it can be either a ROW variable field,
@@ -3267,15 +3259,15 @@ public:
       @param thd         - THD, for mem_root
       @param a           - the first name
       @param b           - the second name
-      @param pos_in_q    - position in the query (for binary log)
-      @param length_in_q - length in the query (for binary log)
+      @param start       - position in the query (for binary log)
+      @param end         - end in the query (for binary log)
       @retval            - NULL on error, or a pointer to a new Item.
   */
   Item *create_item_ident(THD *thd,
                           const LEX_CSTRING *a,
                           const LEX_CSTRING *b,
-                          uint pos_in_q, uint length_in_q);
-
+                          const char *start,
+                          const char *end);
   /*
     Create an item from its qualified name.
     Depending on context, it can be a table field, a table field reference,
@@ -3314,23 +3306,24 @@ public:
     Create an item for a name in LIMIT clause: LIMIT var
       @param THD         - THD, for mem_root
       @param var_name    - the variable name
-      @param pos_in_q    - position in the query (for binary log)
-      @param length_in_q - length in the query (for binary log)
+      @param start       - position in the query (for binary log)
+      @param end         - end in the query (for binary log)
       @retval            - a new Item corresponding to the SP variable,
                            or NULL on error
                            (non in SP, unknown variable, wrong data type).
   */
   Item *create_item_limit(THD *thd,
                           const LEX_CSTRING *var_name,
-                          uint pos_in_q, uint length_in_q);
+                          const char *start,
+                          const char *end);
 
   /*
     Create an item for a qualified name in LIMIT clause: LIMIT var.field
       @param THD         - THD, for mem_root
       @param var_name    - the variable name
       @param field_name  - the variable field name
-      @param pos_in_q    - position in the query (for binary log)
-      @param length_in_q - length in the query (for binary log)
+      @param start       - start in the query (for binary log)
+      @param end         - end in the query (for binary log)
       @retval            - a new Item corresponding to the SP variable,
                            or NULL on error
                            (non in SP, unknown variable, unknown ROW field,
@@ -3339,7 +3332,8 @@ public:
   Item *create_item_limit(THD *thd,
                           const LEX_CSTRING *var_name,
                           const LEX_CSTRING *field_name,
-                          uint pos_in_q, uint length_in_q);
+                          const char *start,
+                          const char *end);
 
   Item *make_item_func_replace(THD *thd, Item *org, Item *find, Item *replace);
 
@@ -3422,16 +3416,7 @@ public:
   bool sp_push_goto_label(THD *thd, const LEX_CSTRING *label_name);
 
   Item_param *add_placeholder(THD *thd, const LEX_CSTRING *name,
-                              uint pos_in_query, uint len_in_query);
-  Item_param *add_placeholder(THD *thd, const LEX_CSTRING *name,
-                              const char *start, const char *end)
-  {
-    size_t pos= start - substatement_query(thd);
-    size_t len= end - start;
-    DBUG_ASSERT(pos < UINT_MAX32);
-    DBUG_ASSERT(len < UINT_MAX32);
-    return add_placeholder(thd, name, (uint) pos, (uint) len);
-  }
+                              const char *start, const char *end);
 
   /* Integer range FOR LOOP methods */
   sp_variable *sp_add_for_loop_variable(THD *thd, const LEX_CSTRING *name,
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index df79a55f8e6..3ea364f2fba 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -2423,6 +2423,31 @@ Field *Type_handler_set::make_table_field(const LEX_CSTRING *name,
 
 /*************************************************************************/
 
+/*
+   If length is not specified for a varchar parameter, set length to the
+   maximum length of the actual argument. Goals are:
+   - avoid to allocate too much unused memory for m_var_table
+   - allow length check inside the callee rather than during copy of
+     returned values in output variables.
+   - allow varchar parameter size greater than 4000
+   Default length has been stored in "decimal" member during parse.
+*/
+bool Type_handler_varchar::adjust_spparam_type(Spvar_definition *def,
+                                               Item *from) const
+{
+  if (def->decimals)
+  {
+    uint def_max_char_length= MAX_FIELD_VARCHARLENGTH / def->charset->mbmaxlen;
+    uint arg_max_length= from->max_char_length();
+    set_if_smaller(arg_max_length, def_max_char_length);
+    def->length= arg_max_length > 0 ? arg_max_length : def->decimals;
+    def->create_length_to_internal_length_string();
+  }
+  return false;
+}
+
+/*************************************************************************/
+
 uint32 Type_handler_decimal_result::max_display_length(const Item *item) const
 {
   return item->max_length;
diff --git a/sql/sql_type.h b/sql/sql_type.h
index d71c48fb7a2..525a05b71e6 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -65,6 +65,7 @@ class in_vector;
 class Type_handler_hybrid_field_type;
 class Sort_param;
 class Arg_comparator;
+class Spvar_definition;
 struct st_value;
 class Protocol;
 class handler;
@@ -688,6 +689,10 @@ public:
   type_handler_adjusted_to_max_octet_length(uint max_octet_length,
                                             CHARSET_INFO *cs) const
   { return this; }
+  virtual bool adjust_spparam_type(Spvar_definition *def, Item *from) const
+  {
+    return false;
+  }
   virtual ~Type_handler() {}
   /**
     Determines MariaDB traditional data types that always present
@@ -2523,6 +2528,7 @@ public:
                           const Record_addr &addr,
                           const Type_all_attributes &attr,
                           TABLE *table) const;
+  bool adjust_spparam_type(Spvar_definition *def, Item *from) const;
 };
 
 
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index e4555ddeb11..a801251f5df 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -11938,9 +11938,7 @@ limit_option:
           LEX *lex= thd->lex;
           Lex_input_stream *lip= & thd->m_parser_state->m_lip;
           if (!($$= lex->create_item_limit(thd, &$1,
-                                           $1.m_pos -
-                                           lex->substatement_query(thd),
-                                           lip->get_tok_end() - $1.m_pos)))
+                                           $1.m_pos, lip->get_tok_end())))
             MYSQL_YYABORT;
         }
         | ident_with_tok_start '.' ident
@@ -11948,9 +11946,7 @@ limit_option:
           LEX *lex= thd->lex;
           Lex_input_stream *lip= & thd->m_parser_state->m_lip;
           if (!($$= lex->create_item_limit(thd, &$1, &$3,
-                                           $1.m_pos -
-                                           lex->substatement_query(thd),
-                                           lip->get_ptr() - $1.m_pos)))
+                                           $1.m_pos, lip->get_ptr())))
             MYSQL_YYABORT;
         }
         | param_marker
@@ -14257,9 +14253,7 @@ simple_ident:
           {
             LEX *lex= thd->lex;
             if (!($$= lex->create_item_ident(thd, &$1, &$3,
-                                             $1.m_pos -
-                                             lex->substatement_query(thd),
-                                             YYLIP->get_tok_end() - $1.m_pos)))
+                                             $1.m_pos, YYLIP->get_tok_end())))
               MYSQL_YYABORT;
           }
         ;
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index a430512f3a1..c3a84fcc482 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -1055,8 +1055,6 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 
 %type <const_simple_string>
         field_length opt_field_length opt_field_length_default_1
-        opt_field_length_default_sp_param_varchar
-        opt_field_length_default_sp_param_char
 
 %type <string>
         text_string hex_or_bin_String opt_gconcat_separator
@@ -1220,6 +1218,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 %type <Lex_cast_type> cast_type cast_type_numeric cast_type_temporal
 
 %type <Lex_length_and_dec> precision opt_precision float_options
+        opt_field_length_default_sp_param_varchar
+        opt_field_length_default_sp_param_char
 
 %type <symbol> keyword keyword_sp
                keyword_directly_assignable
@@ -6550,9 +6550,11 @@ opt_field_length_default_1:
 
 
 /*
-  In sql_mode=ORACLE, a VARCHAR with no length is used
-  in SP parameters and return values and it's translated to VARCHAR(4000),
-  where 4000 is the maximum possible size for VARCHAR.
+  In sql_mode=ORACLE, real size of VARCHAR and CHAR with no length
+  in SP parameters is fixed at runtime with the length of real args.
+  Let's translate VARCHAR to VARCHAR(4000) for return value.
+
+  Since Oracle 9, maximum size for VARCHAR in PL/SQL is 32767.
 
   In MariaDB the limit for VARCHAR is 65535 bytes.
   We could translate VARCHAR with no length to VARCHAR(65535), but
@@ -6563,17 +6565,14 @@ opt_field_length_default_1:
   the maximum possible length in characters in case of mbmaxlen=4
   (e.g. utf32, utf16, utf8mb4). However, we'll have character sets with
   mbmaxlen=5 soon (e.g. gb18030).
-
-  Let's translate VARCHAR to VARCHAR(4000), which covert all possible Oracle
-  values.
 */
 opt_field_length_default_sp_param_varchar:
-          /* empty */  { $$= (char*) "4000"; }
-        | field_length { $$= $1; }
+          /* empty */  { $$.set("4000", "4000"); }
+        | field_length { $$.set($1, NULL); }
 
 opt_field_length_default_sp_param_char:
-          /* empty */  { $$= (char*) "2000"; }
-        | field_length { $$= $1; }
+          /* empty */  { $$.set("2000", "2000"); }
+        | field_length { $$.set($1, NULL); }
 
 opt_precision:
           /* empty */    { $$.set(0, 0); }
@@ -11984,9 +11983,7 @@ limit_option:
           LEX *lex= thd->lex;
           Lex_input_stream *lip= & thd->m_parser_state->m_lip;
           if (!($$= lex->create_item_limit(thd, &$1,
-                                           $1.m_pos -
-                                           lex->substatement_query(thd),
-                                           lip->get_tok_end() - $1.m_pos)))
+                                           $1.m_pos, lip->get_tok_end())))
             MYSQL_YYABORT;
         }
         | ident_with_tok_start '.' ident
@@ -11994,9 +11991,7 @@ limit_option:
           LEX *lex= thd->lex;
           Lex_input_stream *lip= & thd->m_parser_state->m_lip;
           if (!($$= lex->create_item_limit(thd, &$1, &$3,
-                                           $1.m_pos -
-                                           lex->substatement_query(thd),
-                                           lip->get_ptr() - $1.m_pos)))
+                                           $1.m_pos, lip->get_ptr())))
             MYSQL_YYABORT;
         }
         | param_marker
@@ -14322,9 +14317,7 @@ simple_ident:
           {
             LEX *lex= thd->lex;
             if (!($$= lex->create_item_ident(thd, &$1, &$3,
-                                             $1.m_pos -
-                                             lex->substatement_query(thd),
-                                             YYLIP->get_tok_end() - $1.m_pos)))
+                                             $1.m_pos, YYLIP->get_tok_end())))
               MYSQL_YYABORT;
           }
         ;
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index bb5a65afd09..6ae9a7e8f2e 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -2360,10 +2360,10 @@ export sys_var *Sys_old_passwords_ptr= &Sys_old_passwords; // for sql_acl.cc
 static Sys_var_ulong Sys_open_files_limit(
        "open_files_limit",
        "If this is not 0, then mysqld will use this value to reserve file "
-       "descriptors to use with setrlimit(). If this value is 0 then mysqld "
-       "will reserve max_connections*5 or max_connections + table_cache*2 "
-       "(whichever is larger) number of file descriptors",
-       READ_ONLY GLOBAL_VAR(open_files_limit), CMD_LINE(REQUIRED_ARG),
+       "descriptors to use with setrlimit(). If this value is 0 or autoset "
+       "then mysqld will reserve max_connections*5 or max_connections + "
+       "table_cache*2 (whichever is larger) number of file descriptors",
+       AUTO_SET READ_ONLY GLOBAL_VAR(open_files_limit), CMD_LINE(REQUIRED_ARG),
        VALID_RANGE(0, OS_FILE_LIMIT), DEFAULT(0), BLOCK_SIZE(1));
 
 /// @todo change to enum