MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:Alexander Nozdrin Date:December 7 2005 2:01pm
Subject:bk commit into 5.0 tree (anozdrin:1.1982) BUG#15148
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of alik. When alik does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1982 05/12/07 17:01:17 anozdrin@stripped +41 -0
  Patch for WL#2894: Make stored routine variables work
  according to the standard.
  
  The idea is to use Field-classes to implement stored routines
  variables. Also, we should provide facade to Item-hierarchy
  by Item_field class (it is necessary, since SRVs take part
  in expressions).
  
  The patch fixes the following bugs:
    - BUG#8702: Stored Procedures: No Error/Warning shown for inappropriate data 
      type matching; 
   
    - BUG#8768: Functions: For any unsigned data type, -ve values can be passed 
      and returned; 
   
    - BUG#8769: Functions: For Int datatypes, out of range values can be passed 
      and returned; 
   
    - BUG#9078: STORED PROCDURE: Decimal digits are not displayed when we use 
      DECIMAL datatype; 
   
    - BUG#9572: Stored procedures: variable type declarations ignored; 
   
    - BUG#12903: upper function does not work inside a function; 
   
    - BUG#13705: parameters to stored procedures are not verified; 
   
    - BUG#13808: ENUM type stored procedure parameter accepts non-enumerated
      data; 
   
    - BUG#13909: Varchar Stored Procedure Parameter always BINARY string (ignores 
      CHARACTER SET); 
   
    - BUG#14161: Stored procedure cannot retrieve bigint unsigned;
  
    - BUG#14188: BINARY variables have no 0x00 padding;
  
    - BUG#15148: Stored procedure variables accept non-scalar values;

  mysql-test/sp-vars.test
    1.1 05/12/07 17:01:09 anozdrin@stripped +1273 -0
    A new test for checking SP-vars functionality.

  mysql-test/r/sp-vars.result
    1.1 05/12/07 17:01:09 anozdrin@stripped +1077 -0
    Result file for the SP-vars test.

  mysql-test/sp-vars.test
    1.0 05/12/07 17:01:09 anozdrin@stripped +0 -0
    BitKeeper file /home/alik/Documents/AllProgs/MySQL/devel/5.0-sp-vars-merge-2/mysql-test/sp-vars.test

  mysql-test/r/sp-vars.result
    1.0 05/12/07 17:01:09 anozdrin@stripped +0 -0
    BitKeeper file /home/alik/Documents/AllProgs/MySQL/devel/5.0-sp-vars-merge-2/mysql-test/r/sp-vars.result

  mysql-test/include/sp-vars.inc
    1.1 05/12/07 17:01:08 anozdrin@stripped +122 -0
    The definitions of common-procedures, which are created
    under different circumstances.

  sql/sql_yacc.yy
    1.444 05/12/07 17:01:08 anozdrin@stripped +129 -100
    Provide an instance of create_field for each SP-var.

  sql/sql_trigger.cc
    1.36 05/12/07 17:01:08 anozdrin@stripped +3 -3
    Use boolean constants for boolean type instead of numerical ones.

  sql/sql_select.h
    1.104 05/12/07 17:01:08 anozdrin@stripped +0 -1
    Move create_virtual_tmp_table() out of sql_select.h.

  sql/sql_select.cc
    1.380 05/12/07 17:01:08 anozdrin@stripped +13 -0
    Take care of BLOB columns in create_virtual_tmp_table().

  sql/sql_parse.cc
    1.524 05/12/07 17:01:08 anozdrin@stripped +4 -324
    Extract create_field::init() to initialize an existing
    instance of create_field from new_create_field().

  sql/sql_class.h
    1.278 05/12/07 17:01:08 anozdrin@stripped +1 -1
    Polishing.

  sql/sql_class.cc
    1.227 05/12/07 17:01:08 anozdrin@stripped +5 -5
    - Reflect Item_splocal ctor changes;
    - Item_splocal::get_offset() has been renamed to get_var_idx().

  sql/sp_rcontext.h
    1.29 05/12/07 17:01:08 anozdrin@stripped +77 -65
    - Change rules to assign an index of SP-variable: use
      transparent index;
    - Use a tmp virtual table to store SP-vars instead of Items;
    - Provide operations to work with CASE expresion.

  sql/sp_rcontext.cc
    1.36 05/12/07 17:01:08 anozdrin@stripped +256 -50
    - Change rules to assign an index of SP-variable: use
      transparent index;
    - Use a tmp virtual table to store SP-vars instead of Items;
    - Provide operations to work with CASE expresion.

  sql/sp_pcontext.h
    1.25 05/12/07 17:01:08 anozdrin@stripped +84 -14
    - Change rules to assign an index of SP-variable: use
      transparent index;
    - Add an operation to retrieve a list of defined SP-vars
      from the processing context recursively.

  sql/sp_pcontext.cc
    1.28 05/12/07 17:01:08 anozdrin@stripped +55 -20
    - Change rules to assign an index of SP-variable: use
      transparent index;
    - Add an operation to retrieve a list of defined SP-vars
      from the processing context recursively.

  sql/sp_head.h
    1.78 05/12/07 17:01:08 anozdrin@stripped +45 -16
    - Add a function to map enum_field_types to Item::Type;
    - Add sp_instr_push_case_expr instruction -- an instruction
      to push CASE expression into the active running context;
    - Add sp_instr_pop_case_expr instruction -- an instruction
      to pop CASE expression from the active running context;
    - Adapt the SP-execution code to using Fields instead of Items
      for SP-vars;
    - Use create_field structure for field description instead of
      a set of members.

  mysql-test/include/sp-vars.inc
    1.0 05/12/07 17:01:08 anozdrin@stripped +0 -0
    BitKeeper file /home/alik/Documents/AllProgs/MySQL/devel/5.0-sp-vars-merge-2/mysql-test/include/sp-vars.inc

  sql/sp_head.cc
    1.198 05/12/07 17:01:07 anozdrin@stripped +519 -415
    - Add a function to map enum_field_types to Item::Type;
    - Add sp_instr_push_case_expr instruction -- an instruction
      to push CASE expression into the active running context;
    - Add sp_instr_pop_case_expr instruction -- an instruction
      to pop CASE expression from the active running context;
    - Adapt the SP-execution code to using Fields instead of Items
      for SP-vars;
    - Use create_field structure for field description instead of
      a set of members.

  sql/sp.cc
    1.101 05/12/07 17:01:07 anozdrin@stripped +1 -1
    Use create_result_field() instead of make_field().

  sql/mysql_priv.h
    1.373 05/12/07 17:01:07 anozdrin@stripped +2 -1
    Move create_virtual_tmp_table() out of sql_select.h.

  sql/item_func.h
    1.134 05/12/07 17:01:07 anozdrin@stripped +2 -2
    Pass the Field (instead of Item) for the return value of
    a function to the function execution routine.

  sql/item_func.cc
    1.272 05/12/07 17:01:07 anozdrin@stripped +32 -19
    Pass the Field (instead of Item) for the return value of
    a function to the function execution routine.

  sql/item.h
    1.185 05/12/07 17:01:07 anozdrin@stripped +163 -76
    - Introduce a new class: Item_sp_variable -- a base class
      of stored-routine-variables classes;
    - Introduce Item_case_expr -- an Item, which is used to access
      to the expression of CASE statement;

  sql/item.cc
    1.206 05/12/07 17:01:07 anozdrin@stripped +114 -65
    - Introduce a new class: Item_sp_variable -- a base class
      of stored-routine-variables classes;
    - Introduce Item_case_expr -- an Item, which is used to access
      to the expression of CASE statement;

  sql/field.h
    1.170 05/12/07 17:01:07 anozdrin@stripped +18 -0
    Extract create_field::init() to initialize an existing
    instance of create_field from new_create_field().

  sql/field.cc
    1.294 05/12/07 17:01:07 anozdrin@stripped +347 -0
    Extract create_field::init() to initialize an existing
    instance of create_field from new_create_field().

  mysql-test/t/type_newdecimal-big.test
    1.2 05/12/07 17:01:07 anozdrin@stripped +25 -6
    Update type specification so that the variables
    can contain the large values used in the test.

  mysql-test/t/sp.test
    1.164 05/12/07 17:01:07 anozdrin@stripped +6 -3
    Non-scalar values prohibited for assignment to SP-vars;
    polishing.

  mysql-test/t/sp-dynamic.test
    1.3 05/12/07 17:01:07 anozdrin@stripped +6 -0
    Add cleanup statements.

  mysql-test/t/sp-big.test
    1.3 05/12/07 17:01:07 anozdrin@stripped +3 -0
    Add cleanup statement.

  mysql-test/t/skip_name_resolve.test
    1.5 05/12/07 17:01:07 anozdrin@stripped +1 -1
    Ignore columns with unpredictable values.

  mysql-test/t/show_check.test
    1.55 05/12/07 17:01:07 anozdrin@stripped +1 -0
    Drop our test database to not affect this test if some test
    left it cause of failure.

  mysql-test/t/schema.test
    1.2 05/12/07 17:01:07 anozdrin@stripped +6 -0
    Drop our test database to not affect this test if some test
    left it cause of failure.

  mysql-test/t/ctype_ujis.test
    1.17 05/12/07 17:01:06 anozdrin@stripped +1 -1
    Explicitly specify correct charset.

  mysql-test/r/type_newdecimal-big.result
    1.2 05/12/07 17:01:06 anozdrin@stripped +23 -8
    Update result file.

  mysql-test/r/sum_distinct-big.result
    1.2 05/12/07 17:01:06 anozdrin@stripped +14 -15
    Update result file.

  mysql-test/r/sp.result
    1.173 05/12/07 17:01:06 anozdrin@stripped +26 -15
    Update result file.

  mysql-test/r/sp-dynamic.result
    1.3 05/12/07 17:01:06 anozdrin@stripped +2 -0
    Add cleanup statements.

  mysql-test/r/sp-big.result
    1.3 05/12/07 17:01:06 anozdrin@stripped +1 -0
    Add cleanup statement.

  mysql-test/r/skip_name_resolve.result
    1.3 05/12/07 17:01:06 anozdrin@stripped +2 -2
    Ignore columns with unpredictable values.

  mysql-test/r/show_check.result
    1.76 05/12/07 17:01:06 anozdrin@stripped +1 -0
    Drop our test database to not affect this test if some test
    left it cause of failure.

  mysql-test/r/schema.result
    1.3 05/12/07 17:01:06 anozdrin@stripped +1 -0
    Drop our test database to not affect this test if some test
    left it cause of failure.

  mysql-test/r/ctype_ujis.result
    1.16 05/12/07 17:01:06 anozdrin@stripped +1 -1
    Explicitly specify correct charset.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	anozdrin
# Host:	station.home
# Root:	/home/alik/Documents/AllProgs/MySQL/devel/5.0-sp-vars-merge-2

--- 1.293/sql/field.cc	2005-11-29 17:06:42 +03:00
+++ 1.294/sql/field.cc	2005-12-07 17:01:07 +03:00
@@ -6748,7 +6748,10 @@
 {
   flags|= BLOB_FLAG;
   if (table)
+  {
     table->s->blob_fields++;
+    /* TODO: why do not fill table->s->blob_field array here? */
+  }
 }
 
 
@@ -8269,6 +8272,350 @@
               ((decimals & FIELDFLAG_MAX_DEC) << FIELDFLAG_DEC_SHIFT) |
               (maybe_null ? FIELDFLAG_MAYBE_NULL : 0) |
               (is_unsigned ? 0 : FIELDFLAG_DECIMAL));
+}
+
+
+/*
+  Initialize field definition for create
+
+  SYNOPSIS
+    thd                   Thread handle
+    fld_name              Field name
+    fld_type              Field type
+    fld_length            Field length
+    fld_decimals          Decimal (if any)
+    fld_type_modifier     Additional type information
+    fld_default_value     Field default value (if any)
+    fld_on_update_value   The value of ON UPDATE clause
+    fld_comment           Field comment
+    fld_change            Field change
+    fld_interval_list     Interval list (if any)
+    fld_charset           Field charset
+    fld_geom_type         Field geometry type (if any)
+
+  RETURN
+    FALSE on success
+    TRUE  on error
+*/
+
+bool create_field::init(THD *thd, char *fld_name, enum_field_types fld_type,
+                        char *fld_length, char *fld_decimals,
+                        uint fld_type_modifier, Item *fld_default_value,
+                        Item *fld_on_update_value, LEX_STRING *fld_comment,
+                        char *fld_change, List<String> *fld_interval_list,
+                        CHARSET_INFO *fld_charset, uint fld_geom_type)
+{
+  uint sign_len, allowed_type_modifier= 0;
+  ulong max_field_charlength= MAX_FIELD_CHARLENGTH;
+
+  DBUG_ENTER("create_field::init()");
+  
+  field= 0;
+  field_name= fld_name;
+  def= fld_default_value;
+  flags= fld_type_modifier;
+  unireg_check= (fld_type_modifier & AUTO_INCREMENT_FLAG ?
+                 Field::NEXT_NUMBER : Field::NONE);
+  decimals= fld_decimals ? (uint)atoi(fld_decimals) : 0;
+  if (decimals >= NOT_FIXED_DEC)
+  {
+    my_error(ER_TOO_BIG_SCALE, MYF(0), decimals, fld_name,
+             NOT_FIXED_DEC-1);
+    DBUG_RETURN(TRUE);
+  }
+
+  sql_type= fld_type;
+  length= 0;
+  change= fld_change;
+  interval= 0;
+  pack_length= key_length= 0;
+  charset= fld_charset;
+  geom_type= (Field::geometry_type) fld_geom_type;
+  interval_list.empty();
+
+  comment= *fld_comment;
+  /*
+    Set flag if this field doesn't have a default value
+  */
+  if (!fld_default_value && !(fld_type_modifier & AUTO_INCREMENT_FLAG) &&
+      (fld_type_modifier & NOT_NULL_FLAG) && fld_type != FIELD_TYPE_TIMESTAMP)
+    flags|= NO_DEFAULT_VALUE_FLAG;
+
+  if (fld_length && !(length= (uint) atoi(fld_length)))
+    fld_length= 0; /* purecov: inspected */
+  sign_len= fld_type_modifier & UNSIGNED_FLAG ? 0 : 1;
+
+  switch (fld_type) {
+  case FIELD_TYPE_TINY:
+    if (!fld_length)
+      length= MAX_TINYINT_WIDTH+sign_len;
+    allowed_type_modifier= AUTO_INCREMENT_FLAG;
+    break;
+  case FIELD_TYPE_SHORT:
+    if (!fld_length)
+      length= MAX_SMALLINT_WIDTH+sign_len;
+    allowed_type_modifier= AUTO_INCREMENT_FLAG;
+    break;
+  case FIELD_TYPE_INT24:
+    if (!fld_length)
+      length= MAX_MEDIUMINT_WIDTH+sign_len;
+    allowed_type_modifier= AUTO_INCREMENT_FLAG;
+    break;
+  case FIELD_TYPE_LONG:
+    if (!fld_length)
+      length= MAX_INT_WIDTH+sign_len;
+    allowed_type_modifier= AUTO_INCREMENT_FLAG;
+    break;
+  case FIELD_TYPE_LONGLONG:
+    if (!fld_length)
+      length= MAX_BIGINT_WIDTH;
+    allowed_type_modifier= AUTO_INCREMENT_FLAG;
+    break;
+  case FIELD_TYPE_NULL:
+    break;
+  case FIELD_TYPE_NEWDECIMAL:
+    if (!fld_length && !decimals)
+      length= 10;
+    if (length > DECIMAL_MAX_PRECISION)
+    {
+      my_error(ER_TOO_BIG_PRECISION, MYF(0), length, fld_name,
+               DECIMAL_MAX_PRECISION);
+      DBUG_RETURN(TRUE);
+    }
+    if (length < decimals)
+    {
+      my_error(ER_M_BIGGER_THAN_D, MYF(0), fld_name);
+      DBUG_RETURN(TRUE);
+    }
+    length=
+      my_decimal_precision_to_length(length, decimals,
+                                     fld_type_modifier & UNSIGNED_FLAG);
+    pack_length=
+      my_decimal_get_binary_size(length, decimals);
+    break;
+  case MYSQL_TYPE_VARCHAR:
+    /*
+      Long VARCHAR's are automaticly converted to blobs in mysql_prepare_table
+      if they don't have a default value
+    */
+    max_field_charlength= MAX_FIELD_VARCHARLENGTH;
+    break;
+  case MYSQL_TYPE_STRING:
+    break;
+  case FIELD_TYPE_BLOB:
+  case FIELD_TYPE_TINY_BLOB:
+  case FIELD_TYPE_LONG_BLOB:
+  case FIELD_TYPE_MEDIUM_BLOB:
+  case FIELD_TYPE_GEOMETRY:
+    if (fld_default_value)
+    {
+      /* Allow empty as default value. */
+      String str,*res;
+      res= fld_default_value->val_str(&str);
+      if (res->length())
+      {
+        my_error(ER_BLOB_CANT_HAVE_DEFAULT, MYF(0),
+                 fld_name); /* purecov: inspected */
+        DBUG_RETURN(TRUE);
+      }
+      def= 0;
+    }
+    flags|= BLOB_FLAG;
+    break;
+  case FIELD_TYPE_YEAR:
+    if (!fld_length || length != 2)
+      length= 4; /* Default length */
+    flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
+    break;
+  case FIELD_TYPE_FLOAT:
+    /* change FLOAT(precision) to FLOAT or DOUBLE */
+    allowed_type_modifier= AUTO_INCREMENT_FLAG;
+    if (fld_length && !fld_decimals)
+    {
+      uint tmp_length= length;
+      if (tmp_length > PRECISION_FOR_DOUBLE)
+      {
+        my_error(ER_WRONG_FIELD_SPEC, MYF(0), fld_name);
+        DBUG_RETURN(TRUE);
+      }
+      else if (tmp_length > PRECISION_FOR_FLOAT)
+      {
+        sql_type= FIELD_TYPE_DOUBLE;
+        length= DBL_DIG+7; /* -[digits].E+### */
+      }
+      else
+        length= FLT_DIG+6; /* -[digits].E+## */
+      decimals= NOT_FIXED_DEC;
+      break;
+    }
+    if (!fld_length && !fld_decimals)
+    {
+      length=  FLT_DIG+6;
+      decimals= NOT_FIXED_DEC;
+    }
+    if (length < decimals &&
+        decimals != NOT_FIXED_DEC)
+    {
+      my_error(ER_M_BIGGER_THAN_D, MYF(0), fld_name);
+      DBUG_RETURN(TRUE);
+    }
+    break;
+  case FIELD_TYPE_DOUBLE:
+    allowed_type_modifier= AUTO_INCREMENT_FLAG;
+    if (!fld_length && !fld_decimals)
+    {
+      length= DBL_DIG+7;
+      decimals= NOT_FIXED_DEC;
+    }
+    if (length < decimals &&
+        decimals != NOT_FIXED_DEC)
+    {
+      my_error(ER_M_BIGGER_THAN_D, MYF(0), fld_name);
+      DBUG_RETURN(TRUE);
+    }
+    break;
+  case FIELD_TYPE_TIMESTAMP:
+    if (!fld_length)
+      length= 14;  /* Full date YYYYMMDDHHMMSS */
+    else if (length != 19)
+    {
+      /*
+        We support only even TIMESTAMP lengths less or equal than 14
+        and 19 as length of 4.1 compatible representation.
+      */
+      length= ((length+1)/2)*2; /* purecov: inspected */
+      length= min(length,14); /* purecov: inspected */
+    }
+    flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
+    if (fld_default_value)
+    {
+      /* Grammar allows only NOW() value for ON UPDATE clause */
+      if (fld_default_value->type() == Item::FUNC_ITEM && 
+          ((Item_func*)fld_default_value)->functype() == Item_func::NOW_FUNC)
+      {
+        unireg_check= (fld_on_update_value ? Field::TIMESTAMP_DNUN_FIELD:
+                                             Field::TIMESTAMP_DN_FIELD);
+        /*
+          We don't need default value any longer moreover it is dangerous.
+          Everything handled by unireg_check further.
+        */
+        def= 0;
+      }
+      else
+        unireg_check= (fld_on_update_value ? Field::TIMESTAMP_UN_FIELD:
+                                             Field::NONE);
+    }
+    else
+    {
+      /*
+        If we have default TIMESTAMP NOT NULL column without explicit DEFAULT
+        or ON UPDATE values then for the sake of compatiblity we should treat
+        this column as having DEFAULT NOW() ON UPDATE NOW() (when we don't
+        have another TIMESTAMP column with auto-set option before this one)
+        or DEFAULT 0 (in other cases).
+        So here we are setting TIMESTAMP_OLD_FIELD only temporary, and will
+        replace this value by TIMESTAMP_DNUN_FIELD or NONE later when
+        information about all TIMESTAMP fields in table will be availiable.
+
+        If we have TIMESTAMP NULL column without explicit DEFAULT value
+        we treat it as having DEFAULT NULL attribute.
+      */
+      unireg_check= (fld_on_update_value ? Field::TIMESTAMP_UN_FIELD :
+                     (flags & NOT_NULL_FLAG ? Field::TIMESTAMP_OLD_FIELD :
+                                              Field::NONE));
+    }
+    break;
+  case FIELD_TYPE_DATE:
+    /* Old date type. */
+    if (protocol_version != PROTOCOL_VERSION-1)
+      sql_type= FIELD_TYPE_NEWDATE;
+    /* fall trough */
+  case FIELD_TYPE_NEWDATE:
+    length= 10;
+    break;
+  case FIELD_TYPE_TIME:
+    length= 10;
+    break;
+  case FIELD_TYPE_DATETIME:
+    length= 19;
+    break;
+  case FIELD_TYPE_SET:
+    {
+      if (fld_interval_list->elements > sizeof(longlong)*8)
+      {
+        my_error(ER_TOO_BIG_SET, MYF(0), fld_name); /* purecov: inspected */
+        DBUG_RETURN(TRUE);
+      }
+      pack_length= get_set_pack_length(fld_interval_list->elements);
+
+      List_iterator<String> it(*fld_interval_list);
+      String *tmp;
+      while ((tmp= it++))
+        interval_list.push_back(tmp);
+      /*
+        Set fake length to 1 to pass the below conditions.
+        Real length will be set in mysql_prepare_table()
+        when we know the character set of the column
+      */
+      length= 1;
+      break;
+    }
+  case FIELD_TYPE_ENUM:
+    {
+      /* Should be safe. */
+      pack_length= get_enum_pack_length(fld_interval_list->elements);
+
+      List_iterator<String> it(*fld_interval_list);
+      String *tmp;
+      while ((tmp= it++))
+        interval_list.push_back(tmp);
+      length= 1; /* See comment for FIELD_TYPE_SET above. */
+      break;
+   }
+  case MYSQL_TYPE_VAR_STRING:
+    DBUG_ASSERT(0);  /* Impossible. */
+    break;
+  case MYSQL_TYPE_BIT:
+    {
+      if (!fld_length)
+        length= 1;
+      if (length > MAX_BIT_FIELD_LENGTH)
+      {
+        my_error(ER_TOO_BIG_DISPLAYWIDTH, MYF(0), fld_name,
+                 MAX_BIT_FIELD_LENGTH);
+        DBUG_RETURN(TRUE);
+      }
+      pack_length= (length + 7) / 8;
+      break;
+    }
+  case FIELD_TYPE_DECIMAL:
+    DBUG_ASSERT(0); /* Was obsolete */
+  }
+
+  if (!(flags & BLOB_FLAG) &&
+      ((length > max_field_charlength && fld_type != FIELD_TYPE_SET &&
+        fld_type != FIELD_TYPE_ENUM &&
+        (fld_type != MYSQL_TYPE_VARCHAR || fld_default_value)) ||
+       (!length &&
+        fld_type != MYSQL_TYPE_STRING &&
+        fld_type != MYSQL_TYPE_VARCHAR && fld_type != FIELD_TYPE_GEOMETRY)))
+  {
+    my_error((fld_type == MYSQL_TYPE_VAR_STRING ||
+              fld_type == MYSQL_TYPE_VARCHAR ||
+              fld_type == MYSQL_TYPE_STRING) ?  ER_TOO_BIG_FIELDLENGTH :
+                                                ER_TOO_BIG_DISPLAYWIDTH,
+              MYF(0),
+              fld_name, max_field_charlength); /* purecov: inspected */
+    DBUG_RETURN(TRUE);
+  }
+  fld_type_modifier&= AUTO_INCREMENT_FLAG;
+  if ((~allowed_type_modifier) & fld_type_modifier)
+  {
+    my_error(ER_WRONG_FIELD_SPEC, MYF(0), fld_name);
+    DBUG_RETURN(TRUE);
+  }
+
+  DBUG_RETURN(FALSE); /* success */
 }
 
 

--- 1.169/sql/field.h	2005-10-31 09:18:22 +03:00
+++ 1.170/sql/field.h	2005-12-07 17:01:07 +03:00
@@ -130,7 +130,19 @@
             null_bit == field->null_bit);
   }
   virtual bool eq_def(Field *field);
+  
+  /*
+    pack_length() returns size (in bytes) used to store field data in memory
+    (i.e. it returns the maximum size of the field in a row of the table,
+    which is located in RAM).
+  */
   virtual uint32 pack_length() const { return (uint32) field_length; }
+
+  /*
+    pack_length_in_rec() returns size (in bytes) used to store field data on
+    storage (i.e. it returns the maximal size of the field in a row of the
+    table, which is located on disk).
+  */
   virtual uint32 pack_length_in_rec() const { return pack_length(); }
   virtual uint32 sort_length() const { return pack_length(); }
   virtual void reset(void) { bzero(ptr,pack_length()); }
@@ -1395,6 +1407,12 @@
   void init_for_tmp_table(enum_field_types sql_type_arg,
                           uint32 max_length, uint32 decimals,
                           bool maybe_null, bool is_unsigned);
+
+  bool init(THD *thd, char *field_name, enum_field_types type, char *length,
+            char *decimals, uint type_modifier, Item *default_value,
+            Item *on_update_value, LEX_STRING *comment, char *change,
+            List<String> *interval_list, CHARSET_INFO *cs,
+            uint uint_geom_type);
 };
 
 

--- 1.205/sql/item.cc	2005-12-02 10:57:17 +03:00
+++ 1.206/sql/item.cc	2005-12-07 17:01:07 +03:00
@@ -296,23 +296,6 @@
 }
 
 
-void *Item::operator new(size_t size, Item *reuse, uint *rsize)
-{
-  if (reuse && size <= reuse->rsize)
-  {
-    if (rsize)
-      (*rsize)= reuse->rsize;
-    reuse->cleanup();
-    delete reuse;
-    TRASH((void *)reuse, size);
-    return (void *)reuse;
-  }
-  if (rsize)
-    (*rsize)= (uint) size;
-  return (void *)sql_alloc((uint)size);
-}
-
-
 Item::Item():
   rsize(0), name(0), orig_name(0), name_length(0), fixed(0),
   is_autogenerated_name(TRUE),
@@ -802,9 +785,41 @@
 
 
 /*****************************************************************************
-  Item_splocal methods
+  Item_sp_variable methods
 *****************************************************************************/
-double Item_splocal::val_real()
+
+Item_sp_variable::Item_sp_variable(char *sp_var_name_str,
+                                   uint sp_var_name_length)
+  :m_thd(0)
+#ifndef DBUG_OFF
+   , m_sp(0)
+#endif
+{
+  m_name.str= sp_var_name_str;
+  m_name.length= sp_var_name_length;
+}
+
+
+bool Item_sp_variable::fix_fields(THD *thd, Item **)
+{
+  Item *it;
+
+  m_thd= thd; /* NOTE: this must be set before any this_xxx() */
+  it= this_item();
+
+  DBUG_ASSERT(it->fixed);
+
+  max_length= it->max_length;
+  decimals= it->decimals;
+  unsigned_flag= it->unsigned_flag;
+  fixed= 1;
+  collation.set(it->collation.collation, it->collation.derivation);
+
+  return FALSE;
+}
+
+
+double Item_sp_variable::val_real()
 {
   DBUG_ASSERT(fixed);
   Item *it= this_item();
@@ -814,7 +829,7 @@
 }
 
 
-longlong Item_splocal::val_int()
+longlong Item_sp_variable::val_int()
 {
   DBUG_ASSERT(fixed);
   Item *it= this_item();
@@ -824,13 +839,14 @@
 }
 
 
-String *Item_splocal::val_str(String *sp)
+String *Item_sp_variable::val_str(String *sp)
 {
   DBUG_ASSERT(fixed);
   Item *it= this_item();
   String *res= it->val_str(sp);
 
   null_value= it->null_value;
+
   if (!res)
     return NULL;
 
@@ -854,11 +870,12 @@
     str_value.set(res->ptr(), res->length(), res->charset());
   else
     res->mark_as_const();
+
   return &str_value;
 }
 
 
-my_decimal *Item_splocal::val_decimal(my_decimal *decimal_value)
+my_decimal *Item_sp_variable::val_decimal(my_decimal *decimal_value)
 {
   DBUG_ASSERT(fixed);
   Item *it= this_item();
@@ -868,73 +885,108 @@
 }
 
 
