From: Date: December 7 2005 3:01pm Subject: bk commit into 5.0 tree (anozdrin:1.1982) BUG#15148 List-Archive: http://lists.mysql.com/internals/33136 X-Bug: 15148 Message-Id: <20051207140125.2698.qmail@alik.ru> 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 *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 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 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 *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(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 &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 *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 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 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 &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 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 &field_list); void free_tmp_table(THD *thd, TABLE *entry); void count_field_types(TMP_TABLE_PARAM *param, List &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: = */ - 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($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 + root test