-bool Item_splocal::is_null()
+bool Item_sp_variable::is_null()
 {
-  Item *it= this_item();
-  return it->is_null();
+  return this_item()->is_null();
+}
+
+
+/*****************************************************************************
+  Item_splocal methods
+*****************************************************************************/
+
+Item_splocal::Item_splocal(const LEX_STRING &sp_var_name,
+                           uint sp_var_idx,
+                           enum_field_types sp_var_type,
+                           uint pos_in_q)
+  :Item_sp_variable(sp_var_name.str, sp_var_name.length),
+   m_var_idx(sp_var_idx), pos_in_query(pos_in_q)
+{
+  maybe_null= TRUE;
+
+  m_type= sp_map_item_type(sp_var_type);
+  m_result_type= sp_map_result_type(sp_var_type);
 }
 
 
 Item *
 Item_splocal::this_item()
 {
-  DBUG_ASSERT(owner == thd->spcont->owner);
-  return thd->spcont->get_item(m_offset);
+  DBUG_ASSERT(m_sp == m_thd->spcont->sp);
+
+  return m_thd->spcont->get_item(m_var_idx);
+}
+
+const Item *
+Item_splocal::this_item() const
+{
+  DBUG_ASSERT(m_sp == m_thd->spcont->sp);
+
+  return m_thd->spcont->get_item(m_var_idx);
 }
 
 
 Item **
-Item_splocal::this_item_addr(THD *thd, Item **addr)
+Item_splocal::this_item_addr(THD *thd, Item **)
 {
-  DBUG_ASSERT(owner == thd->spcont->owner);
-  return thd->spcont->get_item_addr(m_offset);
+  DBUG_ASSERT(m_sp == thd->spcont->sp);
+
+  return thd->spcont->get_item_addr(m_var_idx);
 }
 
-Item *
-Item_splocal::this_const_item() const
+
+void Item_splocal::print(String *str)
 {
-  DBUG_ASSERT(owner == thd->spcont->owner);
-  return thd->spcont->get_item(m_offset);
+  str->reserve(m_name.length+8);
+  str->append(m_name.str, m_name.length);
+  str->append('@');
+  str->qs_append(m_var_idx);
 }
 
-Item::Type
-Item_splocal::type() const
+
+/*****************************************************************************
+  Item_case_expr methods
+*****************************************************************************/
+
+Item_case_expr::Item_case_expr(int case_expr_id)
+  :Item_sp_variable(STRING_WITH_LEN("case_expr")),
+   m_case_expr_id(case_expr_id)
 {
-  if (thd && thd->spcont)
-  {
-    DBUG_ASSERT(owner == thd->spcont->owner);
-    return thd->spcont->get_item(m_offset)->type();
-  }
-  return NULL_ITEM;		// Anything but SUBSELECT_ITEM
 }
 
 
-bool Item_splocal::fix_fields(THD *thd_arg, Item **ref)
+Item *
+Item_case_expr::this_item()
 {
-  Item *it;
-  thd= thd_arg;                 // Must be set before this_item()
-  it= this_item();
-  DBUG_ASSERT(it->fixed);
-  max_length= it->max_length;
-  decimals= it->decimals;
-  unsigned_flag= it->unsigned_flag;
-  fixed= 1;
-  return FALSE;
+  DBUG_ASSERT(m_sp == m_thd->spcont->sp);
+
+  return m_thd->spcont->get_case_expr(m_case_expr_id);
 }
 
 
-void Item_splocal::cleanup()
+
+const Item *
+Item_case_expr::this_item() const
 {
-  fixed= 0;
+  DBUG_ASSERT(m_sp == m_thd->spcont->sp);
+
+  return m_thd->spcont->get_case_expr(m_case_expr_id);
 }
 
 
-void Item_splocal::print(String *str)
+Item **
+Item_case_expr::this_item_addr(THD *thd, Item **)
 {
-  str->reserve(m_name.length+8);
-  str->append(m_name.str, m_name.length);
-  str->append('@');
-  str->qs_append(m_offset);
+  DBUG_ASSERT(m_sp == thd->spcont->sp);
+
+  return thd->spcont->get_case_expr_addr(m_case_expr_id);
+}
+
+
+void Item_case_expr::print(String *str)
+{
+  str->append(STRING_WITH_LEN("case_expr@"));
+  str->qs_append(m_case_expr_id);
 }
 
 
@@ -1013,12 +1065,6 @@
 }
 
 
-void Item_name_const::cleanup()
-{
-  fixed= 0;
-}
-
-
 void Item_name_const::print(String *str)
 {
   str->append(STRING_WITH_LEN("NAME_CONST("));
@@ -3911,6 +3957,9 @@
       str_value.set_quick(0, 0, cs);
       return set_field_to_null_with_conversions(field, no_conversions);
     }
+
+    /* NOTE: If null_value == FALSE, "result" must be not NULL.  */
+
     field->set_notnull();
     error=field->store(result->ptr(),result->length(),cs);
     str_value.set_quick(0, 0, cs);

--- 1.184/sql/item.h	2005-12-02 10:57:17 +03:00
+++ 1.185/sql/item.h	2005-12-07 17:01:07 +03:00
@@ -341,8 +341,6 @@
   { return (void*) sql_alloc((uint) size); }
   static void *operator new(size_t size, MEM_ROOT *mem_root)
   { return (void*) alloc_root(mem_root, (uint) size); }
-  /* Special for SP local variable assignment - reusing slots */
-  static void *operator new(size_t size, Item *reuse, uint *rsize);
   static void operator delete(void *ptr,size_t size) { TRASH(ptr, size); }
   static void operator delete(void *ptr, MEM_ROOT *mem_root) {}
 
@@ -671,13 +669,13 @@
     current value and pointer to current Item otherwise.
   */
   virtual Item *this_item() { return this; }
+  virtual const Item *this_item() const { return this; }
+
   /*
     For SP local variable returns address of pointer to Item representing its
     current value and pointer passed via parameter otherwise.
   */
   virtual Item **this_item_addr(THD *thd, Item **addr) { return addr; }
-  /* For SPs mostly. */
-  virtual Item *this_const_item() const { return const_cast<Item*>(this); }
 
   // Row emulation
   virtual uint cols() { return 1; }
@@ -706,21 +704,32 @@
 
 class sp_head;
 
-/*
-  A reference to local SP variable (incl. reference to SP parameter), used in
-  runtime.
-  
-  NOTE
-    This item has a "value" item, defined as 
-      this_item() = thd->spcont->get_item(m_offset)
-    and it delegates everything to that item (if !this_item() then this item
-    poses as Item_null) except for name, which is the name of SP local
-    variable.
-*/
 
-class Item_splocal : public Item
+/*****************************************************************************
+  The class is a base class for representation of stored routine variables in
+  the Item-hierarchy. There are the following kinds of SP-vars:
+    - local variables (Item_splocal);
+    - CASE expression (Item_case_expr);
+*****************************************************************************/
+
+class Item_sp_variable :public Item
 {
-  uint m_offset;
+protected:
+  /*
+    THD, which is stored in fix_fields() and is used in this_item() to avoid
+    current_thd use.
+  */
+  THD *m_thd;
+
+public:
+  LEX_STRING m_name;
+
+  /*
+    Buffer, pointing to the string value of the item. We need it to
+    protect internal buffer from changes. See comment to analogous
+    member in Item_param for more details.
+  */
+  String str_value_ptr;
 
 public:
 #ifndef DBUG_OFF
@@ -728,11 +737,74 @@
     Routine to which this Item_splocal belongs. Used for checking if correct
     runtime context is used for variable handling.
   */
-  sp_head *owner;
+  sp_head *m_sp;
 #endif
-  LEX_STRING m_name;
-  THD	     *thd;
 
+public:
+  Item_sp_variable(char *sp_var_name_str, uint sp_var_name_length);
+
+public:
+  bool fix_fields(THD *thd, Item **);
+
+  double val_real();
+  longlong val_int();
+  String *val_str(String *sp);
+  my_decimal *val_decimal(my_decimal *decimal_value);
+  bool is_null();
+
+public:
+  inline void make_field(Send_field *field);
+  
+  inline bool const_item() const;
+  
+  inline int save_in_field(Field *field, bool no_conversions);
+  inline bool send(Protocol *protocol, String *str);
+}; 
+
+/*****************************************************************************
+  Item_sp_variable inline implementation.
+*****************************************************************************/
+
+inline void Item_sp_variable::make_field(Send_field *field)
+{
+  Item *it= this_item();
+
+  if (name)
+    it->set_name(name, (uint) strlen(name), system_charset_info);
+  else
+    it->set_name(m_name.str, m_name.length, system_charset_info);
+  it->make_field(field);
+}
+
+inline bool Item_sp_variable::const_item() const
+{
+  return TRUE;
+}
+
+inline int Item_sp_variable::save_in_field(Field *field, bool no_conversions)
+{
+  return this_item()->save_in_field(field, no_conversions);
+}
+
+inline bool Item_sp_variable::send(Protocol *protocol, String *str)
+{
+  return this_item()->send(protocol, str);
+}
+
+
+/*****************************************************************************
+  A reference to local SP variable (incl. reference to SP parameter), used in
+  runtime.
+*****************************************************************************/
+
+class Item_splocal :public Item_sp_variable
+{
+  uint m_var_idx;
+
+  Type m_type;
+  Item_result m_result_type;
+
+public:
   /* 
     Position of this reference to SP variable in the statement (the
     statement itself is in sp_instr_stmt::m_query).
@@ -745,78 +817,94 @@
   */
   uint pos_in_query;
 
-  Item_splocal(LEX_STRING name, uint offset, uint pos_in_q=0)
-    : m_offset(offset), m_name(name), thd(0), pos_in_query(pos_in_q)
-  {
-    maybe_null= TRUE;
-  }
-
-  /* For error printing */
-  inline LEX_STRING *my_name(LEX_STRING *get_name)
-  {
-    if (!get_name)
-      return &m_name;
-    (*get_name)= m_name;
-    return get_name;
-  }
+  Item_splocal(const LEX_STRING &sp_var_name, uint sp_var_idx,
+               enum_field_types sp_var_type, uint pos_in_q= 0);
 
   bool is_splocal() { return 1; } /* Needed for error checking */
 
   Item *this_item();
+  const Item *this_item() const;
   Item **this_item_addr(THD *thd, Item **);
-  Item *this_const_item() const;
 
-  bool fix_fields(THD *, Item **);
-  void cleanup();
+  void print(String *str);
 
-  inline uint get_offset()
-  {
-    return m_offset;
-  }
+public:
+  inline const LEX_STRING *my_name() const;
 
-  // Abstract methods inherited from Item. Just defer the call to
-  // the item in the frame
-  enum Type type() const;
+  inline uint get_var_idx() const;
 
-  double val_real();
-  longlong val_int();
-  String *val_str(String *sp);
-  my_decimal *val_decimal(my_decimal *);
-  bool is_null();
-  void print(String *str);
+  inline enum Type type() const;
+  inline Item_result result_type() const;
+};
 
-  void make_field(Send_field *field)
-  {
-    Item *it= this_item();
+/*****************************************************************************
+  Item_splocal inline implementation.
+*****************************************************************************/
 
-    if (name)
-      it->set_name(name, (uint) strlen(name), system_charset_info);
-    else
-      it->set_name(m_name.str, m_name.length, system_charset_info);
-    it->make_field(field);
-  }
+inline const LEX_STRING *Item_splocal::my_name() const
+{
+  return &m_name;
+}
 
-  Item_result result_type() const
-  {
-    return this_const_item()->result_type();
-  }
+inline uint Item_splocal::get_var_idx() const
+{
+  return m_var_idx;
+}
 
-  bool const_item() const
-  {
-    return TRUE;
-  }
+inline enum Item::Type Item_splocal::type() const
+{
+  return m_type;
+}
 
-  int save_in_field(Field *field, bool no_conversions)
-  {
-    return this_item()->save_in_field(field, no_conversions);
-  }
+inline Item_result Item_splocal::result_type() const
+{
+  return m_result_type;
+}
 
-  bool send(Protocol *protocol, String *str)
-  {
-    return this_item()->send(protocol, str);
-  }
+
+/*****************************************************************************
+  A reference to case expression in SP, used in runtime.
+*****************************************************************************/
+
+class Item_case_expr :public Item_sp_variable
+{
+public:
+  Item_case_expr(int case_expr_id);
+
+public:
+  Item *this_item();
+  const Item *this_item() const;
+  Item **this_item_addr(THD *thd, Item **);
+
+  inline enum Type type() const;
+  inline Item_result result_type() const;
+
+public:
+  /*
+    NOTE: print() is intended to be used from views and for debug.
+    Item_case_expr can not occur in views, so here it is only for debug
+    purposes.
+  */
+  void print(String *str);
+
+private:
+  int m_case_expr_id;
 };
 
+/*****************************************************************************
+  Item_case_expr inline implementation.
+*****************************************************************************/
+
+inline enum Item::Type Item_case_expr::type() const
+{
+  return this_item()->type();
+}
+
+inline Item_result Item_case_expr::result_type() const
+{
+  return this_item()->result_type();
+}
+
 
 /*
   NAME_CONST(given_name, const_value). 
@@ -843,7 +931,6 @@
   }
 
   bool fix_fields(THD *, Item **);
-  void cleanup();
 
   enum Type type() const;
   double val_real();

--- 1.271/sql/item_func.cc	2005-12-02 10:57:17 +03:00
+++ 1.272/sql/item_func.cc	2005-12-07 17:01:07 +03:00
@@ -4716,7 +4716,7 @@
     share->table_cache_key = empty_name;
     share->table_name = empty_name;
   }
-  field= m_sp->make_field(max_length, name, dummy_table);
+  field= m_sp->create_result_field(max_length, name, dummy_table);
   DBUG_RETURN(field);
 }
 
@@ -4729,17 +4729,17 @@
    1  value =  NULL  or error
 */
 
-int
+bool
 Item_func_sp::execute(Field **flp)
 {
-  Item *it;
+  THD *thd= current_thd;
   Field *f;
-  if (execute(&it))
-  {
-    null_value= 1;
-    context->process_error(current_thd);
-    return 1;
-  }
+
+  /*
+    Get field in virtual tmp table to store result. Create the field if
+    invoked first time.
+  */
+  
   if (!(f= *flp))
   {
     *flp= f= sp_result_field();
@@ -4748,20 +4748,33 @@
     f->null_ptr= (uchar *)&null_value;
     f->null_bit= 1;
   }
-  it->save_in_field(f, 1);
-  return null_value= f->is_null();
+
+  /* Execute function and store the return value in the field. */
+
+  if (execute_impl(thd, f))
+  {
+    null_value= 1;
+    context->process_error(thd);
+    return TRUE;
+  }
+
+  /* Check that the field (the value) is not NULL. */
+
+  null_value= f->is_null();
+
+  return null_value;
 }
 
 
-int
-Item_func_sp::execute(Item **itp)
+bool
+Item_func_sp::execute_impl(THD *thd, Field *return_value_fld)
 {
-  DBUG_ENTER("Item_func_sp::execute");
-  THD *thd= current_thd;
-  int res= -1;
+  bool err_status= TRUE;
   Sub_statement_state statement_state;
   Security_context *save_security_ctx= thd->security_ctx, *save_ctx_func;
 
+  DBUG_ENTER("Item_func_sp::execute_impl");
+
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   if (context->security_ctx)
   {
@@ -4778,7 +4791,7 @@
     function call into binlog.
   */
   thd->reset_sub_statement_state(&statement_state, SUB_STMT_FUNCTION);
-  res= m_sp->execute_function(thd, args, arg_count, itp);
+  err_status= m_sp->execute_function(thd, args, arg_count, return_value_fld);
   thd->restore_sub_statement_state(&statement_state);
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
@@ -4788,7 +4801,7 @@
 #else
 error:
 #endif
-  DBUG_RETURN(res);
+  DBUG_RETURN(err_status);
 }
 
 
@@ -4884,7 +4897,7 @@
   DBUG_ENTER("Item_func_sp::tmp_table_field");
 
   if (m_sp)
-    res= m_sp->make_field(max_length, (const char *)name, t_arg);
+    res= m_sp->create_result_field(max_length, (const char*) name, t_arg);
   
   if (!res) 
     res= Item_func::tmp_table_field(t_arg);

--- 1.133/sql/item_func.h	2005-11-30 10:17:18 +03:00
+++ 1.134/sql/item_func.h	2005-12-07 17:01:07 +03:00
@@ -1374,8 +1374,8 @@
   Field *result_field;
   char result_buf[64];
 
-  int execute(Item **itp);
-  int execute(Field **flp);
+  bool execute(Field **flp);
+  bool execute_impl(THD *thd, Field *return_value_fld);
   Field *sp_result_field(void) const;
 
 public:

--- 1.372/sql/mysql_priv.h	2005-12-04 21:55:15 +03:00
+++ 1.373/sql/mysql_priv.h	2005-12-07 17:01:07 +03:00
@@ -660,6 +660,7 @@
 bool mysql_preload_keys(THD* thd, TABLE_LIST* table_list);
 int reassign_keycache_tables(THD* thd, KEY_CACHE *src_cache,
                              KEY_CACHE *dst_cache);
+TABLE *create_virtual_tmp_table(THD *thd, List<create_field> &field_list);
 
 bool mysql_xa_recover(THD *thd);
 
@@ -1101,8 +1102,8 @@
 uint check_word(TYPELIB *lib, const char *val, const char *end,
 		const char **end_of_word);
 
-bool is_keyword(const char *name, uint len);
 
+bool is_keyword(const char *name, uint len);
 
 #define MY_DB_OPT_FILE "db.opt"
 bool load_db_opt(THD *thd, const char *path, HA_CREATE_INFO *create);

--- 1.226/sql/sql_class.cc	2005-12-01 13:31:16 +03:00
+++ 1.227/sql/sql_class.cc	2005-12-07 17:01:08 +03:00
@@ -1503,10 +1503,10 @@
     my_var *mv= gl++;
     if (mv->local)
     {
-      Item_splocal *var;
-      (void)local_vars.push_back(var= new Item_splocal(mv->s, mv->offset));
+      Item_splocal *var= new Item_splocal(mv->s, mv->offset, mv->type);
+      (void)local_vars.push_back(var);
 #ifndef DBUG_OFF
-      var->owner= mv->owner;
+      var->m_sp= mv->sp;
 #endif
     }
     else
@@ -1779,8 +1779,8 @@
     {
       if ((yy=var_li++)) 
       {
-	if (thd->spcont->set_item_eval(current_thd,
-				       yy->get_offset(), it.ref(), zz->type))
+	if (thd->spcont->set_variable(current_thd, yy->get_var_idx(),
+                                      *it.ref()))
 	  DBUG_RETURN(1);
       }
     }

--- 1.277/sql/sql_class.h	2005-12-03 16:40:20 +03:00
+++ 1.278/sql/sql_class.h	2005-12-07 17:01:08 +03:00
@@ -2100,7 +2100,7 @@
     Routine to which this Item_splocal belongs. Used for checking if correct
     runtime context is used for variable handling.
   */
-  sp_head *owner;
+  sp_head *sp;
 #endif
   bool local;
   uint offset;

--- 1.523/sql/sql_parse.cc	2005-12-06 15:20:53 +03:00
+++ 1.524/sql/sql_parse.cc	2005-12-07 17:01:08 +03:00
@@ -5759,335 +5759,15 @@
                         buf, "TIMESTAMP");
   }
 
-  if (!(new_field= new_create_field(thd, field_name, type, length, decimals,
-		type_modifier, default_value, on_update_value,
-		comment, change, interval_list, cs, uint_geom_type)))
+  if (!(new_field= new create_field()) ||
+      new_field->init(thd, field_name, type, length, decimals, type_modifier,
+                      default_value, on_update_value, comment, change,
+                      interval_list, cs, uint_geom_type))
     DBUG_RETURN(1);
 
   lex->create_list.push_back(new_field);
   lex->last_field=new_field;
   DBUG_RETURN(0);
-}
-
-/*****************************************************************************
-** Create field definition for create
-** Return 0 on failure, otherwise return create_field instance
-******************************************************************************/
-  
-create_field *
-new_create_field(THD *thd, char *field_name, enum_field_types type,
-		 char *length, char *decimals,
-		 uint type_modifier, 
-		 Item *default_value, Item *on_update_value,
-		 LEX_STRING *comment,
-		 char *change, List<String> *interval_list, CHARSET_INFO *cs,
-		 uint uint_geom_type)
-{
-  register create_field *new_field;
-  uint sign_len, allowed_type_modifier=0;
-  ulong max_field_charlength= MAX_FIELD_CHARLENGTH;
-  DBUG_ENTER("new_create_field");
-  
-  if (!(new_field=new create_field()))
-    DBUG_RETURN(NULL);
-  new_field->field=0;
-  new_field->field_name=field_name;
-  new_field->def= default_value;
-  new_field->flags= type_modifier;
-  new_field->unireg_check= (type_modifier & AUTO_INCREMENT_FLAG ?
-			    Field::NEXT_NUMBER : Field::NONE);
-  new_field->decimals= decimals ? (uint)atoi(decimals) : 0;
-  if (new_field->decimals >= NOT_FIXED_DEC)
-  {
-    my_error(ER_TOO_BIG_SCALE, MYF(0), new_field->decimals, field_name,
-             NOT_FIXED_DEC-1);
-    DBUG_RETURN(NULL);
-  }
-
-  new_field->sql_type=type;
-  new_field->length=0;
-  new_field->change=change;
-  new_field->interval=0;
-  new_field->pack_length= new_field->key_length= 0;
-  new_field->charset=cs;
-  new_field->geom_type= (Field::geometry_type) uint_geom_type;
-
-  new_field->comment=*comment;
-  /*
-    Set flag if this field doesn't have a default value
-  */
-  if (!default_value && !(type_modifier & AUTO_INCREMENT_FLAG) &&
-      (type_modifier & NOT_NULL_FLAG) && type != FIELD_TYPE_TIMESTAMP)
-    new_field->flags|= NO_DEFAULT_VALUE_FLAG;
-
-  if (length && !(new_field->length= (uint) atoi(length)))
-    length=0; /* purecov: inspected */
-  sign_len=type_modifier & UNSIGNED_FLAG ? 0 : 1;
-
-  switch (type) {
-  case FIELD_TYPE_TINY:
-    if (!length) new_field->length=MAX_TINYINT_WIDTH+sign_len;
-    allowed_type_modifier= AUTO_INCREMENT_FLAG;
-    break;
-  case FIELD_TYPE_SHORT:
-    if (!length) new_field->length=MAX_SMALLINT_WIDTH+sign_len;
-    allowed_type_modifier= AUTO_INCREMENT_FLAG;
-    break;
-  case FIELD_TYPE_INT24:
-    if (!length) new_field->length=MAX_MEDIUMINT_WIDTH+sign_len;
-    allowed_type_modifier= AUTO_INCREMENT_FLAG;
-    break;
-  case FIELD_TYPE_LONG:
-    if (!length) new_field->length=MAX_INT_WIDTH+sign_len;
-    allowed_type_modifier= AUTO_INCREMENT_FLAG;
-    break;
-  case FIELD_TYPE_LONGLONG:
-    if (!length) new_field->length=MAX_BIGINT_WIDTH;
-    allowed_type_modifier= AUTO_INCREMENT_FLAG;
-    break;
-  case FIELD_TYPE_NULL:
-    break;
-  case FIELD_TYPE_NEWDECIMAL:
-    if (!length && !new_field->decimals)
-      new_field->length= 10;
-    if (new_field->length > DECIMAL_MAX_PRECISION)
-    {
-      my_error(ER_TOO_BIG_PRECISION, MYF(0), new_field->length, field_name,
-               DECIMAL_MAX_PRECISION);
-      DBUG_RETURN(NULL);
-    }
-    if (new_field->length < new_field->decimals)
-    {
-      my_error(ER_M_BIGGER_THAN_D, MYF(0), field_name);
-      DBUG_RETURN(NULL);
-    }
-    new_field->length=
-      my_decimal_precision_to_length(new_field->length, new_field->decimals,
-                                     type_modifier & UNSIGNED_FLAG);
-    new_field->pack_length=
-      my_decimal_get_binary_size(new_field->length, new_field->decimals);
-    break;
-  case MYSQL_TYPE_VARCHAR:
-    /*
-      Long VARCHAR's are automaticly converted to blobs in mysql_prepare_table
-      if they don't have a default value
-    */
-    max_field_charlength= MAX_FIELD_VARCHARLENGTH;
-    break;
-  case MYSQL_TYPE_STRING:
-    break;
-  case FIELD_TYPE_BLOB:
-  case FIELD_TYPE_TINY_BLOB:
-  case FIELD_TYPE_LONG_BLOB:
-  case FIELD_TYPE_MEDIUM_BLOB:
-  case FIELD_TYPE_GEOMETRY:
-    if (default_value)				// Allow empty as default value
-    {
-      String str,*res;
-      res=default_value->val_str(&str);
-      if (res->length())
-      {
-	my_error(ER_BLOB_CANT_HAVE_DEFAULT, MYF(0),
-                 field_name); /* purecov: inspected */
-	DBUG_RETURN(NULL);
-      }
-      new_field->def=0;
-    }
-    new_field->flags|=BLOB_FLAG;
-    break;
-  case FIELD_TYPE_YEAR:
-    if (!length || new_field->length != 2)
-      new_field->length=4;			// Default length
-    new_field->flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
-    break;
-  case FIELD_TYPE_FLOAT:
-    /* change FLOAT(precision) to FLOAT or DOUBLE */
-    allowed_type_modifier= AUTO_INCREMENT_FLAG;
-    if (length && !decimals)
-    {
-      uint tmp_length=new_field->length;
-      if (tmp_length > PRECISION_FOR_DOUBLE)
-      {
-	my_error(ER_WRONG_FIELD_SPEC, MYF(0), field_name);
-	DBUG_RETURN(NULL);
-      }
-      else if (tmp_length > PRECISION_FOR_FLOAT)
-      {
-	new_field->sql_type=FIELD_TYPE_DOUBLE;
-	new_field->length=DBL_DIG+7;			// -[digits].E+###
-      }
-      else
-	new_field->length=FLT_DIG+6;			// -[digits].E+##
-      new_field->decimals= NOT_FIXED_DEC;
-      break;
-    }
-    if (!length && !decimals)
-    {
-      new_field->length =  FLT_DIG+6;
-      new_field->decimals= NOT_FIXED_DEC;
-    }
-    if (new_field->length < new_field->decimals &&
-        new_field->decimals != NOT_FIXED_DEC)
-    {
-      my_error(ER_M_BIGGER_THAN_D, MYF(0), field_name);
-      DBUG_RETURN(NULL);
-    }
-    break;
-  case FIELD_TYPE_DOUBLE:
-    allowed_type_modifier= AUTO_INCREMENT_FLAG;
-    if (!length && !decimals)
-    {
-      new_field->length = DBL_DIG+7;
-      new_field->decimals=NOT_FIXED_DEC;
-    }
-    if (new_field->length < new_field->decimals &&
-        new_field->decimals != NOT_FIXED_DEC)
-    {
-      my_error(ER_M_BIGGER_THAN_D, MYF(0), field_name);
-      DBUG_RETURN(NULL);
-    }
-    break;
-  case FIELD_TYPE_TIMESTAMP:
-    if (!length)
-      new_field->length= 14;			// Full date YYYYMMDDHHMMSS
-    else if (new_field->length != 19)
-    {
-      /*
-        We support only even TIMESTAMP lengths less or equal than 14
-        and 19 as length of 4.1 compatible representation.
-      */
-      new_field->length=((new_field->length+1)/2)*2; /* purecov: inspected */
-      new_field->length= min(new_field->length,14); /* purecov: inspected */
-    }
-    new_field->flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
-    if (default_value)
-    {
-      /* Grammar allows only NOW() value for ON UPDATE clause */
-      if (default_value->type() == Item::FUNC_ITEM && 
-          ((Item_func*)default_value)->functype() == Item_func::NOW_FUNC)
-      {
-        new_field->unireg_check= (on_update_value?Field::TIMESTAMP_DNUN_FIELD:
-                                                  Field::TIMESTAMP_DN_FIELD);
-        /*
-          We don't need default value any longer moreover it is dangerous.
-          Everything handled by unireg_check further.
-        */
-        new_field->def= 0;
-      }
-      else
-        new_field->unireg_check= (on_update_value?Field::TIMESTAMP_UN_FIELD:
-                                                  Field::NONE);
-    }
-    else
-    {
-      /*
-        If we have default TIMESTAMP NOT NULL column without explicit DEFAULT
-        or ON UPDATE values then for the sake of compatiblity we should treat
-        this column as having DEFAULT NOW() ON UPDATE NOW() (when we don't
-        have another TIMESTAMP column with auto-set option before this one)
-        or DEFAULT 0 (in other cases).
-        So here we are setting TIMESTAMP_OLD_FIELD only temporary, and will
-        replace this value by TIMESTAMP_DNUN_FIELD or NONE later when
-        information about all TIMESTAMP fields in table will be availiable.
-
-        If we have TIMESTAMP NULL column without explicit DEFAULT value
-        we treat it as having DEFAULT NULL attribute.
-      */
-      new_field->unireg_check= (on_update_value ?
-                                Field::TIMESTAMP_UN_FIELD :
-                                (new_field->flags & NOT_NULL_FLAG ?
-                                 Field::TIMESTAMP_OLD_FIELD:
-                                 Field::NONE));
-    }
-    break;
-  case FIELD_TYPE_DATE:				// Old date type
-    if (protocol_version != PROTOCOL_VERSION-1)
-      new_field->sql_type=FIELD_TYPE_NEWDATE;
-    /* fall trough */
-  case FIELD_TYPE_NEWDATE:
-    new_field->length=10;
-    break;
-  case FIELD_TYPE_TIME:
-    new_field->length=10;
-    break;
-  case FIELD_TYPE_DATETIME:
-    new_field->length=19;
-    break;
-  case FIELD_TYPE_SET:
-    {
-      if (interval_list->elements > sizeof(longlong)*8)
-      {
-	my_error(ER_TOO_BIG_SET, MYF(0), field_name); /* purecov: inspected */
-	DBUG_RETURN(NULL);
-      }
-      new_field->pack_length= get_set_pack_length(interval_list->elements);
-
-      List_iterator<String> it(*interval_list);
-      String *tmp;
-      while ((tmp= it++))
-        new_field->interval_list.push_back(tmp);
-      /*
-        Set fake length to 1 to pass the below conditions.
-        Real length will be set in mysql_prepare_table()
-        when we know the character set of the column
-      */
-      new_field->length= 1;
-      break;
-    }
-  case FIELD_TYPE_ENUM:
-    {
-      // Should be safe
-      new_field->pack_length= get_enum_pack_length(interval_list->elements);
-
-      List_iterator<String> it(*interval_list);
-      String *tmp;
-      while ((tmp= it++))
-        new_field->interval_list.push_back(tmp);
-      new_field->length= 1; // See comment for FIELD_TYPE_SET above.
-      break;
-   }
-  case MYSQL_TYPE_VAR_STRING:
-    DBUG_ASSERT(0);                             // Impossible
-    break;
-  case MYSQL_TYPE_BIT:
-    {
-      if (!length)
-        new_field->length= 1;
-      if (new_field->length > MAX_BIT_FIELD_LENGTH)
-      {
-        my_error(ER_TOO_BIG_DISPLAYWIDTH, MYF(0), field_name,
-                 MAX_BIT_FIELD_LENGTH);
-        DBUG_RETURN(NULL);
-      }
-      new_field->pack_length= (new_field->length + 7) / 8;
-      break;
-    }
-  case FIELD_TYPE_DECIMAL:
-    DBUG_ASSERT(0); /* Was obsolete */
-  }
-
-  if (!(new_field->flags & BLOB_FLAG) &&
-      ((new_field->length > max_field_charlength && type != FIELD_TYPE_SET && 
-        type != FIELD_TYPE_ENUM &&
-        (type != MYSQL_TYPE_VARCHAR || default_value)) ||
-       (!new_field->length &&
-        type != MYSQL_TYPE_STRING &&
-        type != MYSQL_TYPE_VARCHAR && type != FIELD_TYPE_GEOMETRY)))
-  {
-    my_error((type == MYSQL_TYPE_VAR_STRING || type == MYSQL_TYPE_VARCHAR ||
-              type == MYSQL_TYPE_STRING) ?  ER_TOO_BIG_FIELDLENGTH :
-             ER_TOO_BIG_DISPLAYWIDTH,
-             MYF(0),
-             field_name, max_field_charlength); /* purecov: inspected */
-    DBUG_RETURN(NULL);
-  }
-  type_modifier&= AUTO_INCREMENT_FLAG;
-  if ((~allowed_type_modifier) & type_modifier)
-  {
-    my_error(ER_WRONG_FIELD_SPEC, MYF(0), field_name);
-    DBUG_RETURN(NULL);
-  }
-  DBUG_RETURN(new_field);
 }
 
 

--- 1.379/sql/sql_select.cc	2005-12-01 10:14:00 +03:00
+++ 1.380/sql/sql_select.cc	2005-12-07 17:01:08 +03:00
@@ -8911,6 +8911,7 @@
 TABLE *create_virtual_tmp_table(THD *thd, List<create_field> &field_list)
 {
   uint field_count= field_list.elements;
+  uint blob_count= 0;
   Field **field;
   create_field *cdef;                           /* column definition */
   uint record_length= 0;
@@ -8927,6 +8928,12 @@
   table->s= s= &table->share_not_to_be_used;
   s->fields= field_count;
 
+  if (!(s->blob_field= (uint*)thd->alloc((field_list.elements + 1) *
+                                         sizeof(uint))))
+    return 0;
+
+  s->blob_ptr_size= mi_portable_sizeof_char_ptr;
+
   /* Create all fields and calculate the total length of record */
   List_iterator_fast<create_field> it(field_list);
   while ((cdef= it++))
@@ -8942,9 +8949,15 @@
     record_length+= (**field).pack_length();
     if (! ((**field).flags & NOT_NULL_FLAG))
       ++null_count;
+
+    if ((*field)->flags & BLOB_FLAG)
+      s->blob_field[blob_count++]= (uint) (field - table->field);
+
     ++field;
   }
   *field= NULL;                                 /* mark the end of the list */
+  s->blob_field[blob_count]= 0;             /* mark the end of the list */
+  s->blob_fields= blob_count;
 
   null_pack_length= (null_count + 7)/8;
   s->reclength= record_length + null_pack_length;

--- 1.103/sql/sql_select.h	2005-11-30 13:52:09 +03:00
+++ 1.104/sql/sql_select.h	2005-12-07 17:01:08 +03:00
@@ -406,7 +406,6 @@
 			ORDER *group, bool distinct, bool save_sum_fields,
 			ulonglong select_options, ha_rows rows_limit,
 			char* alias);
-TABLE *create_virtual_tmp_table(THD *thd, List<create_field> &field_list);
 void free_tmp_table(THD *thd, TABLE *entry);
 void count_field_types(TMP_TABLE_PARAM *param, List<Item> &fields,
 		       bool reset_with_sum_func);

--- 1.443/sql/sql_yacc.yy	2005-12-06 15:20:53 +03:00
+++ 1.444/sql/sql_yacc.yy	2005-12-07 17:01:08 +03:00
@@ -1350,41 +1350,11 @@
 	  {
 	    LEX *lex= Lex;
 	    sp_head *sp= lex->sphead;
-            LEX_STRING cmt = { 0, 0 };
-	    create_field *new_field;
-	    uint unused1= 0;
-	    int unused2= 0;
-
-	    if (!(new_field= new_create_field(YYTHD, (char*) "",
-					      (enum enum_field_types)$8,
-			  		      lex->length, lex->dec, lex->type,
-			  		      (Item *)0, (Item *) 0, &cmt, 0,
-					      &lex->interval_list, 
-			  		      (lex->charset ? lex->charset :
-					       default_charset_info),
-					      lex->uint_geom_type)))
-	      YYABORT;
-
-	    sp->m_returns_cs= new_field->charset;
-
-            if (new_field->interval_list.elements)
-            {
-	      new_field->interval= 
-                sp->create_typelib(&new_field->interval_list);
-            }
-            sp_prepare_create_field(YYTHD, new_field);
-
-	    if (prepare_create_field(new_field, &unused1, &unused2, &unused2,
-				     HA_CAN_GEOMETRY))
-	      YYABORT;
 
-	    sp->m_returns= new_field->sql_type;
-	    sp->m_returns_cs= new_field->charset;
-	    sp->m_returns_len= new_field->length;
-	    sp->m_returns_pack= new_field->pack_flag;
-            sp->m_returns_typelib= new_field->interval;
-            sp->m_geom_returns= new_field->geom_type;
-            new_field->interval= NULL;
+            if (sp->fill_field_definition(YYTHD, lex,
+                                          (enum enum_field_types) $8,
+                                          &sp->m_return_field_def))
+              YYABORT;
 
 	    bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics));
 	  }
@@ -1506,8 +1476,28 @@
 	| sp_fdparam
 	;
 
+sp_init_param:
+	  /* Empty */
+	  {
+	    LEX *lex= Lex;
+
+	    lex->length= 0;
+	    lex->dec= 0;
+	    lex->type= 0;
+	  
+	    lex->default_value= 0;
+	    lex->on_update_value= 0;
+	  
+	    lex->comment= null_lex_str;
+	    lex->charset= NULL;
+	  
+	    lex->interval_list.empty();
+	    lex->uint_geom_type= 0;
+	  }
+	;
+
 sp_fdparam:
-	  ident type
+	  ident sp_init_param type
 	  {
 	    LEX *lex= Lex;
 	    sp_pcontext *spc= lex->spcont;
@@ -1517,7 +1507,17 @@
 	      my_error(ER_SP_DUP_PARAM, MYF(0), $1.str);
 	      YYABORT;
 	    }
-	    spc->push_pvar(&$1, (enum enum_field_types)$2, sp_param_in);
+	    sp_pvar_t *pvar= spc->push_pvar(&$1, (enum enum_field_types)$3,
+                                            sp_param_in);
+
+            if (lex->sphead->fill_field_definition(YYTHD, lex,
+                                                   (enum enum_field_types) $3,
+                                                   &pvar->field_def))
+            {
+              YYABORT;
+            }
+            pvar->field_def.field_name= pvar->name.str;
+            pvar->field_def.pack_flag |= FIELDFLAG_MAYBE_NULL;
 	  }
 	;
 
@@ -1533,18 +1533,27 @@
 	;
 
 sp_pdparam:
-	  sp_opt_inout ident type
+	  sp_opt_inout sp_init_param ident type
 	  {
 	    LEX *lex= Lex;
 	    sp_pcontext *spc= lex->spcont;
 
-	    if (spc->find_pvar(&$2, TRUE))
+	    if (spc->find_pvar(&$3, TRUE))
 	    {
-	      my_error(ER_SP_DUP_PARAM, MYF(0), $2.str);
+	      my_error(ER_SP_DUP_PARAM, MYF(0), $3.str);
 	      YYABORT;
 	    }
-	    spc->push_pvar(&$2, (enum enum_field_types)$3,
-			   (sp_param_mode_t)$1);
+	    sp_pvar_t *pvar= spc->push_pvar(&$3, (enum enum_field_types)$4,
+			                    (sp_param_mode_t)$1);
+
+            if (lex->sphead->fill_field_definition(YYTHD, lex,
+                                                   (enum enum_field_types) $4,
+                                                   &pvar->field_def))
+            {
+              YYABORT;
+            }
+            pvar->field_def.field_name= pvar->name.str;
+            pvar->field_def.pack_flag |= FIELDFLAG_MAYBE_NULL;
 	  }
 	;
 
@@ -1596,45 +1605,60 @@
 	;
 
 sp_decl:
-          DECLARE_SYM sp_decl_idents type 
+          DECLARE_SYM sp_decl_idents
           {
             LEX *lex= Lex;
 
             lex->sphead->reset_lex(YYTHD);
             lex->spcont->declare_var_boundary($2);
           }
+          type
           sp_opt_default
           {
             LEX *lex= Lex;
-            sp_pcontext *ctx= lex->spcont;
-            uint max= ctx->context_pvars();
-            enum enum_field_types type= (enum enum_field_types)$3;
-            Item *it= $5;
-            bool has_default= (it != NULL);
-
-            for (uint i = max-$2 ; i < max ; i++)
+            sp_pcontext *pctx= lex->spcont;
+            uint num_vars= pctx->context_pvars();
+            enum enum_field_types var_type= (enum enum_field_types) $4;
+            Item *dflt_value_item= $5;
+            create_field *create_field_op;
+            
+            if (!dflt_value_item)
             {
-              sp_instr_set *in;
-	      uint off= ctx->pvar_context2index(i);
-
-              ctx->set_type(off, type);
-              if (! has_default)
-                it= new Item_null();  /* QQ Set to the type with null_value? */
-              in = new sp_instr_set(lex->sphead->instructions(),
-                                    ctx,
-                                    off,
-                                    it, type, lex,
-                                    (i == max - 1));
-
-              /*
-                The last instruction is assigned to be responsible for
-                freeing LEX.
-              */
-              lex->sphead->add_instr(in);
-              ctx->set_default(off, it);
+              dflt_value_item= new Item_null();
+              /* QQ Set to the var_type with null_value? */
+            }
+            
+            for (uint i = num_vars-$2 ; i < num_vars ; i++)
+            {
+              uint var_idx= pctx->pvar_context2index(i);
+              sp_pvar_t *pvar= pctx->find_pvar(var_idx);
+            
+              if (!pvar)
+                YYABORT;
+            
+              pvar->type= var_type;
+              pvar->dflt= dflt_value_item;
+            
+              if (lex->sphead->fill_field_definition(YYTHD, lex, var_type,
+                                                     &pvar->field_def))
+              {
+                YYABORT;
+              }
+            
+              pvar->field_def.field_name= pvar->name.str;
+              pvar->field_def.pack_flag |= FIELDFLAG_MAYBE_NULL;
+            
+              /* The last instruction is responsible for freeing LEX. */
+
+              lex->sphead->add_instr(
+                new sp_instr_set(lex->sphead->instructions(), pctx, var_idx,
+                                 dflt_value_item, var_type, lex,
+                                 (i == num_vars - 1)));
             }
-            ctx->declare_var_boundary(0);
+
+            pctx->declare_var_boundary(0);
             lex->sphead->restore_lex(YYTHD);
+
             $$.vars= $2;
             $$.conds= $$.hndlrs= $$.curs= 0;
           }
@@ -1857,6 +1881,8 @@
 sp_decl_idents:
 	  ident
 	  {
+            /* NOTE: field definition is filled in sp_decl section. */
+
 	    LEX *lex= Lex;
 	    sp_pcontext *spc= lex->spcont;
 
@@ -1870,6 +1896,8 @@
 	  }
 	| sp_decl_idents ',' ident
 	  {
+            /* NOTE: field definition is filled in sp_decl section. */
+
 	    LEX *lex= Lex;
 	    sp_pcontext *spc= lex->spcont;
 
@@ -1947,8 +1975,8 @@
 	    {
 	      sp_instr_freturn *i;
 
-	      i= new sp_instr_freturn(sp->instructions(), lex->spcont,
-		                      $3, sp->m_returns, lex);
+	      i= new sp_instr_freturn(sp->instructions(), lex->spcont, $3,
+                                      sp->m_return_field_def.sql_type, lex);
 	      sp->add_instr(i);
 	      sp->m_flags|= sp_head::HAS_RETURN;
 	    }
@@ -1964,25 +1992,27 @@
           { Lex->sphead->reset_lex(YYTHD); }
           expr WHEN_SYM
 	  {
-	    /* We "fake" this by using an anonymous variable which we
-	       set to the expression. Note that all WHENs are evaluate
-	       at the same frame level, so we then know that it's the
-	       top-most variable in the frame. */
-	    LEX *lex= Lex;
-	    uint offset= lex->spcont->current_pvars();
-	    sp_instr_set *i = new sp_instr_set(lex->sphead->instructions(),
-                                               lex->spcont, offset, $3,
-                                               MYSQL_TYPE_STRING, lex, TRUE);
-	    LEX_STRING dummy={(char*)"", 0};
-
-	    lex->spcont->push_pvar(&dummy, MYSQL_TYPE_STRING, sp_param_in);
-	    lex->sphead->add_instr(i);
-	    lex->sphead->m_flags|= sp_head::IN_SIMPLE_CASE;
-            lex->sphead->restore_lex(YYTHD);
+	    LEX *lex= Lex;
+	    sp_head *sp= lex->sphead;
+	    sp_pcontext *parsing_ctx= lex->spcont;
+	    int case_expr_id= parsing_ctx->register_case_expr();
+	    
+	    if (parsing_ctx->push_case_expr_id(case_expr_id))
+              YYABORT;
+	    
+	    sp->add_instr(
+	      new sp_instr_set_case_expr(sp->instructions(),
+	                                 parsing_ctx,
+	                                 case_expr_id,
+	                                 $3,
+	                                 lex));
+	    
+	    sp->m_flags|= sp_head::IN_SIMPLE_CASE;
+	    sp->restore_lex(YYTHD);
 	  }
 	  sp_case END CASE_SYM
 	  {
-	    Lex->spcont->pop_pvar();
+	    Lex->spcont->pop_case_expr_id();
 	  }
 	| sp_labeled_control
 	  {}
@@ -2293,20 +2323,20 @@
 	      i= new sp_instr_jump_if_not(ip, ctx, $2, lex);
 	    else
 	    { /* Simple case: <caseval> = <whenval> */
-	      LEX_STRING ivar;
 
-	      ivar.str= (char *)"_tmp_";
-	      ivar.length= 5;
-	      Item_splocal *var= new Item_splocal(ivar,
-                                                  ctx->current_pvars()-1);
+	      Item_case_expr *var;
+              Item *expr;
+
+              var= new Item_case_expr(ctx->get_current_case_expr_id());
+
 #ifndef DBUG_OFF
               if (var)
-                var->owner= sp;
+                var->m_sp= sp;
 #endif
-	      Item *expr= new Item_func_eq(var, $2);
+
+	      expr= new Item_func_eq(var, $2);
 
 	      i= new sp_instr_jump_if_not(ip, ctx, expr, lex);
-              lex->variables_used= 1;
 	    }
 	    sp->push_backpatch(i, ctx->push_label((char *)"", 0));
             sp->add_instr(i);
@@ -4406,11 +4436,9 @@
 	  {
 	    if ($3->is_splocal())
 	    {
-	      LEX_STRING *name;
 	      Item_splocal *il= static_cast<Item_splocal *>($3);
 
-	      name= il->my_name(NULL);
-	      my_error(ER_WRONG_COLUMN_NAME, MYF(0), name->str);
+	      my_error(ER_WRONG_COLUMN_NAME, MYF(0), il->my_name()->str);
 	      YYABORT;
 	    }
 	    $$= new Item_default_value(Lex->current_context(), $3);
@@ -5887,7 +5915,7 @@
                  var_list.push_back(var= new my_var($1,1,t->offset,t->type));
 #ifndef DBUG_OFF
 	       if (var)
-		 var->owner= lex->sphead;
+		 var->sp= lex->sphead;
 #endif
 	     }
 	   }
@@ -7189,11 +7217,12 @@
 	  {
             /* We're compiling a stored procedure and found a variable */
             Item_splocal *splocal;
-            splocal= new Item_splocal($1, spv->offset, lex->tok_start_prev - 
+            splocal= new Item_splocal($1, spv->offset, spv->type,
+                                      lex->tok_start_prev - 
                                       lex->sphead->m_tmp_query);
 #ifndef DBUG_OFF
             if (splocal)
-              splocal->owner= lex->sphead;
+              splocal->m_sp= lex->sphead;
 #endif
 	    $$ = (Item*) splocal;
             lex->variables_used= 1;
--- New file ---
+++ mysql-test/include/sp-vars.inc	05/12/07 17:01:08
delimiter |;

---------------------------------------------------------------------------

CREATE PROCEDURE sp_vars_check_dflt()
BEGIN
  DECLARE v1 TINYINT DEFAULT 1e200;
  DECLARE v1u TINYINT UNSIGNED DEFAULT 1e200;
  DECLARE v2 TINYINT DEFAULT -1e200;
  DECLARE v2u TINYINT UNSIGNED DEFAULT -1e200;
  DECLARE v3 TINYINT DEFAULT 300;
  DECLARE v3u TINYINT UNSIGNED DEFAULT 300;
  DECLARE v4 TINYINT DEFAULT -300;
  DECLARE v4u TINYINT UNSIGNED DEFAULT -300;

  DECLARE v5 TINYINT DEFAULT 10 * 10 * 10;
  DECLARE v5u TINYINT UNSIGNED DEFAULT 10 * 10 * 10;
  DECLARE v6 TINYINT DEFAULT -10 * 10 * 10;
  DECLARE v6u TINYINT UNSIGNED DEFAULT -10 * 10 * 10;

  DECLARE v7 TINYINT DEFAULT '10';
  DECLARE v8 TINYINT DEFAULT '10 ';
  DECLARE v9 TINYINT DEFAULT ' 10 ';
  DECLARE v10 TINYINT DEFAULT 'String 10 ';
  DECLARE v11 TINYINT DEFAULT 'String10';
  DECLARE v12 TINYINT DEFAULT '10 String';
  DECLARE v13 TINYINT DEFAULT '10String';
  DECLARE v14 TINYINT DEFAULT concat('10', ' ');
  DECLARE v15 TINYINT DEFAULT concat(' ', '10');
  DECLARE v16 TINYINT DEFAULT concat('Hello, ', 'world');

  DECLARE v17 DECIMAL(64, 2) DEFAULT 12;
  DECLARE v18 DECIMAL(64, 2) DEFAULT 12.123;
  DECLARE v19 DECIMAL(64, 2) DEFAULT 11 + 1;
  DECLARE v20 DECIMAL(64, 2) DEFAULT 12 + 0.123;

  SELECT v1, v1u, v2, v2u, v3, v3u, v4, v4u;
  SELECT v5, v5u, v6, v6u;
  SELECT v7, v8, v9, v10, v11, v12, v13, v14, v15, v16;
  SELECT v17, v18, v19, v20;
END|

---------------------------------------------------------------------------

CREATE PROCEDURE sp_vars_check_assignment()
BEGIN
  DECLARE i1, i2, i3, i4 TINYINT;
  DECLARE u1, u2, u3, u4 TINYINT UNSIGNED;
  DECLARE d1, d2, d3 DECIMAL(64, 2);
 
  SET i1 = 1e200;
  SET i2 = -1e200;
  SET i3 = 300;
  SET i4 = -300;
 
  SELECT i1, i2, i3, i4;

  SET i1 = 10 * 10 * 10;
  SET i2 = -10 * 10 * 10;
  SET i3 = sign(10 * 10) * 10 * 20;
  SET i4 = sign(-10 * 10) * -10 * 20;
  
  SELECT i1, i2, i3, i4;

  SET u1 = 1e200;
  SET u2 = -1e200;
  SET u3 = 300;
  SET u4 = -300;
 
  SELECT u1, u2, u3, u4;

  SET u1 = 10 * 10 * 10;
  SET u2 = -10 * 10 * 10;
  SET u3 = sign(10 * 10) * 10 * 20;
  SET u4 = sign(-10 * 10) * -10 * 20;
  
  SELECT u1, u2, u3, u4;

  SET d1 = 1234;
  SET d2 = 1234.12;
  SET d3 = 1234.1234;

  SELECT d1, d2, d3;

  SET d1 = 12 * 100 + 34;
  SET d2 = 12 * 100 + 34 + 0.12;
  SET d3 = 12 * 100 + 34 + 0.1234;

  SELECT d1, d2, d3;
END|

---------------------------------------------------------------------------

CREATE FUNCTION sp_vars_check_ret1() RETURNS TINYINT
BEGIN
  RETURN 1e200;
END|

---------------------------------------------------------------------------

CREATE FUNCTION sp_vars_check_ret2() RETURNS TINYINT
BEGIN
  RETURN 10 * 10 * 10;
END|

---------------------------------------------------------------------------

CREATE FUNCTION sp_vars_check_ret3() RETURNS TINYINT
BEGIN
  RETURN 'Hello, world';
END|

---------------------------------------------------------------------------

CREATE FUNCTION sp_vars_check_ret4() RETURNS DECIMAL(64, 2)
BEGIN
  RETURN 12 * 10 + 34 + 0.1234;
END|

---------------------------------------------------------------------------

delimiter ;|

--- New file ---
+++ mysql-test/r/sp-vars.result	05/12/07 17:01:09
DROP PROCEDURE IF EXISTS sp_vars_check_dflt;
DROP PROCEDURE IF EXISTS sp_vars_check_assignment;
DROP FUNCTION IF EXISTS sp_vars_check_ret1;
DROP FUNCTION IF EXISTS sp_vars_check_ret2;
DROP FUNCTION IF EXISTS sp_vars_check_ret3;
DROP FUNCTION IF EXISTS sp_vars_check_ret4;
SET @@sql_mode = 'ansi';
CREATE PROCEDURE sp_vars_check_dflt()
BEGIN
DECLARE v1 TINYINT DEFAULT 1e200;
DECLARE v1u TINYINT UNSIGNED DEFAULT 1e200;
DECLARE v2 TINYINT DEFAULT -1e200;
DECLARE v2u TINYINT UNSIGNED DEFAULT -1e200;
DECLARE v3 TINYINT DEFAULT 300;
DECLARE v3u TINYINT UNSIGNED DEFAULT 300;
DECLARE v4 TINYINT DEFAULT -300;
DECLARE v4u TINYINT UNSIGNED DEFAULT -300;
DECLARE v5 TINYINT DEFAULT 10 * 10 * 10;
DECLARE v5u TINYINT UNSIGNED DEFAULT 10 * 10 * 10;
DECLARE v6 TINYINT DEFAULT -10 * 10 * 10;
DECLARE v6u TINYINT UNSIGNED DEFAULT -10 * 10 * 10;
DECLARE v7 TINYINT DEFAULT '10';
DECLARE v8 TINYINT DEFAULT '10 ';
DECLARE v9 TINYINT DEFAULT ' 10 ';
DECLARE v10 TINYINT DEFAULT 'String 10 ';
DECLARE v11 TINYINT DEFAULT 'String10';
DECLARE v12 TINYINT DEFAULT '10 String';
DECLARE v13 TINYINT DEFAULT '10String';
DECLARE v14 TINYINT DEFAULT concat('10', ' ');
DECLARE v15 TINYINT DEFAULT concat(' ', '10');
DECLARE v16 TINYINT DEFAULT concat('Hello, ', 'world');
DECLARE v17 DECIMAL(64, 2) DEFAULT 12;
DECLARE v18 DECIMAL(64, 2) DEFAULT 12.123;
DECLARE v19 DECIMAL(64, 2) DEFAULT 11 + 1;
DECLARE v20 DECIMAL(64, 2) DEFAULT 12 + 0.123;
SELECT v1, v1u, v2, v2u, v3, v3u, v4, v4u;
SELECT v5, v5u, v6, v6u;
SELECT v7, v8, v9, v10, v11, v12, v13, v14, v15, v16;
SELECT v17, v18, v19, v20;
END|
CREATE PROCEDURE sp_vars_check_assignment()
BEGIN
DECLARE i1, i2, i3, i4 TINYINT;
DECLARE u1, u2, u3, u4 TINYINT UNSIGNED;
DECLARE d1, d2, d3 DECIMAL(64, 2);
SET i1 = 1e200;
SET i2 = -1e200;
SET i3 = 300;
SET i4 = -300;
SELECT i1, i2, i3, i4;
SET i1 = 10 * 10 * 10;
SET i2 = -10 * 10 * 10;
SET i3 = sign(10 * 10) * 10 * 20;
SET i4 = sign(-10 * 10) * -10 * 20;
SELECT i1, i2, i3, i4;
SET u1 = 1e200;
SET u2 = -1e200;
SET u3 = 300;
SET u4 = -300;
SELECT u1, u2, u3, u4;
SET u1 = 10 * 10 * 10;
SET u2 = -10 * 10 * 10;
SET u3 = sign(10 * 10) * 10 * 20;
SET u4 = sign(-10 * 10) * -10 * 20;
SELECT u1, u2, u3, u4;
SET d1 = 1234;
SET d2 = 1234.12;
SET d3 = 1234.1234;
SELECT d1, d2, d3;
SET d1 = 12 * 100 + 34;
SET d2 = 12 * 100 + 34 + 0.12;
SET d3 = 12 * 100 + 34 + 0.1234;
SELECT d1, d2, d3;
END|
CREATE FUNCTION sp_vars_check_ret1() RETURNS TINYINT
BEGIN
RETURN 1e200;
END|
CREATE FUNCTION sp_vars_check_ret2() RETURNS TINYINT
BEGIN
RETURN 10 * 10 * 10;
END|
CREATE FUNCTION sp_vars_check_ret3() RETURNS TINYINT
BEGIN
RETURN 'Hello, world';
END|
CREATE FUNCTION sp_vars_check_ret4() RETURNS DECIMAL(64, 2)
BEGIN
RETURN 12 * 10 + 34 + 0.1234;
END|

---------------------------------------------------------------
Calling the routines, created in ANSI mode.
---------------------------------------------------------------

CALL sp_vars_check_dflt();
v1	v1u	v2	v2u	v3	v3u	v4	v4u
127	255	-128	0	127	255	-128	0
v5	v5u	v6	v6u
127	255	-128	0
v7	v8	v9	v10	v11	v12	v13	v14	v15	v16
10	10	10	0	0	10	10	10	10	0
v17	v18	v19	v20
12.00	12.12	12.00	12.12
Warnings:
Warning	1264	Out of range value adjusted for column 'v1' at row 1
Warning	1264	Out of range value adjusted for column 'v1u' at row 1
Warning	1264	Out of range value adjusted for column 'v2' at row 1
Warning	1264	Out of range value adjusted for column 'v2u' at row 1
Warning	1264	Out of range value adjusted for column 'v3' at row 1
Warning	1264	Out of range value adjusted for column 'v3u' at row 1
Warning	1264	Out of range value adjusted for column 'v4' at row 1
Warning	1264	Out of range value adjusted for column 'v4u' at row 1
Warning	1264	Out of range value adjusted for column 'v5' at row 1
Warning	1264	Out of range value adjusted for column 'v5u' at row 1
Warning	1264	Out of range value adjusted for column 'v6' at row 1
Warning	1264	Out of range value adjusted for column 'v6u' at row 1
Warning	1366	Incorrect integer value: 'String 10 ' for column 'v10' at row 1
Warning	1366	Incorrect integer value: 'String10' for column 'v11' at row 1
Warning	1265	Data truncated for column 'v12' at row 1
Warning	1265	Data truncated for column 'v13' at row 1
Warning	1366	Incorrect integer value: 'Hello, world' for column 'v16' at row 1
Note	1265	Data truncated for column 'v18' at row 1
Note	1265	Data truncated for column 'v20' at row 1
CALL sp_vars_check_assignment();
i1	i2	i3	i4
127	-128	127	-128
i1	i2	i3	i4
127	-128	127	127
u1	u2	u3	u4
255	0	255	0
u1	u2	u3	u4
255	0	200	200
d1	d2	d3
1234.00	1234.12	1234.12
d1	d2	d3
1234.00	1234.12	1234.12
Warnings:
Warning	1264	Out of range value adjusted for column 'i1' at row 1
Warning	1264	Out of range value adjusted for column 'i2' at row 1
Warning	1264	Out of range value adjusted for column 'i3' at row 1
Warning	1264	Out of range value adjusted for column 'i4' at row 1
Warning	1264	Out of range value adjusted for column 'i1' at row 1
Warning	1264	Out of range value adjusted for column 'i2' at row 1
Warning	1264	Out of range value adjusted for column 'i3' at row 1
Warning	1264	Out of range value adjusted for column 'i4' at row 1
Warning	1264	Out of range value adjusted for column 'u1' at row 1
Warning	1264	Out of range value adjusted for column 'u2' at row 1
Warning	1264	Out of range value adjusted for column 'u3' at row 1
Warning	1264	Out of range value adjusted for column 'u4' at row 1
Warning	1264	Out of range value adjusted for column 'u1' at row 1
Warning	1264	Out of range value adjusted for column 'u2' at row 1
Note	1265	Data truncated for column 'd3' at row 1
Note	1265	Data truncated for column 'd3' at row 1
SELECT sp_vars_check_ret1();
sp_vars_check_ret1()
127
Warnings:
Warning	1264	Out of range value adjusted for column 'sp_vars_check_ret1()' at row 1
SELECT sp_vars_check_ret2();
sp_vars_check_ret2()
127
Warnings:
Warning	1264	Out of range value adjusted for column 'sp_vars_check_ret2()' at row 1
SELECT sp_vars_check_ret3();
sp_vars_check_ret3()
0
Warnings:
Warning	1366	Incorrect integer value: 'Hello, world' for column 'sp_vars_check_ret3()' at row 1
SELECT sp_vars_check_ret4();
sp_vars_check_ret4()
154.12
Warnings:
Note	1265	Data truncated for column 'sp_vars_check_ret4()' at row 1
SET @@sql_mode = 'traditional';

---------------------------------------------------------------
Calling in TRADITIONAL mode the routines, created in ANSI mode.
---------------------------------------------------------------

CALL sp_vars_check_dflt();
v1	v1u	v2	v2u	v3	v3u	v4	v4u
127	255	-128	0	127	255	-128	0
v5	v5u	v6	v6u
127	255	-128	0
v7	v8	v9	v10	v11	v12	v13	v14	v15	v16
10	10	10	0	0	10	10	10	10	0
v17	v18	v19	v20
12.00	12.12	12.00	12.12
Warnings:
Warning	1264	Out of range value adjusted for column 'v1' at row 1
Warning	1264	Out of range value adjusted for column 'v1u' at row 1
Warning	1264	Out of range value adjusted for column 'v2' at row 1
Warning	1264	Out of range value adjusted for column 'v2u' at row 1
Warning	1264	Out of range value adjusted for column 'v3' at row 1
Warning	1264	Out of range value adjusted for column 'v3u' at row 1
Warning	1264	Out of range value adjusted for column 'v4' at row 1
Warning	1264	Out of range value adjusted for column 'v4u' at row 1
Warning	1264	Out of range value adjusted for column 'v5' at row 1
Warning	1264	Out of range value adjusted for column 'v5u' at row 1
Warning	1264	Out of range value adjusted for column 'v6' at row 1
Warning	1264	Out of range value adjusted for column 'v6u' at row 1
Warning	1366	Incorrect integer value: 'String 10 ' for column 'v10' at row 1
Warning	1366	Incorrect integer value: 'String10' for column 'v11' at row 1
Warning	1265	Data truncated for column 'v12' at row 1
Warning	1265	Data truncated for column 'v13' at row 1
Warning	1366	Incorrect integer value: 'Hello, world' for column 'v16' at row 1
Note	1265	Data truncated for column 'v18' at row 1
Note	1265	Data truncated for column 'v20' at row 1
CALL sp_vars_check_assignment();
i1	i2	i3	i4
127	-128	127	-128
i1	i2	i3	i4
127	-128	127	127
u1	u2	u3	u4
255	0	255	0
u1	u2	u3	u4
255	0	200	200
d1	d2	d3
1234.00	1234.12	1234.12
d1	d2	d3
1234.00	1234.12	1234.12
Warnings:
Warning	1264	Out of range value adjusted for column 'i1' at row 1
Warning	1264	Out of range value adjusted for column 'i2' at row 1
Warning	1264	Out of range value adjusted for column 'i3' at row 1
Warning	1264	Out of range value adjusted for column 'i4' at row 1
Warning	1264	Out of range value adjusted for column 'i1' at row 1
Warning	1264	Out of range value adjusted for column 'i2' at row 1
Warning	1264	Out of range value adjusted for column 'i3' at row 1
Warning	1264	Out of range value adjusted for column 'i4' at row 1
Warning	1264	Out of range value adjusted for column 'u1' at row 1
Warning	1264	Out of range value adjusted for column 'u2' at row 1
Warning	1264	Out of range value adjusted for column 'u3' at row 1
Warning	1264	Out of range value adjusted for column 'u4' at row 1
Warning	1264	Out of range value adjusted for column 'u1' at row 1
Warning	1264	Out of range value adjusted for column 'u2' at row 1
Note	1265	Data truncated for column 'd3' at row 1
Note	1265	Data truncated for column 'd3' at row 1
SELECT sp_vars_check_ret1();
sp_vars_check_ret1()
127
Warnings:
Warning	1264	Out of range value adjusted for column 'sp_vars_check_ret1()' at row 1
SELECT sp_vars_check_ret2();
sp_vars_check_ret2()
127
Warnings:
Warning	1264	Out of range value adjusted for column 'sp_vars_check_ret2()' at row 1
SELECT sp_vars_check_ret3();
sp_vars_check_ret3()
0
Warnings:
Warning	1366	Incorrect integer value: 'Hello, world' for column 'sp_vars_check_ret3()' at row 1
SELECT sp_vars_check_ret4();
sp_vars_check_ret4()
154.12
Warnings:
Note	1265	Data truncated for column 'sp_vars_check_ret4()' at row 1
DROP PROCEDURE sp_vars_check_dflt;
DROP PROCEDURE sp_vars_check_assignment;
DROP FUNCTION sp_vars_check_ret1;
DROP FUNCTION sp_vars_check_ret2;
DROP FUNCTION sp_vars_check_ret3;
DROP FUNCTION sp_vars_check_ret4;
CREATE PROCEDURE sp_vars_check_dflt()
BEGIN
DECLARE v1 TINYINT DEFAULT 1e200;
DECLARE v1u TINYINT UNSIGNED DEFAULT 1e200;
DECLARE v2 TINYINT DEFAULT -1e200;
DECLARE v2u TINYINT UNSIGNED DEFAULT -1e200;
DECLARE v3 TINYINT DEFAULT 300;
DECLARE v3u TINYINT UNSIGNED DEFAULT 300;
DECLARE v4 TINYINT DEFAULT -300;
DECLARE v4u TINYINT UNSIGNED DEFAULT -300;
DECLARE v5 TINYINT DEFAULT 10 * 10 * 10;
DECLARE v5u TINYINT UNSIGNED DEFAULT 10 * 10 * 10;
DECLARE v6 TINYINT DEFAULT -10 * 10 * 10;
DECLARE v6u TINYINT UNSIGNED DEFAULT -10 * 10 * 10;
DECLARE v7 TINYINT DEFAULT '10';
DECLARE v8 TINYINT DEFAULT '10 ';
DECLARE v9 TINYINT DEFAULT ' 10 ';
DECLARE v10 TINYINT DEFAULT 'String 10 ';
DECLARE v11 TINYINT DEFAULT 'String10';
DECLARE v12 TINYINT DEFAULT '10 String';
DECLARE v13 TINYINT DEFAULT '10String';
DECLARE v14 TINYINT DEFAULT concat('10', ' ');
DECLARE v15 TINYINT DEFAULT concat(' ', '10');
DECLARE v16 TINYINT DEFAULT concat('Hello, ', 'world');
DECLARE v17 DECIMAL(64, 2) DEFAULT 12;
DECLARE v18 DECIMAL(64, 2) DEFAULT 12.123;
DECLARE v19 DECIMAL(64, 2) DEFAULT 11 + 1;
DECLARE v20 DECIMAL(64, 2) DEFAULT 12 + 0.123;
SELECT v1, v1u, v2, v2u, v3, v3u, v4, v4u;
SELECT v5, v5u, v6, v6u;
SELECT v7, v8, v9, v10, v11, v12, v13, v14, v15, v16;
SELECT v17, v18, v19, v20;
END|
CREATE PROCEDURE sp_vars_check_assignment()
BEGIN
DECLARE i1, i2, i3, i4 TINYINT;
DECLARE u1, u2, u3, u4 TINYINT UNSIGNED;
DECLARE d1, d2, d3 DECIMAL(64, 2);
SET i1 = 1e200;
SET i2 = -1e200;
SET i3 = 300;
SET i4 = -300;
SELECT i1, i2, i3, i4;
SET i1 = 10 * 10 * 10;
SET i2 = -10 * 10 * 10;
SET i3 = sign(10 * 10) * 10 * 20;
SET i4 = sign(-10 * 10) * -10 * 20;
SELECT i1, i2, i3, i4;
SET u1 = 1e200;
SET u2 = -1e200;
SET u3 = 300;
SET u4 = -300;
SELECT u1, u2, u3, u4;
SET u1 = 10 * 10 * 10;
SET u2 = -10 * 10 * 10;
SET u3 = sign(10 * 10) * 10 * 20;
SET u4 = sign(-10 * 10) * -10 * 20;
SELECT u1, u2, u3, u4;
SET d1 = 1234;
SET d2 = 1234.12;
SET d3 = 1234.1234;
SELECT d1, d2, d3;
SET d1 = 12 * 100 + 34;
SET d2 = 12 * 100 + 34 + 0.12;
SET d3 = 12 * 100 + 34 + 0.1234;
SELECT d1, d2, d3;
END|
CREATE FUNCTION sp_vars_check_ret1() RETURNS TINYINT
BEGIN
RETURN 1e200;
END|
CREATE FUNCTION sp_vars_check_ret2() RETURNS TINYINT
BEGIN
RETURN 10 * 10 * 10;
END|
CREATE FUNCTION sp_vars_check_ret3() RETURNS TINYINT
BEGIN
RETURN 'Hello, world';
END|
CREATE FUNCTION sp_vars_check_ret4() RETURNS DECIMAL(64, 2)
BEGIN
RETURN 12 * 10 + 34 + 0.1234;
END|

---------------------------------------------------------------
Calling the routines, created in TRADITIONAL mode.
---------------------------------------------------------------

CALL sp_vars_check_dflt();
ERROR 22003: Out of range value adjusted for column 'v1' at row 1
CALL sp_vars_check_assignment();
ERROR 22003: Out of range value adjusted for column 'i1' at row 1
SELECT sp_vars_check_ret1();
ERROR 22003: Out of range value adjusted for column 'sp_vars_check_ret1()' at row 1
SELECT sp_vars_check_ret2();
ERROR 22003: Out of range value adjusted for column 'sp_vars_check_ret2()' at row 1
SELECT sp_vars_check_ret3();
ERROR HY000: Incorrect integer value: 'Hello, world' for column 'sp_vars_check_ret3()' at row 1
SELECT sp_vars_check_ret4();
sp_vars_check_ret4()
154.12
Warnings:
Note	1265	Data truncated for column 'sp_vars_check_ret4()' at row 1
SET @@sql_mode = 'ansi';
DROP PROCEDURE sp_vars_check_dflt;
DROP PROCEDURE sp_vars_check_assignment;
DROP FUNCTION sp_vars_check_ret1;
DROP FUNCTION sp_vars_check_ret2;
DROP FUNCTION sp_vars_check_ret3;
DROP FUNCTION sp_vars_check_ret4;

---------------------------------------------------------------
BIT data type tests
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
CREATE PROCEDURE p1()
BEGIN
DECLARE v1 BIT;
DECLARE v2 BIT(1);
DECLARE v3 BIT(3) DEFAULT b'101';
DECLARE v4 BIT(64) DEFAULT 0x5555555555555555;
DECLARE v5 BIT(3);
DECLARE v6 BIT(64);
DECLARE v7 BIT(8) DEFAULT 128;
DECLARE v8 BIT(8) DEFAULT '128';
DECLARE v9 BIT(8) DEFAULT ' 128';
DECLARE v10 BIT(8) DEFAULT 'x 128';
SET v1 = v4;
SET v2 = 0;
SET v5 = v4; # check overflow
SET v6 = v3; # check padding
SELECT HEX(v1);
SELECT HEX(v2);
SELECT HEX(v3);
SELECT HEX(v4);
SELECT HEX(v5);
SELECT HEX(v6);
SELECT HEX(v7);
SELECT HEX(v8);
SELECT HEX(v9);
SELECT HEX(v10);
END|
CALL p1();
HEX(v1)
01
HEX(v2)
00
HEX(v3)
05
HEX(v4)
5555555555555555
HEX(v5)
07
HEX(v6)
0000000000000005
HEX(v7)
80
HEX(v8)
FF
HEX(v9)
FF
HEX(v10)
FF
Warnings:
Warning	1264	Out of range value adjusted for column 'v8' at row 1
Warning	1264	Out of range value adjusted for column 'v9' at row 1
Warning	1264	Out of range value adjusted for column 'v10' at row 1
Warning	1264	Out of range value adjusted for column 'v1' at row 1
Warning	1264	Out of range value adjusted for column 'v5' at row 1
DROP PROCEDURE p1;

---------------------------------------------------------------
CASE expression tests.
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
Warnings:
Note	1305	PROCEDURE p1 does not exist
DROP PROCEDURE IF EXISTS p2;
Warnings:
Note	1305	PROCEDURE p2 does not exist
DROP TABLE IF EXISTS t1;
Warnings:
Note	1051	Unknown table 't1'
CREATE TABLE t1(log_msg VARCHAR(1024));
CREATE PROCEDURE p1(arg VARCHAR(255))
BEGIN
INSERT INTO t1 VALUES('p1: step1');
CASE arg * 10
WHEN 10 * 10 THEN
INSERT INTO t1 VALUES('p1: case1: on 10');
WHEN 10 * 10 + 10 * 10 THEN
BEGIN
CASE arg / 10
WHEN 1 THEN
INSERT INTO t1 VALUES('p1: case1: case2: on 1');
WHEN 2 THEN
BEGIN
DECLARE i TINYINT DEFAULT 10;
WHILE i > 0 DO
INSERT INTO t1 VALUES(CONCAT('p1: case1: case2: loop: i: ', i));
CASE MOD(i, 2)
WHEN 0 THEN
INSERT INTO t1 VALUES('p1: case1: case2: loop: i is even');
WHEN 1 THEN
INSERT INTO t1 VALUES('p1: case1: case2: loop: i is odd');
ELSE
INSERT INTO t1 VALUES('p1: case1: case2: loop: ERROR');
END CASE;
SET i = i - 1;
END WHILE;
END;
ELSE
INSERT INTO t1 VALUES('p1: case1: case2: ERROR');
END CASE;
CASE arg
WHEN 10 THEN
INSERT INTO t1 VALUES('p1: case1: case3: on 10');
WHEN 20 THEN
INSERT INTO t1 VALUES('p1: case1: case3: on 20');
ELSE
INSERT INTO t1 VALUES('p1: case1: case3: ERROR');
END CASE;
END;
ELSE
INSERT INTO t1 VALUES('p1: case1: ERROR');
END CASE;
CASE arg * 10
WHEN 10 * 10 THEN
INSERT INTO t1 VALUES('p1: case4: on 10');
WHEN 10 * 10 + 10 * 10 THEN
BEGIN
CASE arg / 10
WHEN 1 THEN
INSERT INTO t1 VALUES('p1: case4: case5: on 1');
WHEN 2 THEN
BEGIN
DECLARE i TINYINT DEFAULT 10;
WHILE i > 0 DO
INSERT INTO t1 VALUES(CONCAT('p1: case4: case5: loop: i: ', i));
CASE MOD(i, 2)
WHEN 0 THEN
INSERT INTO t1 VALUES('p1: case4: case5: loop: i is even');
WHEN 1 THEN
INSERT INTO t1 VALUES('p1: case4: case5: loop: i is odd');
ELSE
INSERT INTO t1 VALUES('p1: case4: case5: loop: ERROR');
END CASE;
SET i = i - 1;
END WHILE;
END;
ELSE
INSERT INTO t1 VALUES('p1: case4: case5: ERROR');
END CASE;
CASE arg
WHEN 10 THEN
INSERT INTO t1 VALUES('p1: case4: case6: on 10');
WHEN 20 THEN
INSERT INTO t1 VALUES('p1: case4: case6: on 20');
ELSE
INSERT INTO t1 VALUES('p1: case4: case6: ERROR');
END CASE;
END;
ELSE
INSERT INTO t1 VALUES('p1: case4: ERROR');
END CASE;
END|
CREATE PROCEDURE p2()
BEGIN
DECLARE i TINYINT DEFAULT 3;
WHILE i > 0 DO
IF MOD(i, 2) = 0 THEN
SET @_test_session_var = 10;
ELSE
SET @_test_session_var = 'test';
END IF;
CASE @_test_session_var
WHEN 10 THEN
INSERT INTO t1 VALUES('p2: case: numerical type');
WHEN 'test' THEN
INSERT INTO t1 VALUES('p2: case: string type');
ELSE
INSERT INTO t1 VALUES('p2: case: ERROR');
END CASE;
SET i = i - 1;
END WHILE;
END|
CALL p1(10);
CALL p1(20);
CALL p2();
SELECT * FROM t1;
log_msg
p1: step1
p1: case1: on 10
p1: case4: on 10
p1: step1
p1: case1: case2: loop: i: 10
p1: case1: case2: loop: i is even
p1: case1: case2: loop: i: 9
p1: case1: case2: loop: i is odd
p1: case1: case2: loop: i: 8
p1: case1: case2: loop: i is even
p1: case1: case2: loop: i: 7
p1: case1: case2: loop: i is odd
p1: case1: case2: loop: i: 6
p1: case1: case2: loop: i is even
p1: case1: case2: loop: i: 5
p1: case1: case2: loop: i is odd
p1: case1: case2: loop: i: 4
p1: case1: case2: loop: i is even
p1: case1: case2: loop: i: 3
p1: case1: case2: loop: i is odd
p1: case1: case2: loop: i: 2
p1: case1: case2: loop: i is even
p1: case1: case2: loop: i: 1
p1: case1: case2: loop: i is odd
p1: case1: case3: on 20
p1: case4: case5: loop: i: 10
p1: case4: case5: loop: i is even
p1: case4: case5: loop: i: 9
p1: case4: case5: loop: i is odd
p1: case4: case5: loop: i: 8
p1: case4: case5: loop: i is even
p1: case4: case5: loop: i: 7
p1: case4: case5: loop: i is odd
p1: case4: case5: loop: i: 6
p1: case4: case5: loop: i is even
p1: case4: case5: loop: i: 5
p1: case4: case5: loop: i is odd
p1: case4: case5: loop: i: 4
p1: case4: case5: loop: i is even
p1: case4: case5: loop: i: 3
p1: case4: case5: loop: i is odd
p1: case4: case5: loop: i: 2
p1: case4: case5: loop: i is even
p1: case4: case5: loop: i: 1
p1: case4: case5: loop: i is odd
p1: case4: case6: on 20
p2: case: string type
p2: case: numerical type
p2: case: string type
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;

---------------------------------------------------------------
BUG#14161
---------------------------------------------------------------

DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1(col BIGINT UNSIGNED);
INSERT INTO t1 VALUE(18446744073709551614);
CREATE PROCEDURE p1(IN arg BIGINT UNSIGNED)
BEGIN
SELECT arg;
SELECT * FROM t1;
SELECT * FROM t1 WHERE col = arg;
END|
CALL p1(18446744073709551614);
arg
18446744073709551614
col
18446744073709551614
col
18446744073709551614
DROP TABLE t1;
DROP PROCEDURE p1;

---------------------------------------------------------------
BUG#13705
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
CREATE PROCEDURE p1(x VARCHAR(10), y CHAR(3)) READS SQL DATA
BEGIN
SELECT x, y;
END|
CALL p1('alpha', 'abc');
x	y
alpha	abc
CALL p1('alpha', 'abcdef');
x	y
alpha	abc
Warnings:
Warning	1265	Data truncated for column 'y' at row 1
DROP PROCEDURE p1;

---------------------------------------------------------------
BUG#13675
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
DROP TABLE IF EXISTS t1;
CREATE PROCEDURE p1(x DATETIME)
BEGIN
CREATE TABLE t1 SELECT x;
SHOW CREATE TABLE t1;
DROP TABLE t1;
END|
CALL p1(NOW());
Table	Create Table
t1	CREATE TABLE "t1" (
  "x" varbinary(19) default NULL
)
CALL p1('test');
Table	Create Table
t1	CREATE TABLE "t1" (
  "x" varbinary(19) default NULL
)
Warnings:
Warning	1264	Out of range value adjusted for column 'x' at row 1
DROP PROCEDURE p1;

---------------------------------------------------------------
BUG#12976
---------------------------------------------------------------

DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
CREATE TABLE t1(b BIT(1));
INSERT INTO t1(b) VALUES(b'0'), (b'1');
CREATE PROCEDURE p1()
BEGIN
SELECT HEX(b),
b = 0,
b = FALSE,
b IS FALSE,
b = 1,
b = TRUE,
b IS TRUE
FROM t1;
END|
CREATE PROCEDURE p2()
BEGIN
DECLARE vb BIT(1);
SELECT b INTO vb FROM t1 WHERE b = 0;
SELECT HEX(vb),
vb = 0,
vb = FALSE,
vb IS FALSE,
vb = 1,
vb = TRUE,
vb IS TRUE;
SELECT b INTO vb FROM t1 WHERE b = 1;
SELECT HEX(vb),
vb = 0,
vb = FALSE,
vb IS FALSE,
vb = 1,
vb = TRUE,
vb IS TRUE;
END|
call p1();
HEX(b)	b = 0	b = FALSE	b IS FALSE	b = 1	b = TRUE	b IS TRUE

0	1	1	1	0	0	0
1	0	0	0	1	1	1
call p2();
HEX(vb)	vb = 0	vb = FALSE	vb IS FALSE	vb = 1	vb = TRUE	vb IS TRUE
00	1	1	1	0	0	0
HEX(vb)	vb = 0	vb = FALSE	vb IS FALSE	vb = 1	vb = TRUE	vb IS TRUE
01	0	0	1	1	1	0
DROP TABLE t1;
DROP PROCEDURE p1;
DROP PROCEDURE p2;

---------------------------------------------------------------
BUG#9572
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
DROP PROCEDURE IF EXISTS p3;
DROP PROCEDURE IF EXISTS p4;
DROP PROCEDURE IF EXISTS p5;
DROP PROCEDURE IF EXISTS p6;
SET @@sql_mode = 'traditional';
CREATE PROCEDURE p1()
BEGIN
DECLARE v TINYINT DEFAULT 1e200;
SELECT v;
END|
CREATE PROCEDURE p2()
BEGIN
DECLARE v DECIMAL(5) DEFAULT 1e200;
SELECT v;
END|
CREATE PROCEDURE p3()
BEGIN
DECLARE v CHAR(5) DEFAULT 'abcdef';
SELECT v LIKE 'abc___';
END|
CREATE PROCEDURE p4(arg VARCHAR(2))
BEGIN
DECLARE var VARCHAR(1);
SET var := arg;
SELECT arg, var;
END|
CREATE PROCEDURE p5(arg CHAR(2))
BEGIN
DECLARE var CHAR(1);
SET var := arg;
SELECT arg, var;
END|
CREATE PROCEDURE p6(arg DECIMAL(2))
BEGIN
DECLARE var DECIMAL(1);
SET var := arg;
SELECT arg, var;
END|
CALL p1();
ERROR 22003: Out of range value adjusted for column 'v' at row 1
CALL p2();
ERROR 22003: Out of range value adjusted for column 'v' at row 1
CALL p3();
ERROR 22001: Data too long for column 'v' at row 1
CALL p4('aaa');
ERROR 22001: Data too long for column 'arg' at row 1
CALL p5('aa');
ERROR 22001: Data too long for column 'var' at row 1
CALL p6(10);
ERROR 22003: Out of range value adjusted for column 'var' at row 1
SET @@sql_mode = 'ansi';
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
DROP PROCEDURE p4;
DROP PROCEDURE p5;
DROP PROCEDURE p6;

---------------------------------------------------------------
BUG#9078
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
CREATE PROCEDURE p1 (arg DECIMAL(64,2))
BEGIN
DECLARE var DECIMAL(64,2);
SET var = arg;
SELECT var;
END|
CALL p1(1929);
var
1929.00
CALL p1(1929.00);
var
1929.00
CALL p1(1929.003);
var
1929.00
Warnings:
Note	1265	Data truncated for column 'arg' at row 1
DROP PROCEDURE p1;

---------------------------------------------------------------
BUG#8768
---------------------------------------------------------------

DROP FUNCTION IF EXISTS f1;
CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT
BEGIN
RETURN arg;
END|
SELECT f1(-2500);
f1(-2500)
0
Warnings:
Warning	1264	Out of range value adjusted for column 'arg' at row 1
SET @@sql_mode = 'traditional';
SELECT f1(-2500);
ERROR 22003: Out of range value adjusted for column 'arg' at row 1
DROP FUNCTION f1;
CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT
BEGIN
RETURN arg;
END|
SELECT f1(-2500);
ERROR 22003: Out of range value adjusted for column 'arg' at row 1
SET @@sql_mode = 'ansi';
DROP FUNCTION f1;

---------------------------------------------------------------
BUG#8769
---------------------------------------------------------------

DROP FUNCTION IF EXISTS f1;
CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT
BEGIN
RETURN arg;
END|
SELECT f1(8388699);
f1(8388699)
8388607
Warnings:
Warning	1264	Out of range value adjusted for column 'arg' at row 1
SET @@sql_mode = 'traditional';
SELECT f1(8388699);
ERROR 22003: Out of range value adjusted for column 'arg' at row 1
DROP FUNCTION f1;
CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT
BEGIN
RETURN arg;
END|
SELECT f1(8388699);
ERROR 22003: Out of range value adjusted for column 'arg' at row 1
SET @@sql_mode = 'ansi';
DROP FUNCTION f1;

---------------------------------------------------------------
BUG#8702
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(col VARCHAR(255));
INSERT INTO t1(col) VALUES('Hello, world!');
CREATE PROCEDURE p1()
BEGIN
DECLARE sp_var INTEGER;
SELECT col INTO sp_var FROM t1 LIMIT 1;
SET @user_var = sp_var;
SELECT sp_var;
SELECT @user_var;
END|
CALL p1();
sp_var
0
@user_var
0
Warnings:
Warning	1264	Out of range value adjusted for column 'sp_var' at row 1
DROP PROCEDURE p1;
DROP TABLE t1;

---------------------------------------------------------------
BUG#12903
---------------------------------------------------------------

DROP FUNCTION IF EXISTS f1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(txt VARCHAR(255));
CREATE FUNCTION f1(arg VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
DECLARE v1 VARCHAR(255);
DECLARE v2 VARCHAR(255);
SET v1 = CONCAT(LOWER(arg), UPPER(arg));
SET v2 = CONCAT(LOWER(v1), UPPER(v1));
INSERT INTO t1 VALUES(v1), (v2);
RETURN CONCAT(LOWER(arg), UPPER(arg));
END|
SELECT f1('_aBcDe_');
f1('_aBcDe_')
_abcde__ABCDE_
SELECT * FROM t1;
txt
_abcde__ABCDE_
_abcde__abcde__ABCDE__ABCDE_
DROP FUNCTION f1;
DROP TABLE t1;

---------------------------------------------------------------
BUG#13808
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
DROP FUNCTION IF EXISTS f1;
CREATE PROCEDURE p1(arg ENUM('a', 'b'))
BEGIN
SELECT arg;
END|
CREATE PROCEDURE p2(arg ENUM('a', 'b'))
BEGIN
DECLARE var ENUM('c', 'd') DEFAULT arg;
SELECT arg, var;
END|
CREATE FUNCTION f1(arg ENUM('a', 'b')) RETURNS ENUM('c', 'd')
BEGIN
RETURN arg;
END|
CALL p1('c');
arg

Warnings:
Warning	1265	Data truncated for column 'arg' at row 1
CALL p2('a');
arg	var
a	
Warnings:
Warning	1265	Data truncated for column 'var' at row 1
SELECT f1('a');
f1('a')

Warnings:
Warning	1265	Data truncated for column 'f1('a')' at row 1
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP FUNCTION f1;

---------------------------------------------------------------
BUG#13909
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
CREATE PROCEDURE p1(arg VARCHAR(255))
BEGIN
SELECT CHARSET(arg);
END|
CREATE PROCEDURE p2(arg VARCHAR(255) CHARACTER SET UTF8)
BEGIN
SELECT CHARSET(arg);
END|
CALL p1('t');
CHARSET(arg)
latin1
CALL p1(_UTF8 't');
CHARSET(arg)
latin1
CALL p2('t');
CHARSET(arg)
utf8
CALL p2(_LATIN1 't');
CHARSET(arg)
utf8
DROP PROCEDURE p1;
DROP PROCEDURE p2;

---------------------------------------------------------------
BUG#14188
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
CREATE PROCEDURE p1(arg1 BINARY(2), arg2 VARBINARY(2))
BEGIN
DECLARE var1 BINARY(2) DEFAULT 0x41;
DECLARE var2 VARBINARY(2) DEFAULT 0x42;
SELECT HEX(arg1), HEX(arg2);
SELECT HEX(var1), HEX(var2);
END|
CALL p1(0x41, 0x42);
HEX(arg1)	HEX(arg2)
4100	42
HEX(var1)	HEX(var2)
4100	42
DROP PROCEDURE p1;

---------------------------------------------------------------
BUG#15148
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(col1 TINYINT, col2 TINYINT);
INSERT INTO t1 VALUES(1, 2), (11, 12);
CREATE PROCEDURE p1(arg TINYINT)
BEGIN
SELECT arg;
END|
CALL p1((1, 2));
ERROR 21000: Operand should contain 1 column(s)
CALL p1((SELECT * FROM t1 LIMIT 1));
ERROR 21000: Operand should contain 1 column(s)
CALL p1((SELECT col1, col2 FROM t1 LIMIT 1));
ERROR 21000: Operand should contain 1 column(s)
DROP PROCEDURE p1;
DROP TABLE t1;

---------------------------------------------------------------
BUG#13613
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
CREATE PROCEDURE p1(x VARCHAR(50))
BEGIN
SET x = SUBSTRING(x, 1, 3);
SELECT x;
END|
CREATE FUNCTION f1(x VARCHAR(50)) RETURNS VARCHAR(50)
BEGIN
RETURN SUBSTRING(x, 1, 3);
END|
CALL p1('abcdef');
x
abc
SELECT f1('ABCDEF');
f1('ABCDEF')
ABC
DROP PROCEDURE p1;
DROP FUNCTION f1;

---------------------------------------------------------------
BUG#13665
---------------------------------------------------------------

DROP FUNCTION IF EXISTS f1;
CREATE FUNCTION f1() RETURNS VARCHAR(20000)
BEGIN
DECLARE var VARCHAR(2000);
SET var = '';
SET var = CONCAT(var, 'abc');
SET var = CONCAT(var, '');
RETURN var;
END|
SELECT f1();
f1()
abc
DROP FUNCTION f1;

--- New file ---
+++ mysql-test/sp-vars.test	05/12/07 17:01:09
###########################################################################
#
# Cleanup.
#
###########################################################################

--disable_warnings

# Drop stored routines (if any) for general SP-vars test cases. These routines
# are created in include/sp-vars.inc file.

DROP PROCEDURE IF EXISTS sp_vars_check_dflt;
DROP PROCEDURE IF EXISTS sp_vars_check_assignment;
DROP FUNCTION IF EXISTS sp_vars_check_ret1;
DROP FUNCTION IF EXISTS sp_vars_check_ret2;
DROP FUNCTION IF EXISTS sp_vars_check_ret3;
DROP FUNCTION IF EXISTS sp_vars_check_ret4;

--enable_warnings

###########################################################################
#
# Some general tests for SP-vars functionality.
#
###########################################################################

# Create the procedure in ANSI mode. Check that all necessary warnings are
# emitted properly.

SET @@sql_mode = 'ansi';

--source include/sp-vars.inc

--echo
--echo ---------------------------------------------------------------
--echo Calling the routines, created in ANSI mode.
--echo ---------------------------------------------------------------
--echo

CALL sp_vars_check_dflt();

CALL sp_vars_check_assignment();

SELECT sp_vars_check_ret1();

SELECT sp_vars_check_ret2();

SELECT sp_vars_check_ret3();

SELECT sp_vars_check_ret4();

# Check that changing sql_mode after creating a store procedure does not
# matter.

SET @@sql_mode = 'traditional';

--echo
--echo ---------------------------------------------------------------
--echo Calling in TRADITIONAL mode the routines, created in ANSI mode.
--echo ---------------------------------------------------------------
--echo

CALL sp_vars_check_dflt();

CALL sp_vars_check_assignment();

SELECT sp_vars_check_ret1();

SELECT sp_vars_check_ret2();

SELECT sp_vars_check_ret3();

SELECT sp_vars_check_ret4();

# Create the procedure in TRADITIONAL mode. Check that error will be thrown on
# execution.

DROP PROCEDURE sp_vars_check_dflt;
DROP PROCEDURE sp_vars_check_assignment;
DROP FUNCTION sp_vars_check_ret1;
DROP FUNCTION sp_vars_check_ret2;
DROP FUNCTION sp_vars_check_ret3;
DROP FUNCTION sp_vars_check_ret4;

--source include/sp-vars.inc

--echo
--echo ---------------------------------------------------------------
--echo Calling the routines, created in TRADITIONAL mode.
--echo ---------------------------------------------------------------
--echo

--error ER_WARN_DATA_OUT_OF_RANGE
CALL sp_vars_check_dflt();

--error ER_WARN_DATA_OUT_OF_RANGE
CALL sp_vars_check_assignment();

--error ER_WARN_DATA_OUT_OF_RANGE
SELECT sp_vars_check_ret1();

--error ER_WARN_DATA_OUT_OF_RANGE
SELECT sp_vars_check_ret2();

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
SELECT sp_vars_check_ret3();

# TODO: Is it an error, that only a warning is emitted here? Check the same
# behaviour with tables.

SELECT sp_vars_check_ret4();

SET @@sql_mode = 'ansi';

#
# Cleanup.
#

DROP PROCEDURE sp_vars_check_dflt;
DROP PROCEDURE sp_vars_check_assignment;
DROP FUNCTION sp_vars_check_ret1;
DROP FUNCTION sp_vars_check_ret2;
DROP FUNCTION sp_vars_check_ret3;
DROP FUNCTION sp_vars_check_ret4;

###########################################################################
#
# Tests for BIT data type.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BIT data type tests
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
--enable_warnings

#
# Test case.
#

delimiter |;
CREATE PROCEDURE p1()
BEGIN
  DECLARE v1 BIT;
  DECLARE v2 BIT(1);
  DECLARE v3 BIT(3) DEFAULT b'101';
  DECLARE v4 BIT(64) DEFAULT 0x5555555555555555;
  DECLARE v5 BIT(3);
  DECLARE v6 BIT(64);
  DECLARE v7 BIT(8) DEFAULT 128;
  DECLARE v8 BIT(8) DEFAULT '128';
  DECLARE v9 BIT(8) DEFAULT ' 128';
  DECLARE v10 BIT(8) DEFAULT 'x 128';

  SET v1 = v4;
  SET v2 = 0;
  SET v5 = v4; # check overflow
  SET v6 = v3; # check padding

  SELECT HEX(v1);
  SELECT HEX(v2);
  SELECT HEX(v3);
  SELECT HEX(v4);
  SELECT HEX(v5);
  SELECT HEX(v6);
  SELECT HEX(v7);
  SELECT HEX(v8);
  SELECT HEX(v9);
  SELECT HEX(v10);
END|
delimiter ;|

CALL p1();

#
# Cleanup.
#

DROP PROCEDURE p1;

###########################################################################
#
# Tests for CASE statements functionality:
#   - test for general functionality (scopes, nested cases, CASE in loops);
#   - test that if type of the CASE expression is changed on each iteration,
#     the execution will be correct.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo CASE expression tests.
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
DROP TABLE IF EXISTS t1;

#
# Test case.
#

CREATE TABLE t1(log_msg VARCHAR(1024));

delimiter |;

CREATE PROCEDURE p1(arg VARCHAR(255))
BEGIN
  INSERT INTO t1 VALUES('p1: step1');

  CASE arg * 10
    WHEN 10 * 10 THEN
      INSERT INTO t1 VALUES('p1: case1: on 10');
    WHEN 10 * 10 + 10 * 10 THEN
      BEGIN
        CASE arg / 10
          WHEN 1 THEN
            INSERT INTO t1 VALUES('p1: case1: case2: on 1');
          WHEN 2 THEN
            BEGIN
              DECLARE i TINYINT DEFAULT 10;

              WHILE i > 0 DO
                INSERT INTO t1 VALUES(CONCAT('p1: case1: case2: loop: i: ', i));
                
                CASE MOD(i, 2)
                  WHEN 0 THEN
                    INSERT INTO t1 VALUES('p1: case1: case2: loop: i is even');
                  WHEN 1 THEN
                    INSERT INTO t1 VALUES('p1: case1: case2: loop: i is odd');
                  ELSE
                    INSERT INTO t1 VALUES('p1: case1: case2: loop: ERROR');
                END CASE;
                    
                SET i = i - 1;
              END WHILE;
            END;
          ELSE
            INSERT INTO t1 VALUES('p1: case1: case2: ERROR');
        END CASE;

        CASE arg
          WHEN 10 THEN
            INSERT INTO t1 VALUES('p1: case1: case3: on 10');
          WHEN 20 THEN
            INSERT INTO t1 VALUES('p1: case1: case3: on 20');
          ELSE
            INSERT INTO t1 VALUES('p1: case1: case3: ERROR');
        END CASE;
      END;
    ELSE
      INSERT INTO t1 VALUES('p1: case1: ERROR');
  END CASE;

  CASE arg * 10
    WHEN 10 * 10 THEN
      INSERT INTO t1 VALUES('p1: case4: on 10');
    WHEN 10 * 10 + 10 * 10 THEN
      BEGIN
        CASE arg / 10
          WHEN 1 THEN
            INSERT INTO t1 VALUES('p1: case4: case5: on 1');
          WHEN 2 THEN
            BEGIN
              DECLARE i TINYINT DEFAULT 10;

              WHILE i > 0 DO
                INSERT INTO t1 VALUES(CONCAT('p1: case4: case5: loop: i: ', i));
                
                CASE MOD(i, 2)
                  WHEN 0 THEN
                    INSERT INTO t1 VALUES('p1: case4: case5: loop: i is even');
                  WHEN 1 THEN
                    INSERT INTO t1 VALUES('p1: case4: case5: loop: i is odd');
                  ELSE
                    INSERT INTO t1 VALUES('p1: case4: case5: loop: ERROR');
                END CASE;
                    
                SET i = i - 1;
              END WHILE;
            END;
          ELSE
            INSERT INTO t1 VALUES('p1: case4: case5: ERROR');
        END CASE;

        CASE arg
          WHEN 10 THEN
            INSERT INTO t1 VALUES('p1: case4: case6: on 10');
          WHEN 20 THEN
            INSERT INTO t1 VALUES('p1: case4: case6: on 20');
          ELSE
            INSERT INTO t1 VALUES('p1: case4: case6: ERROR');
        END CASE;
      END;
    ELSE
      INSERT INTO t1 VALUES('p1: case4: ERROR');
  END CASE;
END|

CREATE PROCEDURE p2()
BEGIN
  DECLARE i TINYINT DEFAULT 3;

  WHILE i > 0 DO
    IF MOD(i, 2) = 0 THEN
      SET @_test_session_var = 10;
    ELSE
      SET @_test_session_var = 'test';
    END IF;

    CASE @_test_session_var
      WHEN 10 THEN
        INSERT INTO t1 VALUES('p2: case: numerical type');
      WHEN 'test' THEN
        INSERT INTO t1 VALUES('p2: case: string type');
      ELSE
        INSERT INTO t1 VALUES('p2: case: ERROR');
    END CASE;

    SET i = i - 1;
  END WHILE;
END|

delimiter ;|

CALL p1(10);
CALL p1(20);

CALL p2();

SELECT * FROM t1;

#
# Cleanup.
#

DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;

###########################################################################
#
# Test case for BUG#14161: Stored procedure cannot retrieve bigint unsigned.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#14161
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
--enable_warnings

#
# Test case.
#

CREATE TABLE t1(col BIGINT UNSIGNED);

INSERT INTO t1 VALUE(18446744073709551614);

delimiter |;
CREATE PROCEDURE p1(IN arg BIGINT UNSIGNED)
BEGIN
  SELECT arg;
  SELECT * FROM t1;
  SELECT * FROM t1 WHERE col = arg;
END|
delimiter ;|

CALL p1(18446744073709551614);

#
# Cleanup.
#

DROP TABLE t1;
DROP PROCEDURE p1;

###########################################################################
#
# Test case for BUG#13705: parameters to stored procedures are not verified.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#13705
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
--enable_warnings

#
# Test case.
#

delimiter |;
CREATE PROCEDURE p1(x VARCHAR(10), y CHAR(3)) READS SQL DATA
BEGIN
  SELECT x, y;
END|
delimiter ;|

CALL p1('alpha', 'abc');
CALL p1('alpha', 'abcdef');

#
# Cleanup.
#

DROP PROCEDURE p1;

###########################################################################
#
# Test case for BUG#13675: DATETIME/DATE type in store proc param seems to be
# converted as varbinary.
#
# TODO: test case failed.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#13675
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP TABLE IF EXISTS t1;
--enable_warnings

#
# Test case.
#

delimiter |;
CREATE PROCEDURE p1(x DATETIME)
BEGIN
  CREATE TABLE t1 SELECT x;
  SHOW CREATE TABLE t1;
  DROP TABLE t1;
END|
delimiter ;|

CALL p1(NOW());

CALL p1('test');

#
# Cleanup.
#

DROP PROCEDURE p1;

###########################################################################
#
# Test case for BUG#12976: Boolean values reversed in stored procedures?
#
# TODO: test case failed.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#12976
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
--enable_warnings

#
# Test case.
#

CREATE TABLE t1(b BIT(1));

INSERT INTO t1(b) VALUES(b'0'), (b'1');

delimiter |;
CREATE PROCEDURE p1()
BEGIN
  SELECT HEX(b),
    b = 0,
    b = FALSE,
    b IS FALSE,
    b = 1,
    b = TRUE,
    b IS TRUE
  FROM t1;
END|

CREATE PROCEDURE p2()
BEGIN
  DECLARE vb BIT(1);
  SELECT b INTO vb FROM t1 WHERE b = 0;

  SELECT HEX(vb),
    vb = 0,
    vb = FALSE,
    vb IS FALSE,
    vb = 1,
    vb = TRUE,
    vb IS TRUE;

  SELECT b INTO vb FROM t1 WHERE b = 1;

  SELECT HEX(vb),
    vb = 0,
    vb = FALSE,
    vb IS FALSE,
    vb = 1,
    vb = TRUE,
    vb IS TRUE;
END|
delimiter ;|

# The expected and correct result.

call p1();

# The wrong result. Note that only hex(vb) works, but is printed with two
# digits for some reason in this case.

call p2();

#
# Cleanup.
#

DROP TABLE t1;
DROP PROCEDURE p1;
DROP PROCEDURE p2;

###########################################################################
#
# Test case for BUG#9572: Stored procedures: variable type declarations
# ignored.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#9572
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
DROP PROCEDURE IF EXISTS p3;

DROP PROCEDURE IF EXISTS p4;
DROP PROCEDURE IF EXISTS p5;
DROP PROCEDURE IF EXISTS p6;
--enable_warnings

#
# Test case.
#

SET @@sql_mode = 'traditional';

delimiter |;

CREATE PROCEDURE p1()
BEGIN
  DECLARE v TINYINT DEFAULT 1e200;
  SELECT v;
END|

CREATE PROCEDURE p2()
BEGIN
  DECLARE v DECIMAL(5) DEFAULT 1e200;
  SELECT v;
END|

CREATE PROCEDURE p3()
BEGIN
  DECLARE v CHAR(5) DEFAULT 'abcdef';
  SELECT v LIKE 'abc___';
END|

CREATE PROCEDURE p4(arg VARCHAR(2))
BEGIN
    DECLARE var VARCHAR(1);
    SET var := arg;
    SELECT arg, var;
END|

CREATE PROCEDURE p5(arg CHAR(2))
BEGIN
    DECLARE var CHAR(1);
    SET var := arg;
    SELECT arg, var;
END|

CREATE PROCEDURE p6(arg DECIMAL(2))
BEGIN
    DECLARE var DECIMAL(1);
    SET var := arg;
    SELECT arg, var;
END|

delimiter ;|

--error ER_WARN_DATA_OUT_OF_RANGE
CALL p1();
--error ER_WARN_DATA_OUT_OF_RANGE
CALL p2();
--error ER_DATA_TOO_LONG
CALL p3();

--error ER_DATA_TOO_LONG
CALL p4('aaa'); 
--error ER_DATA_TOO_LONG
CALL p5('aa');
--error ER_WARN_DATA_OUT_OF_RANGE
CALL p6(10);

#
# Cleanup.
#

SET @@sql_mode = 'ansi';

DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;

DROP PROCEDURE p4;
DROP PROCEDURE p5;
DROP PROCEDURE p6;

###########################################################################
#
# Test case for BUG#9078: STORED PROCDURE: Decimal digits are not displayed
# when we use DECIMAL datatype.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#9078
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
--enable_warnings

#
# Test case.
#

delimiter |;
CREATE PROCEDURE p1 (arg DECIMAL(64,2))
BEGIN
  DECLARE var DECIMAL(64,2);

  SET var = arg;
  SELECT var;
END|
delimiter ;|

CALL p1(1929);
CALL p1(1929.00);
CALL p1(1929.003);

#
# Cleanup.
#

DROP PROCEDURE p1;

###########################################################################
#
# Test case for BUG#8768: Functions: For any unsigned data type, -ve values can
# be passed and returned.
#
# TODO: there is a bug here -- the function created in ANSI mode should not
# throw errors instead of warnings if called in TRADITIONAL mode.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#8768
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP FUNCTION IF EXISTS f1;
--enable_warnings

#
# Test case.
#

# Create a function in ANSI mode.

delimiter |;
CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT
BEGIN
  RETURN arg;
END|
delimiter ;|

SELECT f1(-2500);

# Call in TRADITIONAL mode the function created in ANSI mode.

SET @@sql_mode = 'traditional';

# TODO: a warning should be emitted here.
--error ER_WARN_DATA_OUT_OF_RANGE
SELECT f1(-2500);

# Recreate the function in TRADITIONAL mode.

DROP FUNCTION f1;

delimiter |;
CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT
BEGIN
  RETURN arg;
END|
delimiter ;|

--error ER_WARN_DATA_OUT_OF_RANGE
SELECT f1(-2500);

#
# Cleanup.
#

SET @@sql_mode = 'ansi';

DROP FUNCTION f1;

###########################################################################
#
# Test case for BUG#8769: Functions: For Int datatypes, out of range values can
# be passed and returned.
#
# TODO: there is a bug here -- the function created in ANSI mode should not
# throw errors instead of warnings if called in TRADITIONAL mode.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#8769
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP FUNCTION IF EXISTS f1;
--enable_warnings

#
# Test case.
#

# Create a function in ANSI mode.

delimiter |;
CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT
BEGIN
  RETURN arg;
END|
delimiter ;|

SELECT f1(8388699);

# Call in TRADITIONAL mode the function created in ANSI mode.

SET @@sql_mode = 'traditional';

# TODO: a warning should be emitted here.
--error ER_WARN_DATA_OUT_OF_RANGE
SELECT f1(8388699);

# Recreate the function in TRADITIONAL mode.

DROP FUNCTION f1;

delimiter |;
CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT
BEGIN
  RETURN arg;
END|
delimiter ;|

--error ER_WARN_DATA_OUT_OF_RANGE
SELECT f1(8388699);

#
# Cleanup.
#

SET @@sql_mode = 'ansi';

DROP FUNCTION f1;

###########################################################################
#
# Test case for BUG#8702: Stored Procedures: No Error/Warning shown for
# inappropriate data type matching.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#8702
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP TABLE IF EXISTS t1;
--enable_warnings

#
# Test case.
#

CREATE TABLE t1(col VARCHAR(255));

INSERT INTO t1(col) VALUES('Hello, world!');

delimiter |;
CREATE PROCEDURE p1()
BEGIN
  DECLARE sp_var INTEGER;

  SELECT col INTO sp_var FROM t1 LIMIT 1;
  SET @user_var = sp_var;

  SELECT sp_var;
  SELECT @user_var;
END|
delimiter ;|

CALL p1();

#
# Cleanup.
#

DROP PROCEDURE p1;
DROP TABLE t1;

###########################################################################
#
# Test case for BUG#12903: upper function does not work inside a function.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#12903
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP FUNCTION IF EXISTS f1;
DROP TABLE IF EXISTS t1;
--enable_warnings

#
# Test case.
#

CREATE TABLE t1(txt VARCHAR(255));

delimiter |;
CREATE FUNCTION f1(arg VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
  DECLARE v1 VARCHAR(255);
  DECLARE v2 VARCHAR(255);

  SET v1 = CONCAT(LOWER(arg), UPPER(arg));
  SET v2 = CONCAT(LOWER(v1), UPPER(v1));

  INSERT INTO t1 VALUES(v1), (v2);

  RETURN CONCAT(LOWER(arg), UPPER(arg));
END|
delimiter ;|

SELECT f1('_aBcDe_');

SELECT * FROM t1;

#
# Cleanup.
#

DROP FUNCTION f1;
DROP TABLE t1;

###########################################################################
#
# Test case for BUG#13808: ENUM type stored procedure parameter accepts
# non-enumerated data.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#13808
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
DROP FUNCTION IF EXISTS f1;
--enable_warnings

#
# Test case.
#

delimiter |;

CREATE PROCEDURE p1(arg ENUM('a', 'b'))
BEGIN
  SELECT arg;
END|

CREATE PROCEDURE p2(arg ENUM('a', 'b'))
BEGIN
  DECLARE var ENUM('c', 'd') DEFAULT arg;

  SELECT arg, var;
END|

CREATE FUNCTION f1(arg ENUM('a', 'b')) RETURNS ENUM('c', 'd')
BEGIN
  RETURN arg;
END|

delimiter ;|

CALL p1('c');

CALL p2('a');

SELECT f1('a');

#
# Cleanup.
#

DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP FUNCTION f1;

###########################################################################
#
# Test case for BUG#13909: Varchar Stored Procedure Parameter always BINARY
# string (ignores CHARACTER SET).
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#13909
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
--enable_warnings

#
# Test case.
#

delimiter |;

CREATE PROCEDURE p1(arg VARCHAR(255))
BEGIN
  SELECT CHARSET(arg);
END|

CREATE PROCEDURE p2(arg VARCHAR(255) CHARACTER SET UTF8)
BEGIN
    SELECT CHARSET(arg);
END|

delimiter ;|

CALL p1('t');
CALL p1(_UTF8 't');


CALL p2('t');
CALL p2(_LATIN1 't');

#
# Cleanup.
#

DROP PROCEDURE p1;
DROP PROCEDURE p2;

###########################################################################
#
# Test case for BUG#14188: BINARY variables have no 0x00 padding.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#14188
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
--enable_warnings

#
# Test case.
#

delimiter |;
CREATE PROCEDURE p1(arg1 BINARY(2), arg2 VARBINARY(2))
BEGIN
  DECLARE var1 BINARY(2) DEFAULT 0x41;
  DECLARE var2 VARBINARY(2) DEFAULT 0x42;

  SELECT HEX(arg1), HEX(arg2);
  SELECT HEX(var1), HEX(var2);
END|
delimiter ;|

CALL p1(0x41, 0x42);

#
# Cleanup.
#

DROP PROCEDURE p1;

###########################################################################
#
# Test case for BUG#15148: Stored procedure variables accept non-scalar values.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#15148
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP TABLE IF EXISTS t1;
--enable_warnings

#
# Test case.
#

CREATE TABLE t1(col1 TINYINT, col2 TINYINT);

INSERT INTO t1 VALUES(1, 2), (11, 12);

delimiter |;
CREATE PROCEDURE p1(arg TINYINT)
BEGIN
  SELECT arg;
END|
delimiter ;|

--error ER_OPERAND_COLUMNS
CALL p1((1, 2));

--error ER_OPERAND_COLUMNS
CALL p1((SELECT * FROM t1 LIMIT 1));

--error ER_OPERAND_COLUMNS
CALL p1((SELECT col1, col2 FROM t1 LIMIT 1));

#
# Cleanup.
#

DROP PROCEDURE p1;
DROP TABLE t1;

###########################################################################
#
# Test case for BUG#13613: substring function in stored procedure.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#13613
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
--enable_warnings

#
# Test case.
#

delimiter |;

CREATE PROCEDURE p1(x VARCHAR(50))
BEGIN
  SET x = SUBSTRING(x, 1, 3);
  SELECT x;
END|

CREATE FUNCTION f1(x VARCHAR(50)) RETURNS VARCHAR(50)
BEGIN
  RETURN SUBSTRING(x, 1, 3);
END|

delimiter ;|

CALL p1('abcdef');

SELECT f1('ABCDEF');

#
# Cleanup.
#

DROP PROCEDURE p1;
DROP FUNCTION f1;

###########################################################################
#
# Test case for BUG#13665: concat with '' produce incorrect results in SP.
#
###########################################################################

--echo
--echo ---------------------------------------------------------------
--echo BUG#13665
--echo ---------------------------------------------------------------
--echo

#
# Prepare.
#

--disable_warnings
DROP FUNCTION IF EXISTS f1;
--enable_warnings

#
# Test case.
#

delimiter |;
CREATE FUNCTION f1() RETURNS VARCHAR(20000)
BEGIN
  DECLARE var VARCHAR(2000);

  SET var = '';
  SET var = CONCAT(var, 'abc');
  SET var = CONCAT(var, '');

  RETURN var;
END|
delimiter ;|

SELECT f1();

#
# Cleanup.
#

DROP FUNCTION f1;


--- 1.2/mysql-test/r/schema.result	2004-12-10 12:06:17 +03:00
+++ 1.3/mysql-test/r/schema.result	2005-12-07 17:01:06 +03:00
@@ -1,3 +1,4 @@
+drop database if exists mysqltest1;
 create schema foo;
 show create schema foo;
 Database	Create Database

--- 1.1/mysql-test/t/schema.test	2004-10-01 23:35:22 +04:00
+++ 1.2/mysql-test/t/schema.test	2005-12-07 17:01:07 +03:00
@@ -1,6 +1,12 @@
 #
 # Just a couple of tests to make sure that schema works.
 #
+# Drop mysqltest1 database, as it can left from the previous tests.
+#
+
+--disable_warnings
+drop database if exists mysqltest1;
+--enable_warnings
 
 create schema foo;
 show create schema foo;

--- 1.35/sql/sql_trigger.cc	2005-11-23 03:49:40 +03:00
+++ 1.36/sql/sql_trigger.cc	2005-12-07 17:01:08 +03:00
@@ -1123,7 +1123,7 @@
                                            trg_action_time_type time_type,
                                            bool old_row_is_record1)
 {
-  int res= 0;
+  bool err_status= FALSE;
   sp_head *sp_trigger= bodies[event][time_type];
 
   if (sp_trigger)
@@ -1183,7 +1183,7 @@
 #endif // NO_EMBEDDED_ACCESS_CHECKS
 
     thd->reset_sub_statement_state(&statement_state, SUB_STMT_TRIGGER);
-    res= sp_trigger->execute_function(thd, 0, 0, 0);
+    err_status= sp_trigger->execute_function(thd, 0, 0, 0);
     thd->restore_sub_statement_state(&statement_state);
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
@@ -1191,7 +1191,7 @@
 #endif // NO_EMBEDDED_ACCESS_CHECKS
   }
 
-  return res;
+  return err_status;
 }
 
 

--- 1.2/mysql-test/r/skip_name_resolve.result	2005-09-22 23:03:48 +04:00
+++ 1.3/mysql-test/r/skip_name_resolve.result	2005-12-07 17:01:06 +03:00
@@ -10,5 +10,5 @@
 #
 show processlist;
 Id	User	Host	db	Command	Time	State	Info
-#	root	#	test	Sleep	#		NULL
-#	root	#	test	Query	#	NULL	show processlist
+<id>	root	<host>	test	<command>	<time>	<state>	<info>
+<id>	root	<host>	test	<command>	<time>	<state>	<info>

--- 1.2/mysql-test/r/sp-big.result	2005-09-22 02:10:57 +04:00
+++ 1.3/mysql-test/r/sp-big.result	2005-12-07 17:01:06 +03:00
@@ -25,6 +25,7 @@
 select count(*) from t2;
 count(*)
 0
+drop procedure if exists p1;
 create procedure p1()
 begin
 declare done integer default 0;

--- 1.4/mysql-test/t/skip_name_resolve.test	2005-09-22 23:03:48 +04:00
+++ 1.5/mysql-test/t/skip_name_resolve.test	2005-12-07 17:01:07 +03:00
@@ -15,6 +15,6 @@
 connect (con1, 127.0.0.1, root, , test, $MASTER_MYPORT, );
 --replace_column 1 #
 select user();
---replace_column 1 # 6 # 3 #
+--replace_column 1 <id> 3 <host> 5 <command> 6 <time> 7 <state> 8 <info>
 show processlist;
 connection default;

--- 1.2/mysql-test/t/sp-big.test	2005-09-22 02:10:57 +04:00
+++ 1.3/mysql-test/t/sp-big.test	2005-12-07 17:01:07 +03:00
@@ -52,6 +52,9 @@
 --enable_query_log
 select count(*) from t1;
 select count(*) from t2;
+--disable_warnings
+drop procedure if exists p1;
+--enable_warnings
 delimiter |;
 create procedure p1()
 begin

--- 1.1/mysql-test/r/type_newdecimal-big.result	2005-11-29 15:29:56 +03:00
+++ 1.2/mysql-test/r/type_newdecimal-big.result	2005-12-07 17:01:06 +03:00
@@ -1,11 +1,26 @@
 drop procedure if exists sp1;
-create procedure sp1 () begin 
-declare v1, v2, v3, v4 decimal(16,12); declare v5 int; 
-set v1 = 1; set v2 = 2; set v3 = 1000000000000; set v4 = 2000000000000; set v5 = 0; 
-while v5 < 100000 do
-set v1 = v1 + 0.000000000001; set v2 = v2 - 0.000000000001; set v3 = v3 + 1; set v4 = v4 - 1; set v5 = v5 + 1; 
-end while; select v1, v2, v3 * 0.000000000001, v4 * 0.000000000001; end;//
+CREATE PROCEDURE sp1()
+BEGIN 
+DECLARE v1, v2, v3, v4 DECIMAL(28,12);
+DECLARE v3_2, v4_2 DECIMAL(28, 12);
+DECLARE counter INT;
+SET v1 = 1;
+SET v2 = 2;
+SET v3 = 1000000000000;
+SET v4 = 2000000000000;
+SET counter = 0;
+WHILE counter < 100000 DO
+SET v1 = v1 + 0.000000000001;
+SET v2 = v2 - 0.000000000001;
+SET v3 = v3 + 1;
+SET v4 = v4 - 1;
+SET counter = counter + 1; 
+END WHILE;
+SET v3_2 = v3 * 0.000000000001;
+SET v4_2 = v4 * 0.000000000001;
+SELECT v1, v2, v3, v3_2, v4, v4_2;
+END//
 call sp1()//
-v1	v2	v3 * 0.000000000001	v4 * 0.000000000001
-1.000000100000	1.999999900000	1.000000100000	1.999999900000
+v1	v2	v3	v3_2	v4	v4_2
+1.000000100000	1.999999900000	1000000100000.000000000000	1.000000100000	1999999900000.000000000000	1.999999900000
 drop procedure sp1;

--- 1.1/mysql-test/t/type_newdecimal-big.test	2005-11-29 15:29:55 +03:00
+++ 1.2/mysql-test/t/type_newdecimal-big.test	2005-12-07 17:01:07 +03:00
@@ -12,12 +12,31 @@
 
 delimiter //;
 #
-create procedure sp1 () begin 
-  declare v1, v2, v3, v4 decimal(16,12); declare v5 int; 
-  set v1 = 1; set v2 = 2; set v3 = 1000000000000; set v4 = 2000000000000; set v5 = 0; 
-  while v5 < 100000 do
-   set v1 = v1 + 0.000000000001; set v2 = v2 - 0.000000000001; set v3 = v3 + 1; set v4 = v4 - 1; set v5 = v5 + 1; 
-  end while; select v1, v2, v3 * 0.000000000001, v4 * 0.000000000001; end;//
+CREATE PROCEDURE sp1()
+BEGIN 
+  DECLARE v1, v2, v3, v4 DECIMAL(28,12);
+  DECLARE v3_2, v4_2 DECIMAL(28, 12);
+  DECLARE counter INT;
+
+  SET v1 = 1;
+  SET v2 = 2;
+  SET v3 = 1000000000000;
+  SET v4 = 2000000000000;
+  SET counter = 0;
+  
+  WHILE counter < 100000 DO
+   SET v1 = v1 + 0.000000000001;
+   SET v2 = v2 - 0.000000000001;
+   SET v3 = v3 + 1;
+   SET v4 = v4 - 1;
+   SET counter = counter + 1; 
+  END WHILE;
+
+  SET v3_2 = v3 * 0.000000000001;
+  SET v4_2 = v4 * 0.000000000001;
+
+  SELECT v1, v2, v3, v3_2, v4, v4_2;
+END//
 #
 call sp1()//
 #-- should return 

--- 1.2/mysql-test/r/sp-dynamic.result	2005-11-23 02:11:05 +03:00
+++ 1.3/mysql-test/r/sp-dynamic.result	2005-12-07 17:01:06 +03:00
@@ -1,3 +1,5 @@
+drop procedure if exists p1|
+drop procedure if exists p2|
 create procedure p1()
 begin
 prepare stmt from "select 1";

--- 1.2/mysql-test/t/sp-dynamic.test	2005-11-23 02:11:05 +03:00
+++ 1.3/mysql-test/t/sp-dynamic.test	2005-12-07 17:01:07 +03:00
@@ -1,4 +1,10 @@
 delimiter |;
+
+--disable_warnings
+drop procedure if exists p1|
+drop procedure if exists p2|
+--enable_warnings
+
 ###################################################################### 
 # Test Dynamic SQL in stored procedures. #############################
 ###################################################################### 

--- 1.75/mysql-test/r/show_check.result	2005-10-24 17:03:28 +04:00
+++ 1.76/mysql-test/r/show_check.result	2005-12-07 17:01:06 +03:00
@@ -1,6 +1,7 @@
 drop table if exists t1,t2;
 drop table if exists t1aa,t2aa;
 drop database if exists mysqltest;
+drop database if exists mysqltest1;
 delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3';
 delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3';
 flush privileges;

--- 1.54/mysql-test/t/show_check.test	2005-10-21 15:46:50 +04:00
+++ 1.55/mysql-test/t/show_check.test	2005-12-07 17:01:07 +03:00
@@ -10,6 +10,7 @@
 drop table if exists t1,t2;
 drop table if exists t1aa,t2aa;
 drop database if exists mysqltest;
+drop database if exists mysqltest1;
 
 delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3';
 delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3';

--- 1.15/mysql-test/r/ctype_ujis.result	2005-10-06 16:40:13 +04:00
+++ 1.16/mysql-test/r/ctype_ujis.result	2005-12-07 17:01:06 +03:00
@@ -2317,7 +2317,7 @@
 INSERT INTO t1 VALUES(_ujis 0xA4A2);
 CREATE PROCEDURE sp1()
 BEGIN
-DECLARE a CHAR(1);
+DECLARE a CHAR(2) CHARSET ujis;
 DECLARE cur1 CURSOR FOR SELECT c1 FROM t1;
 OPEN cur1;
 FETCH cur1 INTO a;

--- 1.16/mysql-test/t/ctype_ujis.test	2005-10-06 16:36:54 +04:00
+++ 1.17/mysql-test/t/ctype_ujis.test	2005-12-07 17:01:06 +03:00
@@ -1170,7 +1170,7 @@
 DELIMITER |;
 CREATE PROCEDURE sp1()
 BEGIN
-  DECLARE a CHAR(1);
+  DECLARE a CHAR(2) CHARSET ujis;
   DECLARE cur1 CURSOR FOR SELECT c1 FROM t1;
   OPEN cur1;
   FETCH cur1 INTO a;

--- 1.1/mysql-test/r/sum_distinct-big.result	2005-04-07 20:24:09 +04:00
+++ 1.2/mysql-test/r/sum_distinct-big.result	2005-12-07 17:01:06 +03:00
@@ -1,5 +1,4 @@
-using_big_test
-0
+DROP TABLE IF EXISTS t1, t2;
 CREATE TABLE t1 (id INTEGER);
 CREATE TABLE t2 (id INTEGER);
 INSERT INTO t1 (id) VALUES (1), (1), (1),(1);
@@ -40,19 +39,19 @@
 512.5000
 SELECT SUM(DISTINCT id)/COUNT(DISTINCT id) FROM t1 GROUP BY id % 13;
 SUM(DISTINCT id)/COUNT(DISTINCT id)
-513.50000
-508.00000
-509.00000
-510.00000
-511.00000
-512.00000
-513.00000
-514.00000
-515.00000
-516.00000
-517.00000
-511.50000
-512.50000
+513.5000
+508.0000
+509.0000
+510.0000
+511.0000
+512.0000
+513.0000
+514.0000
+515.0000
+516.0000
+517.0000
+511.5000
+512.5000
 INSERT INTO t1 SELECT id+1024 FROM t1;
 INSERT INTO t1 SELECT id+2048 FROM t1;
 INSERT INTO t1 SELECT id+4096 FROM t1;

--- 1.172/mysql-test/r/sp.result	2005-12-03 16:40:19 +03:00
+++ 1.173/mysql-test/r/sp.result	2005-12-07 17:01:06 +03:00
@@ -248,13 +248,13 @@
 call sub1("sub1a", (select 7))|
 call sub1("sub1b", (select max(i) from t2))|
 call sub1("sub1c", (select i,d from t2 limit 1))|
+ERROR 21000: Operand should contain 1 column(s)
 call sub1("sub1d", (select 1 from (select 1) a))|
 call sub2("sub2")|
 select * from t1|
 id	data
 sub1a	7
 sub1b	3
-sub1c	1
 sub1d	1
 sub2	6
 select sub3((select max(i) from t2))|
@@ -2686,7 +2686,7 @@
 s	x	y	z
 16	3	1	6
 a
-3.2000
+3.2
 drop procedure bug8937|
 delete from t1|
 drop procedure if exists bug6900|
@@ -2890,21 +2890,30 @@
 select bug9775('a'),bug9775('b'),bug9775('c')|
 bug9775('a')	bug9775('b')	bug9775('c')
 a	b	
+Warnings:
+Warning	1265	Data truncated for column 'bug9775('c')' at row 1
 drop function bug9775|
 create function bug9775(v1 int) returns enum('a','b') return v1|
 select bug9775(1),bug9775(2),bug9775(3)|
 bug9775(1)	bug9775(2)	bug9775(3)
 a	b	
+Warnings:
+Warning	1265	Data truncated for column 'bug9775(3)' at row 1
 drop function bug9775|
 create function bug9775(v1 char(1)) returns set('a','b') return v1|
 select bug9775('a'),bug9775('b'),bug9775('a,b'),bug9775('c')|
 bug9775('a')	bug9775('b')	bug9775('a,b')	bug9775('c')
-a	b	a,b	
+a	b	a	
+Warnings:
+Warning	1265	Data truncated for column 'v1' at row 1
+Warning	1265	Data truncated for column 'bug9775('c')' at row 1
 drop function bug9775|
 create function bug9775(v1 int) returns set('a','b') return v1|
 select bug9775(1),bug9775(2),bug9775(3),bug9775(4)|
 bug9775(1)	bug9775(2)	bug9775(3)	bug9775(4)
 a	b	a,b	
+Warnings:
+Warning	1265	Data truncated for column 'bug9775(4)' at row 1
 drop function bug9775|
 drop function if exists bug8861|
 create function bug8861(v1 int) returns year return v1|
@@ -2927,12 +2936,10 @@
 call bug9004_1(x)|
 call bug9004_1('12345678901234567')|
 Warnings:
-Warning	1265	Data truncated for column 'id' at row 1
-Warning	1265	Data truncated for column 'id' at row 2
+Warning	1265	Data truncated for column 'x' at row 1
 call bug9004_2('12345678901234567890')|
 Warnings:
-Warning	1265	Data truncated for column 'id' at row 1
-Warning	1265	Data truncated for column 'id' at row 2
+Warning	1265	Data truncated for column 'x' at row 1
 delete from t1|
 drop procedure bug9004_1|
 drop procedure bug9004_2|
@@ -3527,14 +3534,15 @@
 call bug12589_1()|
 Table	Create Table
 tm1	CREATE TEMPORARY TABLE `tm1` (
-  `spv1` decimal(1,0) unsigned default NULL
+  `spv1` decimal(3,3) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 Warnings:
-Warning	1292	Truncated incorrect DECIMAL value: 'test'
+Warning	1264	Out of range value adjusted for column 'spv1' at row 1
+Warning	1366	Incorrect decimal value: 'test' for column 'spv1' at row 1
 call bug12589_2()|
 Table	Create Table
 tm1	CREATE TEMPORARY TABLE `tm1` (
-  `spv1` decimal(6,3) unsigned default NULL
+  `spv1` decimal(6,3) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 call bug12589_3()|
 Table	Create Table
@@ -4016,34 +4024,37 @@
 begin
 declare continue handler for sqlexception select 'boo' as 'Handler';
 begin
-declare v int default x;
+declare v int default undefined_var;
 if v = 1 then
 select 1;
 else
-select 2;
+select v, isnull(v);
 end if;
 end;
 end|
 create procedure bug14643_2()
 begin
 declare continue handler for sqlexception select 'boo' as 'Handler';
-case x
+case undefined_var
 when 1 then
 select 1;
 else
 select 2;
 end case;
+select undefined_var;
 end|
 call bug14643_1()|
 Handler
 boo
-2
-2
+v	isnull(v)
+NULL	1
 call bug14643_2()|
 Handler
 boo
 2
 2
+Handler
+boo
 drop procedure bug14643_1|
 drop procedure bug14643_2|
 drop procedure if exists bug14304|

--- 1.163/mysql-test/t/sp.test	2005-12-06 15:20:52 +03:00
+++ 1.164/mysql-test/t/sp.test	2005-12-07 17:01:07 +03:00
@@ -367,6 +367,7 @@
 
 call sub1("sub1a", (select 7))|
 call sub1("sub1b", (select max(i) from t2))|
+--error ER_OPERAND_COLUMNS
 call sub1("sub1c", (select i,d from t2 limit 1))|
 call sub1("sub1d", (select 1 from (select 1) a))|
 call sub2("sub2")|
@@ -4797,12 +4798,12 @@
   declare continue handler for sqlexception select 'boo' as 'Handler';
 
   begin
-    declare v int default x;
+    declare v int default undefined_var;
 
     if v = 1 then
       select 1;
     else
-      select 2;
+      select v, isnull(v);
     end if;
   end;
 end|
@@ -4811,12 +4812,14 @@
 begin
   declare continue handler for sqlexception select 'boo' as 'Handler';
 
-  case x
+  case undefined_var
   when 1 then
     select 1;
   else
     select 2;
   end case;
+
+  select undefined_var;
 end|
 
 call bug14643_1()|

--- 1.100/sql/sp.cc	2005-12-06 16:25:05 +03:00
+++ 1.101/sql/sp.cc	2005-12-07 17:01:07 +03:00
@@ -467,7 +467,7 @@
   bzero(&table, sizeof(table));
   table.in_use= thd;
   table.s = &table.share_not_to_be_used;
-  field= sp->make_field(0, 0, &table);
+  field= sp->create_result_field(0, 0, &table);
   field->sql_type(result);
   delete field;
 }

--- 1.197/sql/sp_head.cc	2005-12-03 16:40:20 +03:00
+++ 1.198/sql/sp_head.cc	2005-12-07 17:01:07 +03:00
@@ -27,8 +27,7 @@
 Item_result
 sp_map_result_type(enum enum_field_types type)
 {
-  switch (type)
-  {
+  switch (type) {
   case MYSQL_TYPE_TINY:
   case MYSQL_TYPE_SHORT:
   case MYSQL_TYPE_LONG:
@@ -46,6 +45,81 @@
   }
 }
 
+
+Item::Type
+sp_map_item_type(enum enum_field_types type)
+{
+  switch (type) {
+  case MYSQL_TYPE_TINY:
+  case MYSQL_TYPE_SHORT:
+  case MYSQL_TYPE_LONG:
+  case MYSQL_TYPE_LONGLONG:
+  case MYSQL_TYPE_INT24:
+    return Item::INT_ITEM;
+  case MYSQL_TYPE_DECIMAL:
+  case MYSQL_TYPE_NEWDECIMAL:
+    return Item::DECIMAL_ITEM;
+  case MYSQL_TYPE_FLOAT:
+  case MYSQL_TYPE_DOUBLE:
+    return Item::REAL_ITEM;
+  default:
+    return Item::STRING_ITEM;
+  }
+}
+
+
+/*
+  Return a string representation of the Item value.
+
+  NOTE: this is a legacy-compatible implementation. It fails if the value
+  contains non-ordinary symbols, which should be escaped.
+
+  SYNOPSIS
+    item    a pointer to the Item
+    str     string buffer for representation of the value
+
+  RETURN
+    NULL  on error
+    a pointer to valid a valid string on success
+*/
+
+static String *
+sp_get_item_value(Item *item, String *str)
+{
+  Item_result result_type= item->result_type();
+
+  switch (item->result_type()) {
+  case REAL_RESULT:
+  case INT_RESULT:
+  case DECIMAL_RESULT:
+    return item->val_str(str);
+
+  case STRING_RESULT:
+    {
+      char buf_holder[STRING_BUFFER_USUAL_SIZE];
+      String buf(buf_holder, sizeof(buf_holder), &my_charset_latin1);
+      String *result= item->val_str(str);
+      
+      if (!result)
+        return NULL;
+      
+      buf.append('_');
+      buf.append(result->charset()->csname);
+      buf.append('\'');
+      buf.append(*result);
+      buf.append('\'');
+      str->copy(buf);
+
+      return str;
+    }
+
+  case ROW_RESULT:
+  default:
+    return NULL;
+  }
+}
+
+
 /*
   SYNOPSIS
     sp_get_flags_for_command()
@@ -170,7 +244,7 @@
 
 
 /*
-  Prepare Item for execution (call of fix_fields)
+  Prepare an Item for evaluation (call of fix_fields).
 
   SYNOPSIS
     sp_prepare_func_item()
@@ -182,14 +256,15 @@
     prepared item
 */
 
-static Item *
+Item *
 sp_prepare_func_item(THD* thd, Item **it_addr)
 {
-  Item *it= *it_addr;
   DBUG_ENTER("sp_prepare_func_item");
-  it_addr= it->this_item_addr(thd, it_addr);
+  it_addr= (*it_addr)->this_item_addr(thd, it_addr);
 
-  if (!it->fixed && (*it_addr)->fix_fields(thd, it_addr))
+  if (!(*it_addr)->fixed &&
+      ((*it_addr)->fix_fields(thd, it_addr) ||
+       (*it_addr)->check_cols(1)))
   {
     DBUG_PRINT("info", ("fix_fields() failed"));
     DBUG_RETURN(NULL);
@@ -198,202 +273,62 @@
 }
 
 
-/* Macro to switch arena in sp_eval_func_item */
-#define CREATE_ON_CALLERS_ARENA(new_command, condition, backup_arena)   \
-  do                                                                    \
-  {                                                                     \
-    if (condition)                                                      \
-      thd->set_n_backup_active_arena(thd->spcont->callers_arena,        \
-                                     backup_arena);                     \
-    new_command;                                                        \
-    if (condition)                                                      \
-      thd->restore_active_arena(thd->spcont->callers_arena,             \
-                                backup_arena);                          \
-  } while(0)
-
 /*
-  Evaluate an item and store it in the returned item
+  Evaluate an expression and store the result in the field.
 
   SYNOPSIS
-    sp_eval_func_item()
-      name                  - current thread object
-      it_addr               - pointer to the item to evaluate
-      type                  - type of the item we evaluating
-      reuse                 - used if we would like to reuse existing item
-                              instead of allocation of the new one
-      use_callers_arena     - TRUE if we want to use caller's arena
-                              rather then current one.
-  DESCRIPTION
-   We use this function to evaluate result for stored functions
-   and stored procedure parameters. It is also used to evaluate and
-   (re) allocate variables.
+    sp_eval_expr()
+      thd                   - current thread object
+      expr_item             - the root item of the expression
+      result_field          - the field to store the result
 
   RETURN VALUES
-    Evaluated item is returned
+    FALSE  on success
+    TRUE   on error
 */
 
-Item *
-sp_eval_func_item(THD *thd, Item **it_addr, enum enum_field_types type,
-		  Item *reuse, bool use_callers_arena)
+bool
+sp_eval_expr(THD *thd, Field *result_field, Item *expr_item)
 {
-  DBUG_ENTER("sp_eval_func_item");
-  Item *it= sp_prepare_func_item(thd, it_addr);
-  uint rsize;
-  Query_arena backup_arena;
-  Item *old_item_next, *old_free_list, **p_free_list;
-  DBUG_PRINT("info", ("type: %d", type));
+  DBUG_ENTER("sp_eval_expr");
 
-  if (!it)
-    DBUG_RETURN(NULL);
+  if (!(expr_item= sp_prepare_func_item(thd, &expr_item)))
+    DBUG_RETURN(TRUE);
 
-  if (reuse)
-  {
-    old_item_next= reuse->next;
-    p_free_list= use_callers_arena ? &thd->spcont->callers_arena->free_list :
-                                     &thd->free_list;
-    old_free_list= *p_free_list;
-  }
+  bool err_status= FALSE;
 
-  switch (sp_map_result_type(type)) {
-  case INT_RESULT:
-  {
-    longlong i= it->val_int();
+  /*
+    Set THD flags to emit warnings/errors in case of overflow/type errors
+    during saving the item into the field.
 
-    if (it->null_value)
-    {
-      DBUG_PRINT("info", ("INT_RESULT: null"));
-      goto return_null_item;
-    }
-    DBUG_PRINT("info", ("INT_RESULT: %d", i));
-    CREATE_ON_CALLERS_ARENA(it= new(reuse, &rsize) Item_int(i),
-                            use_callers_arena, &backup_arena);
-    break;
-  }
-  case REAL_RESULT:
-  {
-    double d= it->val_real();
-    uint8 decimals;
-    uint32 max_length;
+    Save original values and restore them after save.
+  */
+  
+  enum_check_fields save_count_cuted_fields= thd->count_cuted_fields;
+  bool save_abort_on_warning= thd->abort_on_warning;
+  bool save_no_trans_update= thd->no_trans_update;
 
-    if (it->null_value)
-    {
-      DBUG_PRINT("info", ("REAL_RESULT: null"));
-      goto return_null_item;
-    }
+  thd->count_cuted_fields= CHECK_FIELD_ERROR_FOR_NULL;
+  thd->abort_on_warning=
+    thd->variables.sql_mode &
+    (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES);
+  thd->no_trans_update= 0;
 
-    /*
-      There's some difference between Item::new_item() and the
-      constructor; the former crashes, the latter works... weird.
-    */
-    decimals= it->decimals;
-    max_length= it->max_length;
-    DBUG_PRINT("info", ("REAL_RESULT: %g", d));
-    CREATE_ON_CALLERS_ARENA(it= new(reuse, &rsize) Item_float(d),
-                            use_callers_arena, &backup_arena);
-    it->decimals= decimals;
-    it->max_length= max_length;
-    break;
-  }
-  case DECIMAL_RESULT:
-  {
-    my_decimal value, *val= it->val_decimal(&value);
-    if (it->null_value)
-      goto return_null_item;
-    CREATE_ON_CALLERS_ARENA(it= new(reuse, &rsize) Item_decimal(val),
-                            use_callers_arena, &backup_arena);
-#ifndef DBUG_OFF
-    {
-      char dbug_buff[DECIMAL_MAX_STR_LENGTH+1];
-      DBUG_PRINT("info", ("DECIMAL_RESULT: %s",
-                          dbug_decimal_as_string(dbug_buff, val)));
-    }
-#endif
-    break;
-  }
-  case STRING_RESULT:
-  {
-    char buffer[MAX_FIELD_WIDTH];
-    String tmp(buffer, sizeof(buffer), it->collation.collation);
-    String *s= it->val_str(&tmp);
-
-    if (type == MYSQL_TYPE_NULL || it->null_value)
-    {
-      DBUG_PRINT("info", ("STRING_RESULT: null"));
-      goto return_null_item;
-    }
-    DBUG_PRINT("info",("STRING_RESULT: %.*s",
-                       s->length(), s->c_ptr_quick()));
-    /*
-      Reuse mechanism in sp_eval_func_item() is only employed for assignments
-      to local variables and OUT/INOUT SP parameters repsesented by
-      Item_splocal. Usually we have some expression, which needs
-      to be calculated and stored into the local variable. However in the
-      case if "it" equals to "reuse", there is no "calculation" step. So,
-      no reason to employ reuse mechanism to save variable into itself.
-    */
-    if (it == reuse)
-      DBUG_RETURN(it);
+  /* Save the value in the field. Convert the value if needed. */
 
-    /*
-      For some functions, 's' is now pointing to an argument of the
-      function, which might be a local variable that is to be reused.
-      In this case, new(reuse, &rsize) below will call the destructor
-      and 's' ends up pointing to freed memory.
-      A somewhat ugly fix is to simply copy the string to our local one
-      (which is unused by most functions anyway), but only if 's' is
-      pointing somewhere else than to 'tmp' or 'it->str_value'.
-     */
-    if (reuse && s != &tmp && s != &it->str_value)
-    {
-      if (tmp.copy((const String)(*s)))
-        DBUG_RETURN(NULL);
-      s= &tmp;
-    }
-
-    CREATE_ON_CALLERS_ARENA(it= new(reuse, &rsize)
-                            Item_string(it->collation.collation),
-                            use_callers_arena, &backup_arena);
-    /*
-      We have to use special constructor and allocate string
-      on system heap here. This is because usual Item_string
-      constructor would allocate memory in the callers arena.
-      This would lead to the memory leak in SP loops.
-      See Bug #11333 "Stored Procedure: Memory blow up on
-      repeated SELECT ... INTO query" for sample of such SP.
-      TODO: Usage of the system heap gives significant overhead,
-      however usual "reuse" mechanism does not work here, as
-      Item_string has no max size. That is, if we have a loop, which
-      has string variable with constantly increasing size, we would have
-      to allocate new pieces of memory again and again on each iteration.
-      In future we should probably reserve some area of memory for
-      not-very-large strings and reuse it. But for large strings
-      we would have to use system heap anyway.
-    */
-    ((Item_string*) it)->set_str_with_copy(s->ptr(), s->length());
-    break;
-  }
-  case ROW_RESULT:
-  default:
-    DBUG_ASSERT(0);
-  }
-  goto end;
+  expr_item->save_in_field(result_field, 0);
 
-return_null_item:
-  CREATE_ON_CALLERS_ARENA(it= new(reuse, &rsize) Item_null(),
-                          use_callers_arena, &backup_arena);
-end:
-  it->rsize= rsize;
+  thd->count_cuted_fields= save_count_cuted_fields;
+  thd->abort_on_warning= save_abort_on_warning;
+  thd->no_trans_update= save_no_trans_update;
 
-  if (reuse && it == reuse)
+  if (thd->net.report_error)
   {
-    /*
-      The Item constructor registered itself in the arena free list,
-      while the item slot is reused, so we have to restore the list.
-    */
-    it->next= old_item_next;
-    *p_free_list= old_free_list;
+    /* Return error status if something went wrong. */
+    err_status= TRUE;
   }
-  DBUG_RETURN(it);
+
+  DBUG_RETURN(err_status);
 }
 
 
@@ -478,9 +413,11 @@
 
 sp_head::sp_head()
   :Query_arena(&main_mem_root, INITIALIZED_FOR_SP),
-   m_flags(0), m_returns_cs(NULL), m_recursion_level(0), m_next_cached_sp(0),
+   m_flags(0), m_recursion_level(0), m_next_cached_sp(0),
    m_first_instance(this), m_first_free_instance(this), m_last_cached_sp(this)
 {
+  m_return_field_def.charset = NULL;
+
   extern byte *
     sp_table_key(const byte *ptr, uint *plen, my_bool first);
   DBUG_ENTER("sp_head::sp_head");
@@ -499,6 +436,7 @@
   DBUG_ENTER("sp_head::init");
 
   lex->spcont= m_pcont= new sp_pcontext(NULL);
+
   /*
     Altough trg_table_fields list is used only in triggers we init for all
     types of stored procedures to simplify reset_lex()/restore_lex() code.
@@ -510,7 +448,7 @@
     m_body.str= m_defstr.str= 0;
   m_qname.length= m_db.length= m_name.length= m_params.length=
     m_body.length= m_defstr.length= 0;
-  m_returns_cs= NULL;
+  m_return_field_def.charset= NULL;
   DBUG_VOID_RETURN;
 }
 
@@ -569,12 +507,13 @@
   DBUG_VOID_RETURN;
 }
 
-TYPELIB *
-sp_head::create_typelib(List<String> *src)
+
+static TYPELIB *
+create_typelib(MEM_ROOT *mem_root, create_field *field_def, List<String> *src)
 {
   TYPELIB *result= NULL;
-  CHARSET_INFO *cs= m_returns_cs;
-  DBUG_ENTER("sp_head::clone_typelib");
+  CHARSET_INFO *cs= field_def->charset;
+  DBUG_ENTER("create_typelib");
   if (src->elements)
   {
     result= (TYPELIB*) alloc_root(mem_root, sizeof(TYPELIB));
@@ -619,6 +558,7 @@
   return result;
 }
 
+
 int
 sp_head::create(THD *thd)
 {
@@ -706,17 +646,30 @@
 */
 
 Field *
-sp_head::make_field(uint max_length, const char *name, TABLE *dummy)
+sp_head::create_result_field(uint field_max_length, const char *field_name,
+                             TABLE *table)
 {
+  uint field_length;
   Field *field;
-  DBUG_ENTER("sp_head::make_field");
 
-  field= ::make_field((char *)0,
-		!m_returns_len ? max_length : m_returns_len, 
-		(uchar *)"", 0, m_returns_pack, m_returns, m_returns_cs,
-		m_geom_returns, Field::NONE, 
-		m_returns_typelib,
-		name ? name : (const char *)m_name.str, dummy);
+  DBUG_ENTER("sp_head::create_result_field");
+
+  field_length= !m_return_field_def.length ?
+                field_max_length : m_return_field_def.length;
+
+  field= ::make_field((char*) 0,                    /* field ptr */
+                      field_length,                 /* field [max] length */
+                      (uchar*) "",                  /* null ptr */
+                      0,                            /* null bit */
+                      m_return_field_def.pack_flag,
+                      m_return_field_def.sql_type,
+                      m_return_field_def.charset,
+                      m_return_field_def.geom_type,
+                      Field::NONE,                  /* unreg check */
+                      m_return_field_def.interval,
+                      field_name ? field_name : (const char *) m_name.str,
+                      table);
+  
   DBUG_RETURN(field);
 }
 
@@ -814,12 +767,14 @@
   variables with NAME_CONST('sp_var_name', value) calls.
  
   RETURN
-    0  Ok, thd->query{_length} either has been appropriately replaced or
-       there is no need for replacements.
-    1  Out of memory error.
+    FALSE  on success
+           thd->query{_length} either has been appropriately replaced or there
+           is no need for replacements.
+    TRUE   out of memory error.
 */
 
-static bool subst_spvars(THD *thd, sp_instr *instr, LEX_STRING *query_str)
+static bool
+subst_spvars(THD *thd, sp_instr *instr, LEX_STRING *query_str)
 {
   DBUG_ENTER("subst_spvars");
   if (thd->prelocked_mode == NON_PRELOCKED && mysql_bin_log.is_open())
@@ -829,7 +784,7 @@
     String qbuf(buffer, sizeof(buffer), &my_charset_bin);
     int prev_pos, res;
 
-    /* Find all instances of item_splocal used in this statement */
+    /* Find all instances of Item_splocal used in this statement */
     for (Item *item= instr->free_list; item; item= item->next)
     {
       if (item->is_splocal())
@@ -840,7 +795,7 @@
       }
     }
     if (!sp_vars_uses.elements())
-      DBUG_RETURN(0);
+      DBUG_RETURN(FALSE);
       
     /* Sort SP var refs by their occurences in the query */
     sp_vars_uses.sort(cmp_splocal_locations);
@@ -856,7 +811,12 @@
          splocal < sp_vars_uses.back(); splocal++)
     {
       Item *val;
-      (*splocal)->thd= thd;            // fix_fields() is not yet done
+
+      char str_buffer[STRING_BUFFER_USUAL_SIZE];
+      String str_value_holder(str_buffer, sizeof(str_buffer),
+                              &my_charset_latin1);
+      String *str_value;
+      
       /* append the text between sp ref occurences */
       res|= qbuf.append(cur + prev_pos, (*splocal)->pos_in_query - prev_pos);
       prev_pos= (*splocal)->pos_in_query + (*splocal)->m_name.length;
@@ -865,24 +825,33 @@
       res|= qbuf.append(STRING_WITH_LEN(" NAME_CONST('"));
       res|= qbuf.append((*splocal)->m_name.str, (*splocal)->m_name.length);
       res|= qbuf.append(STRING_WITH_LEN("',"));
+      res|= (*splocal)->fix_fields(thd, (Item **) splocal);
+
+      if (res)
+        break;
+
       val= (*splocal)->this_item();
       DBUG_PRINT("info", ("print %p", val));
-      val->print(&qbuf);
+      str_value= sp_get_item_value(val, &str_value_holder);
+      if (str_value)
+        res|= qbuf.append(*str_value);
+      else
+        res|= qbuf.append(STRING_WITH_LEN("NULL"));
       res|= qbuf.append(')');
       if (res)
         break;
     }
     res|= qbuf.append(cur + prev_pos, query_str->length - prev_pos);
     if (res)
-      DBUG_RETURN(1);
+      DBUG_RETURN(TRUE);
 
     if (!(pbuf= thd->strmake(qbuf.ptr(), qbuf.length())))
-      DBUG_RETURN(1);
+      DBUG_RETURN(TRUE);
 
     thd->query= pbuf;
     thd->query_length= qbuf.length();
   }
-  DBUG_RETURN(0);
+  DBUG_RETURN(FALSE);
 }
 
 
@@ -916,17 +885,19 @@
   Assume the parameters already set.
   
   RETURN
-    -1  on error
+    FALSE  on success
+    TRUE   on error
 
 */
 
-int sp_head::execute(THD *thd)
+bool
+sp_head::execute(THD *thd)
 {
   DBUG_ENTER("sp_head::execute");
   char olddb[128];
   bool dbchanged;
   sp_rcontext *ctx;
-  int ret= 0;
+  bool err_status= FALSE;
   uint ip= 0;
   ulong save_sql_mode;
   Query_arena *old_arena;
@@ -942,9 +913,7 @@
 
   /* Use some extra margin for possible SP recursion and functions */
   if (check_stack_overrun(thd, 8 * STACK_MIN_SIZE, (char*)&old_packet))
-  {
-    DBUG_RETURN(-1);
-  }
+    DBUG_RETURN(TRUE);
 
   /* init per-instruction memroot */
   init_alloc_root(&execute_mem_root, MEM_ROOT_BLOCK_SIZE, 0);
@@ -972,7 +941,8 @@
 
   dbchanged= FALSE;
   if (m_db.length &&
-      (ret= sp_use_new_db(thd, m_db.str, olddb, sizeof(olddb), 0, &dbchanged)))
+      (err_status= sp_use_new_db(thd, m_db.str, olddb, sizeof(olddb), 0,
+                                 &dbchanged)))
     goto done;
 
   if ((ctx= thd->spcont))
@@ -1050,7 +1020,7 @@
     if (thd->prelocked_mode == NON_PRELOCKED)
       thd->user_var_events_alloc= thd->mem_root;
     
-    ret= i->execute(thd, &ip);
+    err_status= i->execute(thd, &ip);
 
     /*
       If this SP instruction have sent eof, it has caused no_send_error to be
@@ -1078,11 +1048,10 @@
 
     /*
       Check if an exception has occurred and a handler has been found
-      Note: We havo to check even if ret==0, since warnings (and some
-      errors don't return a non-zero value.
-      We also have to check even if thd->killed != 0, since some
-      errors return with this even when a handler has been found
-      (e.g. "bad data").
+      Note: We have to check even if err_status == FALSE, since warnings (and
+      some errors) don't return a non-zero value. We also have to check even
+      if thd->killed != 0, since some errors return with this even when a
+      handler has been found (e.g. "bad data").
     */
     if (ctx)
     {
@@ -1093,13 +1062,12 @@
 	break;
       case SP_HANDLER_CONTINUE:
         thd->restore_active_arena(&execute_arena, &backup_arena);
-        ctx->save_variables(hf);
         thd->set_n_backup_active_arena(&execute_arena, &backup_arena);
         ctx->push_hstack(ip);
         // Fall through
       default:
 	ip= hip;
-	ret= 0;
+	err_status= FALSE;
 	ctx->clear_handler();
 	ctx->enter_handler(hip);
         thd->clear_error();
@@ -1107,7 +1075,7 @@
 	continue;
       }
     }
-  } while (ret == 0 && !thd->killed);
+  } while (!err_status && !thd->killed);
 
   thd->restore_active_arena(&execute_arena, &backup_arena);
 
@@ -1128,11 +1096,11 @@
   state= EXECUTED;
 
  done:
-  DBUG_PRINT("info", ("ret=%d killed=%d query_error=%d",
-		      ret, thd->killed, thd->query_error));
+  DBUG_PRINT("info", ("err_status=%d killed=%d query_error=%d",
+		      err_status, thd->killed, thd->query_error));
 
   if (thd->killed)
-    ret= -1;
+    err_status= TRUE;
   /* If the DB has changed, the pointer has changed too, but the
      original thd->db will then have been freed */
   if (dbchanged)
@@ -1140,7 +1108,7 @@
     /* No access check when changing back to where we came from.
        (It would generate an error from mysql_change_db() when olddb=="") */
     if (! thd->killed)
-      ret= mysql_change_db(thd, olddb, 1);
+      err_status= mysql_change_db(thd, olddb, 1);
   }
   m_flags&= ~IS_INVOKED;
   DBUG_PRINT("info", ("first free for 0x%lx --: 0x%lx->0x%lx, level: %lu, flags %x",
@@ -1166,7 +1134,7 @@
                m_first_instance->m_first_free_instance->m_recursion_level ==
                m_recursion_level + 1));
   m_first_instance->m_first_free_instance= this;
-  DBUG_RETURN(ret);
+  DBUG_RETURN(err_status);
 }
 
 
@@ -1178,33 +1146,41 @@
 
   SYNOPSIS
     sp_head::execute_function()
-      thd        Thread handle
-      argp       Passed arguments (these are items from containing statement?)
-      argcount   Number of passed arguments. We need to check if this is
-                 correct.
-      resp   OUT Put result item here (q: is it a constant Item always?) 
+      thd               Thread handle
+      argp              Passed arguments (these are items from containing
+                        statement?)
+      argcount          Number of passed arguments. We need to check if this is
+                        correct.
+      return_value_fld  Save result here.
    
   RETURN
-    0      on OK
-    other  on error
+    FALSE  on success
+    TRUE   on error
 */
 
-int
-sp_head::execute_function(THD *thd, Item **argp, uint argcount, Item **resp)
+bool
+sp_head::execute_function(THD *thd, Item **argp, uint argcount,
+                          Field *return_value_fld)
 {
-  Item **param_values;
+  Item_cache **param_values;
   ulonglong binlog_save_options;
   bool need_binlog_call;
-  DBUG_ENTER("sp_head::execute_function");
-  DBUG_PRINT("info", ("function %s", m_name.str));
-  uint csize = m_pcont->max_pvars();
-  uint params = m_pcont->current_pvars();
-  uint hmax = m_pcont->max_handlers();
-  uint cmax = m_pcont->max_cursors();
+  uint params;
   sp_rcontext *octx = thd->spcont;
   sp_rcontext *nctx = NULL;
-  uint i;
-  int ret= -1;                                  // Assume error
+  bool err_status= FALSE;
+
+  DBUG_ENTER("sp_head::execute_function");
+  DBUG_PRINT("info", ("function %s", m_name.str));
+
+  params = m_pcont->context_pvars();
+
+  /*
+    Check that the function is called with all specified arguments.
+
+    If it is not, use my_error() to report an error, or it will not terminate
+    the invoking query properly.
+  */
 
   if (argcount != params)
   {
@@ -1214,37 +1190,56 @@
     */
     my_error(ER_SP_WRONG_NO_OF_ARGS, MYF(0),
              "FUNCTION", m_qname.str, params, argcount);
-    goto end;
+    DBUG_RETURN(TRUE);
   }
 
-  if (!(param_values= (Item**)thd->alloc(sizeof(Item*)*argcount)))
-    DBUG_RETURN(-1);
+  /* Allocate param_values to be used for dumping the call into binlog. */
+
+  if (!(param_values= (Item_cache**)thd->alloc(sizeof(Item_cache*)*argcount)))
+    DBUG_RETURN(TRUE);
 
   // QQ Should have some error checking here? (types, etc...)
-  if (!(nctx= new sp_rcontext(octx, csize, hmax, cmax)))
-    goto end;
+
+  if (!(nctx= new sp_rcontext(m_pcont, return_value_fld, octx)) ||
+      nctx->init(thd))
+  {
+    delete nctx; /* Delete nctx if it was init() that failed. */
+    DBUG_RETURN(TRUE);
+  }
+
 #ifndef DBUG_OFF
-  nctx->owner= this;
+  nctx->sp= this;
 #endif
-  for (i= 0 ; i < argcount ; i++)
+
+  /* Pass arguments. */
+
   {
-    sp_pvar_t *pvar = m_pcont->find_pvar(i);
-    Item *it= sp_eval_func_item(thd, argp++, pvar->type, NULL, FALSE);
-    param_values[i]= it;
+    uint i;
+    
+    for (i= 0 ; i < argcount ; i++)
+    {
+      if (!argp[i]->fixed && argp[i]->fix_fields(thd, &argp[i]))
+      {
+        err_status= TRUE;
+        break;
+      }
 
-    if (!it)
-      goto end;                                 // EOM error
-    nctx->push_item(it);
-  }
+      param_values[i]= Item_cache::get_cache(argp[i]->result_type());
+      param_values[i]->store(argp[i]);
 
+      if (nctx->set_variable(thd, i, param_values[i]))
+      {
+        err_status= TRUE;
+        break;
+      }
+    }
+  }
 
-  /*
-    The rest of the frame are local variables which are all IN.
-    Push NULLs to get the right size (and make the reuse mechanism work) -
-    the will be initialized by set instructions in each frame.
-  */
-  for (; i < csize ; i++)
-    nctx->push_item(NULL);
+  if (err_status)
+  {
+    delete nctx;
+    DBUG_RETURN(TRUE);
+  }
 
   thd->spcont= nctx;
 
@@ -1257,7 +1252,7 @@
   }
     
   thd->options&= ~OPTION_BIN_LOG;
-  ret= execute(thd);
+  err_status= execute(thd);
   thd->options= binlog_save_options;
   
   if (need_binlog_call)
@@ -1273,9 +1268,18 @@
     bufstr.append('(');
     for (uint i=0; i < argcount; i++)
     {
+      String str_value_holder;
+      String *str_value;
+
       if (i)
         bufstr.append(',');
-      param_values[i]->print(&bufstr);
+
+      str_value= sp_get_item_value(param_values[i], &str_value_holder);
+
+      if (str_value)
+        bufstr.append(*str_value);
+      else
+        bufstr.append(STRING_WITH_LEN("NULL"));
     }
     bufstr.append(')');
     
@@ -1291,26 +1295,22 @@
     reset_dynamic(&thd->user_var_events);
   }
 
-  if (m_type == TYPE_ENUM_FUNCTION && ret == 0)
+  if (m_type == TYPE_ENUM_FUNCTION && !err_status)
   {
     /* We need result only in function but not in trigger */
-    Item *it= nctx->get_result();
 
-    if (it)
-      *resp= sp_eval_func_item(thd, &it, m_returns, NULL, FALSE);
-    else
+    if (!nctx->is_return_value_set())
     {
       my_error(ER_SP_NORETURNEND, MYF(0), m_name.str);
-      ret= -1;
+      err_status= TRUE;
     }
   }
 
   nctx->pop_all_cursors();	// To avoid memory leaks after an error
-  delete nctx;                                  // Doesn't do anything
+  delete nctx;
   thd->spcont= octx;
 
-end:
-  DBUG_RETURN(ret);
+  DBUG_RETURN(err_status);
 }
 
 
@@ -1342,17 +1342,15 @@
    - copy back values of INOUT and OUT parameters
 
   RETURN
-    0   Ok
-    -1  Error
+    FALSE  on success
+    TRUE   on error
 */
 
-int sp_head::execute_procedure(THD *thd, List<Item> *args)
+bool
+sp_head::execute_procedure(THD *thd, List<Item> *args)
 {
-  int ret= 0;
-  uint csize = m_pcont->max_pvars();
-  uint params = m_pcont->current_pvars();
-  uint hmax = m_pcont->max_handlers();
-  uint cmax = m_pcont->max_cursors();
+  bool err_status= FALSE;
+  uint params = m_pcont->context_pvars();
   sp_rcontext *save_spcont, *octx;
   sp_rcontext *nctx = NULL;
   DBUG_ENTER("sp_head::execute_procedure");
@@ -1362,16 +1360,21 @@
   {
     my_error(ER_SP_WRONG_NO_OF_ARGS, MYF(0), "PROCEDURE",
              m_qname.str, params, args->elements);
-    DBUG_RETURN(-1);
+    DBUG_RETURN(TRUE);
   }
 
   save_spcont= octx= thd->spcont;
   if (! octx)
   {				// Create a temporary old context
-    if (!(octx= new sp_rcontext(octx, csize, hmax, cmax)))
-      DBUG_RETURN(-1);
+    if (!(octx= new sp_rcontext(m_pcont, NULL, octx)) ||
+        octx->init(thd))
+    {
+      delete octx; /* Delete octx if it was init() that failed. */
+      DBUG_RETURN(TRUE);
+    }
+    
 #ifndef DBUG_OFF
-    octx->owner= 0;
+    octx->sp= 0;
 #endif
     thd->spcont= octx;
 
@@ -1379,63 +1382,62 @@
     thd->spcont->callers_arena= thd;
   }
 
-  if (!(nctx= new sp_rcontext(octx, csize, hmax, cmax)))
+  if (!(nctx= new sp_rcontext(m_pcont, NULL, octx)) ||
+      nctx->init(thd))
   {
+    delete nctx; /* Delete nctx if it was init() that failed. */
     thd->spcont= save_spcont;
-    DBUG_RETURN(-1);
+    DBUG_RETURN(TRUE);
   }
 #ifndef DBUG_OFF
-  nctx->owner= this;
+  nctx->sp= this;
 #endif
 
-  if (csize > 0 || hmax > 0 || cmax > 0)
+  if (params > 0)
   {
-    Item_null *nit= NULL;	// Re-use this, and only create if needed
-    uint i;
-    List_iterator<Item> li(*args);
-    Item *it;
+    List_iterator<Item> it_args(*args);
 
-    /* Evaluate SP arguments (i.e. get the values passed as parameters) */
-    // QQ: Should do type checking?
     DBUG_PRINT("info",(" %.*s: eval args", m_name.length, m_name.str));
-    for (i = 0 ; (it= li++) && i < params ; i++)
+
+    for (uint i= 0 ; i < params ; i++)
     {
+      Item *arg_item= it_args++;
       sp_pvar_t *pvar= m_pcont->find_pvar(i);
 
-      if (pvar)
+      if (!arg_item)
+        break;
+
+      if (!pvar)
+        continue;
+
+      if (pvar->mode != sp_param_in)
       {
-	if (pvar->mode != sp_param_in)
-	{
-	  if (!it->is_splocal() && !item_is_user_var(it))
-	  {
-	    my_error(ER_SP_NOT_VAR_ARG, MYF(0), i+1, m_qname.str);
-	    ret= -1;
-	    break;
-	  }
-	}
-	if (pvar->mode == sp_param_out)
-	{
-	  if (! nit)
-          {
-	    if (!(nit= new Item_null()))
-            {
-              ret= -1;
-              break;
-            }
-          }
-	  nctx->push_item(nit); // OUT
-	}
-	else
-	{
-	  Item *it2= sp_eval_func_item(thd, li.ref(), pvar->type, NULL, FALSE);
+        if (!arg_item->is_splocal() && !item_is_user_var(arg_item))
+        {
+          my_error(ER_SP_NOT_VAR_ARG, MYF(0), i+1, m_qname.str);
+          err_status= TRUE;
+          break;
+        }
+      }
 
-	  if (!it2)
-	  {
-	    ret= -1;		// Eval failed
-	    break;
-	  }
-          nctx->push_item(it2); // IN or INOUT
-	}
+      if (pvar->mode == sp_param_out)
+      {
+        Item_null *null_item= new Item_null();
+
+        if (!null_item ||
+            nctx->set_variable(thd, i, null_item))
+        {
+          err_status= TRUE;
+          break;
+        }
+      }
+      else
+      {
+        if (nctx->set_variable(thd, i, *it_args.ref()))
+        {
+          err_status= TRUE;
+          break;
+        }
       }
     }
 
@@ -1448,20 +1450,12 @@
       close_thread_tables(thd, 0, 0);
 
     DBUG_PRINT("info",(" %.*s: eval args done", m_name.length, m_name.str));
-
-    /*
-      The rest of the frame are local variables which are all IN.
-      Push NULLs to get the right size (and make the reuse mechanism work) -
-      the will be initialized by set instructions in each frame.
-    */
-    for (; i < csize ; i++)
-      nctx->push_item(NULL);
   }
 
   thd->spcont= nctx;
 
-  if (! ret)
-    ret= execute(thd);
+  if (!err_status)
+    err_status= execute(thd);
 
   /*
     In the case when we weren't able to employ reuse mechanism for
@@ -1471,75 +1465,67 @@
   */
   thd->spcont->callers_arena= octx->callers_arena;
 
-  if (!ret && csize > 0)
+  if (!err_status && params > 0)
   {
-    List_iterator<Item> li(*args);
-    Item *it;
+    List_iterator<Item> it_args(*args);
 
     /*
       Copy back all OUT or INOUT values to the previous frame, or
       set global user variables
     */
-    for (uint i = 0 ; (it= li++) && i < params ; i++)
+    for (uint i= 0 ; i < params ; i++)
     {
+      Item *arg_item= it_args++;
+
+      if (!arg_item)
+        break;
+
       sp_pvar_t *pvar= m_pcont->find_pvar(i);
 
-      if (pvar->mode != sp_param_in)
+      if (pvar->mode == sp_param_in)
+        continue;
+
+      if (arg_item->is_splocal())
       {
-	if (it->is_splocal())
-	{
-	  // Have to copy the item to the caller's mem_root
-	  Item *copy;
-	  uint offset= static_cast<Item_splocal *>(it)->get_offset();
-	  Item *val= nctx->get_item(i);
-	  Item *orig= octx->get_item(offset);
+        if (octx->set_variable(thd,
+                               ((Item_splocal*) arg_item)->get_var_idx(),
+                               nctx->get_item(i)))
+        {
+          err_status= TRUE;
+          break;
+        }
+      }
+      else
+      {
+        Item_func_get_user_var *guv= item_is_user_var(arg_item);
 
-          /*
-            We might need to allocate new item if we weren't able to
-            employ reuse mechanism. Then we should do it on the callers arena.
-          */
-	  copy= sp_eval_func_item(thd, &val, pvar->type, orig, TRUE); // Copy
-
-	  if (!copy)
-	  {
-	    ret= -1;
-	    break;
-	  }
-	  if (copy != orig)
-	    octx->set_item(offset, copy);
-	}
-	else
-	{
-	  Item_func_get_user_var *guv= item_is_user_var(it);
+        if (guv)
+        {
+          Item *item= nctx->get_item(i);
+          Item_func_set_user_var *suv;
 
-	  if (guv)
-	  {
-	    Item *item= nctx->get_item(i);
-	    Item_func_set_user_var *suv;
-
-	    suv= new Item_func_set_user_var(guv->get_name(), item);
-	    /*
-	      we do not check suv->fixed, because it can't be fixed after
-	      creation
-	    */
-	    suv->fix_fields(thd, &item);
-	    suv->fix_length_and_dec();
-	    suv->check();
-	    suv->update();
-	  }
-	}
+          suv= new Item_func_set_user_var(guv->get_name(), item);
+          /*
+             we do not check suv->fixed, because it can't be fixed after
+             creation
+           */
+          suv->fix_fields(thd, &item);
+          suv->fix_length_and_dec();
+          suv->check();
+          suv->update();
+        }
       }
     }
   }
 
   if (!save_spcont)
-    delete octx;                                // Does nothing
+    delete octx;
 
   nctx->pop_all_cursors();	// To avoid memory leaks after an error
-  delete nctx;                                  // Does nothing
+  delete nctx;
   thd->spcont= save_spcont;
 
-  DBUG_RETURN(ret);
+  DBUG_RETURN(err_status);
 }
 
 
@@ -1575,6 +1561,15 @@
   sublex->trg_chistics= oldlex->trg_chistics;
   sublex->trg_table_fields.empty();
   sublex->sp_lex_in_use= FALSE;
+
+  /* Reset type info. */
+
+  sublex->charset= NULL;
+  sublex->length= NULL;
+  sublex->dec= NULL;
+  sublex->interval_list.empty();
+  sublex->type= 0;
+
   DBUG_VOID_RETURN;
 }
 
@@ -1669,6 +1664,55 @@
   return 0;
 }
 
+
+/*
+  Prepare an instance of create_field for field creation (fill all necessary
+  attributes).
+
+  SYNOPSIS
+    sp_head::fill_field_definition()
+      thd         [IN] Thread handle
+      lex         [IN] Yacc parsing context
+      field_type  [IN] Field type
+      field_def   [OUT] An instance of create_field to be filled
+
+  RETURN
+    FALSE  on success
+    TRUE   on error
+*/
+
+bool
+sp_head::fill_field_definition(THD *thd, LEX *lex,
+                               enum enum_field_types field_type,
+                               create_field *field_def)
+{
+  LEX_STRING cmt = { 0, 0 };
+  uint unused1= 0;
+  int unused2= 0;
+
+  if (field_def->init(thd, (char*) "", field_type, lex->length, lex->dec,
+                      lex->type, (Item*) 0, (Item*) 0, &cmt, 0,
+                      &lex->interval_list,
+                      (lex->charset ? lex->charset : default_charset_info),
+                      lex->uint_geom_type))
+    return TRUE;
+
+  if (field_def->interval_list.elements)
+    field_def->interval= create_typelib(mem_root, field_def,
+                                        &field_def->interval_list);
+
+  sp_prepare_create_field(thd, field_def);
+
+  if (prepare_create_field(field_def, &unused1, &unused2, &unused2,
+                           HA_CAN_GEOMETRY))
+  {
+    return TRUE;
+  }
+
+  return FALSE;
+}
+
+
 void
 sp_head::set_info(longlong created, longlong modified,
 		  st_sp_chistics *chistics, ulong sql_mode)
@@ -2211,28 +2255,28 @@
 int
 sp_instr_set::exec_core(THD *thd, uint *nextp)
 {
-  int res= thd->spcont->set_item_eval(thd, m_offset, &m_value, m_type);
+  int res= thd->spcont->set_variable(thd, m_offset, m_value);
 
-  if (res < 0 &&
-      thd->spcont->get_item(m_offset) == NULL &&
-      thd->spcont->found_handler_here())
+  if (res && thd->spcont->found_handler_here())
   {
     /*
-      Failed to evaluate the value, the variable is still not initialized,
-      and a handler has been found. Set to null so we can continue.
+      Failed to evaluate the value, and a handler has been found. Reset the
+      variable to NULL.
     */
-    Item *it= new Item_null();
 
-    if (!it || thd->spcont->set_item_eval(thd, m_offset, &it, m_type) < 0)
-    {                           /* If this also failed, we have to abort */
-      sp_rcontext *spcont= thd->spcont;
+    if (thd->spcont->set_variable(thd, m_offset, 0))
+    {
+      /* If this also failed, let's abort. */
 
+      sp_rcontext *spcont= thd->spcont;
+    
       thd->spcont= 0;           /* Avoid handlers */
       my_error(ER_OUT_OF_RESOURCES, MYF(0));
       spcont->clear_handler();
       thd->spcont= spcont;
     }
   }
+
   *nextp = m_ip+1;
   return res;
 }
@@ -2501,20 +2545,22 @@
 int
 sp_instr_freturn::exec_core(THD *thd, uint *nextp)
 {
-  Item *it;
-  int res;
+  /*
+    Change <next instruction pointer>, so that this will be the last
+    instruction in the stored function.
+  */
 
-  it= sp_eval_func_item(thd, &m_value, m_type, NULL, TRUE);
-  if (! it)
-    res= -1;
-  else
-  {
-    res= 0;
-    thd->spcont->set_result(it);
-  }
   *nextp= UINT_MAX;
 
-  return res;
+  /*
+    Evaluate the value of return expression and store it in current runtime
+    context.
+
+    NOTE: It's necessary to evaluate result item right here, because we must
+    do it in scope of execution the current context/block.
+  */
+
+  return thd->spcont->set_return_value(thd, m_value);
 }
 
 void
@@ -2635,7 +2681,6 @@
     *nextp= m_dest;
   else
   {
-    thd->spcont->restore_variables(m_frame);
     *nextp= thd->spcont->pop_hstack();
   }
   thd->spcont->exit_handler();
@@ -2950,6 +2995,65 @@
     return;
   str->qs_append(STRING_WITH_LEN("error "));
   str->qs_append(m_errcode);
+}
+
+
+/**************************************************************************
+  sp_instr_set_case_expr class implementation
+**************************************************************************/
+
+int
+sp_instr_set_case_expr::execute(THD *thd, uint *nextp)
+{
+  DBUG_ENTER("sp_instr_set_case_expr::execute");
+
+  DBUG_RETURN(m_lex_keeper.reset_lex_and_exec_core(thd, nextp, TRUE, this));
+}
+
+
+int
+sp_instr_set_case_expr::exec_core(THD *thd, uint *nextp)
+{
+  int res= thd->spcont->set_case_expr(thd, m_case_expr_id, m_case_expr);
+
+  if (res &&
+      !thd->spcont->get_case_expr(m_case_expr_id) &&
+      thd->spcont->found_handler_here())
+  {
+    /*
+      Failed to evaluate the value, the case expression is still not
+      initialized, and a handler has been found. Set to NULL so we can continue.
+    */
+
+    Item *null_item= new Item_null();
+    
+    if (!null_item ||
+        thd->spcont->set_case_expr(thd, m_case_expr_id, null_item))
+    {
+      /* If this also failed, we have to abort. */
+
+      sp_rcontext *spcont= thd->spcont;
+    
+      thd->spcont= 0;           /* Avoid handlers */
+      my_error(ER_OUT_OF_RESOURCES, MYF(0));
+      spcont->clear_handler();
+      thd->spcont= spcont;
+    }
+  }
+
+  *nextp = m_ip+1;
+
+  return res; /* no error */
+}
+
+
+void
+sp_instr_set_case_expr::print(String *str)
+{
+  str->append(STRING_WITH_LEN("set_case_expr "));
+  str->qs_append(m_case_expr_id);
+  str->append(' ');
+  m_case_expr->print(str);
 }
 
 

--- 1.77/sql/sp_head.h	2005-11-23 02:12:35 +03:00
+++ 1.78/sql/sp_head.h	2005-12-07 17:01:08 +03:00
@@ -33,6 +33,9 @@
 Item_result
 sp_map_result_type(enum enum_field_types type);
 
+Item::Type
+sp_map_item_type(enum enum_field_types type);
+
 uint
 sp_get_flags_for_command(LEX *lex);
 
@@ -123,12 +126,9 @@
   /* TYPE_ENUM_FUNCTION, TYPE_ENUM_PROCEDURE or TYPE_ENUM_TRIGGER */
   int m_type;
   uint m_flags;                 // Boolean attributes of a stored routine
-  enum enum_field_types m_returns; // For FUNCTIONs only
-  Field::geometry_type m_geom_returns;
-  CHARSET_INFO *m_returns_cs;	// For FUNCTIONs only
-  TYPELIB *m_returns_typelib;	// For FUNCTIONs only
-  uint m_returns_len;		// For FUNCTIONs only
-  uint m_returns_pack;		// For FUNCTIONs only
+
+  create_field m_return_field_def; /* This is used for FUNCTIONs only. */
+
   uchar *m_tmp_query;		// Temporary pointer to sub query string
   uint m_old_cmq;		// Old CLIENT_MULTI_QUERIES value
   st_sp_chistics *m_chistics;
@@ -202,9 +202,6 @@
   void
   init_strings(THD *thd, LEX *lex, sp_name *name);
 
-  TYPELIB *
-  create_typelib(List<String> *src);
-
   int
   create(THD *thd);
 
@@ -214,10 +211,10 @@
   void
   destroy();
 
-  int
-  execute_function(THD *thd, Item **args, uint argcount, Item **resp);
+  bool
+  execute_function(THD *thd, Item **args, uint argcount, Field *return_fld);
 
-  int
+  bool
   execute_procedure(THD *thd, List<Item> *args);
 
   int
@@ -278,7 +275,12 @@
 
   char *create_string(THD *thd, ulong *lenp);
 
-  Field *make_field(uint max_length, const char *name, TABLE *dummy);
+  Field *create_result_field(uint field_max_length, const char *field_name,
+                             TABLE *table);
+
+  bool fill_field_definition(THD *thd, LEX *lex,
+                             enum enum_field_types field_type,
+                             create_field *field_def);
 
   void set_info(longlong created, longlong modified,
 		st_sp_chistics *chistics, ulong sql_mode);
@@ -363,7 +365,7 @@
   */
   HASH m_sptabs;
 
-  int
+  bool
   execute(THD *thd);
 
   /*
@@ -1074,6 +1076,31 @@
 }; // class sp_instr_error : public sp_instr
 
 
+class sp_instr_set_case_expr :public sp_instr
+{
+public:
+
+  sp_instr_set_case_expr(uint ip, sp_pcontext *ctx, uint case_expr_id,
+                         Item *case_expr, LEX *lex)
+    :sp_instr(ip, ctx), m_case_expr_id(case_expr_id), m_case_expr(case_expr),
+     m_lex_keeper(lex, TRUE)
+  {}
+
+  virtual int execute(THD *thd, uint *nextp);
+
+  virtual int exec_core(THD *thd, uint *nextp);
+
+  virtual void print(String *str);
+
+private:
+
+  uint m_case_expr_id;
+  Item *m_case_expr;
+  sp_lex_keeper m_lex_keeper;
+
+}; // class sp_instr_set_case_expr : public sp_instr
+
+
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
 bool
 sp_change_security_context(THD *thd, sp_head *sp,
@@ -1086,8 +1113,10 @@
 sp_add_to_query_tables(THD *thd, LEX *lex,
 		       const char *db, const char *name,
 		       thr_lock_type locktype);
+Item *
+sp_prepare_func_item(THD* thd, Item **it_addr);
 
-Item *sp_eval_func_item(THD *thd, Item **it, enum_field_types type,
-                        Item *reuse, bool use_callers_arena);
+bool
+sp_eval_expr(THD *thd, Field *result_field, Item *expr_item);
 
 #endif /* _SP_HEAD_H_ */

--- 1.27/sql/sp_pcontext.cc	2005-12-02 16:58:50 +03:00
+++ 1.28/sql/sp_pcontext.cc	2005-12-07 17:01:08 +03:00
@@ -51,21 +51,26 @@
 }
 
 sp_pcontext::sp_pcontext(sp_pcontext *prev)
-  : Sql_alloc(), m_psubsize(0), m_csubsize(0), m_hsubsize(0),
-    m_handlers(0), m_parent(prev), m_pboundary(0)
+  :Sql_alloc(), m_total_pvars(0), m_csubsize(0), m_hsubsize(0),
+   m_handlers(0), m_parent(prev), m_pboundary(0)
 {
   VOID(my_init_dynamic_array(&m_pvar, sizeof(sp_pvar_t *), 16, 8));
+  VOID(my_init_dynamic_array(&m_case_expr_id_lst, sizeof(int), 16, 8));
   VOID(my_init_dynamic_array(&m_cond, sizeof(sp_cond_type_t *), 16, 8));
   VOID(my_init_dynamic_array(&m_cursor, sizeof(LEX_STRING), 16, 8));
   VOID(my_init_dynamic_array(&m_handler, sizeof(sp_cond_type_t *), 16, 8));
   m_label.empty();
   m_children.empty();
   if (!prev)
+  {
     m_poffset= m_coffset= 0;
+    m_num_case_exprs= 0;
+  }
   else
   {
-    m_poffset= prev->current_pvars();
+    m_poffset= prev->m_poffset + prev->m_total_pvars;
     m_coffset= prev->current_cursors();
+    m_num_case_exprs= prev->get_num_case_exprs();
   }
 }
 
@@ -81,6 +86,7 @@
   m_children.empty();
   m_label.empty();
   delete_dynamic(&m_pvar);
+  delete_dynamic(&m_case_expr_id_lst);
   delete_dynamic(&m_cond);
   delete_dynamic(&m_cursor);
   delete_dynamic(&m_handler);
@@ -99,16 +105,19 @@
 sp_pcontext *
 sp_pcontext::pop_context()
 {
-  uint submax= max_pvars();
+  m_parent->m_total_pvars= m_parent->m_total_pvars + m_total_pvars;
 
-  if (submax > m_parent->m_psubsize)
-    m_parent->m_psubsize= submax;
-  submax= max_handlers();
+  uint submax= max_handlers();
   if (submax > m_parent->m_hsubsize)
     m_parent->m_hsubsize= submax;
+
   submax= max_cursors();
   if (submax > m_parent->m_csubsize)
     m_parent->m_csubsize= submax;
+
+  if (m_num_case_exprs > m_parent->m_num_case_exprs)
+    m_parent->m_num_case_exprs= m_num_case_exprs;
+
   return m_parent;
 }
 
@@ -191,26 +200,29 @@
   return NULL;                  // index out of bounds
 }
 
-void
+sp_pvar_t *
 sp_pcontext::push_pvar(LEX_STRING *name, enum enum_field_types type,
 		       sp_param_mode_t mode)
 {
   sp_pvar_t *p= (sp_pvar_t *)sql_alloc(sizeof(sp_pvar_t));
 
-  if (p)
-  {
-    if (m_pvar.elements == m_psubsize)
-      m_psubsize+= 1;
-    p->name.str= name->str;
-    p->name.length= name->length;
-    p->type= type;
-    p->mode= mode;
-    p->offset= current_pvars();
-    p->dflt= NULL;
-    insert_dynamic(&m_pvar, (gptr)&p);
-  }
+  if (!p)
+    return NULL;
+
+  ++m_total_pvars;
+
+  p->name.str= name->str;
+  p->name.length= name->length;
+  p->type= type;
+  p->mode= mode;
+  p->offset= current_pvars();
+  p->dflt= NULL;
+  insert_dynamic(&m_pvar, (gptr)&p);
+
+  return p;
 }
 
+
 sp_label_t *
 sp_pcontext::push_label(char *name, uint ip)
 {
@@ -352,6 +364,29 @@
   if (!scoped && m_parent)
     return m_parent->find_cursor(name, poff, scoped);
   return FALSE;
+}
+
+
+void
+sp_pcontext::retrieve_field_definitions(List<create_field> *field_def_lst)
+{
+  /* Put local/context fields in the result list. */
+
+  for (uint i = 0; i < m_pvar.elements; ++i)
+  {
+    sp_pvar_t *var_def;
+    get_dynamic(&m_pvar, (gptr) &var_def, i);
+
+    field_def_lst->push_back(&var_def->field_def);
+  }
+
+  /* Put the fields of the enclosed contexts in the result list. */
+
+  List_iterator_fast<sp_pcontext> li(m_children);
+  sp_pcontext *ctx;
+
+  while ((ctx = li++))
+    ctx->retrieve_field_definitions(field_def_lst);
 }
 
 /*

--- 1.24/sql/sp_pcontext.h	2005-12-02 17:04:43 +03:00
+++ 1.25/sql/sp_pcontext.h	2005-12-07 17:01:08 +03:00
@@ -34,8 +34,16 @@
   LEX_STRING name;
   enum enum_field_types type;
   sp_param_mode_t mode;
-  uint offset;			// Offset in current frame
+  
+  /*
+    offset -- basically, this is an index of variable in the scope of root
+    parsing context. This means, that all variables in a stored routine
+    have distinct indexes/offsets.
+  */
+  uint offset;
+
   Item *dflt;
+  create_field field_def;
 } sp_pvar_t;
 
 
@@ -114,9 +122,9 @@
   //
 
   inline uint
-  max_pvars()
+  total_pvars()
   {
-    return m_psubsize + m_pvar.elements;
+    return m_total_pvars;
   }
 
   inline uint
@@ -155,16 +163,15 @@
       p->dflt= it;
   }
 
-  void
+  sp_pvar_t *
   push_pvar(LEX_STRING *name, enum enum_field_types type, sp_param_mode_t mode);
 
-  // Pop the last 'num' slots of the frame
-  inline void
-  pop_pvar(uint num = 1)
-  {
-    while (num--)
-      pop_dynamic(&m_pvar);
-  }
+  /*
+    Retrieve definitions of fields from the current context and its
+    children.
+  */
+  void
+  retrieve_field_definitions(List<create_field> *field_def_lst);
 
   // Find by name
   sp_pvar_t *
@@ -175,7 +182,7 @@
   find_pvar(uint offset);
 
   /*
-    Set the current scope boundary (for default values)
+    Set the current scope boundary (for default values).
     The argument is the number of variables to skip.   
   */
   inline void
@@ -184,6 +191,45 @@
     m_pboundary= n;
   }
 
+  /*
+    CASE expressions support.
+  */
+
+  inline int
+  register_case_expr()
+  {
+    return m_num_case_exprs++;
+  }
+
+  inline int
+  get_num_case_exprs() const
+  {
+    return m_num_case_exprs;
+  }
+
+  inline bool
+  push_case_expr_id(int case_expr_id)
+  {
+    return insert_dynamic(&m_case_expr_id_lst, (gptr) &case_expr_id);
+  }
+
+  inline void
+  pop_case_expr_id()
+  {
+    pop_dynamic(&m_case_expr_id_lst);
+  }
+
+  inline int
+  get_current_case_expr_id() const
+  {
+    int case_expr_id;
+
+    get_dynamic((DYNAMIC_ARRAY*)&m_case_expr_id_lst, (gptr) &case_expr_id,
+                m_case_expr_id_lst.elements - 1);
+
+    return case_expr_id;
+  }
+
   //
   // Labels
   //
@@ -280,8 +326,18 @@
 
 protected:
 
+  /*
+    m_total_pvars -- number of variables (including all types of arguments)
+    in this context including all children contexts.
+    
+    m_total_pvars >= m_pvar.elements.
+
+    m_total_pvars of the root parsing context contains number of all
+    variables (including arguments) in all enclosed contexts.
+  */
+  uint m_total_pvars;		
+
   // The maximum sub context's framesizes
-  uint m_psubsize;		
   uint m_csubsize;
   uint m_hsubsize;
   uint m_handlers;		// No. of handlers in this context
@@ -290,8 +346,19 @@
 
   sp_pcontext *m_parent;	// Parent context
 
-  uint m_poffset;		// Variable offset for this context
+  /*
+    m_poffset -- basically, this is an index of the first variable in this
+    parsing context.
+    
+    m_poffset is 0 for root context.
+
+    Since now each variable is stored in separate place, no reuse is done,
+    so m_poffset is different for all enclosed contexts.
+  */
+  uint m_poffset;
+
   uint m_coffset;		// Cursor offset for this context
+
   /*
     Boundary for finding variables in this context. This is the number
     of variables currently "invisible" to default clauses.
@@ -300,7 +367,10 @@
   */
   uint m_pboundary;
 
+  int m_num_case_exprs;
+
   DYNAMIC_ARRAY m_pvar;		// Parameters/variables
+  DYNAMIC_ARRAY m_case_expr_id_lst; /* Stack of CASE expression ids. */
   DYNAMIC_ARRAY m_cond;		// Conditions
   DYNAMIC_ARRAY m_cursor;	// Cursors
   DYNAMIC_ARRAY m_handler;	// Handlers, for checking of duplicates

--- 1.35/sql/sp_rcontext.cc	2005-09-26 20:46:25 +04:00
+++ 1.36/sql/sp_rcontext.cc	2005-12-07 17:01:08 +03:00
@@ -29,41 +29,137 @@
 #include "sp_rcontext.h"
 #include "sp_pcontext.h"
 
-sp_rcontext::sp_rcontext(sp_rcontext *prev, uint fsize, uint hmax, uint cmax)
-  : m_count(0), m_fsize(fsize), m_result(NULL), m_hcount(0), m_hsp(0),
-    m_ihsp(0), m_hfound(-1), m_ccount(0), m_prev_ctx(prev)
-{
-  m_frame= (Item **)sql_alloc(fsize * sizeof(Item*));
-  m_handler= (sp_handler_t *)sql_alloc(hmax * sizeof(sp_handler_t));
-  m_hstack= (uint *)sql_alloc(hmax * sizeof(uint));
-  m_in_handler= (uint *)sql_alloc(hmax * sizeof(uint));
-  m_cstack= (sp_cursor **)sql_alloc(cmax * sizeof(sp_cursor *));
-  m_saved.empty();
+
+sp_rcontext::sp_rcontext(sp_pcontext *root_parsing_ctx,
+                         Field *return_value_fld,
+                         sp_rcontext *prev_runtime_ctx)
+  :m_root_parsing_ctx(root_parsing_ctx),
+   m_var_table(0),
+   m_var_items(0),
+   m_return_value_fld(return_value_fld),
+   m_return_value_set(FALSE),
+   m_hcount(0),
+   m_hsp(0),
+   m_ihsp(0),
+   m_hfound(-1),
+   m_ccount(0),
+   m_case_expr_holders(0),
+   m_prev_runtime_ctx(prev_runtime_ctx)
+{
 }
 
 
-int
-sp_rcontext::set_item_eval(THD *thd, uint idx, Item **item_addr,
-			   enum_field_types type)
+sp_rcontext::~sp_rcontext()
+{
+  if (m_var_table)
+    free_blobs(m_var_table);
+}
+
+
+/*
+  Initialize sp_rcontext instance.
+
+  SYNOPSIS
+    thd   Thread handle
+  RETURN
+    FALSE   on success
+    TRUE    on error
+*/
+
+bool sp_rcontext::init(THD *thd)
+{
+  if (init_var_table(thd) || init_var_items())
+    return TRUE;
+
+  return
+    !(m_handler=
+      (sp_handler_t*)thd->alloc(m_root_parsing_ctx->max_handlers() *
+                                sizeof(sp_handler_t))) ||
+    !(m_hstack=
+      (uint*)thd->alloc(m_root_parsing_ctx->max_handlers() *
+                        sizeof(uint))) ||
+    !(m_in_handler=
+      (uint*)thd->alloc(m_root_parsing_ctx->max_handlers() *
+                        sizeof(uint))) ||
+    !(m_cstack=
+      (sp_cursor**)thd->alloc(m_root_parsing_ctx->max_cursors() *
+                              sizeof(sp_cursor*))) ||
+    !(m_case_expr_holders=
+      (Item_cache**)thd->calloc(m_root_parsing_ctx->get_num_case_exprs() *
+                               sizeof (Item_cache*)));
+}
+
+
+/*
+  Create and initialize a table to store SP-vars.
+
+  SYNOPSIS
+    thd   Thread handler.
+  RETURN
+    FALSE   on success
+    TRUE    on error
+*/
+
+bool
+sp_rcontext::init_var_table(THD *thd)
 {
-  Item *it;
-  Item *reuse_it;
-  /* sp_eval_func_item will use callers_arena */
-  int res;
+  List<create_field> field_def_lst;
+
+  if (!m_root_parsing_ctx->total_pvars())
+    return FALSE;
+
+  m_root_parsing_ctx->retrieve_field_definitions(&field_def_lst);
+
+  DBUG_ASSERT(field_def_lst.elements == m_root_parsing_ctx->total_pvars());
+  
+  if (!(m_var_table= create_virtual_tmp_table(thd, field_def_lst)))
+    return TRUE;
+
+  m_var_table->copy_blobs= TRUE;
+  m_var_table->alias= "";
+
+  return FALSE;
+}
 
-  reuse_it= get_item(idx);
-  it= sp_eval_func_item(thd, item_addr, type, reuse_it, TRUE);
-  if (! it)
-    res= -1;
-  else
+
+/*
+  Create and initialize an Item-adapter (Item_field) for each SP-var field.
+
+  RETURN
+    FALSE   on success
+    TRUE    on error
+*/
+
+bool
+sp_rcontext::init_var_items()
+{
+  uint idx;
+  uint num_vars= m_root_parsing_ctx->total_pvars();
+
+  if (!(m_var_items= (Item**) sql_alloc(num_vars * sizeof (Item *))))
+    return TRUE;
+
+  for (idx = 0; idx < num_vars; ++idx)
   {
-    res= 0;
-    set_item(idx, it);
+    if (!(m_var_items[idx]= new Item_field(m_var_table->field[idx])))
+      return TRUE;
   }
 
-  return res;
+  return FALSE;
 }
 
+
+bool
+sp_rcontext::set_return_value(THD *thd, Item *return_value_item)
+{
+  DBUG_ASSERT(m_return_value_fld);
+
+  m_return_value_set = TRUE;
+
+  return sp_eval_expr(thd, m_return_value_fld, return_value_item);
+}
+
+
 bool
 sp_rcontext::find_handler(uint sql_errno,
                           MYSQL_ERROR::enum_warning_level level)
@@ -117,32 +213,14 @@
   }
   if (found < 0)
   {
-    if (m_prev_ctx)
-      return m_prev_ctx->find_handler(sql_errno, level);
+    if (m_prev_runtime_ctx)
+      return m_prev_runtime_ctx->find_handler(sql_errno, level);
     return FALSE;
   }
   m_hfound= found;
   return TRUE;
 }
 
-void
-sp_rcontext::save_variables(uint fp)
-{
-  while (fp < m_count)
-  {
-    m_saved.push_front(m_frame[fp]);
-    m_frame[fp++]= NULL;	// Prevent reuse
-  }
-}
-
-void
-sp_rcontext::restore_variables(uint fp)
-{
-  uint i= m_count;
-
-  while (i-- > fp)
-    m_frame[i]= m_saved.pop();
-}
 
 void
 sp_rcontext::push_cursor(sp_lex_keeper *lex_keeper, sp_instr_cpush *i)
@@ -150,6 +228,7 @@
   m_cstack[m_ccount++]= new sp_cursor(lex_keeper, i);
 }
 
+
 void
 sp_rcontext::pop_cursors(uint count)
 {
@@ -160,6 +239,40 @@
 }
 
 
+int
+sp_rcontext::set_variable(THD *thd, uint var_idx, Item *value)
+{
+  return set_variable(thd, m_var_table->field[var_idx], value);
+}
+
+
+int
+sp_rcontext::set_variable(THD *thd, Field *field, Item *value)
+{
+  if (!value)
+  {
+    field->set_null();
+    return 0;
+  }
+
+  return sp_eval_expr(thd, field, value);
+}
+
+
+Item *
+sp_rcontext::get_item(uint var_idx)
+{
+  return m_var_items[var_idx];
+}
+
+
+Item **
+sp_rcontext::get_item_addr(uint var_idx)
+{
+  return m_var_items + var_idx;
+}
+
+
 /*
  *
  *  sp_cursor
@@ -263,6 +376,102 @@
 }
 
 
+/*
+  Create an instance of appropriate Item_cache class depending on the
+  specified type in the callers arena.
+
+  SYNOPSIS
+    thd           thread handler
+    result_type   type of the expression
+
+  RETURN
+    Pointer to valid object     on success
+    NULL                        on error
+
+  NOTE
+    We should create cache items in the callers arena, as they are used
+    between in several instructions.
+*/
+
+Item_cache *
+sp_rcontext::create_case_expr_holder(THD *thd, Item_result result_type)
+{
+  Item_cache *holder;
+  Query_arena current_arena;
+
+  thd->set_n_backup_active_arena(thd->spcont->callers_arena, &current_arena);
+
+  holder= Item_cache::get_cache(result_type);
+
+  thd->restore_active_arena(thd->spcont->callers_arena, &current_arena);
+
+  return holder;
+}
+
+
+/*
+  Set CASE expression to the specified value.
+
+  SYNOPSIS
+    thd             thread handler
+    case_expr_id    identifier of the CASE expression
+    case_expr_item  a value of the CASE expression
+
+  RETURN
+    FALSE   on success
+    TRUE    on error
+
+  NOTE
+    The idea is to reuse Item_cache for the expression of the one CASE
+    statement. This optimization takes place when there is CASE statement
+    inside of a loop. So, in other words, we will use the same object on each
+    iteration instead of creating a new one for each iteration.
+
+  TODO
+    Hypothetically, a type of CASE expression can be different for each
+    iteration. For instance, this can happen if the expression contains a
+    session variable (something like @@VAR) and its type is changed from one
+    iteration to another.
+    
+    In order to cope with this problem, we check type each time, when we use
+    already created object. If the type does not match, we re-create Item.
+    This also can (should?) be optimized.
+*/
+
+int
+sp_rcontext::set_case_expr(THD *thd, int case_expr_id, Item *case_expr_item)
+{
+  if (!(case_expr_item= sp_prepare_func_item(thd, &case_expr_item)))
+    return TRUE;
+
+  if (!m_case_expr_holders[case_expr_id] ||
+      m_case_expr_holders[case_expr_id]->result_type() !=
+        case_expr_item->result_type())
+  {
+    m_case_expr_holders[case_expr_id]=
+      create_case_expr_holder(thd, case_expr_item->result_type());
+  }
+
+  m_case_expr_holders[case_expr_id]->store(case_expr_item);
+
+  return FALSE;
+}
+
+
+Item *
+sp_rcontext::get_case_expr(int case_expr_id)
+{
+  return m_case_expr_holders[case_expr_id];
+}
+
+
+Item **
+sp_rcontext::get_case_expr_addr(int case_expr_id)
+{
+  return (Item**) m_case_expr_holders + case_expr_id;
+}
+
+
 /***************************************************************************
  Select_fetch_into_spvars
 ****************************************************************************/
@@ -294,11 +503,8 @@
   */
   for (; pv= pv_iter++, item= item_iter++; )
   {
-    Item *reuse= thd->spcont->get_item(pv->offset);
-    /* Evaluate a new item on the arena of the calling instruction */
-    Item *it= sp_eval_func_item(thd, &item, pv->type, reuse, TRUE);
-
-    thd->spcont->set_item(pv->offset, it);
+    if (thd->spcont->set_variable(thd, pv->offset, item))
+      return TRUE;
   }
   return FALSE;
 }

--- 1.28/sql/sp_rcontext.h	2005-11-23 01:58:09 +03:00
+++ 1.29/sql/sp_rcontext.h	2005-12-07 17:01:08 +03:00
@@ -43,12 +43,22 @@
 
 
 /*
-  This is a run context? of one SP ?
-  THis is 
-   - a stack of cursors? 
-   - a stack of handlers?
-   - a stack of Items ?
-   - a stack of instruction locations in SP?
+  This class is a runtime context of a Stored Routine. It is used in an
+  execution and is intended to contain all dynamic objects (i.e.  objects, which
+  can be changed during execution), such as:
+    - stored routine variables;
+    - cursors;
+    - handlers;
+
+  Runtime context is used with sp_head class. sp_head class is intended to
+  contain all static things, related to the stored routines (code, for example).
+  sp_head instance creates runtime context for the execution of a stored
+  routine.
+
+  There is a parsing context (an instance of sp_pcontext class), which is used
+  on parsing stage. However, now it contains some necessary for an execution
+  things, such as definition of used stored routine variables. That's why
+  runtime context needs a reference to the parsing context.
 */
 
 class sp_rcontext : public Sql_alloc
@@ -68,62 +78,34 @@
 
 #ifndef DBUG_OFF
   /*
-    Routine to which this Item_splocal belongs. Used for checking if correct
-    runtime context is used for variable handling.
+    The routine for which this runtime context is created. Used for checking
+    if correct runtime context is used for variable handling.
   */
-  sp_head *owner;
+  sp_head *sp;
 #endif
 
-  sp_rcontext(sp_rcontext *prev, uint fsize, uint hmax, uint cmax);
+  sp_rcontext(sp_pcontext *root_parsing_ctx, Field *return_value_fld,
+              sp_rcontext *prev_runtime_ctx);
+  bool init(THD *thd);
 
-  ~sp_rcontext()
-  {
-    // Not needed?
-    //sql_element_free(m_frame);
-    //m_saved.empty();
-  }
-
-  inline void
-  push_item(Item *i)
-  {
-    if (m_count < m_fsize)
-      m_frame[m_count++]= i;
-  }
+  ~sp_rcontext();
 
-  inline void
-  set_item(uint idx, Item *i)
-  {
-    if (idx < m_count)
-      m_frame[idx]= i;
-  }
-
-  /* Returns 0 on success, -1 on (eval) failure */
   int
-  set_item_eval(THD *thd, uint idx, Item **i, enum_field_types type);
-
-  inline Item *
-  get_item(uint idx)
-  {
-    return m_frame[idx];
-  }
+  set_variable(THD *thd, uint var_idx, Item *value);
 
-  inline Item **
-  get_item_addr(uint idx)
-  {
-    return m_frame + idx;
-  }
+  Item *
+  get_item(uint var_idx);
 
+  Item **
+  get_item_addr(uint var_idx);
 
-  inline void
-  set_result(Item *it)
-  {
-    m_result= it;
-  }
+  bool
+  set_return_value(THD *thd, Item *return_value_item);
 
-  inline Item *
-  get_result()
+  inline bool
+  is_return_value_set() const
   {
-    return m_result;
+    return m_return_value_set;
   }
 
   inline void
@@ -195,14 +177,6 @@
     m_ihsp-= 1;
   }
 
-  // Save variables starting at fp and up
-  void
-  save_variables(uint fp);
-
-  // Restore variables down to fp
-  void
-  restore_variables(uint fp);
-
   void
   push_cursor(sp_lex_keeper *lex_keeper, sp_instr_cpush *i);
 
@@ -221,13 +195,42 @@
     return m_cstack[i];
   }
 
+  /*
+    CASE expressions support.
+  */
+
+  int
+  set_case_expr(THD *thd, int case_expr_id, Item *case_expr_item);
+
+  Item *
+  get_case_expr(int case_expr_id);
+
+  Item **
+  get_case_expr_addr(int case_expr_id);
+
 private:
+  sp_pcontext *m_root_parsing_ctx;
 
-  uint m_count;
-  uint m_fsize;
-  Item **m_frame;
+  /* Virtual table for storing variables. */
+  TABLE *m_var_table;
 
-  Item *m_result;		// For FUNCTIONs
+  /*
+    Collection of Item_field proxies, each of them points to the corresponding
+    field in m_var_table.
+  */
+  Item **m_var_items;
+
+  /*
+    This is a pointer to a field, which should contain return value for stored
+    functions (only). For stored procedures, this pointer is NULL.
+  */
+  Field *m_return_value_fld;
+
+  /*
+    Indicates whether the return value (in m_return_value_fld) has been set
+    during execution.
+  */
+  bool m_return_value_set;
 
   sp_handler_t *m_handler;      // Visible handlers
   uint m_hcount;                // Stack pointer for m_handler
@@ -236,13 +239,22 @@
   uint *m_in_handler;           // Active handler, for recursion check
   uint m_ihsp;                  // Stack pointer for m_in_handler
   int m_hfound;                 // Set by find_handler; -1 if not found
-  List<Item> m_saved;           // Saved variables during handler exec.
 
   sp_cursor **m_cstack;
   uint m_ccount;
 
-  sp_rcontext *m_prev_ctx;      // Previous context (NULL if none)
+  Item_cache **m_case_expr_holders;
+
+  /* Previous runtime context (NULL if none) */
+  sp_rcontext *m_prev_runtime_ctx;
+
+private:
+  bool init_var_table(THD *thd);
+  bool init_var_items();
+
+  Item_cache *create_case_expr_holder(THD *thd, Item_result result_type);
 
+  int set_variable(THD *thd, Field *field, Item *value);
 }; // class sp_rcontext : public Sql_alloc
 
 
Thread
bk commit into 5.0 tree (anozdrin:1.1982) BUG#15148Alexander Nozdrin7 Dec