| List: | Internals | « Previous MessageNext Message » | |
| From: | Jim Winstead | Date: | April 6 2005 4:45am |
| Subject: | bk commit into 5.0 tree (jimw:1.1876) | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 5.0 repository of jimw. When jimw 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.1876 05/04/05 19:45:34 jimw@stripped +23 -0 Merge mysql-test/t/strict.test 1.11 05/04/05 19:45:34 jimw@stripped +0 -0 SCCS merged sql/sql_insert.cc 1.139 05/04/05 19:45:08 jimw@stripped +0 -0 Auto merged sql/item.h 1.113 05/04/05 19:45:08 jimw@stripped +0 -0 Auto merged sql/item.cc 1.106 05/04/05 19:45:08 jimw@stripped +0 -0 Auto merged mysql-test/t/type_ranges.test 1.12 05/04/05 19:45:08 jimw@stripped +0 -0 Auto merged mysql-test/t/type_blob.test 1.27 05/04/05 19:45:08 jimw@stripped +0 -0 Auto merged mysql-test/t/sp.test 1.99 05/04/05 19:45:08 jimw@stripped +0 -0 Auto merged mysql-test/t/show_check.test 1.47 05/04/05 19:45:08 jimw@stripped +0 -0 Auto merged mysql-test/t/order_by.test 1.32 05/04/05 19:45:08 jimw@stripped +0 -0 Auto merged mysql-test/t/limit.test 1.9 05/04/05 19:45:08 jimw@stripped +0 -0 Auto merged mysql-test/t/ctype_latin1_de.test 1.21 05/04/05 19:45:08 jimw@stripped +0 -0 Auto merged mysql-test/r/warnings.result 1.31 05/04/05 19:45:08 jimw@stripped +0 -0 Auto merged mysql-test/r/type_ranges.result 1.32 05/04/05 19:45:08 jimw@stripped +0 -0 Auto merged mysql-test/r/type_blob.result 1.45 05/04/05 19:45:08 jimw@stripped +0 -0 Auto merged mysql-test/r/strict.result 1.15 05/04/05 19:45:08 jimw@stripped +41 -41 Auto merged mysql-test/r/sp.result 1.104 05/04/05 19:45:07 jimw@stripped +0 -0 Auto merged mysql-test/r/rpl000001.result 1.29 05/04/05 19:45:07 jimw@stripped +0 -0 Auto merged mysql-test/r/order_by.result 1.48 05/04/05 19:45:07 jimw@stripped +0 -0 Auto merged mysql-test/r/limit.result 1.9 05/04/05 19:45:07 jimw@stripped +0 -0 Auto merged mysql-test/r/grant2.result 1.18 05/04/05 19:45:07 jimw@stripped +0 -0 Auto merged mysql-test/r/grant.result 1.39 05/04/05 19:45:07 jimw@stripped +0 -0 Auto merged mysql-test/r/ctype_latin1_de.result 1.23 05/04/05 19:45:07 jimw@stripped +0 -0 Auto merged mysql-test/r/create.result 1.84 05/04/05 19:45:07 jimw@stripped +0 -0 Auto merged # 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: jimw # Host: rama.(none) # Root: /home/jimw/my/mysql-5.0-clean/RESYNC --- 1.105/sql/item.cc 2005-01-13 10:48:31 -08:00 +++ 1.106/sql/item.cc 2005-04-05 19:45:08 -07:00 @@ -33,6 +33,131 @@ const String my_null_string("NULL", 4, default_charset_info); +/****************************************************************************/ + +/* Hybrid_type_traits {_real} */ + +void Hybrid_type_traits::fix_length_and_dec(Item *item, Item *arg) const +{ + item->decimals= NOT_FIXED_DEC; + item->max_length= item->float_length(arg->decimals); +} + + +const Hybrid_type_traits *Hybrid_type_traits::instance() +{ + static const Hybrid_type_traits real_traits; + return &real_traits; +} + + +my_decimal * +Hybrid_type_traits::val_decimal(Hybrid_type *val, my_decimal *to) const +{ + double2my_decimal(E_DEC_FATAL_ERROR, val->real, val->dec_buf); + return val->dec_buf; +} + + +String * +Hybrid_type_traits::val_str(Hybrid_type *val, String *to, uint8 decimals) const +{ + to->set(val->real, decimals, &my_charset_bin); + return to; +} + +/* Hybrid_type_traits_decimal */ + +const Hybrid_type_traits_decimal *Hybrid_type_traits_decimal::instance() +{ + static const Hybrid_type_traits_decimal decimal_traits; + return &decimal_traits; +} + + +void +Hybrid_type_traits_decimal::fix_length_and_dec(Item *item, Item *arg) const +{ + item->decimals= arg->decimals; + item->max_length= min(arg->max_length + DECIMAL_LONGLONG_DIGITS, + DECIMAL_MAX_LENGTH); +} + + +void Hybrid_type_traits_decimal::set_zero(Hybrid_type *val) const +{ + my_decimal_set_zero(&val->dec_buf[0]); + val->used_dec_buf_no= 0; +} + + +void Hybrid_type_traits_decimal::add(Hybrid_type *val, Field *f) const +{ + my_decimal_add(E_DEC_FATAL_ERROR, + &val->dec_buf[val->used_dec_buf_no ^ 1], + &val->dec_buf[val->used_dec_buf_no], + f->val_decimal(&val->dec_buf[2])); + val->used_dec_buf_no^= 1; +} + + +void Hybrid_type_traits_decimal::div(Hybrid_type *val, ulonglong u) const +{ + int2my_decimal(E_DEC_FATAL_ERROR, u, TRUE, &val->dec_buf[2]); + /* XXX: what is '4' for scale? */ + my_decimal_div(E_DEC_FATAL_ERROR, + &val->dec_buf[val->used_dec_buf_no ^ 1], + &val->dec_buf[val->used_dec_buf_no], + &val->dec_buf[2], 4); + val->used_dec_buf_no^= 1; +} + + +longlong +Hybrid_type_traits_decimal::val_int(Hybrid_type *val, bool unsigned_flag) const +{ + longlong result; + my_decimal2int(E_DEC_FATAL_ERROR, &val->dec_buf[val->used_dec_buf_no], + unsigned_flag, &result); + return result; +} + + +double +Hybrid_type_traits_decimal::val_real(Hybrid_type *val) const +{ + my_decimal2double(E_DEC_FATAL_ERROR, &val->dec_buf[val->used_dec_buf_no], + &val->real); + return val->real; +} + + +String * +Hybrid_type_traits_decimal::val_str(Hybrid_type *val, String *to, + uint8 decimals) const +{ + my_decimal_round(E_DEC_FATAL_ERROR, &val->dec_buf[val->used_dec_buf_no], + decimals, FALSE, &val->dec_buf[2]); + my_decimal2string(E_DEC_FATAL_ERROR, &val->dec_buf[2], 0, 0, 0, to); + return to; +} + +/* Hybrid_type_traits_integer */ + +const Hybrid_type_traits_integer *Hybrid_type_traits_integer::instance() +{ + static const Hybrid_type_traits_integer integer_traits; + return &integer_traits; +} + +void +Hybrid_type_traits_integer::fix_length_and_dec(Item *item, Item *arg) const +{ + item->decimals= 0; + item->max_length= 21; + item->unsigned_flag= 0; +} + /***************************************************************************** ** Item functions *****************************************************************************/ @@ -44,9 +169,130 @@ item_user_lock_init(); } + +/* +TODO: make this functions class dependent +*/ + +bool Item::val_bool() +{ + switch(result_type()) { + case INT_RESULT: + return val_int() != 0; + case DECIMAL_RESULT: + { + my_decimal decimal_value; + my_decimal *val= val_decimal(&decimal_value); + if (val) + return !my_decimal_is_zero(val); + return 0; + } + case REAL_RESULT: + case STRING_RESULT: + return val_real() != 0.0; + case ROW_RESULT: + default: + DBUG_ASSERT(0); + return 0; // Wrong (but safe) + } +} + + +String *Item::val_string_from_real(String *str) +{ + double nr= val_real(); + if (null_value) + return 0; /* purecov: inspected */ + str->set(nr,decimals, &my_charset_bin); + return str; +} + + +String *Item::val_string_from_int(String *str) +{ + longlong nr= val_int(); + if (null_value) + return 0; + if (unsigned_flag) + str->set((ulonglong) nr, &my_charset_bin); + else + str->set(nr, &my_charset_bin); + return str; +} + + +String *Item::val_string_from_decimal(String *str) +{ + my_decimal dec_buf, *dec= val_decimal(&dec_buf); + if (null_value) + return 0; + my_decimal_round(E_DEC_FATAL_ERROR, dec, decimals, FALSE, &dec_buf); + my_decimal2string(E_DEC_FATAL_ERROR, &dec_buf, 0, 0, 0, str); + return str; +} + + +my_decimal *Item::val_decimal_from_real(my_decimal *decimal_value) +{ + double nr= val_real(); + if (null_value) + return 0; + double2my_decimal(E_DEC_FATAL_ERROR, nr, decimal_value); + return (decimal_value); +} + + +my_decimal *Item::val_decimal_from_int(my_decimal *decimal_value) +{ + longlong nr= val_int(); + if (null_value) + return 0; + int2my_decimal(E_DEC_FATAL_ERROR, nr, unsigned_flag, decimal_value); + return decimal_value; +} + + +my_decimal *Item::val_decimal_from_string(my_decimal *decimal_value) +{ + String *res; + char *end_ptr; + if (!(res= val_str(&str_value))) + return 0; // NULL or EOM + + end_ptr= (char*) res->ptr()+ res->length(); + str2my_decimal(E_DEC_FATAL_ERROR, res->ptr(), res->length(), res->charset(), + decimal_value); + return decimal_value; +} + + +double Item::val_real_from_decimal() +{ + /* Note that fix_fields may not be called for Item_avg_field items */ + double result; + my_decimal value_buff, *dec_val= val_decimal(&value_buff); + if (null_value) + return 0.0; + my_decimal2double(E_DEC_FATAL_ERROR, dec_val, &result); + return result; +} + + +longlong Item::val_int_from_decimal() +{ + /* Note that fix_fields may not be called for Item_avg_field items */ + longlong result; + my_decimal value, *dec_val= val_decimal(&value); + if (null_value) + return 0; + my_decimal2int(E_DEC_FATAL_ERROR, dec_val, unsigned_flag, &result); + return result; +} + + Item::Item(): name(0), orig_name(0), name_length(0), fixed(0), - collation(default_charset(), DERIVATION_COERCIBLE) + collation(&my_charset_bin, DERIVATION_COERCIBLE) { marker= 0; maybe_null=null_value=with_sum_func=unsigned_flag=0; @@ -72,7 +318,7 @@ } /* - Constructor used by Item_field, Item_*_ref & agregate (sum) functions. + Constructor used by Item_field, Item_*_ref & aggregate (sum) functions. Used for duplicating lists in processing queries with temporary tables */ @@ -148,7 +394,7 @@ void Item::rename(char *new_name) { /* - we can compare pointers to names here, bacause if name was not changed, + we can compare pointers to names here, because if name was not changed, pointer will be same */ if (!orig_name && new_name != name) @@ -340,6 +586,8 @@ return NULL; } conv->str_value.copy(); + /* Ensure that no one is going to change the result string */ + conv->str_value.mark_as_const(); return conv; } @@ -411,6 +659,55 @@ } +double Item_splocal::val_real() +{ + DBUG_ASSERT(fixed); + Item *it= this_item(); + double ret= it->val_real(); + Item::null_value= it->null_value; + return ret; +} + + +longlong Item_splocal::val_int() +{ + DBUG_ASSERT(fixed); + Item *it= this_item(); + longlong ret= it->val_int(); + Item::null_value= it->null_value; + return ret; +} + + +String *Item_splocal::val_str(String *sp) +{ + DBUG_ASSERT(fixed); + Item *it= this_item(); + String *ret= it->val_str(sp); + Item::null_value= it->null_value; + return ret; +} + + +my_decimal *Item_splocal::val_decimal(my_decimal *decimal_value) +{ + DBUG_ASSERT(fixed); + Item *it= this_item(); + my_decimal value, *val= it->val_decimal(&value); + Item::null_value= it->null_value; + return val; +} + + +bool Item_splocal::is_null() +{ + Item *it= this_item(); + bool ret= it->is_null(); + Item::null_value= it->null_value; + return ret; +} + + Item * Item_splocal::this_item() { @@ -438,12 +735,90 @@ } +bool Item_splocal::fix_fields(THD *, struct st_table_list *, Item **) +{ + Item *it= this_item(); + DBUG_ASSERT(it->fixed); + max_length= it->max_length; + decimals= it->decimals; + fixed= 1; + return FALSE; +} + + +void Item_splocal::cleanup() +{ + fixed= 0; +} + + +void Item_splocal::print(String *str) +{ + str->reserve(m_name.length+8); + str->append(m_name.str, m_name.length); + str->append('@'); + str->qs_append(m_offset); +} + + + +/* + Move SUM items out from item tree and replace with reference + + SYNOPSIS + split_sum_func2() + thd Thread handler + ref_pointer_array Pointer to array of reference fields + fields All fields in select + ref Pointer to item + + NOTES + This is from split_sum_func2() for items that should be split + + All found SUM items are added FIRST in the fields list and + we replace the item with a reference. + + thd->fatal_error() may be called if we are out of memory +*/ + + +void Item::split_sum_func2(THD *thd, Item **ref_pointer_array, + List<Item> &fields, Item **ref) +{ + if (type() != SUM_FUNC_ITEM && with_sum_func) + { + /* Will split complicated items and ignore simple ones */ + split_sum_func(thd, ref_pointer_array, fields); + } + else if ((type() == SUM_FUNC_ITEM || + (used_tables() & ~PARAM_TABLE_BIT)) && + type() != REF_ITEM) + { + /* + Replace item with a reference so that we can easily calculate + it (in case of sum functions) or copy it (in case of fields) + + The test above is to ensure we don't do a reference for things + that are constants (PARAM_TABLE_BIT is in effect a constant) + or already referenced (for example an item in HAVING) + */ + uint el= fields.elements; + Item *new_item; + ref_pointer_array[el]= this; + if (!(new_item= new Item_ref(ref_pointer_array + el, 0, name))) + return; // fatal_error is set + fields.push_front(this); + ref_pointer_array[el]= this; + thd->change_item_tree(ref, new_item); + } +} + /* Aggregate two collations together taking into account their coercibility (aka derivation): - 0 == DERIVATION_EXPLICIT - an explicitely written COLLATE clause + 0 == DERIVATION_EXPLICIT - an explicitly written COLLATE clause 1 == DERIVATION_NONE - a mix of two different collations 2 == DERIVATION_IMPLICIT - a column 3 == DERIVATION_COERCIBLE - a string constant @@ -476,13 +851,12 @@ */ bool DTCollation::aggregate(DTCollation &dt, uint flags) { - nagg++; if (!my_charset_same(collation, dt.collation)) { /* We do allow to use binary strings (like BLOBS) together with character strings. - Binaries have more precedance than a character + Binaries have more precedence than a character string of the same derivation. */ if (collation == &my_charset_bin) @@ -492,7 +866,6 @@ else { set(dt); - strong= nagg; } } else if (dt.collation == &my_charset_bin) @@ -500,7 +873,6 @@ if (dt.derivation <= derivation) { set(dt); - strong= nagg; } else ; // Do nothing @@ -516,20 +888,18 @@ dt.collation->state & MY_CS_UNICODE) { set(dt); - strong= nagg; } else if ((flags & MY_COLL_ALLOW_COERCIBLE_CONV) && derivation < dt.derivation && - dt.derivation >= DERIVATION_COERCIBLE) + dt.derivation >= DERIVATION_SYSCONST) { // Do nothing; } else if ((flags & MY_COLL_ALLOW_COERCIBLE_CONV) && dt.derivation < derivation && - derivation >= DERIVATION_COERCIBLE) + derivation >= DERIVATION_SYSCONST) { set(dt); - strong= nagg; } else { @@ -545,7 +915,6 @@ else if (dt.derivation < derivation) { set(dt); - strong= nagg; } else { @@ -634,8 +1003,8 @@ { field=result_field=field_par; // for easy coding with fields maybe_null=field->maybe_null(); - max_length=field_par->field_length; decimals= field->decimals(); + max_length= field_par->representation_length(); table_name= *field_par->table_name; field_name= field_par->field_name; db_name= field_par->table->s->db; @@ -743,6 +1112,7 @@ return field->val_str(str,&str_value); } + double Item_field::val_real() { DBUG_ASSERT(fixed == 1); @@ -751,6 +1121,7 @@ return field->val_real(); } + longlong Item_field::val_int() { DBUG_ASSERT(fixed == 1); @@ -760,6 +1131,14 @@ } +my_decimal *Item_field::val_decimal(my_decimal *decimal_value) +{ + if ((null_value= field->is_null())) + return 0; + return field->val_decimal(decimal_value); +} + + String *Item_field::str_result(String *str) { if ((null_value=result_field->is_null())) @@ -814,6 +1193,40 @@ } +my_decimal *Item_field::val_decimal_result(my_decimal *decimal_value) +{ + if ((null_value= result_field->is_null())) + return 0; + return result_field->val_decimal(decimal_value); +} + + +bool Item_field::val_bool_result() +{ + if ((null_value= result_field->is_null())) + return FALSE; + switch (result_field->result_type()) { + case INT_RESULT: + return result_field->val_int() != 0; + case DECIMAL_RESULT: + { + my_decimal decimal_value; + my_decimal *val= result_field->val_decimal(&decimal_value); + if (val) + return !my_decimal_is_zero(val); + return 0; + } + case REAL_RESULT: + case STRING_RESULT: + return result_field->val_real() != 0.0; + case ROW_RESULT: + default: + DBUG_ASSERT(0); + return 0; // Shut up compiler + } +} + + bool Item_field::eq(const Item *item, bool binary_cmp) const { if (item->type() != FIELD_ITEM) @@ -861,8 +1274,9 @@ /* - Create an item from a string we KNOW points to a valid longlong/ulonglong - end \0 terminated number string + Create an item from a string we KNOW points to a valid longlong + end \0 terminated number string. + This is always 'signed'. Unsigned values are created with Item_uint() */ Item_int::Item_int(const char *str_arg, uint length) @@ -876,6 +1290,12 @@ } +my_decimal *Item_int::val_decimal(my_decimal *decimal_value) +{ + int2my_decimal(E_DEC_FATAL_ERROR, value, unsigned_flag, decimal_value); + return decimal_value; +} + String *Item_int::val_str(String *str) { // following assert is redundant, because fixed=1 assigned in constructor @@ -916,7 +1336,98 @@ } -String *Item_real::val_str(String *str) +Item_decimal::Item_decimal(const char *str_arg, uint length, + CHARSET_INFO *charset) +{ + str2my_decimal(E_DEC_FATAL_ERROR, str_arg, length, charset, &decimal_value); + name= (char*) str_arg; + decimals= (uint8) decimal_value.frac; + max_length= my_decimal_max_length(&decimal_value); + fixed= 1; + unsigned_flag= !decimal_value.sign(); +} + +Item_decimal::Item_decimal(longlong val, bool unsig) +{ + int2my_decimal(E_DEC_FATAL_ERROR, val, unsig, &decimal_value); + decimals= (uint8) decimal_value.frac; + max_length= my_decimal_max_length(&decimal_value); + fixed= 1; + unsigned_flag= !decimal_value.sign(); +} + + +Item_decimal::Item_decimal(double val, int precision, int scale) +{ + double2my_decimal(E_DEC_FATAL_ERROR, val, &decimal_value); + decimals= (uint8) decimal_value.frac; + max_length= my_decimal_max_length(&decimal_value); + fixed= 1; + unsigned_flag= !decimal_value.sign(); +} + + +Item_decimal::Item_decimal(const char *str, const my_decimal *val_arg, + uint decimal_par, uint length) +{ + my_decimal2decimal(val_arg, &decimal_value); + name= (char*) str; + decimals= (uint8) decimal_par; + max_length= length; + fixed= 1; + unsigned_flag= !decimal_value.sign(); +} + + +Item_decimal::Item_decimal(my_decimal *value_par) +{ + my_decimal2decimal(value_par, &decimal_value); + decimals= (uint8) decimal_value.frac; + max_length= my_decimal_max_length(value_par); + fixed= 1; + unsigned_flag= !decimal_value.sign(); +} + + +Item_decimal::Item_decimal(const char *bin, int precision, int scale) +{ + binary2my_decimal(E_DEC_FATAL_ERROR, bin, &decimal_value, precision, scale); + decimals= (uint8) decimal_value.frac; + max_length= my_decimal_max_length(&decimal_value); + fixed= 1; + unsigned_flag= !decimal_value.sign(); +} + + +longlong Item_decimal::val_int() +{ + longlong result; + my_decimal2int(E_DEC_FATAL_ERROR, &decimal_value, unsigned_flag, &result); + return result; +} + +double Item_decimal::val_real() +{ + double result; + my_decimal2double(E_DEC_FATAL_ERROR, &decimal_value, &result); + return result; +} + +String *Item_decimal::val_str(String *result) +{ + result->set_charset(&my_charset_bin); + my_decimal2string(E_DEC_FATAL_ERROR, &decimal_value, 0, 0, 0, result); + return result; +} + +void Item_decimal::print(String *str) +{ + my_decimal2string(E_DEC_FATAL_ERROR, &decimal_value, 0, 0, 0, &str_value); + str->append(str_value); +} + + +String *Item_float::val_str(String *str) { // following assert is redundant, because fixed=1 assigned in constructor DBUG_ASSERT(fixed == 1); @@ -925,6 +1436,15 @@ } +my_decimal *Item_float::val_decimal(my_decimal *decimal_value) +{ + // following assert is redundant, because fixed=1 assigned in constructor + DBUG_ASSERT(fixed == 1); + double2my_decimal(E_DEC_FATAL_ERROR, value, decimal_value); + return (decimal_value); +} + + void Item_string::print(String *str) { str->append('_'); @@ -934,8 +1454,78 @@ str->append('\''); } + +inline bool check_if_only_end_space(CHARSET_INFO *cs, char *str, char *end) +{ + return str+ cs->cset->scan(cs, str, end, MY_SEQ_SPACES) == end; +} + + +double Item_string::val_real() +{ + DBUG_ASSERT(fixed == 1); + int error; + char *end, *org_end; + double tmp; + CHARSET_INFO *cs= str_value.charset(); + + org_end= (char*) str_value.ptr() + str_value.length(); + tmp= my_strntod(cs, (char*) str_value.ptr(), str_value.length(), &end, + &error); + if (error || (end != org_end && !check_if_only_end_space(cs, end, org_end))) + { + /* + We can use str_value.ptr() here as Item_string is gurantee to put an + end \0 here. + */ + push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_TRUNCATED_WRONG_VALUE, + ER(ER_TRUNCATED_WRONG_VALUE), "DOUBLE", + str_value.ptr()); + } + return tmp; +} + + +longlong Item_string::val_int() +{ + DBUG_ASSERT(fixed == 1); + int err; + longlong tmp; + char *end= (char*) str_value.ptr()+ str_value.length(); + char *org_end= end; + CHARSET_INFO *cs= str_value.charset(); + + tmp= (*(cs->cset->strtoll10))(cs, str_value.ptr(), &end, &err); + /* + TODO: Give error if we wanted a signed integer and we got an unsigned + one + */ + if (err > 0 || + (end != org_end && !check_if_only_end_space(cs, end, org_end))) + { + push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_TRUNCATED_WRONG_VALUE, + ER(ER_TRUNCATED_WRONG_VALUE), "INTEGER", + str_value.ptr()); + } + return tmp; +} + + +my_decimal *Item_string::val_decimal(my_decimal *decimal_value) +{ + /* following assert is redundant, because fixed=1 assigned in constructor */ + DBUG_ASSERT(fixed == 1); + string2my_decimal(E_DEC_FATAL_ERROR, &str_value, decimal_value); + return (decimal_value); +} + + bool Item_null::eq(const Item *item, bool binary_cmp) const { return item->type() == type(); } + + double Item_null::val_real() { // following assert is redundant, because fixed=1 assigned in constructor @@ -959,6 +1549,11 @@ return 0; } +my_decimal *Item_null::val_decimal(my_decimal *decimal_value) +{ + return 0; +} + Item *Item_null::safe_charset_converter(CHARSET_INFO *tocs) { @@ -1005,7 +1600,6 @@ { DBUG_ENTER("Item_param::set_null"); /* These are cleared after each execution by reset() method */ - max_length= 0; null_value= 1; /* Because of NULL and string values we need to set max_length for each new @@ -1042,6 +1636,35 @@ /* + Set decimal parameter value from string. + + SYNOPSIS + set_decimal() + str - character string + length - string length + + NOTE + as we use character strings to send decimal values in + binary protocol, we use str2my_decimal to convert it to + internal decimal value. +*/ + +void Item_param::set_decimal(const char *str, ulong length) +{ + char *end; + DBUG_ENTER("Item_param::set_decimal"); + + end= (char*) str+length; + str2my_decimal(E_DEC_FATAL_ERROR, str, &decimal_value, &end); + state= DECIMAL_VALUE; + decimals= decimal_value.frac; + max_length= decimal_value.intg + decimals + 2; + maybe_null= 0; + DBUG_VOID_RETURN; +} + + +/* Set parameter value from TIME value. SYNOPSIS @@ -1094,6 +1717,7 @@ &dummy_errors)) DBUG_RETURN(TRUE); state= STRING_VALUE; + max_length= length; maybe_null= 0; /* max_length and decimals are set after charset conversion */ /* sic: str may be not null-terminated, don't add DBUG_PRINT here */ @@ -1133,7 +1757,7 @@ RETURN 0 OK - 1 Out of memort + 1 Out of memory */ bool Item_param::set_from_user_var(THD *thd, const user_var_entry *entry) @@ -1179,6 +1803,15 @@ DBUG_RETURN(1); break; } + case DECIMAL_RESULT: + { + const my_decimal *ent_value= (const my_decimal *)entry->value; + my_decimal2decimal(ent_value, &decimal_value); + state= DECIMAL_VALUE; + decimals= ent_value->frac; + max_length= ent_value->intg + decimals + 2; + break; + } default: DBUG_ASSERT(0); set_null(); @@ -1210,7 +1843,7 @@ str_value.length(0); str_value_ptr.length(0); /* - We must prevent all charset conversions untill data has been written + We must prevent all charset conversions until data has been written to the binary log. */ str_value.set_charset(&my_charset_bin); @@ -1238,6 +1871,8 @@ return field->store(value.integer); case REAL_VALUE: return field->store(value.real); + case DECIMAL_VALUE: + return field->store_decimal(&decimal_value); case TIME_VALUE: field->store_time(&value.time, value.time.time_type); return 0; @@ -1288,13 +1923,20 @@ return value.real; case INT_VALUE: return (double) value.integer; + case DECIMAL_VALUE: + { + double result; + my_decimal2double(E_DEC_FATAL_ERROR, &decimal_value, &result); + return result; + } case STRING_VALUE: case LONG_DATA_VALUE: - { - int dummy_err; - return my_strntod(str_value.charset(), (char*) str_value.ptr(), - str_value.length(), (char**) 0, &dummy_err); - } + { + int dummy_err; + char *end_not_used; + return my_strntod(str_value.charset(), (char*) str_value.ptr(), + str_value.length(), &end_not_used, &dummy_err); + } case TIME_VALUE: /* This works for example when user says SELECT ?+0.0 and supplies @@ -1317,6 +1959,12 @@ return (longlong) (value.real + (value.real > 0 ? 0.5 : -0.5)); case INT_VALUE: return value.integer; + case DECIMAL_VALUE: + { + longlong i; + my_decimal2int(E_DEC_FATAL_ERROR, &decimal_value, unsigned_flag, &i); + return i; + } case STRING_VALUE: case LONG_DATA_VALUE: { @@ -1335,6 +1983,36 @@ } +my_decimal *Item_param::val_decimal(my_decimal *dec) +{ + switch (state) { + case DECIMAL_VALUE: + return &decimal_value; + case REAL_VALUE: + double2my_decimal(E_DEC_FATAL_ERROR, value.real, dec); + return dec; + case INT_VALUE: + int2my_decimal(E_DEC_FATAL_ERROR, value.integer, unsigned_flag, dec); + return dec; + case STRING_VALUE: + case LONG_DATA_VALUE: + string2my_decimal(E_DEC_FATAL_ERROR, &str_value, dec); + return dec; + case TIME_VALUE: + { + longlong i= (longlong) TIME_to_ulonglong(&value.time); + int2my_decimal(E_DEC_FATAL_ERROR, i, 0, dec); + return dec; + } + case NULL_VALUE: + return 0; + default: + DBUG_ASSERT(0); + } + return 0; +} + + String *Item_param::val_str(String* str) { switch (state) { @@ -1347,6 +2025,11 @@ case INT_VALUE: str->set(value.integer, &my_charset_bin); return str; + case DECIMAL_VALUE: + if (my_decimal2string(E_DEC_FATAL_ERROR, &decimal_value, + 0, 0, 0, str) <= 1) + return str; + return NULL; case TIME_VALUE: { if (str->reserve(MAX_DATE_STRING_REP_LENGTH)) @@ -1379,6 +2062,11 @@ case REAL_VALUE: str->set(value.real, NOT_FIXED_DEC, &my_charset_bin); break; + case DECIMAL_VALUE: + if (my_decimal2string(E_DEC_FATAL_ERROR, &decimal_value, + 0, 0, 0, str) > 1) + return &my_null_string; + break; case TIME_VALUE: { char *buf, *ptr; @@ -1410,7 +2098,7 @@ buf= str->c_ptr_quick(); ptr= buf; *ptr++= '\''; - ptr+= escape_string_for_mysql(str_value.charset(), ptr, + ptr+= escape_string_for_mysql(str_value.charset(), ptr, 0, str_value.ptr(), str_value.length()); *ptr++= '\''; str->length(ptr - buf); @@ -1464,6 +2152,34 @@ return rc; } +bool Item_param::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref) +{ + DBUG_ASSERT(fixed == 0); + SELECT_LEX *cursel= (SELECT_LEX *) thd->lex->current_select; + + /* + Parameters in a subselect should mark the subselect as not constant + during prepare + */ + if (state == NO_VALUE) + { + /* + SELECT_LEX_UNIT::item set only for subqueries, so test of it presence + can be barrier to stop before derived table SELECT or very outer SELECT + */ + for(; + cursel->master_unit()->item; + cursel= cursel->outer_select()) + { + Item_subselect *subselect_item= cursel->master_unit()->item; + subselect_item->used_tables_cache|= OUTER_REF_TABLE_BIT; + subselect_item->const_item_cache= 0; + } + } + fixed= 1; + return 0; +} + void Item_param::print(String *str) { @@ -1473,7 +2189,7 @@ } else { - char buffer[80]; + char buffer[STRING_BUFFER_USUAL_SIZE]; String tmp(buffer, sizeof(buffer), &my_charset_bin); const String *res; res= query_val_str(&tmp); @@ -1504,6 +2220,17 @@ } +my_decimal *Item_copy_string::val_decimal(my_decimal *decimal_value) +{ + // Item_copy_string is used without fix_fields call + if (null_value) + return 0; + string2my_decimal(E_DEC_FATAL_ERROR, &str_value, decimal_value); + return (decimal_value); +} + + + int Item_copy_string::save_in_field(Field *field, bool no_conversions) { if (null_value) @@ -1547,6 +2274,24 @@ } +my_decimal *Item_ref_null_helper::val_decimal(my_decimal *decimal_value) +{ + DBUG_ASSERT(fixed == 1); + my_decimal *val= (*ref)->val_decimal_result(decimal_value); + owner->was_null|= null_value= (*ref)->null_value; + return val; +} + + +bool Item_ref_null_helper::val_bool() +{ + DBUG_ASSERT(fixed == 1); + bool val= (*ref)->val_bool_result(); + owner->was_null|= null_value= (*ref)->null_value; + return val; +} + + String* Item_ref_null_helper::val_str(String* s) { DBUG_ASSERT(fixed == 1); @@ -1563,23 +2308,29 @@ /* - Mark item and SELECT_LEXs as dependent if it is not outer resolving + Mark item and SELECT_LEXs as dependent if item was resolved in outer SELECT SYNOPSIS mark_as_dependent() thd - thread handler last - select from which current item depend current - current select - item - item which should be marked + resolved_item - item which was resolved in outer SELECT(for warning) + mark_item - item which should be marked (can be differ in case of + substitution) */ static void mark_as_dependent(THD *thd, SELECT_LEX *last, SELECT_LEX *current, - Item_ident *item) + Item_ident *resolved_item, + Item_ident *mark_item) { - const char *db_name= item->db_name ? item->db_name : ""; - const char *table_name= item->table_name ? item->table_name : ""; + const char *db_name= (resolved_item->db_name ? + resolved_item->db_name : ""); + const char *table_name= (resolved_item->table_name ? + resolved_item->table_name : ""); /* store pointer on SELECT_LEX from which item is dependent */ - item->depended_from= last; + if (mark_item) + mark_item->depended_from= last; current->mark_as_dependent(last); if (thd->lex->describe & DESCRIBE_EXTENDED) { @@ -1587,7 +2338,7 @@ sprintf(warn_buff, ER(ER_WARN_FIELD_RESOLVED), db_name, (db_name[0] ? "." : ""), table_name, (table_name [0] ? "." : ""), - item->field_name, + resolved_item->field_name, current->select_number, last->select_number); push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, ER_WARN_FIELD_RESOLVED, warn_buff); @@ -1635,7 +2386,7 @@ else return NULL; - DBUG_ASSERT(field_name); + DBUG_ASSERT(field_name != 0); for (ORDER *cur_group= group_list ; cur_group ; cur_group= cur_group->next) { @@ -1644,7 +2395,7 @@ cur_field= (Item_field*) *cur_group->item; cur_match_degree= 0; - DBUG_ASSERT(cur_field->field_name); + DBUG_ASSERT(cur_field->field_name != 0); if (!my_strcasecmp(system_charset_info, cur_field->field_name, field_name)) @@ -1773,13 +2524,14 @@ { if (select_ref != not_found_item && !ambiguous_fields) { - DBUG_ASSERT(*select_ref); - if (! (*select_ref)->fixed) + DBUG_ASSERT(*select_ref != 0); + if (!select->ref_pointer_array[counter]) { my_error(ER_ILLEGAL_REFERENCE, MYF(0), ref->name, "forward reference in item list"); return NULL; } + DBUG_ASSERT((*select_ref)->fixed); return (select->ref_pointer_array + counter); } if (group_by_ref) @@ -1852,6 +2604,11 @@ { bool upward_lookup= FALSE; Field *from_field= (Field *)not_found_field; + /* + In case of view, find_field_in_tables() write pointer to view field + expression to 'reference', i.e. it substitute that expression instead + of this Item_field + */ if ((from_field= find_field_in_tables(thd, this, tables, reference, IGNORE_EXCEPT_NON_UNIQUE, !any_privileges)) == @@ -1897,6 +2654,10 @@ Check table fields only if the subquery is used somewhere out of HAVING, or the outer SELECT does not use grouping (i.e. tables are accessible). + + In case of view, find_field_in_tables() write pointer to view + field expression to 'reference', i.e. it substitute that + expression instead of this Item_field */ if ((place != IN_HAVING || (outer_sel->with_sum_func == 0 && @@ -1916,10 +2677,21 @@ } else { + Item::Type type= (*reference)->type(); prev_subselect_item->used_tables_cache|= (*reference)->used_tables(); prev_subselect_item->const_item_cache&= (*reference)->const_item(); + mark_as_dependent(thd, last, current_sel, this, + ((type == REF_ITEM || type == FIELD_ITEM) ? + (Item_ident*) (*reference) : + 0)); + /* + view reference found, we substituted it instead of this + Item (find_field_in_tables do it by assigning new value to + *reference), so can quit + */ + return FALSE; } } break; @@ -1929,7 +2701,7 @@ if (outer_sel->resolve_mode == SELECT_LEX::SELECT_MODE) { if (!(ref= resolve_ref_in_select_and_group(thd, this, outer_sel))) - return TRUE; /* Some error occured (e.g. ambigous names). */ + return TRUE; /* Some error occurred (e.g. ambiguous names). */ if (ref != not_found_item) { DBUG_ASSERT(*ref && (*ref)->fixed); @@ -1949,7 +2721,7 @@ } } - DBUG_ASSERT(ref); + DBUG_ASSERT(ref != 0); if (!from_field) return TRUE; if (ref == not_found_item && from_field == not_found_field) @@ -1993,15 +2765,16 @@ rf is Item_ref => never substitute other items (in this case) during fix_fields() => we can use rf after fix_fields() */ - if (rf->fix_fields(thd, tables, reference) || rf->check_cols(1)) + DBUG_ASSERT(!rf->fixed); // Assured by Item_ref() + if (rf->fix_fields(thd, tables, reference) || rf->check_cols(1)) return TRUE; - mark_as_dependent(thd, last, current_sel, rf); + mark_as_dependent(thd, last, current_sel, this, rf); return FALSE; } else { - mark_as_dependent(thd, last, current_sel, this); + mark_as_dependent(thd, last, current_sel, this, this); if (last->having_fix_field) { Item_ref *rf; @@ -2014,7 +2787,8 @@ rf is Item_ref => never substitute other items (in this case) during fix_fields() => we can use rf after fix_fields() */ - return rf->fix_fields(thd, tables, reference) || rf->check_cols(1); + DBUG_ASSERT(!rf->fixed); // Assured by Item_ref() + return (rf->fix_fields(thd, tables, reference) || rf->check_cols(1)); } } } @@ -2033,8 +2807,10 @@ Also we suppose that view can't be changed during PS/SP life. */ - if (from_field != view_ref_found) - set_field(from_field); + if (from_field == view_ref_found) + return FALSE; + + set_field(from_field); } else if (thd->set_query_id && field->query_id != thd->query_id) { @@ -2087,7 +2863,7 @@ Item_ident::cleanup(); /* Even if this object was created by direct link to field in setup_wild() - it will be linked correctly next tyme by name of field and table alias. + it will be linked correctly next time by name of field and table alias. I.e. we can drop 'field'. */ field= result_field= 0; @@ -2261,9 +3037,16 @@ enum_field_types Item::field_type() const { - return ((result_type() == STRING_RESULT) ? MYSQL_TYPE_VARCHAR : - (result_type() == INT_RESULT) ? FIELD_TYPE_LONGLONG : - FIELD_TYPE_DOUBLE); + switch (result_type()) { + case STRING_RESULT: return MYSQL_TYPE_VARCHAR; + case INT_RESULT: return FIELD_TYPE_LONGLONG; + case DECIMAL_RESULT: return FIELD_TYPE_NEWDECIMAL; + case REAL_RESULT: return FIELD_TYPE_DOUBLE; + case ROW_RESULT: + default: + DBUG_ASSERT(0); + return MYSQL_TYPE_VARCHAR; + } } @@ -2316,6 +3099,11 @@ case MYSQL_TYPE_DECIMAL: return new Field_decimal((char*) 0, max_length, null_ptr, 0, Field::NONE, name, table, decimals, 0, unsigned_flag); + case MYSQL_TYPE_NEWDECIMAL: + return new Field_new_decimal((char*) 0, max_length - (decimals?1:0), + null_ptr, 0, + Field::NONE, name, table, decimals, 0, + unsigned_flag); case MYSQL_TYPE_TINY: return new Field_tiny((char*) 0, max_length, null_ptr, 0, Field::NONE, name, table, 0, unsigned_flag); @@ -2339,10 +3127,10 @@ case MYSQL_TYPE_NULL: return new Field_null((char*) 0, max_length, Field::NONE, name, table, &my_charset_bin); - case MYSQL_TYPE_NEWDATE: case MYSQL_TYPE_INT24: return new Field_medium((char*) 0, max_length, null_ptr, 0, Field::NONE, name, table, 0, unsigned_flag); + case MYSQL_TYPE_NEWDATE: case MYSQL_TYPE_DATE: return new Field_date(maybe_null, name, table, &my_charset_bin); case MYSQL_TYPE_TIME: @@ -2379,7 +3167,7 @@ void Item_field::make_field(Send_field *tmp_field) { field->make_field(tmp_field); - DBUG_ASSERT(tmp_field->table_name); + DBUG_ASSERT(tmp_field->table_name != 0); if (name) tmp_field->col_name=name; // Use user supplied name } @@ -2451,7 +3239,7 @@ field Field where we want to store NULL RETURN VALUES - 0 ok + 0 OK 1 Field doesn't support NULL values */ @@ -2490,6 +3278,15 @@ field->set_notnull(); error=field->store(nr); } + else if (result_type() == DECIMAL_RESULT) + { + my_decimal decimal_value; + my_decimal *value= val_decimal(&decimal_value); + if (null_value) + return set_field_to_null(field); + field->set_notnull(); + error=field->store_decimal(value); + } else { longlong nr=val_int(); @@ -2531,9 +3328,41 @@ return field->store(nr); } + +int Item_decimal::save_in_field(Field *field, bool no_conversions) +{ + field->set_notnull(); + return field->store_decimal(&decimal_value); +} + + Item_num *Item_uint::neg() { - return new Item_real(name, - ((double) value), 0, max_length); + Item_decimal *item= new Item_decimal(value, 0); + return item->neg(); +} + + +static uint nr_of_decimals(const char *str, const char *end) +{ + const char *decimal_point; + + /* Find position for '.' */ + for (;;) + { + if (str == end) + return 0; + if (*str == 'e' || *str == 'E') + return NOT_FIXED_DEC; + if (*str++ == '.') + break; + } + decimal_point= str; + for (; my_isdigit(system_charset_info, *str) ; str++) + ; + if (*str == 'e' || *str == 'E') + return NOT_FIXED_DEC; + return (uint) (str - decimal_point); } @@ -2542,11 +3371,12 @@ value is not a true double value (overflow) */ -Item_real::Item_real(const char *str_arg, uint length) +Item_float::Item_float(const char *str_arg, uint length) { int error; - char *end; - value= my_strntod(&my_charset_bin, (char*) str_arg, length, &end, &error); + char *end_not_used; + value= my_strntod(&my_charset_bin, (char*) str_arg, length, &end_not_used, + &error); if (error) { /* @@ -2557,13 +3387,13 @@ my_error(ER_ILLEGAL_VALUE_FOR_TYPE, MYF(0), "double", (char*) str_arg); } presentation= name=(char*) str_arg; - decimals=(uint8) nr_of_decimals(str_arg); + decimals=(uint8) nr_of_decimals(str_arg, str_arg+length); max_length=length; fixed= 1; } -int Item_real::save_in_field(Field *field, bool no_conversions) +int Item_float::save_in_field(Field *field, bool no_conversions) { double nr= val_real(); if (null_value) @@ -2573,7 +3403,7 @@ } -void Item_real::print(String *str) +void Item_float::print(String *str) { if (presentation) { @@ -2636,6 +3466,16 @@ } +my_decimal *Item_hex_string::val_decimal(my_decimal *decimal_value) +{ + // following assert is redundant, because fixed=1 assigned in constructor + DBUG_ASSERT(fixed == 1); + ulonglong value= (ulonglong)val_int(); + int2my_decimal(E_DEC_FATAL_ERROR, value, TRUE, decimal_value); + return (decimal_value); +} + + int Item_hex_string::save_in_field(Field *field, bool no_conversions) { int error; @@ -2725,6 +3565,7 @@ case MYSQL_TYPE_VAR_STRING: case MYSQL_TYPE_VARCHAR: case MYSQL_TYPE_BIT: + case MYSQL_TYPE_NEWDECIMAL: { String *res; if ((res=val_str(buffer))) @@ -2815,6 +3656,20 @@ } +Item_ref::Item_ref(Item **item, const char *table_name_par, + const char *field_name_par) + :Item_ident(NullS, table_name_par, field_name_par), result_field(0), + ref(item) +{ + /* + This constructor used to create some internals references over fixed items + */ + DBUG_ASSERT(ref != 0); + if (*ref) + set_properties(); +} + + /* Resolve the name of a reference to a column reference. @@ -2865,6 +3720,9 @@ Item_field::fix_fields, here we first search the SELECT and GROUP BY clauses, and then we search the FROM clause. + POSTCONDITION + Item_ref::ref is 0 or points to a valid item + RETURN TRUE if error FALSE on success @@ -2876,165 +3734,165 @@ enum_parsing_place place= NO_MATTER; SELECT_LEX *current_sel= thd->lex->current_select; - if (!ref) + if (!ref || ref == not_found_item) { SELECT_LEX_UNIT *prev_unit= current_sel->master_unit(); SELECT_LEX *outer_sel= prev_unit->outer_select(); - ORDER *group_list= (ORDER*) current_sel->group_list.first; - bool ambiguous_fields= FALSE; - Item **group_by_ref= NULL; if (!(ref= resolve_ref_in_select_and_group(thd, this, current_sel))) - return TRUE; /* Some error occured (e.g. ambigous names). */ + return TRUE; /* Some error occurred (e.g. ambiguous names). */ if (ref == not_found_item) /* This reference was not resolved. */ { + TABLE_LIST *table_list; + Field *from_field; + SELECT_LEX *last; + ref= 0; + + if (!outer_sel || (current_sel->master_unit()->first_select()->linkage == + DERIVED_TABLE_TYPE)) + { + /* The current reference cannot be resolved in this query. */ + my_error(ER_BAD_FIELD_ERROR,MYF(0), + this->full_name(), current_thd->where); + return TRUE; + } /* If there is an outer select, and it is not a derived table (which do not support the use of outer fields for now), try to resolve this reference in the outer select(s). - + We treat each subselect as a separate namespace, so that different subselects may contain columns with the same names. The subselects are searched starting from the innermost. */ - if (outer_sel && (current_sel->master_unit()->first_select()->linkage != - DERIVED_TABLE_TYPE)) + from_field= (Field*) not_found_field; + last= 0; + + /* The following loop will always be excuted at least once */ + for ( ; outer_sel ; + outer_sel= (prev_unit= outer_sel->master_unit())->outer_select()) { - TABLE_LIST *table_list; - Field *from_field= (Field*) not_found_field; - SELECT_LEX *last= 0; + last= outer_sel; + Item_subselect *prev_subselect_item= prev_unit->item; - for ( ; outer_sel ; - outer_sel= (prev_unit= outer_sel->master_unit())->outer_select()) + /* Search in the SELECT and GROUP lists of the outer select. */ + if (outer_sel->resolve_mode == SELECT_LEX::SELECT_MODE) { - last= outer_sel; - Item_subselect *prev_subselect_item= prev_unit->item; - - /* Search in the SELECT and GROUP lists of the outer select. */ - if (outer_sel->resolve_mode == SELECT_LEX::SELECT_MODE) + if (!(ref= resolve_ref_in_select_and_group(thd, this, outer_sel))) + return TRUE; /* Some error occurred (e.g. ambiguous names). */ + if (ref != not_found_item) { - if (!(ref= resolve_ref_in_select_and_group(thd, this, outer_sel))) - return TRUE; /* Some error occured (e.g. ambigous names). */ - if (ref != not_found_item) - { - DBUG_ASSERT(*ref && (*ref)->fixed); - prev_subselect_item->used_tables_cache|= (*ref)->used_tables(); - prev_subselect_item->const_item_cache&= (*ref)->const_item(); - break; - } + DBUG_ASSERT(*ref && (*ref)->fixed); + prev_subselect_item->used_tables_cache|= (*ref)->used_tables(); + prev_subselect_item->const_item_cache&= (*ref)->const_item(); + break; } - - /* Search in the tables of the FROM clause of the outer select. */ - table_list= outer_sel->get_table_list(); - if (outer_sel->resolve_mode == SELECT_LEX::INSERT_MODE && table_list) - /* - It is a primary INSERT st_select_lex => do not resolve against the - first table. - */ - table_list= table_list->next_local; - - place= prev_subselect_item->parsing_place; /* - Check table fields only if the subquery is used somewhere out of - HAVING or the outer SELECT does not use grouping (i.e. tables are - accessible). - TODO: - Here we could first find the field anyway, and then test this - condition, so that we can give a better error message - - ER_WRONG_FIELD_WITH_GROUP, instead of the less informative - ER_BAD_FIELD_ERROR which we produce now. + Set ref to 0 to ensure that we get an error in case we replaced + this item with another item and still use this item in some + other place of the parse tree. */ - if ((place != IN_HAVING || - (!outer_sel->with_sum_func && - outer_sel->group_list.elements == 0))) - { - if ((from_field= find_field_in_tables(thd, this, table_list, - reference, - IGNORE_EXCEPT_NON_UNIQUE, - TRUE)) != - not_found_field) - { - if (from_field != view_ref_found) - { - prev_subselect_item->used_tables_cache|= from_field->table->map; - prev_subselect_item->const_item_cache= 0; - } - else - { - prev_subselect_item->used_tables_cache|= - (*reference)->used_tables(); - prev_subselect_item->const_item_cache&= - (*reference)->const_item(); - } - break; - } - } - - /* Reference is not found => depend on outer (or just error). */ - prev_subselect_item->used_tables_cache|= OUTER_REF_TABLE_BIT; - prev_subselect_item->const_item_cache= 0; - - if (outer_sel->master_unit()->first_select()->linkage == - DERIVED_TABLE_TYPE) - break; /* Do not consider derived tables. */ + ref= 0; } - DBUG_ASSERT(ref); - if (!from_field) - return TRUE; - if (ref == not_found_item && from_field == not_found_field) + /* Search in the tables of the FROM clause of the outer select. */ + table_list= outer_sel->get_table_list(); + if (outer_sel->resolve_mode == SELECT_LEX::INSERT_MODE && table_list) { - my_error(ER_BAD_FIELD_ERROR, MYF(0), - this->full_name(), current_thd->where); - ref= 0; // Safety - return TRUE; + /* + It is a primary INSERT st_select_lex => do not resolve against + the first table. + */ + table_list= table_list->next_local; } - if (from_field != not_found_field) + + place= prev_subselect_item->parsing_place; + /* + Check table fields only if the subquery is used somewhere out of + HAVING or the outer SELECT does not use grouping (i.e. tables are + accessible). + TODO: + Here we could first find the field anyway, and then test this + condition, so that we can give a better error message - + ER_WRONG_FIELD_WITH_GROUP, instead of the less informative + ER_BAD_FIELD_ERROR which we produce now. + */ + if ((place != IN_HAVING || + (!outer_sel->with_sum_func && + outer_sel->group_list.elements == 0))) { /* - Set ref to 0 as we are replacing this item with the found item and - this will ensure we get an error if this item would be used - elsewhere + In case of view, find_field_in_tables() write pointer to view + field expression to 'reference', i.e. it substitute that + expression instead of this Item_ref */ - ref= 0; // Safety - if (from_field != view_ref_found) + from_field= find_field_in_tables(thd, this, table_list, + reference, + IGNORE_EXCEPT_NON_UNIQUE, + TRUE); + if (! from_field) + return TRUE; + if (from_field == view_ref_found) { - Item_field* fld; - if (!(fld= new Item_field(from_field))) - return TRUE; - thd->change_item_tree(reference, fld); - mark_as_dependent(thd, last, thd->lex->current_select, fld); + Item::Type type= (*reference)->type(); + prev_subselect_item->used_tables_cache|= + (*reference)->used_tables(); + prev_subselect_item->const_item_cache&= + (*reference)->const_item(); + DBUG_ASSERT((*reference)->type() == REF_ITEM); + mark_as_dependent(thd, last, current_sel, this, + ((type == REF_ITEM || type == FIELD_ITEM) ? + (Item_ident*) (*reference) : + 0)); + /* + view reference found, we substituted it instead of this + Item, so can quit + */ return FALSE; } - /* - We can leave expression substituted from view for next PS/SP - re-execution (i.e. do not register this substitution for reverting - on cleanup() (register_item_tree_changing())), because this subtree - will be fix_field'ed during setup_tables()->setup_ancestor() - (i.e. before all other expressions of query, and references on - tables which do not present in query will not make problems. - - Also we suppose that view can't be changed during PS/SP life. - */ - } - else - { - /* Should be checked in resolve_ref_in_select_and_group(). */ - DBUG_ASSERT(*ref && (*ref)->fixed); - mark_as_dependent(thd, last, current_sel, this); + if (from_field != not_found_field) + { + prev_subselect_item->used_tables_cache|= from_field->table->map; + prev_subselect_item->const_item_cache= 0; + break; + } } + DBUG_ASSERT(from_field == not_found_field); + + /* Reference is not found => depend on outer (or just error). */ + prev_subselect_item->used_tables_cache|= OUTER_REF_TABLE_BIT; + prev_subselect_item->const_item_cache= 0; + + if (outer_sel->master_unit()->first_select()->linkage == + DERIVED_TABLE_TYPE) + break; /* Do not consider derived tables. */ } - else + + DBUG_ASSERT(from_field != 0 && from_field != view_ref_found); + if (from_field != not_found_field) { - /* The current reference cannot be resolved in this query. */ - my_error(ER_BAD_FIELD_ERROR,MYF(0), + Item_field* fld; + if (!(fld= new Item_field(from_field))) + return TRUE; + thd->change_item_tree(reference, fld); + mark_as_dependent(thd, last, thd->lex->current_select, this, fld); + return FALSE; + } + if (ref == 0) + { + /* The item was not a table field and not a reference */ + my_error(ER_BAD_FIELD_ERROR, MYF(0), this->full_name(), current_thd->where); return TRUE; } + /* Should be checked in resolve_ref_in_select_and_group(). */ + DBUG_ASSERT(*ref && (*ref)->fixed); + mark_as_dependent(thd, last, current_sel, this, this); } } + DBUG_ASSERT(*ref); /* Check if this is an incorrect reference in a group function or forward reference. Do not issue an error if this is an unnamed reference inside an @@ -3055,18 +3913,24 @@ set_properties(); - if (ref && (*ref)->check_cols(1)) - return 1; - return 0; + if ((*ref)->check_cols(1)) + return TRUE; + return FALSE; } + void Item_ref::set_properties() { max_length= (*ref)->max_length; maybe_null= (*ref)->maybe_null; decimals= (*ref)->decimals; collation.set((*ref)->collation); + /* + We have to remember if we refer to a sum function, to ensure that + split_sum_func() doesn't try to change the reference. + */ with_sum_func= (*ref)->with_sum_func; + unsigned_flag= (*ref)->unsigned_flag; if ((*ref)->type() == FIELD_ITEM) alias_name_used= ((Item_ident *) (*ref))->alias_name_used; else @@ -3138,6 +4002,105 @@ } +my_decimal *Item_ref::val_decimal_result(my_decimal *decimal_value) +{ + if (result_field) + { + if ((null_value= result_field->is_null())) + return 0; + return result_field->val_decimal(decimal_value); + } + return val_decimal(decimal_value); +} + + +bool Item_ref::val_bool_result() +{ + if (result_field) + { + if ((null_value= result_field->is_null())) + return 0; + switch (result_field->result_type()) { + case INT_RESULT: + return result_field->val_int() != 0; + case DECIMAL_RESULT: + { + my_decimal decimal_value; + my_decimal *val= result_field->val_decimal(&decimal_value); + if (val) + return !my_decimal_is_zero(val); + return 0; + } + case REAL_RESULT: + case STRING_RESULT: + return result_field->val_real() != 0.0; + case ROW_RESULT: + default: + DBUG_ASSERT(0); + } + } + return val_bool(); +} + + +double Item_ref::val_real() +{ + DBUG_ASSERT(fixed); + double tmp=(*ref)->val_result(); + null_value=(*ref)->null_value; + return tmp; +} + + +longlong Item_ref::val_int() +{ + DBUG_ASSERT(fixed); + longlong tmp=(*ref)->val_int_result(); + null_value=(*ref)->null_value; + return tmp; +} + + +bool Item_ref::val_bool() +{ + DBUG_ASSERT(fixed); + bool tmp= (*ref)->val_bool_result(); + null_value= (*ref)->null_value; + return tmp; +} + + +String *Item_ref::val_str(String* tmp) +{ + DBUG_ASSERT(fixed); + tmp=(*ref)->str_result(tmp); + null_value=(*ref)->null_value; + return tmp; +} + + +bool Item_ref::is_null() +{ + DBUG_ASSERT(fixed); + (void) (*ref)->val_int_result(); + return (*ref)->null_value; +} + + +bool Item_ref::get_date(TIME *ltime,uint fuzzydate) +{ + return (null_value=(*ref)->get_date_result(ltime,fuzzydate)); +} + + +my_decimal *Item_ref::val_decimal(my_decimal *decimal_value) +{ + my_decimal *val= (*ref)->val_decimal(decimal_value); + null_value= (*ref)->null_value; + return val; +} + + void Item_ref_null_helper::print(String *str) { str->append("<ref_null_helper>(", 18); @@ -3149,6 +4112,59 @@ } +double Item_direct_ref::val_real() +{ + double tmp=(*ref)->val_real(); + null_value=(*ref)->null_value; + return tmp; +} + + +longlong Item_direct_ref::val_int() +{ + longlong tmp=(*ref)->val_int(); + null_value=(*ref)->null_value; + return tmp; +} + + +String *Item_direct_ref::val_str(String* tmp) +{ + tmp=(*ref)->val_str(tmp); + null_value=(*ref)->null_value; + return tmp; +} + + +my_decimal *Item_direct_ref::val_decimal(my_decimal *decimal_value) +{ + my_decimal *tmp= (*ref)->val_decimal(decimal_value); + null_value=(*ref)->null_value; + return tmp; +} + + +bool Item_direct_ref::val_bool() +{ + bool tmp= (*ref)->val_bool(); + null_value=(*ref)->null_value; + return tmp; +} + + +bool Item_direct_ref::is_null() +{ + (void) (*ref)->val_int(); + return (*ref)->null_value; +} + + +bool Item_direct_ref::get_date(TIME *ltime,uint fuzzydate) +{ + return (null_value=(*ref)->get_date(ltime,fuzzydate)); +} + + void Item_null_helper::print(String *str) { str->append("<null_helper>(", 14); @@ -3395,6 +4411,9 @@ return INT_RESULT; else if (a == ROW_RESULT || b == ROW_RESULT) return ROW_RESULT; + if ((a == INT_RESULT || a == DECIMAL_RESULT) && + (b == INT_RESULT || b == DECIMAL_RESULT)) + return DECIMAL_RESULT; return REAL_RESULT; } @@ -3409,7 +4428,8 @@ item->result_type()); char *name=item->name; // Alloced by sql_alloc - if (res_type == STRING_RESULT) + switch (res_type) { + case STRING_RESULT: { char buff[MAX_FIELD_WIDTH]; String tmp(buff,sizeof(buff),&my_charset_bin),*result; @@ -3422,22 +4442,40 @@ char *tmp_str= sql_strmake(result->ptr(), length); new_item= new Item_string(name, tmp_str, length, result->charset()); } + break; } - else if (res_type == INT_RESULT) + case INT_RESULT: { longlong result=item->val_int(); uint length=item->max_length; bool null_value=item->null_value; new_item= (null_value ? (Item*) new Item_null(name) : (Item*) new Item_int(name, result, length)); + break; } - else + case REAL_RESULT: { // It must REAL_RESULT double result= item->val_real(); uint length=item->max_length,decimals=item->decimals; bool null_value=item->null_value; new_item= (null_value ? (Item*) new Item_null(name) : (Item*) - new Item_real(name, result, decimals, length)); + new Item_float(name, result, decimals, length)); + break; + } + case DECIMAL_RESULT: + { + my_decimal decimal_value; + my_decimal *result= item->val_decimal(&decimal_value); + uint length= item->max_length, decimals= item->decimals; + bool null_value= item->null_value; + new_item= (null_value ? + (Item*) new Item_null(name) : + (Item*) new Item_decimal(name, result, length, decimals)); + break; + } + case ROW_RESULT: + default: + DBUG_ASSERT(0); } if (new_item) thd->change_item_tree(ref, new_item); @@ -3468,6 +4506,16 @@ } if (res_type == INT_RESULT) return 1; // Both where of type int + if (res_type == DECIMAL_RESULT) + { + my_decimal item_buf, *item_val, + field_buf, *field_val; + item_val= item->val_decimal(&item_buf); + if (item->null_value) + return 1; // This must be true + field_val= field->val_decimal(&field_buf); + return !my_decimal_cmp(item_val, field_val); + } double result= item->val_real(); if (item->null_value) return 1; @@ -3476,12 +4524,13 @@ Item_cache* Item_cache::get_cache(Item_result type) { - switch (type) - { + switch (type) { case INT_RESULT: return new Item_cache_int(); case REAL_RESULT: return new Item_cache_real(); + case DECIMAL_RESULT: + return new Item_cache_decimal(); case STRING_RESULT: return new Item_cache_str(); case ROW_RESULT: @@ -3509,6 +4558,23 @@ { value= item->val_int_result(); null_value= item->null_value; + unsigned_flag= item->unsigned_flag; +} + + +String *Item_cache_int::val_str(String *str) +{ + DBUG_ASSERT(fixed == 1); + str->set(value, default_charset()); + return str; +} + + +my_decimal *Item_cache_int::val_decimal(my_decimal *decimal_val) +{ + DBUG_ASSERT(fixed == 1); + int2my_decimal(E_DEC_FATAL_ERROR, value, unsigned_flag, decimal_val); + return decimal_val; } @@ -3519,6 +4585,68 @@ } +longlong Item_cache_real::val_int() +{ + DBUG_ASSERT(fixed == 1); + return (longlong) (value+(value > 0 ? 0.5 : -0.5)); +} + + +String* Item_cache_real::val_str(String *str) +{ + DBUG_ASSERT(fixed == 1); + str->set(value, decimals, default_charset()); + return str; +} + + +my_decimal *Item_cache_real::val_decimal(my_decimal *decimal_val) +{ + DBUG_ASSERT(fixed == 1); + double2my_decimal(E_DEC_FATAL_ERROR, value, decimal_val); + return decimal_val; +} + + +void Item_cache_decimal::store(Item *item) +{ + my_decimal *val= item->val_decimal_result(&decimal_value); + if (!(null_value= item->null_value) && val != &decimal_value) + my_decimal2decimal(val, &decimal_value); +} + +double Item_cache_decimal::val_real() +{ + DBUG_ASSERT(fixed); + double res; + my_decimal2double(E_DEC_FATAL_ERROR, &decimal_value, &res); + return res; +} + +longlong Item_cache_decimal::val_int() +{ + DBUG_ASSERT(fixed); + longlong res; + my_decimal2int(E_DEC_FATAL_ERROR, &decimal_value, unsigned_flag, &res); + return res; +} + +String* Item_cache_decimal::val_str(String *str) +{ + DBUG_ASSERT(fixed); + my_decimal_round(E_DEC_FATAL_ERROR, &decimal_value, decimals, FALSE, + &decimal_value); + my_decimal2string(E_DEC_FATAL_ERROR, &decimal_value, 0, 0, 0, str); + return str; +} + +my_decimal *Item_cache_decimal::val_decimal(my_decimal *val) +{ + DBUG_ASSERT(fixed); + return &decimal_value; +} + + void Item_cache_str::store(Item *item) { value_buff.set(buffer, sizeof(buffer), item->collation.collation); @@ -3540,16 +4668,15 @@ } } - double Item_cache_str::val_real() { DBUG_ASSERT(fixed == 1); - int err; + int err_not_used; + char *end_not_used; if (value) return my_strntod(value->charset(), (char*) value->ptr(), - value->length(), (char**) 0, &err); - else - return (double)0; + value->length(), &end_not_used, &err_not_used); + return (double) 0; } @@ -3564,6 +4691,16 @@ return (longlong)0; } +my_decimal *Item_cache_str::val_decimal(my_decimal *decimal_val) +{ + DBUG_ASSERT(fixed == 1); + if (value) + string2my_decimal(E_DEC_FATAL_ERROR, value, decimal_val); + else + decimal_val= 0; + return decimal_val; +} + bool Item_cache_row::allocate(uint num) { @@ -3653,162 +4790,308 @@ Item_type_holder::Item_type_holder(THD *thd, Item *item) - :Item(thd, item), item_type(item->result_type()), - orig_type(item_type) + :Item(thd, item), enum_set_typelib(0), fld_type(get_real_type(item)) { DBUG_ASSERT(item->fixed); - /* - It is safe assign pointer on field, because it will be used just after - all JOIN::prepare calls and before any SELECT execution - */ - if (item->type() == Item::FIELD_ITEM) - field_example= ((Item_field*) item)->field; - else - field_example= 0; - max_length= real_length(item); + max_length= display_length(item); maybe_null= item->maybe_null; collation.set(item->collation); + get_full_info(item); + /* fix variable decimals which always is NOT_FIXED_DEC */ + if (Field::result_merge_type(fld_type) == INT_RESULT) + decimals= 0; } /* - STRING_RESULT, REAL_RESULT, INT_RESULT, ROW_RESULT + Return expression type of Item_type_holder - ROW_RESULT should never appear in Item_type_holder::join_types, - but it is included in following table just to make table full - (there DBUG_ASSERT in function to catch ROW_RESULT) + SYNOPSIS + Item_type_holder::result_type() + + RETURN + Item_result (type of internal MySQL expression result) */ -static Item_result type_convertor[4][4]= -{{STRING_RESULT, STRING_RESULT, STRING_RESULT, ROW_RESULT}, - {STRING_RESULT, REAL_RESULT, REAL_RESULT, ROW_RESULT}, - {STRING_RESULT, REAL_RESULT, INT_RESULT, ROW_RESULT}, - {ROW_RESULT, ROW_RESULT, ROW_RESULT, ROW_RESULT}}; + +Item_result Item_type_holder::result_type() const +{ + return Field::result_merge_type(fld_type); +} /* - Values of 'from' field can be stored in 'to' field. + Find real field type of item SYNOPSIS - is_attr_compatible() - from Item which values should be saved - to Item where values should be saved + Item_type_holder::get_real_type() RETURN - 1 can be saved - 0 can not be saved + type of field which should be created to store item value */ -inline bool is_attr_compatible(Item *from, Item *to) +enum_field_types Item_type_holder::get_real_type(Item *item) { - return ((to->max_length >= from->max_length) && - (to->maybe_null || !from->maybe_null) && - (to->result_type() != STRING_RESULT || - from->result_type() != STRING_RESULT || - my_charset_same(from->collation.collation, - to->collation.collation))); -} - - -bool Item_type_holder::join_types(THD *thd, Item *item) -{ - uint32 new_length= real_length(item); - bool use_new_field= 0, use_expression_type= 0; - Item_result new_result_type= type_convertor[item_type][item->result_type()]; - bool item_is_a_field= item->type() == Item::FIELD_ITEM; - - /* - Check if both items point to fields: in this case we - can adjust column types of result table in the union smartly. - */ - if (field_example && item_is_a_field) - { - Field *field= ((Item_field *)item)->field; - /* Can 'field_example' field store data of the column? */ - if ((use_new_field= - (!field->field_cast_compatible(field_example->field_cast_type()) || - !is_attr_compatible(item, this)))) - { - /* - The old field can't store value of the new field. - Check if the new field can store value of the old one. - */ - use_expression_type|= - (!field_example->field_cast_compatible(field->field_cast_type()) || - !is_attr_compatible(this, item)); - } + switch(item->type()) + { + case FIELD_ITEM: + { + /* + Item_fields::field_type ask Field_type() but sometimes field return + a different type, like for enum/set, so we need to ask real type. + */ + Field *field= ((Item_field *) item)->field; + enum_field_types type= field->real_type(); + /* work around about varchar type field detection */ + if (type == MYSQL_TYPE_STRING && field->type() == MYSQL_TYPE_VAR_STRING) + return MYSQL_TYPE_VAR_STRING; + return type; } - else if (field_example || item_is_a_field) + case SUM_FUNC_ITEM: { /* - Expression types can't be mixed with field types, we have to use - expression types. + Argument of aggregate function sometimes should be asked about field + type */ - use_new_field= 1; // make next if test easier - use_expression_type= 1; + Item_sum *item_sum= (Item_sum *) item; + if (item_sum->keep_field_type()) + return get_real_type(item_sum->args[0]); + break; } - - /* Check whether size/type of the result item should be changed */ - if (use_new_field || - (new_result_type != item_type) || (new_length > max_length) || - (!maybe_null && item->maybe_null) || - (item_type == STRING_RESULT && - collation.collation != item->collation.collation)) - { - const char *old_cs,*old_derivation; - if (use_expression_type || !item_is_a_field) - field_example= 0; - else + case FUNC_ITEM: + if (((Item_func *) item)->functype() == Item_func::GUSERVAR_FUNC) { /* - It is safe to assign a pointer to field here, because it will be used - before any table is closed. + There are work around of problem with changing variable type on the + fly and variable always report "string" as field type to get + acceptable information for client in send_field, so we make field + type from expression type. */ - field_example= ((Item_field*) item)->field; + switch (item->result_type()) + { + case STRING_RESULT: + return MYSQL_TYPE_VAR_STRING; + case INT_RESULT: + return MYSQL_TYPE_LONGLONG; + case REAL_RESULT: + return MYSQL_TYPE_DOUBLE; + case DECIMAL_RESULT: + return MYSQL_TYPE_NEWDECIMAL; + case ROW_RESULT: + default: + DBUG_ASSERT(0); + return MYSQL_TYPE_VAR_STRING; + } } + break; + default: + break; + } + return item->field_type(); +} + +/* + Find field type which can carry current Item_type_holder type and + type of given Item. + + SYNOPSIS + Item_type_holder::join_types() + thd thread handler + item given item to join its parameters with this item ones + + RETURN + TRUE error - types are incompatible + FALSE OK +*/ +bool Item_type_holder::join_types(THD *thd, Item *item) +{ + DBUG_ENTER("Item_type_holder::join_types"); + DBUG_PRINT("info:", ("was type %d len %d, dec %d name %s", + fld_type, max_length, decimals, + (name ? name : "<NULL>"))); + DBUG_PRINT("info:", ("in type %d len %d, dec %d", + get_real_type(item), + item->max_length, item->decimals)); + fld_type= Field::field_type_merge(fld_type, get_real_type(item)); + { + int item_decimals= item->decimals; + /* fix variable decimals which always is NOT_FIXED_DEC */ + if (Field::result_merge_type(fld_type) == INT_RESULT) + item_decimals= 0; + decimals= max(decimals, item_decimals); + } + if (Field::result_merge_type(fld_type) == DECIMAL_RESULT) + { + int item_length= display_length(item); + int intp1= item_length - min(item->decimals, NOT_FIXED_DEC - 1); + int intp2= max_length - min(decimals, NOT_FIXED_DEC - 1); + /* can't be overflow because it work only for decimals (no strings) */ + int dec_length= max(intp1, intp2) + decimals; + max_length= max(max_length, (uint) max(item_length, dec_length)); + /* + we can't allow decimals to be NOT_FIXED_DEC, to prevent creation + decimal with max precision (see Field_new_decimal constcuctor) + */ + if (decimals >= NOT_FIXED_DEC) + decimals= NOT_FIXED_DEC - 1; + } + else + max_length= max(max_length, display_length(item)); + if (Field::result_merge_type(fld_type) == STRING_RESULT) + { + const char *old_cs, *old_derivation; old_cs= collation.collation->name; old_derivation= collation.derivation_name(); - if (item_type == STRING_RESULT && collation.aggregate(item->collation)) + if (collation.aggregate(item->collation)) { my_error(ER_CANT_AGGREGATE_2COLLATIONS, MYF(0), - old_cs, old_derivation, - item->collation.collation->name, - item->collation.derivation_name(), - "UNION"); - return 1; + old_cs, old_derivation, + item->collation.collation->name, + item->collation.derivation_name(), + "UNION"); + DBUG_RETURN(TRUE); } - - max_length= max(max_length, new_length); - decimals= max(decimals, item->decimals); - maybe_null|= item->maybe_null; - item_type= new_result_type; } - DBUG_ASSERT(item_type != ROW_RESULT); - return 0; + maybe_null|= item->maybe_null; + get_full_info(item); + DBUG_PRINT("info", ("become type: %d len: %u dec: %u", + (int) fld_type, max_length, (uint) decimals)); + DBUG_RETURN(FALSE); } +/* + Calculate lenth for merging result for given Item type -uint32 Item_type_holder::real_length(Item *item) + SYNOPSIS + Item_type_holder::real_length() + item Item for lrngth detection + + RETURN + length +*/ + +uint32 Item_type_holder::display_length(Item *item) { if (item->type() == Item::FIELD_ITEM) return ((Item_field *)item)->max_disp_length(); - switch (item->result_type()) + switch (item->field_type()) { - case STRING_RESULT: + case MYSQL_TYPE_DECIMAL: + case MYSQL_TYPE_TIMESTAMP: + case MYSQL_TYPE_DATE: + case MYSQL_TYPE_TIME: + case MYSQL_TYPE_DATETIME: + case MYSQL_TYPE_YEAR: + case MYSQL_TYPE_NEWDATE: + case MYSQL_TYPE_VARCHAR: + case MYSQL_TYPE_BIT: + case MYSQL_TYPE_NEWDECIMAL: + case MYSQL_TYPE_ENUM: + case MYSQL_TYPE_SET: + case MYSQL_TYPE_TINY_BLOB: + case MYSQL_TYPE_MEDIUM_BLOB: + case MYSQL_TYPE_LONG_BLOB: + case MYSQL_TYPE_BLOB: + case MYSQL_TYPE_VAR_STRING: + case MYSQL_TYPE_STRING: + case MYSQL_TYPE_GEOMETRY: return item->max_length; - case REAL_RESULT: + case MYSQL_TYPE_TINY: + return 4; + case MYSQL_TYPE_SHORT: + return 6; + case MYSQL_TYPE_LONG: + return 11; + case MYSQL_TYPE_FLOAT: + return 25; + case MYSQL_TYPE_DOUBLE: return 53; - case INT_RESULT: + case MYSQL_TYPE_NULL: + return 4; + case MYSQL_TYPE_LONGLONG: return 20; - case ROW_RESULT: + case MYSQL_TYPE_INT24: + return 8; default: DBUG_ASSERT(0); // we should never go there return 0; } } + +/* + Make temporary table field according collected information about type + of UNION result + + SYNOPSIS + Item_type_holder::make_field_by_type() + table temporary table for which we create fields + + RETURN + created field +*/ + +Field *Item_type_holder::make_field_by_type(TABLE *table) +{ + /* + The field functions defines a field to be not null if null_ptr is not 0 + */ + uchar *null_ptr= maybe_null ? (uchar*) "" : 0; + switch (fld_type) + { + case MYSQL_TYPE_ENUM: + DBUG_ASSERT(enum_set_typelib); + return new Field_enum((char *) 0, max_length, null_ptr, 0, + Field::NONE, name, + table, get_enum_pack_length(enum_set_typelib->count), + enum_set_typelib, collation.collation); + case MYSQL_TYPE_SET: + DBUG_ASSERT(enum_set_typelib); + return new Field_set((char *) 0, max_length, null_ptr, 0, + Field::NONE, name, + table, get_set_pack_length(enum_set_typelib->count), + enum_set_typelib, collation.collation); + default: + break; + } + return tmp_table_field_from_field_type(table); +} + + +/* + Get full information from Item about enum/set fields to be able to create + them later + + SYNOPSIS + Item_type_holder::get_full_info + item Item for information collection +*/ +void Item_type_holder::get_full_info(Item *item) +{ + if (fld_type == MYSQL_TYPE_ENUM || + fld_type == MYSQL_TYPE_SET) + { + /* + We can have enum/set type after merging only if we have one enum/set + field and number of NULL fields + */ + DBUG_ASSERT((enum_set_typelib && + get_real_type(item) == MYSQL_TYPE_NULL) || + (!enum_set_typelib && + item->type() == Item::FIELD_ITEM && + (get_real_type(item) == MYSQL_TYPE_ENUM || + get_real_type(item) == MYSQL_TYPE_SET) && + ((Field_enum*)((Item_field *) item)->field)->typelib)); + if (!enum_set_typelib) + { + enum_set_typelib= ((Field_enum*)((Item_field *) item)->field)->typelib; + } + } +} + + double Item_type_holder::val_real() { DBUG_ASSERT(0); // should never be called @@ -3822,6 +5105,11 @@ return 0; } +my_decimal *Item_type_holder::val_decimal(my_decimal *) +{ + DBUG_ASSERT(0); // should never be called + return 0; +} String *Item_type_holder::val_str(String*) { --- 1.112/sql/item.h 2005-04-01 04:06:32 -08:00 +++ 1.113/sql/item.h 2005-04-05 19:45:08 -07:00 @@ -1502,15 +1502,7 @@ bool eq(const Item *item, bool binary_cmp) const; bool fix_fields(THD *, struct st_table_list *, Item **); void print(String *str); - int save_in_field(Field *field_arg, bool no_conversions) - { - if (!arg) - { - field_arg->set_default(); - return 0; - } - return Item_field::save_in_field(field_arg, no_conversions); - } + int save_in_field(Field *field_arg, bool no_conversions); table_map used_tables() const { return (table_map)0L; } bool walk(Item_processor processor, byte *args) --- 1.138/sql/sql_insert.cc 2005-01-14 11:32:23 -08:00 +++ 1.139/sql/sql_insert.cc 2005-04-05 19:45:08 -07:00 @@ -31,7 +31,7 @@ extern "C" pthread_handler_decl(handle_delayed_insert,arg); static void unlink_blobs(register TABLE *table); #endif -static bool check_view_insertability(TABLE_LIST *view, ulong query_id); +static bool check_view_insertability(TABLE_LIST *view, query_id_t query_id); /* Define to force use of my_malloc() if the allocated memory block is big */ @@ -168,7 +168,7 @@ runs without --log-update or --log-bin). */ bool log_on= (thd->options & OPTION_BIN_LOG) || (!(thd->master_access & SUPER_ACL)); - bool transactional_table, log_delayed; + bool transactional_table; uint value_count; ulong counter = 1; ulonglong id; @@ -217,9 +217,11 @@ } if ((table= delayed_get_table(thd,table_list)) && !thd->is_fatal_error) { - res= 0; - if (table_list->next_global) /* if sub select */ - res= open_and_lock_tables(thd, table_list->next_global); + /* + Open tables used for sub-selects or in stored functions, will also + cache these functions. + */ + res= open_and_lock_tables(thd, table_list->next_global); /* First is not processed by open_and_lock_tables() => we need set updateability flags "by hands". @@ -434,7 +436,7 @@ /* Invalidate the table in the query cache if something changed. For the transactional algorithm to work the invalidation must be - before binlog writing and ha_autocommit_... + before binlog writing and ha_autocommit_or_rollback */ if (info.copied || info.deleted || info.updated) { @@ -443,7 +445,6 @@ transactional_table= table->file->has_transactions(); - log_delayed= (transactional_table || table->s->tmp_table); if ((info.copied || info.deleted || info.updated) && (error <= 0 || !transactional_table)) { @@ -452,11 +453,11 @@ if (error <= 0) thd->clear_error(); Query_log_event qinfo(thd, thd->query, thd->query_length, - log_delayed, FALSE); + transactional_table, FALSE); if (mysql_bin_log.write(&qinfo) && transactional_table) error=1; } - if (!log_delayed) + if (!transactional_table) thd->options|=OPTION_STATUS_NO_TRANS_UPDATE; } if (transactional_table) @@ -538,7 +539,7 @@ TRUE - can't be used for insert */ -static bool check_view_insertability(TABLE_LIST *view, ulong query_id) +static bool check_view_insertability(TABLE_LIST *view, query_id_t query_id) { uint num= view->view->select_lex.item_list.elements; TABLE *table= view->table; @@ -546,7 +547,7 @@ *trans_end= trans_start + num; Field_translator *trans; Field **field_ptr= table->field; - ulong other_query_id= query_id - 1; + query_id_t other_query_id= query_id - 1; DBUG_ENTER("check_key_in_view"); DBUG_ASSERT(view->table != 0 && view->field_translation != 0); @@ -672,6 +673,7 @@ bool insert_into_view= (table_list->view != 0); /* TODO: use this condition for 'WITH CHECK OPTION' */ bool res; + TABLE_LIST *next_local; DBUG_ENTER("mysql_prepare_insert"); DBUG_PRINT("enter", ("table_list 0x%lx, table 0x%lx, view %d", (ulong)table_list, (ulong)table, @@ -688,6 +690,8 @@ select_insert)) DBUG_RETURN(TRUE); + next_local= table_list->next_local; + table_list->next_local= 0; if ((values && check_insert_fields(thd, table_list, fields, *values, 1, !insert_into_view)) || (values && setup_fields(thd, 0, table_list, *values, 0, 0, 0)) || @@ -698,6 +702,7 @@ res) || setup_fields(thd, 0, table_list, update_values, 1, 0, 0)))) DBUG_RETURN(TRUE); + table_list->next_local= next_local; if (!table) table= table_list->table; @@ -947,7 +952,8 @@ thd.current_tablenr=0; thd.version=refresh_version; thd.command=COM_DELAYED_INSERT; - thd.lex->current_select= 0; /* for my_message_sql */ + thd.lex->current_select= 0; // for my_message_sql + thd.lex->sql_command= SQLCOM_INSERT; // For innodb::store_lock() bzero((char*) &thd.net, sizeof(thd.net)); // Safety bzero((char*) &table_list, sizeof(table_list)); // Safety @@ -1725,7 +1731,6 @@ { LEX *lex= thd->lex; TABLE_LIST *first_select_leaf_table; - int res; DBUG_ENTER("mysql_insert_select_prepare"); /* SELECT_LEX do not belong to INSERT statement, so we can't add WHERE @@ -1743,7 +1748,7 @@ exclude first table from leaf tables list, because it belong to INSERT */ - DBUG_ASSERT(lex->select_lex.leaf_tables); + DBUG_ASSERT(lex->select_lex.leaf_tables != 0); lex->leaf_tables_insert= lex->select_lex.leaf_tables; /* skip all leaf tables belonged to view where we are insert */ for (first_select_leaf_table= lex->select_lex.leaf_tables->next_leaf; @@ -1804,13 +1809,22 @@ thd->lex->current_select->options|= OPTION_BUFFER_RESULT; thd->lex->current_select->join->select_options|= OPTION_BUFFER_RESULT; } - + else + { + /* + We must not yet prepare the result table if it is the same as one of the + source tables (INSERT SELECT). The preparation may disable + indexes on the result table, which may be used during the select, if it + is the same table (Bug #6034). Do the preparation after the select phase + in select_insert::prepare2(). + */ + table->file->start_bulk_insert((ha_rows) 0); + } restore_record(table,s->default_values); // Get empty record table->next_number_field=table->found_next_number_field; thd->cuted_fields=0; if (info.ignore || info.handle_duplicates != DUP_ERROR) table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); - table->file->start_bulk_insert((ha_rows) 0); thd->no_trans_update= 0; thd->abort_on_warning= (!info.ignore && (thd->variables.sql_mode & @@ -1821,6 +1835,31 @@ } +/* + Finish the preparation of the result table. + + SYNOPSIS + select_insert::prepare2() + void + + DESCRIPTION + If the result table is the same as one of the source tables (INSERT SELECT), + the result table is not finally prepared at the join prepair phase. + Do the final preparation now. + + RETURN + 0 OK +*/ + +int select_insert::prepare2(void) +{ + DBUG_ENTER("select_insert::prepare2"); + if (thd->lex->current_select->options & OPTION_BUFFER_RESULT) + table->file->start_bulk_insert((ha_rows) 0); + DBUG_RETURN(0); +} + + void select_insert::cleanup() { /* select_insert/select_create are never re-used in prepared statement */ @@ -1938,7 +1977,7 @@ /* We must invalidate the table in the query cache before binlog writing - and ha_autocommit_... + and ha_autocommit_or_rollback */ if (info.copied || info.deleted || info.updated) --- 1.83/mysql-test/r/create.result 2005-04-01 04:04:43 -08:00 +++ 1.84/mysql-test/r/create.result 2005-04-05 19:45:07 -07:00 @@ -267,11 +267,15 @@ create table t1 (a int not null, b int, primary key (a)); insert into t1 values (1,1); create table if not exists t1 select 2; +Warnings: +Warning 1364 Field 'a' doesn't have a default value select * from t1; a b 1 1 0 2 create table if not exists t1 select 3 as 'a',4 as 'b'; +Warnings: +Warning 1364 Field 'a' doesn't have a default value create table if not exists t1 select 3 as 'a',3 as 'b'; ERROR 23000: Duplicate entry '3' for key 1 select * from t1; --- 1.8/mysql-test/r/limit.result 2005-01-14 11:29:56 -08:00 +++ 1.9/mysql-test/r/limit.result 2005-04-05 19:45:07 -07:00 @@ -67,3 +67,12 @@ id id2 3 0 DROP TABLE t1; +create table t1 (a integer); +insert into t1 values (1); +select 1 as a from t1 union all select 1 from dual limit 1; +a +1 +(select 1 as a from t1) union all (select 1 from dual) limit 1; +a +1 +drop table t1; --- 1.47/mysql-test/r/order_by.result 2005-01-14 11:30:02 -08:00 +++ 1.48/mysql-test/r/order_by.result 2005-04-05 19:45:07 -07:00 @@ -740,3 +740,58 @@ 1 2 1 1 drop table t1; +create table t1 ( +`sid` decimal(8,0) default null, +`wnid` varchar(11) not null default '', +key `wnid14` (`wnid`(4)), +key `wnid` (`wnid`) +) engine=myisam default charset=latin1; +insert into t1 (`sid`, `wnid`) values +('10100','01019000000'),('37986','01019000000'),('37987','01019010000'), +('39560','01019090000'),('37989','01019000000'),('37990','01019011000'), +('37991','01019011000'),('37992','01019019000'),('37993','01019030000'), +('37994','01019090000'),('475','02070000000'),('25253','02071100000'), +('25255','02071100000'),('25256','02071110000'),('25258','02071130000'), +('25259','02071190000'),('25260','02071200000'),('25261','02071210000'), +('25262','02071290000'),('25263','02071300000'),('25264','02071310000'), +('25265','02071310000'),('25266','02071320000'),('25267','02071320000'), +('25269','02071330000'),('25270','02071340000'),('25271','02071350000'), +('25272','02071360000'),('25273','02071370000'),('25281','02071391000'), +('25282','02071391000'),('25283','02071399000'),('25284','02071400000'), +('25285','02071410000'),('25286','02071410000'),('25287','02071420000'), +('25288','02071420000'),('25291','02071430000'),('25290','02071440000'), +('25292','02071450000'),('25293','02071460000'),('25294','02071470000'), +('25295','02071491000'),('25296','02071491000'),('25297','02071499000'); +explain select * from t1 where wnid like '0101%' order by wnid; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range wnid14,wnid wnid 13 NULL 10 Using where +select * from t1 where wnid like '0101%' order by wnid; +sid wnid +10100 01019000000 +37986 01019000000 +37989 01019000000 +37987 01019010000 +37990 01019011000 +37991 01019011000 +37992 01019019000 +37993 01019030000 +39560 01019090000 +37994 01019090000 +drop table t1; +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (2), (1), (1), (2), (1); +SELECT a FROM t1 ORDER BY a; +a +1 +1 +1 +2 +2 +(SELECT a FROM t1) ORDER BY a; +a +1 +1 +1 +2 +2 +DROP TABLE t1; --- 1.44/mysql-test/r/type_blob.result 2005-01-14 11:30:17 -08:00 +++ 1.45/mysql-test/r/type_blob.result 2005-04-05 19:45:08 -07:00 @@ -33,11 +33,11 @@ ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1,t2,t3,t4; CREATE TABLE t1 (a char(257) default "hello"); -ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB instead +ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead CREATE TABLE t2 (a char(256)); -ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB instead +ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead CREATE TABLE t1 (a varchar(70000) default "hello"); -ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB instead +ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead CREATE TABLE t2 (a blob default "hello"); ERROR 42000: BLOB/TEXT column 'a' can't have a default value drop table if exists t1,t2; @@ -80,17 +80,17 @@ lock tables t1 READ; show full fields from t1; Field Type Collation Null Key Default Extra Privileges Comment -t text latin1_swedish_ci YES NULL select,insert,update,references -c char(10) latin1_swedish_ci YES NULL select,insert,update,references -b blob NULL YES NULL select,insert,update,references -d binary(10) NULL YES NULL select,insert,update,references +t text latin1_swedish_ci YES NULL # +c char(10) latin1_swedish_ci YES NULL # +b blob NULL YES NULL # +d binary(10) NULL YES NULL # lock tables t1 WRITE; show full fields from t1; Field Type Collation Null Key Default Extra Privileges Comment -t text latin1_swedish_ci YES NULL select,insert,update,references -c char(10) latin1_swedish_ci YES NULL select,insert,update,references -b blob NULL YES NULL select,insert,update,references -d binary(10) NULL YES NULL select,insert,update,references +t text latin1_swedish_ci YES NULL # +c char(10) latin1_swedish_ci YES NULL # +b blob NULL YES NULL # +d binary(10) NULL YES NULL # unlock tables; select t from t1 where t like "hello"; t @@ -509,7 +509,7 @@ collation(load_file('../../std_data/words.dat')), coercibility(load_file('../../std_data/words.dat')); charset(load_file('../../std_data/words.dat')) collation(load_file('../../std_data/words.dat')) coercibility(load_file('../../std_data/words.dat')) -binary binary 3 +binary binary 4 explain extended select charset(load_file('../../std_data/words.dat')), collation(load_file('../../std_data/words.dat')), @@ -523,10 +523,10 @@ if(imagem is null, "ERROR", "OK") length(imagem) OK 581 drop table t1; -create table t1 select load_file('../../std_data/words.dat'); +create table t1 select load_file('../../std_data/words.dat') l; show full fields from t1; Field Type Collation Null Key Default Extra Privileges Comment -load_file('../../std_data/words.dat') longblob NULL YES NULL select,insert,update,references +l longblob NULL YES NULL # drop table t1; create table t1 (id integer primary key auto_increment, txt text not null, unique index txt_index (txt (20))); insert into t1 (txt) values ('Chevy'), ('Chevy '); --- 1.31/mysql-test/r/type_ranges.result 2005-01-14 11:30:22 -08:00 +++ 1.32/mysql-test/r/type_ranges.result 2005-04-05 19:45:08 -07:00 @@ -40,30 +40,30 @@ ); show full fields from t1; Field Type Collation Null Key Default Extra Privileges Comment -auto int(5) unsigned NULL NO PRI NULL auto_increment select,insert,update,references -string char(10) latin1_swedish_ci YES hello select,insert,update,references -tiny tinyint(4) NULL NO MUL 0 select,insert,update,references -short smallint(6) NULL NO MUL 1 select,insert,update,references -medium mediumint(8) NULL NO MUL 0 select,insert,update,references -long_int int(11) NULL NO 0 select,insert,update,references -longlong bigint(13) NULL NO MUL 0 select,insert,update,references -real_float float(13,1) NULL NO MUL 0.0 select,insert,update,references -real_double double(16,4) NULL YES NULL select,insert,update,references -utiny tinyint(3) unsigned NULL NO MUL 0 select,insert,update,references -ushort smallint(5) unsigned zerofill NULL NO MUL 00000 select,insert,update,references -umedium mediumint(8) unsigned NULL NO MUL 0 select,insert,update,references -ulong int(11) unsigned NULL NO MUL 0 select,insert,update,references -ulonglong bigint(13) unsigned NULL NO MUL 0 select,insert,update,references -time_stamp timestamp NULL YES CURRENT_TIMESTAMP select,insert,update,references -date_field date NULL YES NULL select,insert,update,references -time_field time NULL YES NULL select,insert,update,references -date_time datetime NULL YES NULL select,insert,update,references -blob_col blob NULL YES NULL select,insert,update,references -tinyblob_col tinyblob NULL YES NULL select,insert,update,references -mediumblob_col mediumblob NULL NO select,insert,update,references -longblob_col longblob NULL NO select,insert,update,references -options enum('one','two','tree') latin1_swedish_ci NO MUL one select,insert,update,references -flags set('one','two','tree') latin1_swedish_ci NO select,insert,update,references +auto int(5) unsigned NULL NO PRI NULL auto_increment # +string char(10) latin1_swedish_ci YES hello # +tiny tinyint(4) NULL NO MUL 0 # +short smallint(6) NULL NO MUL 1 # +medium mediumint(8) NULL NO MUL 0 # +long_int int(11) NULL NO 0 # +longlong bigint(13) NULL NO MUL 0 # +real_float float(13,1) NULL NO MUL 0.0 # +real_double double(16,4) NULL YES NULL # +utiny tinyint(3) unsigned NULL NO MUL 0 # +ushort smallint(5) unsigned zerofill NULL NO MUL 00000 # +umedium mediumint(8) unsigned NULL NO MUL 0 # +ulong int(11) unsigned NULL NO MUL 0 # +ulonglong bigint(13) unsigned NULL NO MUL 0 # +time_stamp timestamp NULL YES CURRENT_TIMESTAMP # +date_field date NULL YES NULL # +time_field time NULL YES NULL # +date_time datetime NULL YES NULL # +blob_col blob NULL YES NULL # +tinyblob_col tinyblob NULL YES NULL # +mediumblob_col mediumblob NULL NO # +longblob_col longblob NULL NO # +options enum('one','two','tree') latin1_swedish_ci NO MUL one # +flags set('one','two','tree') latin1_swedish_ci NO # show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 auto A 0 NULL NULL BTREE @@ -87,6 +87,8 @@ insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,0,0,0,1,1,1,1,'one','one'); insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,NULL,2,2,'two','two,one'); insert into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','10:10:10','19970303101010','','','','3',3,3); +Warnings: +Warning 1265 Data truncated for column 'string' at row 1 insert into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,080706,19970403090807,-1,-1,-1,'-1',-1,-1); Warnings: Warning 1264 Out of range value adjusted for column 'utiny' at row 1 @@ -122,7 +124,7 @@ auto string tiny short medium long_int longlong real_float real_double utiny ushort umedium ulong ulonglong mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000) date_field time_field date_time blob_col tinyblob_col mediumblob_col longblob_col 10 1 1 1 1 1 1 1.0 1.0000 1 00001 1 1 1 0 0000-00-00 00:00:00 0000-00-00 00:00:00 1 1 1 1 11 2 2 2 2 2 2 2.0 2.0000 2 00002 2 2 2 0 NULL NULL NULL NULL NULL 2 2 -12 0.33 3 3 3 3 3 3.0 3.0000 3 00003 3 3 3 0 1997-03-03 10:10:10 1997-03-03 10:10:10 3 +12 0.33333333 3 3 3 3 3 3.0 3.0000 3 00003 3 3 3 0 1997-03-03 10:10:10 1997-03-03 10:10:10 3 13 -1 -1 -1 -1 -1 -1 -1.0 -1.0000 0 00000 0 0 18446744073709551615 0 1997-08-07 08:07:06 1997-04-03 09:08:07 -1 -1 -1 -1 14 -429496729 -128 -32768 -8388608 -2147483648 -4294967295 -4294967296.0 -4294967295.0000 0 00000 0 0 18446744069414584321 0 0000-00-00 00:00:00 0000-00-00 00:00:00 -4294967295 -4294967295 -4294967295 -4294967295 15 4294967295 127 32767 8388607 2147483647 4294967295 4294967296.0 4294967295.0000 255 65535 16777215 4294967295 4294967295 0 0000-00-00 00:00:00 0000-00-00 00:00:00 4294967295 4294967295 4294967295 4294967295 @@ -131,7 +133,7 @@ add new_field char(10) default "new" not null, change blob_col new_blob_col varchar(20), change date_field date_field char(10), -alter column string set default "new default", +alter column string set default "newdefault", alter short drop default, DROP INDEX utiny, DROP INDEX ushort, @@ -174,7 +176,7 @@ select * from t2; auto string mediumblob_col new_field 1 2 2 ne -2 0.33 ne +2 0.33333333 ne 3 -1 -1 ne 4 -429496729 -4294967295 ne 5 4294967295 4294967295 ne @@ -208,56 +210,56 @@ update t2 set string="changed" where auto=16; show full columns from t1; Field Type Collation Null Key Default Extra Privileges Comment -auto int(5) unsigned NULL NO MUL NULL auto_increment select,insert,update,references -string char(10) latin1_swedish_ci YES new defaul select,insert,update,references -tiny tinyint(4) NULL NO MUL 0 select,insert,update,references -short smallint(6) NULL NO MUL 0 select,insert,update,references -medium mediumint(8) NULL NO MUL 0 select,insert,update,references -long_int int(11) NULL NO 0 select,insert,update,references -longlong bigint(13) NULL NO MUL 0 select,insert,update,references -real_float float(13,1) NULL NO MUL 0.0 select,insert,update,references -real_double double(16,4) NULL YES NULL select,insert,update,references -utiny tinyint(3) unsigned NULL NO 0 select,insert,update,references -ushort smallint(5) unsigned zerofill NULL NO 00000 select,insert,update,references -umedium mediumint(8) unsigned NULL NO MUL 0 select,insert,update,references -ulong int(11) unsigned NULL NO MUL 0 select,insert,update,references -ulonglong bigint(13) unsigned NULL NO MUL 0 select,insert,update,references -time_stamp timestamp NULL YES CURRENT_TIMESTAMP select,insert,update,references -date_field char(10) latin1_swedish_ci YES NULL select,insert,update,references -time_field time NULL YES NULL select,insert,update,references -date_time datetime NULL YES NULL select,insert,update,references -new_blob_col varchar(20) latin1_swedish_ci YES NULL select,insert,update,references -tinyblob_col tinyblob NULL YES NULL select,insert,update,references -mediumblob_col mediumblob NULL NO select,insert,update,references -options enum('one','two','tree') latin1_swedish_ci NO MUL one select,insert,update,references -flags set('one','two','tree') latin1_swedish_ci NO select,insert,update,references -new_field char(10) latin1_swedish_ci NO new select,insert,update,references +auto int(5) unsigned NULL NO MUL NULL auto_increment # +string char(10) latin1_swedish_ci YES newdefault # +tiny tinyint(4) NULL NO MUL 0 # +short smallint(6) NULL NO MUL 0 # +medium mediumint(8) NULL NO MUL 0 # +long_int int(11) NULL NO 0 # +longlong bigint(13) NULL NO MUL 0 # +real_float float(13,1) NULL NO MUL 0.0 # +real_double double(16,4) NULL YES NULL # +utiny tinyint(3) unsigned NULL NO 0 # +ushort smallint(5) unsigned zerofill NULL NO 00000 # +umedium mediumint(8) unsigned NULL NO MUL 0 # +ulong int(11) unsigned NULL NO MUL 0 # +ulonglong bigint(13) unsigned NULL NO MUL 0 # +time_stamp timestamp NULL YES CURRENT_TIMESTAMP # +date_field char(10) latin1_swedish_ci YES NULL # +time_field time NULL YES NULL # +date_time datetime NULL YES NULL # +new_blob_col varchar(20) latin1_swedish_ci YES NULL # +tinyblob_col tinyblob NULL YES NULL # +mediumblob_col mediumblob NULL NO # +options enum('one','two','tree') latin1_swedish_ci NO MUL one # +flags set('one','two','tree') latin1_swedish_ci NO # +new_field char(10) latin1_swedish_ci NO new # show full columns from t2; Field Type Collation Null Key Default Extra Privileges Comment -auto int(5) unsigned NULL NO 0 select,insert,update,references -string char(10) latin1_swedish_ci YES new defaul select,insert,update,references -tiny tinyint(4) NULL NO 0 select,insert,update,references -short smallint(6) NULL NO 0 select,insert,update,references -medium mediumint(8) NULL NO 0 select,insert,update,references -long_int int(11) NULL NO 0 select,insert,update,references -longlong bigint(13) NULL NO 0 select,insert,update,references -real_float float(13,1) NULL NO 0.0 select,insert,update,references -real_double double(16,4) NULL YES NULL select,insert,update,references -utiny tinyint(3) unsigned NULL NO 0 select,insert,update,references -ushort smallint(5) unsigned zerofill NULL NO 00000 select,insert,update,references -umedium mediumint(8) unsigned NULL NO 0 select,insert,update,references -ulong int(11) unsigned NULL NO 0 select,insert,update,references -ulonglong bigint(13) unsigned NULL NO 0 select,insert,update,references -time_stamp timestamp NULL YES 0000-00-00 00:00:00 select,insert,update,references -date_field char(10) latin1_swedish_ci YES NULL select,insert,update,references -time_field time NULL YES NULL select,insert,update,references -date_time datetime NULL YES NULL select,insert,update,references -new_blob_col varchar(20) latin1_swedish_ci YES NULL select,insert,update,references -tinyblob_col tinyblob NULL YES NULL select,insert,update,references -mediumblob_col mediumblob NULL NO select,insert,update,references -options enum('one','two','tree') latin1_swedish_ci NO one select,insert,update,references -flags set('one','two','tree') latin1_swedish_ci NO select,insert,update,references -new_field char(10) latin1_swedish_ci NO new select,insert,update,references +auto int(5) unsigned NULL NO 0 # +string char(10) latin1_swedish_ci YES newdefault # +tiny tinyint(4) NULL NO 0 # +short smallint(6) NULL NO 0 # +medium mediumint(8) NULL NO 0 # +long_int int(11) NULL NO 0 # +longlong bigint(13) NULL NO 0 # +real_float float(13,1) NULL NO 0.0 # +real_double double(16,4) NULL YES NULL # +utiny tinyint(3) unsigned NULL NO 0 # +ushort smallint(5) unsigned zerofill NULL NO 00000 # +umedium mediumint(8) unsigned NULL NO 0 # +ulong int(11) unsigned NULL NO 0 # +ulonglong bigint(13) unsigned NULL NO 0 # +time_stamp timestamp NULL YES 0000-00-00 00:00:00 # +date_field char(10) latin1_swedish_ci YES NULL # +time_field time NULL YES NULL # +date_time datetime NULL YES NULL # +new_blob_col varchar(20) latin1_swedish_ci YES NULL # +tinyblob_col tinyblob NULL YES NULL # +mediumblob_col mediumblob NULL NO # +options enum('one','two','tree') latin1_swedish_ci NO one # +flags set('one','two','tree') latin1_swedish_ci NO # +new_field char(10) latin1_swedish_ci NO new # select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.time_field<>t2.time_field and (t1.time_field is not null or t2.time_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.new_blob_col<>t2.new_blob_col and (t1.new_blob_col is not null or t2.new_blob_col is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)) or (t1.new_field<>t2.new_field and (t1.new_field is not null or t2.new_field is not null))); auto auto 16 16 @@ -268,15 +270,15 @@ create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, 'a' as t2, repeat('a',256) as t3, binary repeat('b',256) as t4, repeat('a',4096) as t5, binary repeat('b',4096) as t6, '' as t7, binary '' as t8 from t1; show full columns from t2; Field Type Collation Null Key Default Extra Privileges Comment -auto bigint(17) unsigned NULL NO PRI 0 select,insert,update,references -t1 bigint(1) NULL NO 0 select,insert,update,references -t2 varchar(1) latin1_swedish_ci NO select,insert,update,references -t3 varchar(256) latin1_swedish_ci NO select,insert,update,references -t4 varbinary(256) NULL NO select,insert,update,references -t5 longtext latin1_swedish_ci NO select,insert,update,references -t6 longblob NULL NO select,insert,update,references -t7 char(0) latin1_swedish_ci NO select,insert,update,references -t8 binary(0) NULL NO select,insert,update,references +auto int(6) unsigned NULL NO PRI 0 # +t1 bigint(1) NULL NO 0 # +t2 varchar(1) latin1_swedish_ci NO # +t3 varchar(256) latin1_swedish_ci NO # +t4 varbinary(256) NULL NO # +t5 longtext latin1_swedish_ci NO # +t6 longblob NULL NO # +t7 char(0) latin1_swedish_ci NO # +t8 binary(0) NULL NO # select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2; t1 t2 length(t3) length(t4) length(t5) length(t6) t7 t8 1 a 256 256 4096 4096 @@ -295,9 +297,9 @@ create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1, t2; show full columns from t3; Field Type Collation Null Key Default Extra Privileges Comment -c1 int(11) NULL YES NULL select,insert,update,references -c2 int(11) NULL YES NULL select,insert,update,references -const bigint(1) NULL NO 0 select,insert,update,references +c1 int(11) NULL YES NULL # +c2 int(11) NULL YES NULL # +const bigint(1) NULL NO 0 # drop table t1,t2,t3; create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield)); drop table t1; --- 1.8/mysql-test/t/limit.test 2005-01-13 19:14:57 -08:00 +++ 1.9/mysql-test/t/limit.test 2005-04-05 19:45:08 -07:00 @@ -49,3 +49,13 @@ DELETE FROM t1 WHERE id2 = 0 ORDER BY id desc LIMIT 1; SELECT * FROM t1; DROP TABLE t1; + +# +# Bug#8023 - limit on UNION with from DUAL, causes syntax error +# +create table t1 (a integer); +insert into t1 values (1); +# both queries must return one row +select 1 as a from t1 union all select 1 from dual limit 1; +(select 1 as a from t1) union all (select 1 from dual) limit 1; +drop table t1; --- 1.31/mysql-test/t/order_by.test 2005-01-13 19:17:13 -08:00 +++ 1.32/mysql-test/t/order_by.test 2005-04-05 19:45:08 -07:00 @@ -506,3 +506,46 @@ select a, b from t1 group by a, b order by sum(c); drop table t1; +# +# Bug #7331 +# + +create table t1 ( + `sid` decimal(8,0) default null, + `wnid` varchar(11) not null default '', + key `wnid14` (`wnid`(4)), + key `wnid` (`wnid`) +) engine=myisam default charset=latin1; + +insert into t1 (`sid`, `wnid`) values +('10100','01019000000'),('37986','01019000000'),('37987','01019010000'), +('39560','01019090000'),('37989','01019000000'),('37990','01019011000'), +('37991','01019011000'),('37992','01019019000'),('37993','01019030000'), +('37994','01019090000'),('475','02070000000'),('25253','02071100000'), +('25255','02071100000'),('25256','02071110000'),('25258','02071130000'), +('25259','02071190000'),('25260','02071200000'),('25261','02071210000'), +('25262','02071290000'),('25263','02071300000'),('25264','02071310000'), +('25265','02071310000'),('25266','02071320000'),('25267','02071320000'), +('25269','02071330000'),('25270','02071340000'),('25271','02071350000'), +('25272','02071360000'),('25273','02071370000'),('25281','02071391000'), +('25282','02071391000'),('25283','02071399000'),('25284','02071400000'), +('25285','02071410000'),('25286','02071410000'),('25287','02071420000'), +('25288','02071420000'),('25291','02071430000'),('25290','02071440000'), +('25292','02071450000'),('25293','02071460000'),('25294','02071470000'), +('25295','02071491000'),('25296','02071491000'),('25297','02071499000'); + +explain select * from t1 where wnid like '0101%' order by wnid; + +select * from t1 where wnid like '0101%' order by wnid; + +drop table t1; + +# +# Bug #7672 - a wrong result for a select query in braces followed by order by +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (2), (1), (1), (2), (1); +SELECT a FROM t1 ORDER BY a; +(SELECT a FROM t1) ORDER BY a; +DROP TABLE t1; --- 1.46/mysql-test/t/show_check.test 2005-04-01 19:02:07 -08:00 +++ 1.47/mysql-test/t/show_check.test 2005-04-05 19:45:08 -07:00 @@ -106,7 +106,7 @@ # Do a create table that tries to cover all types and options # create table t1 ( -type_bool bool not null, +type_bool bool not null default false, type_tiny tinyint not null auto_increment primary key, type_short smallint(3), type_mediumint mediumint, @@ -117,9 +117,9 @@ type_char char(2), type_varchar varchar(10), type_timestamp timestamp not null, -type_date date not null, -type_time time not null, -type_datetime datetime not null, +type_date date not null default '0000-00-00', +type_time time not null default '00:00:00', +type_datetime datetime not null default '0000-00-00 00:00:00', type_year year, type_enum enum ('red', 'green', 'blue'), type_set enum ('red', 'green', 'blue'), --- 1.26/mysql-test/t/type_blob.test 2005-01-13 19:21:52 -08:00 +++ 1.27/mysql-test/t/type_blob.test 2005-04-05 19:45:08 -07:00 @@ -81,8 +81,11 @@ update t1 set c="",b=null where c="1"; lock tables t1 READ; +# We mask out the Privileges column because it differs for embedded server +--replace_column 8 # show full fields from t1; lock tables t1 WRITE; +--replace_column 8 # show full fields from t1; unlock tables; @@ -303,18 +306,25 @@ create table t1 (id integer auto_increment unique,imagem LONGBLOB not null default ''); insert into t1 (id) values (1); -select - charset(load_file('../../std_data/words.dat')), - collation(load_file('../../std_data/words.dat')), - coercibility(load_file('../../std_data/words.dat')); -explain extended select - charset(load_file('../../std_data/words.dat')), - collation(load_file('../../std_data/words.dat')), - coercibility(load_file('../../std_data/words.dat')); -update t1 set imagem=load_file('../../std_data/words.dat') where id=1; +# We have to clean up the path in the results for safe comparison +--replace_result $MYSQL_TEST_DIR ../.. +eval select + charset(load_file('$MYSQL_TEST_DIR/std_data/words.dat')), + collation(load_file('$MYSQL_TEST_DIR/std_data/words.dat')), + coercibility(load_file('$MYSQL_TEST_DIR/std_data/words.dat')); +--replace_result $MYSQL_TEST_DIR ../.. +eval explain extended select + charset(load_file('$MYSQL_TEST_DIR/std_data/words.dat')), + collation(load_file('$MYSQL_TEST_DIR/std_data/words.dat')), + coercibility(load_file('$MYSQL_TEST_DIR/std_data/words.dat')); +--replace_result $MYSQL_TEST_DIR ../.. +eval update t1 set imagem=load_file('$MYSQL_TEST_DIR/std_data/words.dat') where id=1; select if(imagem is null, "ERROR", "OK"),length(imagem) from t1 where id = 1; drop table t1; -create table t1 select load_file('../../std_data/words.dat'); +--replace_result $MYSQL_TEST_DIR ../.. +eval create table t1 select load_file('$MYSQL_TEST_DIR/std_data/words.dat') l; +# We mask out the Privileges column because it differs for embedded server +--replace_column 8 # show full fields from t1; drop table t1; --- 1.11/mysql-test/t/type_ranges.test 2005-01-13 19:22:55 -08:00 +++ 1.12/mysql-test/t/type_ranges.test 2005-04-05 19:45:08 -07:00 @@ -46,6 +46,8 @@ KEY (options,flags) ); +# We mask out the Privileges column because it differs with embedded server +--replace_column 8 # show full fields from t1; show keys from t1; @@ -69,7 +71,7 @@ add new_field char(10) default "new" not null, change blob_col new_blob_col varchar(20), change date_field date_field char(10), -alter column string set default "new default", +alter column string set default "newdefault", alter short drop default, DROP INDEX utiny, DROP INDEX ushort, @@ -120,7 +122,10 @@ create table t2 select * from t1; update t2 set string="changed" where auto=16; +# We mask out the Privileges column because it differs with embedded server +--replace_column 8 # show full columns from t1; +--replace_column 8 # show full columns from t2; select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.time_field<>t2.time_field and (t1.time_field is not null or t2.time_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.new_blob_col<>t2.new_blob_col and (t1.new_blob_col is not null or t2.new_blob_col is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)) or (t1.new_field<>t2.new_field and (t1.new_field is not null or t2.new_field is not null))); select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and not (t1.string<=>t2.string and t1.tiny<=>t2.tiny and t1.short<=>t2.short and t1.medium<=>t2.medium and t1.long_int<=>t2.long_int and t1.longlong<=>t2.longlong and t1.real_float<=>t2.real_float and t1.real_double<=>t2.real_double and t1.utiny<=>t2.utiny and t1.ushort<=>t2.ushort and t1.umedium<=>t2.umedium and t1.ulong<=>t2.ulong and t1.ulonglong<=>t2.ulonglong and t1.time_stamp<=>t2.time_stamp and t1.date_field<=>t2.date_field and t1.time_field<=>t2.time_field and t1.date_time<=>t2.date_time and t1.new_blob_col<=>t2.new_blob_col and t1.tinyblob_col<=>t2.tinyblob_col and t1.mediumblob_col<=>t2.mediumblob_col and t1.options<=>t2.options and t1.flags<=>t2.flags and t1.new_field<=>t2.new_field); @@ -128,6 +133,8 @@ drop table t2; create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, 'a' as t2, repeat('a',256) as t3, binary repeat('b',256) as t4, repeat('a',4096) as t5, binary repeat('b',4096) as t6, '' as t7, binary '' as t8 from t1; +# We mask out the Privileges column because it differs with embedded server +--replace_column 8 # show full columns from t2; select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2; drop table t1,t2; @@ -138,6 +145,8 @@ --error 1060 create table t3 select * from t1, t2; # Should give an error create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1, t2; +# We mask out the Privileges column because it differs with embedded server +--replace_column 8 # show full columns from t3; drop table t1,t2,t3; --- 1.22/mysql-test/r/ctype_latin1_de.result 2005-02-18 04:14:26 -08:00 +++ 1.23/mysql-test/r/ctype_latin1_de.result 2005-04-05 19:45:07 -07:00 @@ -220,12 +220,12 @@ a test drop table t1; -create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word)); +create table t1 (word varchar(255) not null, word2 varchar(255) not null default '', index(word)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `word` varchar(255) collate latin1_german2_ci NOT NULL, - `word2` varchar(255) collate latin1_german2_ci NOT NULL, + `word2` varchar(255) collate latin1_german2_ci NOT NULL default '', KEY `word` (`word`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae'); --- 1.20/mysql-test/t/ctype_latin1_de.test 2005-02-18 04:10:05 -08:00 +++ 1.21/mysql-test/t/ctype_latin1_de.test 2005-04-05 19:45:08 -07:00 @@ -66,7 +66,7 @@ # # The below checks both binary and character comparisons. # -create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word)); +create table t1 (word varchar(255) not null, word2 varchar(255) not null default '', index(word)); show create table t1; insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae'); update t1 set word2=word; --- 1.38/mysql-test/r/grant.result 2005-01-14 11:29:44 -08:00 +++ 1.39/mysql-test/r/grant.result 2005-04-05 19:45:07 -07:00 @@ -10,8 +10,8 @@ GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' grant delete on mysqltest.* to mysqltest_1@localhost; select * from mysql.user where user="mysqltest_1"; -Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections -localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N N N N N SPECIFIED EDH-RSA-DES-CBC3-SHA 0 0 0 0 +Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections +localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N N N N N N SPECIFIED EDH-RSA-DES-CBC3-SHA 0 0 0 0 show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' @@ -41,15 +41,15 @@ flush privileges; grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10; select * from mysql.user where user="mysqltest_1"; -Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections -localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N N N N N 10 0 0 0 +Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections +localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N N N N N N 10 0 0 0 show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30; select * from mysql.user where user="mysqltest_1"; -Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections -localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N N N N N 10 20 30 0 +Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections +localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N N N N N N 10 20 30 0 show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30 @@ -444,6 +444,7 @@ Create routine Functions,Procedures To use CREATE FUNCTION/PROCEDURE Create temporary tables Databases To use CREATE TEMPORARY TABLE Create view Tables To create new views +Create user Server Admin To create new users Delete Tables To delete existing rows Drop Databases,Tables To drop databases, tables, and views Execute Functions,Procedures To execute stored routines @@ -464,3 +465,11 @@ Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. Update Tables To update existing rows Usage Server Admin No privileges - allow connect only +create database mysqltest; +create table mysqltest.t1 (a int,b int,c int); +grant all on mysqltest.t1 to mysqltest_1@localhost; +alter table t1 rename t2; +ERROR 42000: INSERT,CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2' +revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; +delete from mysql.user where user=_binary'mysqltest_1'; +drop database mysqltest; --- 1.14/mysql-test/r/strict.result 2005-01-14 11:30:12 -08:00 +++ 1.15/mysql-test/r/strict.result 2005-04-05 19:45:08 -07:00 @@ -1,7 +1,8 @@ +set @org_mode=@@sql_mode; set @@sql_mode='ansi,traditional'; select @@sql_mode; @@sql_mode -REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER DROP TABLE IF EXISTS t1; CREATE TABLE t1 (col1 date); INSERT INTO t1 VALUES('2004-01-01'),('0000-10-31'),('2004-02-29'); @@ -129,6 +130,8 @@ ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1 INSERT INTO t1 VALUES('2004-10-32 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2003-02-29 15:30:00'); +ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col1' at row 1 INSERT INTO t1 VALUES('2004-13-15 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1 INSERT INTO t1 VALUES('0000-00-00 15:30:00'); @@ -153,6 +156,8 @@ ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1 INSERT INTO t1 VALUES('2004-10-32 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2003-02-29 15:30:00'); +ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col1' at row 1 INSERT INTO t1 VALUES('2004-13-15 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1 INSERT INTO t1 VALUES('2004-02-29 25:30:00'); @@ -194,6 +199,111 @@ 0000-00-00 00:00:00 0000-00-00 00:00:00 DROP TABLE t1; +CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); +INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y')); +INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); +INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect date value: '2004-00-31 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect date value: '2004-10-00 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect date value: '2004-09-31 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_time +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect date value: '2003-02-29 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); +ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_time +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); +ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect datetime value: '2004-00-31 15:30:00' for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_time +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); +ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_time +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); +ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col2' at row 1 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect datetime value: '2004-00-31 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_time +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); +ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_time +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); +ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col3' at row 1 +drop table t1; +CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); +INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE)); +INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); +INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); +INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE)); +INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE)); +ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE)); +ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' +INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); +INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); +ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); +ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' +INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); +ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); +ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); +ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' +drop table t1; +CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); +INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE)); +INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); +INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); +INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE)); +INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE)); +ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE)); +ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' +INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); +INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); +ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); +ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' +INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); +ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); +ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); +ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' +drop table t1; CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED); INSERT INTO t1 VALUES(-128,0),(0,0),(127,255),('-128','0'),('0','0'),('127','255'),(-128.0,0.0),(0.0,0.0),(127.0,255.0); SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2; @@ -531,7 +641,6 @@ Warning 1264 Out of range value adjusted for column 'col2' at row 1 Warning 1264 Out of range value adjusted for column 'col1' at row 2 Warning 1264 Out of range value adjusted for column 'col2' at row 2 -Warning 1264 Out of range value adjusted for column 'col2' at row 2 UPDATE IGNORE t1 SET col2=1/NULL where col1=0; SELECT * FROM t1; col1 col2 @@ -556,6 +665,7 @@ INSERT INTO t1 VALUES('-9223372036854775808','0'),('9223372036854775807','18446744073709551615'); INSERT INTO t1 VALUES(-9223372036854774000.0,0.0),(9223372036854775700.0,1844674407370954000.0); INSERT INTO t1 (col1) VALUES(-9223372036854775809); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(9223372036854775808); INSERT INTO t1 (col2) VALUES(-1); INSERT INTO t1 (col2) VALUES(18446744073709551616); @@ -595,6 +705,7 @@ Error 1365 Division by 0 INSERT IGNORE INTO t1 VALUES(-9223372036854775809,-1),(9223372036854775808,18446744073709551616); Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 Warning 1264 Out of range value adjusted for column 'col2' at row 2 INSERT IGNORE INTO t1 VALUES('-9223372036854775809','-1'),('9223372036854775808','18446744073709551616'); Warnings: @@ -616,9 +727,8 @@ 9223372036854775807 18446744073709551615 -9223372036854775808 0 9223372036854775807 18446744073709551615 --9223372036854773760 0 -9223372036854775807 1844674407370953984 --9223372036854775808 NULL +-9223372036854774000 0 +9223372036854775700 1844674407370954000 -9223372036854775808 NULL NULL 18446744073709551615 2 NULL @@ -632,12 +742,17 @@ DROP TABLE t1; CREATE TABLE t1 (col1 NUMERIC(4,2)); INSERT INTO t1 VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01); +Warnings: +Note 1265 Data truncated for column 'col1' at row 2 +Note 1265 Data truncated for column 'col1' at row 5 INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01'); Warnings: Note 1265 Data truncated for column 'col1' at row 2 Note 1265 Data truncated for column 'col1' at row 4 INSERT INTO t1 VALUES (101.55); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 INSERT INTO t1 VALUES (101); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 INSERT INTO t1 VALUES (-101.55); ERROR 22003: Out of range value adjusted for column 'col1' at row 1 INSERT INTO t1 VALUES (1010.55); @@ -645,7 +760,9 @@ INSERT INTO t1 VALUES (1010); ERROR 22003: Out of range value adjusted for column 'col1' at row 1 INSERT INTO t1 VALUES ('101.55'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 INSERT INTO t1 VALUES ('101'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 INSERT INTO t1 VALUES ('-101.55'); ERROR 22003: Out of range value adjusted for column 'col1' at row 1 INSERT INTO t1 VALUES ('-1010.55'); @@ -661,14 +778,15 @@ UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); -ERROR 01000: Data truncated for column 'col1' at row 1 +ERROR HY000: Incorrect decimal value: '' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); -ERROR 01000: Data truncated for column 'col1' at row 1 +ERROR HY000: Incorrect decimal value: 'a59b' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('1a'); -ERROR 01000: Data truncated for column 'col1' at row 1 +Warnings: +Note 1265 Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); Warnings: -Warning 1265 Data truncated for column 'col1' at row 1 +Note 1265 Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 values (1/0); Warnings: Error 1365 Division by 0 @@ -695,22 +813,19 @@ 11.00 10.00 10.55 -10.55 --10.55 +10.56 -10.55 +-10.56 11.00 10.00 -101.55 -101.00 -101.55 -101.00 +1.00 2.00 NULL -999.99 +99.99 -99.99 -999.99 +99.99 -99.99 -999.99 +99.99 -99.99 DROP TABLE t1; CREATE TABLE t1 (col1 FLOAT, col2 FLOAT UNSIGNED); @@ -764,11 +879,11 @@ 3.40282e+38 0 DROP TABLE t1; CREATE TABLE t1 (col1 DOUBLE PRECISION, col2 DOUBLE PRECISION UNSIGNED); -INSERT INTO t1 VALUES (-2.2E-307,0),(+1.7E+308,+1.7E+308); -INSERT INTO t1 VALUES ('-2.2E-307',0),('+1.7E+308','+1.7E+308'); +INSERT INTO t1 VALUES (-2.2E-307,0),(2E-307,0),(+1.7E+308,+1.7E+308); +INSERT INTO t1 VALUES ('-2.2E-307',0),('-2E-307',0),('+1.7E+308','+1.7E+308'); INSERT INTO t1 (col1) VALUES (-2.2E-330); INSERT INTO t1 (col1) VALUES (+1.7E+309); -ERROR 22007: Illegal double '1.7E+309' value found during parsing +Got one of the listed errors INSERT INTO t1 (col2) VALUES (-1.1E-3); ERROR 22003: Out of range value adjusted for column 'col2' at row 1 INSERT INTO t1 (col1) VALUES ('+1.8E+309'); @@ -776,7 +891,7 @@ INSERT INTO t1 (col2) VALUES ('-1.2E-3'); ERROR 22003: Out of range value adjusted for column 'col2' at row 1 UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0; -ERROR 22003: Out of range value adjusted for column 'col1' at row 2 +ERROR 22003: Out of range value adjusted for column 'col1' at row 3 UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0; ERROR 22012: Division by 0 UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0; @@ -802,9 +917,11 @@ Warning 1264 Out of range value adjusted for column 'col2' at row 1 SELECT * FROM t1; col1 col2 --2.2e-307 0 +0 0 +1e-303 0 1.7e+308 1.7e+308 --2.2e-307 0 +0 0 +-2e-307 0 1.7e+308 1.7e+308 0 NULL 2 NULL @@ -814,16 +931,16 @@ CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6)); INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello ', 'hello '); INSERT INTO t1 (col1) VALUES ('hellobob'); -ERROR 01000: Data truncated for column 'col1' at row 1 +ERROR 22001: Data too long for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES ('hellobob'); -ERROR 01000: Data truncated for column 'col2' at row 1 +ERROR 22001: Data too long for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES ('hello '); Warnings: Note 1265 Data truncated for column 'col2' at row 1 UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he'; -ERROR 01000: Data truncated for column 'col1' at row 2 +ERROR 22001: Data too long for column 'col1' at row 2 UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he'; -ERROR 01000: Data truncated for column 'col2' at row 2 +ERROR 22001: Data too long for column 'col2' at row 2 INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 @@ -966,3 +1083,152 @@ Warnings: Warning 1364 Field 'i' doesn't have a default value DROP TABLE t1; +set sql_mode='traditional'; +create table t1 (charcol char(255), varcharcol varchar(255), +binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext, +tinyblobcol tinyblob); +insert into t1 (charcol) values (repeat('x',256)); +ERROR 22001: Data too long for column 'charcol' at row 1 +insert into t1 (varcharcol) values (repeat('x',256)); +ERROR 22001: Data too long for column 'varcharcol' at row 1 +insert into t1 (binarycol) values (repeat('x',256)); +ERROR 22001: Data too long for column 'binarycol' at row 1 +insert into t1 (varbinarycol) values (repeat('x',256)); +ERROR 22001: Data too long for column 'varbinarycol' at row 1 +insert into t1 (tinytextcol) values (repeat('x',256)); +ERROR 22001: Data too long for column 'tinytextcol' at row 1 +insert into t1 (tinyblobcol) values (repeat('x',256)); +ERROR 22001: Data too long for column 'tinyblobcol' at row 1 +select * from t1; +charcol varcharcol binarycol varbinarycol tinytextcol tinyblobcol +drop table t1; +set sql_mode='traditional'; +create table t1 (col1 datetime); +insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i')); +ERROR 22007: Truncated incorrect datetime value: '31.10.2004 15.30 abc' +insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_time +insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r')); +ERROR HY000: Incorrect time value: '22:22:33 AM' for function str_to_time +insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T')); +ERROR HY000: Incorrect time value: 'abc' for function str_to_time +set sql_mode=''; +insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i')); +Warnings: +Warning 1292 Truncated incorrect datetime value: '31.10.2004 15.30 abc' +insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +Warnings: +Error 1411 Incorrect datetime value: '32.10.2004 15.30' for function str_to_time +insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r')); +Warnings: +Error 1411 Incorrect time value: '22:22:33 AM' for function str_to_time +insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T')); +Warnings: +Error 1411 Incorrect time value: 'abc' for function str_to_time +insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i')); +insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r')); +insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T')); +select * from t1; +col1 +2004-10-31 15:30:00 +NULL +NULL +NULL +2004-10-31 15:30:00 +2004-12-12 11:22:33 +2004-12-12 10:22:59 +set sql_mode='traditional'; +select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL; +count(*) +7 +Warnings: +Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_time +Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_time +Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_time +drop table t1; +create table t1 (col1 char(3), col2 integer); +insert into t1 (col1) values (cast(1000 as char(3))); +ERROR 22007: Truncated incorrect CHAR(3) value: '1000' +insert into t1 (col1) values (cast(1000E+0 as char(3))); +ERROR 22007: Truncated incorrect CHAR(3) value: '1000' +insert into t1 (col1) values (cast(1000.0 as char(3))); +ERROR 22007: Truncated incorrect CHAR(3) value: '1000.0' +insert into t1 (col2) values (cast('abc' as signed integer)); +ERROR 22007: Truncated incorrect INTEGER value: 'abc' +insert into t1 (col2) values (10E+0 + 'a'); +ERROR 22007: Truncated incorrect DOUBLE value: 'a' +insert into t1 (col2) values (cast('10a' as unsigned integer)); +ERROR 22007: Truncated incorrect INTEGER value: '10a' +insert into t1 (col2) values (cast('10' as unsigned integer)); +insert into t1 (col2) values (cast('10' as signed integer)); +insert into t1 (col2) values (10E+0 + '0 '); +select * from t1; +col1 col2 +NULL 10 +NULL 10 +NULL 10 +drop table t1; +create table t1 (col1 date, col2 datetime, col3 timestamp); +insert into t1 values (0,0,0); +ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1 +insert into t1 values (0.0,0.0,0.0); +ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1 +insert into t1 (col1) values (convert('0000-00-00',date)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' +insert into t1 (col1) values (cast('0000-00-00' as date)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' +set sql_mode='no_zero_date'; +insert into t1 values (0,0,0); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1265 Data truncated for column 'col3' at row 1 +insert into t1 values (0.0,0.0,0.0); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1265 Data truncated for column 'col3' at row 1 +drop table t1; +set sql_mode='traditional'; +create table t1 (col1 date); +insert ignore into t1 values ('0000-00-00'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +insert into t1 select * from t1; +ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 +insert ignore into t1 values ('0000-00-00'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +insert ignore into t1 (col1) values (cast('0000-00-00' as date)); +Warnings: +Warning 1292 Truncated incorrect datetime value: '0000-00-00' +insert into t1 select * from t1; +ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 +alter table t1 modify col1 datetime; +ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col1' at row 1 +alter ignore table t1 modify col1 datetime; +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +insert into t1 select * from t1; +ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1 +select * from t1; +col1 +0000-00-00 00:00:00 +0000-00-00 00:00:00 +NULL +drop table t1; +create table t1 (col1 tinyint); +drop procedure if exists t1; +Warnings: +Note 1305 PROCEDURE t1 does not exist +create procedure t1 () begin declare exit handler for sqlexception +select'a'; insert into t1 values (200); end;| +call t1(); +a +a +select * from t1; +col1 +drop procedure t1; +drop table t1; +set sql_mode=@org_mode; --- 1.10/mysql-test/t/strict.test 2005-01-13 17:52:45 -08:00 +++ 1.11/mysql-test/t/strict.test 2005-04-05 19:45:34 -07:00 @@ -2,6 +2,7 @@ -- source include/have_innodb.inc +set @org_mode=@@sql_mode; set @@sql_mode='ansi,traditional'; select @@sql_mode; @@ -13,6 +14,9 @@ CREATE TABLE t1 (col1 date); INSERT INTO t1 VALUES('2004-01-01'),('0000-10-31'),('2004-02-29'); + +# All test cases expected to fail should return +# SQLSTATE 22007 <invalid date value> --error 1292 INSERT INTO t1 VALUES('2004-0-31'); --error 1292 @@ -94,6 +98,9 @@ CREATE TABLE t1 (col1 datetime); INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('0000-10-31 15:30:00'),('2004-02-29 15:30:00'); + +# All test cases expected to fail should return +# SQLSTATE 22007 <invalid datetime value> --error 1292 INSERT INTO t1 VALUES('2004-0-31 15:30:00'); --error 1292 @@ -103,6 +110,8 @@ --error 1292 INSERT INTO t1 VALUES('2004-10-32 15:30:00'); --error 1292 +INSERT INTO t1 VALUES('2003-02-29 15:30:00'); +--error 1292 INSERT INTO t1 VALUES('2004-13-15 15:30:00'); --error 1292 INSERT INTO t1 VALUES('0000-00-00 15:30:00'); @@ -116,6 +125,9 @@ CREATE TABLE t1 (col1 timestamp); INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00'); + +# All test cases expected to fail should return +# SQLSTATE 22007 <invalid datetime value> # Standard says we should return ok, but we can't as this is out of range --error 1292 INSERT INTO t1 VALUES('0000-10-31 15:30:00'); @@ -128,6 +140,8 @@ --error 1292 INSERT INTO t1 VALUES('2004-10-32 15:30:00'); --error 1292 +INSERT INTO t1 VALUES('2003-02-29 15:30:00'); +--error 1292 INSERT INTO t1 VALUES('2004-13-15 15:30:00'); --error 1292 INSERT INTO t1 VALUES('2004-02-29 25:30:00'); @@ -163,6 +177,263 @@ SELECT * FROM t1; DROP TABLE t1; + +#### Test INSERT with STR_TO_DATE into DATE/DATETIME/TIMESTAMP + +CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); + +INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y')); +INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); +INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); + +## Test INSERT with STR_TO_DATE into DATE +# All test cases expected to fail should return +# SQLSTATE 22007 <invalid date value> + +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); + +--error 1292 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); +--error 1292 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); +--error 1292 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); +--error 1411 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +--error 1292 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); +--error 1411 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); +--error 1292 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); + +## Test INSERT with STR_TO_DATE into DATETIME +# All test cases expected to fail should return +# SQLSTATE 22007 <invalid datetime value> + +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); + +--error 1292 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); +--error 1292 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); +--error 1292 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); +--error 1411 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +--error 1292 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); +--error 1411 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); +--error 1292 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); + +## Test INSERT with STR_TO_DATE into TIMESTAMP +# All test cases expected to fail should return +# SQLSTATE 22007 <invalid datetime value> + +--error 1292 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); +--error 1292 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); +--error 1292 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); +--error 1292 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); +--error 1411 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +--error 1292 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); +--error 1411 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); +--error 1292 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); + +drop table t1; + + +#### Test INSERT with CAST AS DATE/DATETIME into DATE/DATETIME/TIMESTAMP + +CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); + +INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE)); +INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); +INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); + + +## Test INSERT with CAST AS DATE into DATE +# All test cases expected to fail should return +# SQLSTATE 22007 <invalid date value> +INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE)); + +--error 1292 +INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE)); +--error 1292 +INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE)); + +# deactivated because of Bug#8294 +# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE +# --error 1292 +# INSERT INTO t1 (col1) VALUES(CAST('2004-9-31' AS DATE)); +# --error 1292 +# INSERT INTO t1 (col1) VALUES(CAST('2004-10-32' AS DATE)); +# --error 1292 +# INSERT INTO t1 (col1) VALUES(CAST('2003-02-29' AS DATE)); +# --error 1292 +# INSERT INTO t1 (col1) VALUES(CAST('2004-13-15' AS DATE)); + +# deactivated because of Bug#6145 +# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values +--error 1292 +INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE)); + +## Test INSERT with CAST AS DATETIME into DATETIME +# All test cases expected to fail should return +# SQLSTATE 22007 <invalid datetime value> +INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); + +--error 1292 +INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); +--error 1292 +INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); + +# deactivated because of Bug#8294 +# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE +#--error 1292 +#INSERT INTO t1 (col2) VALUES(CAST('2004-9-31 15:30' AS DATETIME)); +#--error 1292 +#INSERT INTO t1 (col2) VALUES(CAST('2004-10-32 15:30' AS DATETIME)); +#--error 1292 +#INSERT INTO t1 (col2) VALUES(CAST('2003-02-29 15:30' AS DATETIME)); +#--error 1292 +#INSERT INTO t1 (col2) VALUES(CAST('2004-13-15 15:30' AS DATETIME)); + +# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values +--error 1292 +INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME)); + +## Test INSERT with CAST AS DATETIME into TIMESTAMP +# All test cases expected to fail should return +# SQLSTATE 22007 <invalid datetime value> +!$1292 +INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); +-- should return OK +-- We accept this to be a failure + +--error 1292 +INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); +--error 1292 +INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); +-- should return SQLSTATE 22007 <invalid datetime value> + +# deactivated because of Bug#8294 +# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE +#--error 1292 +#INSERT INTO t1 (col3) VALUES(CAST('2004-9-31 15:30' AS DATETIME)); +#--error 1292 +#INSERT INTO t1 (col3) VALUES(CAST('2004-10-32 15:30' AS DATETIME)); +#--error 1292 +#INSERT INTO t1 (col3) VALUES(CAST('2003-02-29 15:30' AS DATETIME)); +#--error 1292 +#INSERT INTO t1 (col3) VALUES(CAST('2004-13-15 15:30' AS DATETIME)); + +# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values +--error 1292 +INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME)); + +drop table t1; + + +#### Test INSERT with CONVERT to DATE/DATETIME into DATE/DATETIME/TIMESTAMP + +CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); + +INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE)); +INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); +INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); + + +## Test INSERT with CONVERT to DATE into DATE +# All test cases expected to fail should return +# SQLSTATE 22007 <invalid date value> +INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE)); + +--error 1292 +INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE)); +--error 1292 +INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE)); + +# deactivated because of Bug#8294 +# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE +#--error 1292 +#INSERT INTO t1 (col1) VALUES(CONVERT('2004-9-31' , DATE)); +#--error 1292 +#INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-32' , DATE)); +#--error 1292 +#INSERT INTO t1 (col1) VALUES(CONVERT('2003-02-29' , DATE)); +#--error 1292 +#INSERT INTO t1 (col1) VALUES(CONVERT('2004-13-15',DATE)); + +# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values +--error 1292 +INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE)); + +## Test INSERT with CONVERT to DATETIME into DATETIME +# All test cases expected to fail should return +# SQLSTATE 22007 <invalid datetime value> +INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); + +--error 1292 +INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); +--error 1292 +INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); + +# deactivated because of Bug#8294 +# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE +#--error 1292 +#INSERT INTO t1 (col2) VALUES(CONVERT('2004-9-31 15:30',DATETIME)); +#--error 1292 +#INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-32 15:30',DATETIME)); +#--error 1292 +#INSERT INTO t1 (col2) VALUES(CONVERT('2003-02-29 15:30',DATETIME)); +#--error 1292 +#INSERT INTO t1 (col2) VALUES(CONVERT('2004-13-15 15:30',DATETIME)); + +# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values +--error 1292 +INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME)); + +## Test INSERT with CONVERT to DATETIME into DATETIME +# All test cases expected to fail should return +# SQLSTATE 22007 <invalid datetime value> +!$1292 +INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); +-- should return OK +-- We accept this to be a failure + +--error 1292 +INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); +--error 1292 +INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); + +# deactivated because of Bug#8294 +# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE +#--error 1292 +#INSERT INTO t1 (col3) VALUES(CONVERT('2004-9-31 15:30',DATETIME)); +#--error 1292 +#INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-32 15:30',DATETIME)); +#--error 1292 +#INSERT INTO t1 (col3) VALUES(CONVERT('2003-02-29 15:30',DATETIME)); +#--error 1292 +#INSERT INTO t1 (col3) VALUES(CONVERT('2004-13-15 15:30',DATETIME)); + +# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values +--error 1292 +INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME)); + +drop table t1; + + # Test INSERT with TINYINT CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED); @@ -388,9 +659,7 @@ INSERT INTO t1 VALUES('-9223372036854775808','0'),('9223372036854775807','18446744073709551615'); INSERT INTO t1 VALUES(-9223372036854774000.0,0.0),(9223372036854775700.0,1844674407370954000.0); -# The following should give an error, but doesn't until we fix the interface -# for Field_longlong::store() - +-- error 1264 INSERT INTO t1 (col1) VALUES(-9223372036854775809); INSERT INTO t1 (col1) VALUES(9223372036854775808); INSERT INTO t1 (col2) VALUES(-1); @@ -449,7 +718,9 @@ -- The 2 following inserts should generate a warning, but doesn't yet -- because NUMERIC works like DECIMAL +--error 1264 INSERT INTO t1 VALUES (101.55); +--error 1264 INSERT INTO t1 VALUES (101); --error 1264 INSERT INTO t1 VALUES (-101.55); @@ -459,7 +730,9 @@ INSERT INTO t1 VALUES (1010); -- The 2 following inserts should generate a warning, but doesn't yet -- because NUMERIC works like DECIMAL +--error 1264 INSERT INTO t1 VALUES ('101.55'); +--error 1264 INSERT INTO t1 VALUES ('101'); --error 1264 INSERT INTO t1 VALUES ('-101.55'); @@ -475,11 +748,13 @@ UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; --error 1365 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; ---error 1265 +#--error 1265 +--error 1366 INSERT INTO t1 (col1) VALUES (''); ---error 1265 +#--error 1265 +--error 1366 INSERT INTO t1 (col1) VALUES ('a59b'); ---error 1265 +#--error 1265 INSERT INTO t1 (col1) VALUES ('1a'); INSERT IGNORE INTO t1 (col1) VALUES ('2a'); INSERT IGNORE INTO t1 values (1/0); @@ -527,11 +802,11 @@ # Test INSERT with DOUBLE CREATE TABLE t1 (col1 DOUBLE PRECISION, col2 DOUBLE PRECISION UNSIGNED); -INSERT INTO t1 VALUES (-2.2E-307,0),(+1.7E+308,+1.7E+308); -INSERT INTO t1 VALUES ('-2.2E-307',0),('+1.7E+308','+1.7E+308'); +INSERT INTO t1 VALUES (-2.2E-307,0),(2E-307,0),(+1.7E+308,+1.7E+308); +INSERT INTO t1 VALUES ('-2.2E-307',0),('-2E-307',0),('+1.7E+308','+1.7E+308'); # We don't give warnings for underflow INSERT INTO t1 (col1) VALUES (-2.2E-330); ---error 1367 +--error 1367,1264 INSERT INTO t1 (col1) VALUES (+1.7E+309); --error 1264 INSERT INTO t1 (col2) VALUES (-1.1E-3); @@ -565,14 +840,14 @@ CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6)); INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello ', 'hello '); ---error 1265 +--error 1406 INSERT INTO t1 (col1) VALUES ('hellobob'); ---error 1265 +--error 1406 INSERT INTO t1 (col2) VALUES ('hellobob'); INSERT INTO t1 (col2) VALUES ('hello '); ---error 1265 +--error 1406 UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he'; ---error 1265 +--error 1406 UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he'; INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob'); UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he'; @@ -638,6 +913,142 @@ INSERT IGNORE INTO t1 () values (); SELECT * FROM t1; DROP TABLE t1; + +# +# Bug #9029 Traditional: Wrong SQLSTATE returned for string truncation +# + +set sql_mode='traditional'; +create table t1 (charcol char(255), varcharcol varchar(255), +binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext, +tinyblobcol tinyblob); +--error 1406 +insert into t1 (charcol) values (repeat('x',256)); +--error 1406 +insert into t1 (varcharcol) values (repeat('x',256)); +--error 1406 +insert into t1 (binarycol) values (repeat('x',256)); +--error 1406 +insert into t1 (varbinarycol) values (repeat('x',256)); +--error 1406 +insert into t1 (tinytextcol) values (repeat('x',256)); +--error 1406 +insert into t1 (tinyblobcol) values (repeat('x',256)); +select * from t1; +drop table t1; + +# +# Bug #5902: STR_TO_DATE() didn't give errors in traditional mode +# + +set sql_mode='traditional'; +create table t1 (col1 datetime); +--error 1292 +insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i')); +--error 1411 +insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +--error 1411 +insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r')); +--error 1411 +insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T')); +set sql_mode=''; +insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i')); +insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r')); +insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T')); + +# Some correct values, just to test the functions +insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i')); +insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r')); +insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T')); + +select * from t1; + +# Check that select don't abort even in strict mode (for now) +set sql_mode='traditional'; + +--disable_ps_warnings +select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL; +--enable_ps_warnings + +drop table t1; + +# +# Check insert with wrong CAST() (Bug #5912) +# + +create table t1 (col1 char(3), col2 integer); +--error 1292 +insert into t1 (col1) values (cast(1000 as char(3))); +--error 1292 +insert into t1 (col1) values (cast(1000E+0 as char(3))); +--error 1292 +insert into t1 (col1) values (cast(1000.0 as char(3))); +--error 1292 +insert into t1 (col2) values (cast('abc' as signed integer)); +--error 1292 +insert into t1 (col2) values (10E+0 + 'a'); +--error 1292 +insert into t1 (col2) values (cast('10a' as unsigned integer)); +insert into t1 (col2) values (cast('10' as unsigned integer)); +insert into t1 (col2) values (cast('10' as signed integer)); +insert into t1 (col2) values (10E+0 + '0 '); +select * from t1; +drop table t1; + +# +# Zero dates using numbers was not checked properly (Bug #5933 & #6145) +# + +create table t1 (col1 date, col2 datetime, col3 timestamp); +--error 1292 +insert into t1 values (0,0,0); +--error 1292 +insert into t1 values (0.0,0.0,0.0); +--error 1292 +insert into t1 (col1) values (convert('0000-00-00',date)); +--error 1292 +insert into t1 (col1) values (cast('0000-00-00' as date)); + +set sql_mode='no_zero_date'; +insert into t1 values (0,0,0); +insert into t1 values (0.0,0.0,0.0); +drop table t1; +set sql_mode='traditional'; +create table t1 (col1 date); +insert ignore into t1 values ('0000-00-00'); +--error 1292 +insert into t1 select * from t1; +insert ignore into t1 values ('0000-00-00'); +insert ignore into t1 (col1) values (cast('0000-00-00' as date)); +--error 1292 +insert into t1 select * from t1; +--error 1292 +alter table t1 modify col1 datetime; +alter ignore table t1 modify col1 datetime; +--error 1292 +insert into t1 select * from t1; +select * from t1; +drop table t1; + +# +# Test of inserting an invalid value via a stored procedure (Bug #5907) +# +create table t1 (col1 tinyint); +drop procedure if exists t1; +delimiter |; +create procedure t1 () begin declare exit handler for sqlexception +select'a'; insert into t1 values (200); end;| +delimiter ;| +call t1(); +select * from t1; +drop procedure t1; +drop table t1; + +# +# Restore mode +# +set sql_mode=@org_mode; # Test fields with no default value that are NOT NULL (Bug #5986) SET @@sql_mode = 'traditional'; --- 1.103/mysql-test/r/sp.result 2005-01-14 11:30:08 -08:00 +++ 1.104/mysql-test/r/sp.result 2005-04-05 19:45:07 -07:00 @@ -237,6 +237,13 @@ drop procedure if exists sub1| create procedure sub1(id char(16), x int) insert into test.t1 values (id, x)| +drop procedure if exists sub2| +create procedure sub2(id char(16)) +begin +declare x int; +set x = (select sum(t.i) from test.t2 t); +insert into test.t1 values (id, x); +end| drop procedure if exists sub3| create function sub3(i int) returns int return i+1| @@ -244,16 +251,19 @@ call sub1("sub1b", (select max(i) from t2))| call sub1("sub1c", (select i,d from t2 limit 1))| call sub1("sub1d", (select 1 from (select 1) a))| +call sub2("sub2"); select * from t1| id data sub1a 7 sub1b 3 sub1c 1 sub1d 1 +sub2 6 select sub3((select max(i) from t2))| sub3((select max(i) from t2)) 4 drop procedure sub1| +drop procedure sub2| drop function sub3| delete from t2| drop procedure if exists a0| @@ -269,6 +279,7 @@ sub1b 3 sub1c 1 sub1d 1 +sub2 6 a0 2 a0 1 a0 0 @@ -656,7 +667,6 @@ drop table if exists t3| create table t3 ( s char(16), d int)| call into_test4()| -Warnings: select * from t3| s d into4 NULL @@ -693,7 +703,7 @@ create procedure create_select(x char(16), y int) begin insert into test.t1 values (x, y); -create table test.t3 select * from test.t1; +create temporary table test.t3 select * from test.t1; insert into test.t3 values (concat(x, "2"), y+2); end| drop table if exists t3| @@ -775,11 +785,11 @@ create procedure hndlr1(val int) begin declare x int default 0; -declare foo condition for 1146; +declare foo condition for 1136; declare bar condition for sqlstate '42S98'; # Just for testing syntax declare zip condition for sqlstate value '42S99'; # Just for testing syntax declare continue handler for foo set x = 1; -insert into test.t666 values ("hndlr1", val); # Non-existing table +insert into test.t1 values ("hndlr1", val, 2); # Too many values if (x) then insert into test.t1 values ("hndlr1", val); # This instead then end if; @@ -795,8 +805,8 @@ begin declare x int default 0; begin -declare exit handler for sqlstate '42S02' set x = 1; -insert into test.t666 values ("hndlr2", val); # Non-existing table +declare exit handler for sqlstate '21S01' set x = 1; +insert into test.t1 values ("hndlr2", val, 2); # Too many values end; insert into test.t1 values ("hndlr2", x); end| @@ -820,7 +830,7 @@ begin declare y int; set y = val + 10; -insert into test.t666 values ("hndlr3", y); # Non-existing table +insert into test.t1 values ("hndlr3", y, 2); # Too many values if x then insert into test.t1 values ("hndlr3", y); end if; @@ -952,7 +962,7 @@ return 42| show create function chistics| Function sql_mode Create Function -chistics CREATE FUNCTION `test`.`chistics`() RETURNS int +chistics CREATE FUNCTION `test`.`chistics`() RETURNS int(11) DETERMINISTIC SQL SECURITY INVOKER COMMENT 'Characteristics procedure test' @@ -965,7 +975,7 @@ comment 'Characteristics function test'| show create function chistics| Function sql_mode Create Function -chistics CREATE FUNCTION `test`.`chistics`() RETURNS int +chistics CREATE FUNCTION `test`.`chistics`() RETURNS int(11) NO SQL DETERMINISTIC SQL SECURITY INVOKER @@ -1046,6 +1056,493 @@ row_count() -1 drop procedure rc| +drop function if exists f0| +drop function if exists f1| +drop function if exists f2| +drop function if exists f3| +drop function if exists f4| +drop function if exists f5| +drop function if exists f6| +drop function if exists f7| +drop function if exists f8| +drop view if exists v0| +drop view if exists v1| +drop view if exists v2| +delete from t1| +delete from t2| +insert into t1 values ("a", 1), ("b", 2) | +insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) | +create function f1() returns int +return (select sum(data) from t1)| +select f1()| +f1() +3 +select id, f1() from t1| +id f1() +a 3 +b 3 +create function f2() returns int +return (select data from t1 where data <= (select sum(data) from t1) limit 1)| +select f2()| +f2() +1 +select id, f2() from t1| +id f2() +a 1 +b 1 +create function f3() returns int +begin +declare n int; +declare m int; +set n:= (select min(data) from t1); +set m:= (select max(data) from t1); +return n < m; +end| +select f3()| +f3() +1 +select id, f3() from t1| +id f3() +a 1 +b 1 +select f1(), f3()| +f1() f3() +3 1 +select id, f1(), f3() from t1| +id f1() f3() +a 3 1 +b 3 1 +create function f4() returns double +return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")| +select f4()| +f4() +2 +select s, f4() from t2| +s f4() +a 2 +b 2 +c 2 +create function f5(i int) returns int +begin +if i <= 0 then +return 0; +elseif i = 1 then +return (select count(*) from t1 where data = i); +else +return (select count(*) + f5( i - 1) from t1 where data = i); +end if; +end| +select f5(1)| +f5(1) +1 +select f5(2)| +ERROR HY000: Table 't1' was not locked with LOCK TABLES +create function f6() returns int +begin +declare n int; +set n:= f1(); +return (select count(*) from t1 where data <= f7() and data <= n); +end| +create function f7() returns int +return (select sum(data) from t1 where data <= f1())| +select f6()| +f6() +2 +select id, f6() from t1| +id f6() +a 2 +b 2 +create view v1 (a) as select f1()| +select * from v1| +a +3 +select id, a from t1, v1| +id a +a 3 +b 3 +select * from v1, v1 as v| +a a +3 3 +create view v2 (a) as select a*10 from v1| +select * from v2| +a +30 +select id, a from t1, v2| +id a +a 30 +b 30 +select * from v1, v2| +a a +3 30 +create function f8 () returns int +return (select count(*) from v2)| +select *, f8() from v1| +a f8() +3 1 +drop function f1| +select * from v1| +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) +create function f1() returns int +return (select sum(data) from t1) + (select sum(data) from v1)| +drop function f1| +create function f1() returns int +return (select sum(data) from t1)| +create function f0() returns int +return (select * from (select 100) as r)| +select f0()| +f0() +100 +select *, f0() from (select 1) as t| +1 f0() +1 100 +create view v0 as select f0()| +select * from v0| +f0() +100 +select *, f0() from v0| +f0() f0() +100 100 +lock tables t1 read, t1 as t11 read, mysql.proc read| +select f3()| +f3() +1 +select id, f3() from t1 as t11| +id f3() +a 1 +b 1 +select f0()| +f0() +100 +select * from v0| +f0() +100 +select *, f0() from v0, (select 123) as d1| +f0() 123 f0() +100 123 100 +select id, f3() from t1| +ERROR HY000: Table 't1' was not locked with LOCK TABLES +select f4()| +ERROR HY000: Table 't2' was not locked with LOCK TABLES +unlock tables| +lock tables v2 read, mysql.proc read| +select * from v2| +a +30 +select * from v1| +a +3 +select * from v1, v2| +ERROR HY000: Table 't1' was not locked with LOCK TABLES +select f4()| +ERROR HY000: Table 't2' was not locked with LOCK TABLES +unlock tables| +drop function f0| +drop function f1| +drop function f2| +drop function f3| +drop function f4| +drop function f5| +drop function f6| +drop function f7| +drop function f8| +drop view v0| +drop view v1| +drop view v2| +delete from t1 | +delete from t2 | +drop table if exists fac| +create table fac (n int unsigned not null primary key, f bigint unsigned)| +drop procedure if exists ifac| +create procedure ifac(n int unsigned) +begin +declare i int unsigned default 1; +if n > 20 then +set n = 20; # bigint overflow otherwise +end if; +while i <= n do +begin +insert into test.fac values (i, fac(i)); +set i = i + 1; +end; +end while; +end| +call ifac(20)| +select * from fac| +n f +1 1 +2 2 +3 6 +4 24 +5 120 +6 720 +7 5040 +8 40320 +9 362880 +10 3628800 +11 39916800 +12 479001600 +13 6227020800 +14 87178291200 +15 1307674368000 +16 20922789888000 +17 355687428096000 +18 6402373705728000 +19 121645100408832000 +20 2432902008176640000 +drop table fac| +show function status like '%f%'| +Db Name Type Definer Modified Created Security_type Comment +test fac FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER +drop procedure ifac| +drop function fac| +show function status like '%f%'| +Db Name Type Definer Modified Created Security_type Comment +drop table if exists primes| +create table primes ( +i int unsigned not null primary key, +p bigint unsigned not null +)| +insert into primes values +( 0, 3), ( 1, 5), ( 2, 7), ( 3, 11), ( 4, 13), +( 5, 17), ( 6, 19), ( 7, 23), ( 8, 29), ( 9, 31), +(10, 37), (11, 41), (12, 43), (13, 47), (14, 53), +(15, 59), (16, 61), (17, 67), (18, 71), (19, 73), +(20, 79), (21, 83), (22, 89), (23, 97), (24, 101), +(25, 103), (26, 107), (27, 109), (28, 113), (29, 127), +(30, 131), (31, 137), (32, 139), (33, 149), (34, 151), +(35, 157), (36, 163), (37, 167), (38, 173), (39, 179), +(40, 181), (41, 191), (42, 193), (43, 197), (44, 199)| +drop procedure if exists opp| +create procedure opp(n bigint unsigned, out pp bool) +begin +declare r double; +declare b, s bigint unsigned default 0; +set r = sqrt(n); +again: +loop +if s = 45 then +set b = b+200, s = 0; +else +begin +declare p bigint unsigned; +select t.p into p from test.primes t where t.i = s; +if b+p > r then +set pp = 1; +leave again; +end if; +if mod(n, b+p) = 0 then +set pp = 0; +leave again; +end if; +set s = s+1; +end; +end if; +end loop; +end| +drop procedure if exists ip| +create procedure ip(m int unsigned) +begin +declare p bigint unsigned; +declare i int unsigned; +set i=45, p=201; +while i < m do +begin +declare pp bool default 0; +call opp(p, pp); +if pp then +insert into test.primes values (i, p); +set i = i+1; +end if; +set p = p+2; +end; +end while; +end| +show create procedure opp| +Procedure sql_mode Create Procedure +opp CREATE PROCEDURE `test`.`opp`(n bigint unsigned, out pp bool) +begin +declare r double; +declare b, s bigint unsigned default 0; +set r = sqrt(n); +again: +loop +if s = 45 then +set b = b+200, s = 0; +else +begin +declare p bigint unsigned; +select t.p into p from test.primes t where t.i = s; +if b+p > r then +set pp = 1; +leave again; +end if; +if mod(n, b+p) = 0 then +set pp = 0; +leave again; +end if; +set s = s+1; +end; +end if; +end loop; +end +show procedure status like '%p%'| +Db Name Type Definer Modified Created Security_type Comment +test ip PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER +test opp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER +call ip(200)| +select * from primes where i=45 or i=100 or i=199| +i p +45 211 +100 557 +199 1229 +drop table primes| +drop procedure opp| +drop procedure ip| +show procedure status like '%p%'| +Db Name Type Definer Modified Created Security_type Comment +drop table if exists fib| +create table fib ( f bigint unsigned not null )| +insert into fib values (1), (1)| +drop procedure if exists fib| +create procedure fib(n int unsigned) +begin +if n > 0 then +begin +declare x, y bigint unsigned; +declare c cursor for select f from fib order by f desc limit 2; +open c; +fetch c into y; +fetch c into x; +close c; +insert into fib values (x+y); +call fib(n-1); +end; +end if; +end| +call fib(20)| +select * from fib order by f asc| +f +1 +1 +2 +3 +5 +8 +13 +21 +34 +55 +89 +144 +233 +377 +610 +987 +1597 +2584 +4181 +6765 +10946 +17711 +drop table fib| +drop procedure fib| +drop procedure if exists bar| +create procedure bar(x char(16), y int) +comment "111111111111" sql security invoker +insert into test.t1 values (x, y)| +show procedure status like 'bar'| +Db Name Type Definer Modified Created Security_type Comment +test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER 111111111111 +alter procedure bar comment "2222222222" sql security definer| +alter procedure bar comment "3333333333"| +alter procedure bar| +show create procedure bar| +Procedure sql_mode Create Procedure +bar CREATE PROCEDURE `test`.`bar`(x char(16), y int) + COMMENT '3333333333' +insert into test.t1 values (x, y) +show procedure status like 'bar'| +Db Name Type Definer Modified Created Security_type Comment +test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER 3333333333 +drop procedure bar| +drop procedure if exists p1| +create procedure p1 () +select (select s1 from t3) from t3| +create table t3 (s1 int)| +call p1()| +(select s1 from t3) +insert into t3 values (1)| +call p1()| +(select s1 from t3) +1 +drop procedure p1| +drop table t3| +drop function if exists foo| +create function `foo` () returns int +return 5| +select `foo` ()| +`foo` () +5 +drop function `foo`| +drop function if exists t1max| +Warnings: +Note 1305 FUNCTION t1max does not exist +create function t1max() returns int +begin +declare x int; +select max(data) into x from t1; +return x; +end| +insert into t1 values ("foo", 3), ("bar", 2), ("zip", 5), ("zap", 1)| +select t1max()| +t1max() +5 +drop function t1max| +drop table if exists t3| +create table t3 ( +v char(16) not null primary key, +c int unsigned not null +)| +create function getcount(s char(16)) returns int +begin +declare x int; +select count(*) into x from t3 where v = s; +if x = 0 then +insert into t3 values (s, 1); +else +update t3 set c = c+1 where v = s; +end if; +return x; +end| +select * from t1 where data = getcount("bar")| +id data +zap 1 +select * from t3| +v c +bar 4 +select getcount("zip")| +getcount("zip") +0 +select getcount("zip")| +getcount("zip") +1 +select * from t3| +v c +bar 4 +zip 2 +select getcount(id) from t1 where data = 3| +getcount(id) +0 +select getcount(id) from t1 where data = 5| +getcount(id) +1 +select * from t3| +v c +bar 4 +zip 3 +foo 1 +drop table t3| +drop function getcount| drop procedure if exists bug822| create procedure bug822(a_id char(16), a_data int) begin @@ -1055,6 +1552,7 @@ insert into t1 (id, data) values (a_id, a_data); end if; end| +delete from t1| call bug822('foo', 42)| call bug822('foo', 42)| call bug822('bar', 666)| @@ -1178,56 +1676,6 @@ @x2 2 drop procedure bug2260| -drop procedure if exists bug2267_1| -create procedure bug2267_1() -begin -show procedure status; -end| -drop procedure if exists bug2267_2| -create procedure bug2267_2() -begin -show function status; -end| -drop procedure if exists bug2267_3| -create procedure bug2267_3() -begin -show create procedure bug2267_1; -end| -drop procedure if exists bug2267_4| -create procedure bug2267_4() -begin -show create function fac; -end| -call bug2267_1()| -Db Name Type Definer Modified Created Security_type Comment -test bug2267_1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test bug2267_2 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test bug2267_3 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test bug2267_4 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -call bug2267_2()| -Db Name Type Definer Modified Created Security_type Comment -test fac FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -call bug2267_3()| -Procedure sql_mode Create Procedure -bug2267_1 CREATE PROCEDURE `test`.`bug2267_1`() -begin -show procedure status; -end -call bug2267_4()| -Function sql_mode Create Function -fac CREATE FUNCTION `test`.`fac`(n int unsigned) RETURNS bigint unsigned -begin -declare f bigint unsigned default 1; -while n > 1 do -set f = f * n; -set n = n - 1; -end while; -return f; -end -drop procedure bug2267_1| -drop procedure bug2267_2| -drop procedure bug2267_3| -drop procedure bug2267_4| drop procedure if exists bug2227| create procedure bug2227(x int) begin @@ -1239,18 +1687,6 @@ 1.3 x y 42 z 1.3 9 2.6 42 zzz drop procedure bug2227| -drop procedure if exists bug2614| -create procedure bug2614() -begin -drop table if exists t3; -create table t3 (id int default '0' not null); -insert into t3 select 12; -insert into t3 select * from t3; -end| -call bug2614()| -call bug2614()| -drop table t3| -drop procedure bug2614| drop function if exists bug2674| create function bug2674() returns int return @@sort_buffer_size| @@ -1344,7 +1780,7 @@ declare t3 int; declare rc int default 0; declare continue handler for 1065 set rc = 1; -drop table if exists temp_t1; +drop temporary table if exists temp_t1; create temporary table temp_t1 ( f1 int auto_increment, f2 varchar(20), primary key (f1) ); @@ -1356,14 +1792,13 @@ insert into t4 values (2, rc, t3); end| call bug1863(10)| -Warnings: call bug1863(10)| -Warnings: select * from t4| f1 rc t3 2 0 NULL 2 0 NULL drop procedure bug1863| +drop temporary table temp_t1; drop table t3, t4| drop table if exists t3, t4| create table t3 ( @@ -1551,7 +1986,7 @@ create procedure bug2460_2() begin drop table if exists t3; -create table t3 (s1 int); +create temporary table t3 (s1 int); insert into t3 select 1 union select 1; end| call bug2460_2()| @@ -1591,11 +2026,11 @@ insert into "t1" values ('foo', 1) show create function bug2564_3| Function sql_mode Create Function -bug2564_3 CREATE FUNCTION `test`.`bug2564_3`(x int, y int) RETURNS int +bug2564_3 CREATE FUNCTION `test`.`bug2564_3`(x int, y int) RETURNS int(11) return x || y show create function bug2564_4| Function sql_mode Create Function -bug2564_4 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI CREATE FUNCTION "test"."bug2564_4"(x int, y int) RETURNS int +bug2564_4 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI CREATE FUNCTION "test"."bug2564_4"(x int, y int) RETURNS int(11) return x || y drop procedure bug2564_1| drop procedure bug2564_2| @@ -1655,13 +2090,33 @@ end| call bug4579_1()| call bug4579_1()| -Warnings: call bug4579_1()| -Warnings: drop procedure bug4579_1| drop procedure bug4579_2| drop table t3| drop table if exists t3| +drop procedure if exists bug2773| +create function bug2773() returns int return null| +create table t3 as select bug2773()| +show create table t3| +Table Create Table +t3 CREATE TABLE `t3` ( + `bug2773()` int(11) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t3| +drop function bug2773| +drop procedure if exists bug3788| +create function bug3788() returns date return cast("2005-03-04" as date)| +select bug3788()| +bug3788() +2005-03-04 +drop function bug3788| +create function bug3788() returns binary(5) return 5| +select bug3788()| +bug3788() +5 +drop function bug3788| +drop table if exists t3| create table t3 (f1 int, f2 int, f3 int)| insert into t3 values (1,1,1)| drop procedure if exists bug4726| @@ -1681,22 +2136,6 @@ call bug4726()| drop procedure bug4726| drop table t3| -drop table if exists t3| -create table t3 (s1 int)| -insert into t3 values (3), (4)| -drop procedure if exists bug4318| -create procedure bug4318() -handler t3 read next| -handler t3 open| -call bug4318()| -s1 -3 -call bug4318()| -s1 -4 -handler t3 close| -drop procedure bug4318| -drop table t3| drop procedure if exists bug4902| create procedure bug4902() begin @@ -1746,6 +2185,7 @@ Create routine Functions,Procedures To use CREATE FUNCTION/PROCEDURE Create temporary tables Databases To use CREATE TEMPORARY TABLE Create view Tables To create new views +Create user Server Admin To create new users Delete Tables To delete existing rows Drop Databases,Tables To drop databases, tables, and views Execute Functions,Procedures To execute stored routines @@ -1799,6 +2239,7 @@ Create routine Functions,Procedures To use CREATE FUNCTION/PROCEDURE Create temporary tables Databases To use CREATE TEMPORARY TABLE Create view Tables To create new views +Create user Server Admin To create new users Delete Tables To delete existing rows Drop Databases,Tables To drop databases, tables, and views Execute Functions,Procedures To execute stored routines @@ -2087,237 +2528,255 @@ 1136 drop procedure bug6029| drop table t3| -drop table if exists fac| -create table fac (n int unsigned not null primary key, f bigint unsigned)| -drop procedure if exists ifac| -create procedure ifac(n int unsigned) -begin -declare i int unsigned default 1; -if n > 20 then -set n = 20; # bigint overflow otherwise -end if; -while i <= n do +drop procedure if exists bug8540| +create procedure bug8540() begin -insert into test.fac values (i, fac(i)); -set i = i + 1; -end; -end while; +declare x int default 1; +select x as y, x+0 as z; end| -call ifac(20)| -select * from fac| -n f +call bug8540()| +y z 1 1 -2 2 -3 6 -4 24 -5 120 -6 720 -7 5040 -8 40320 -9 362880 -10 3628800 -11 39916800 -12 479001600 -13 6227020800 -14 87178291200 -15 1307674368000 -16 20922789888000 -17 355687428096000 -18 6402373705728000 -19 121645100408832000 -20 2432902008176640000 -drop table fac| -show function status like '%f%'| -Db Name Type Definer Modified Created Security_type Comment -test fac FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -drop procedure ifac| -drop function fac| -show function status like '%f%'| -Db Name Type Definer Modified Created Security_type Comment -drop table if exists primes| -create table primes ( -i int unsigned not null primary key, -p bigint unsigned not null -)| -insert into primes values -( 0, 3), ( 1, 5), ( 2, 7), ( 3, 11), ( 4, 13), -( 5, 17), ( 6, 19), ( 7, 23), ( 8, 29), ( 9, 31), -(10, 37), (11, 41), (12, 43), (13, 47), (14, 53), -(15, 59), (16, 61), (17, 67), (18, 71), (19, 73), -(20, 79), (21, 83), (22, 89), (23, 97), (24, 101), -(25, 103), (26, 107), (27, 109), (28, 113), (29, 127), -(30, 131), (31, 137), (32, 139), (33, 149), (34, 151), -(35, 157), (36, 163), (37, 167), (38, 173), (39, 179), -(40, 181), (41, 191), (42, 193), (43, 197), (44, 199)| -drop procedure if exists opp| -create procedure opp(n bigint unsigned, out pp bool) -begin -declare r double; -declare b, s bigint unsigned default 0; -set r = sqrt(n); -again: -loop -if s = 45 then -set b = b+200, s = 0; -else -begin -declare p bigint unsigned; -select t.p into p from test.primes t where t.i = s; -if b+p > r then -set pp = 1; -leave again; -end if; -if mod(n, b+p) = 0 then -set pp = 0; -leave again; -end if; -set s = s+1; -end; -end if; -end loop; +drop procedure bug8540| +drop table if exists t3| +create table t3 (s1 int)| +drop procedure if exists bug6642| +create procedure bug6642() +select abs(count(s1)) from t3| +call bug6642()| +abs(count(s1)) +0 +call bug6642()| +abs(count(s1)) +0 +drop procedure bug6642| +insert into t3 values (0),(1)| +drop procedure if exists bug7013| +create procedure bug7013() +select s1,count(s1) from t3 group by s1 with rollup| +call bug7013()| +s1 count(s1) +0 1 +1 1 +NULL 2 +call bug7013()| +s1 count(s1) +0 1 +1 1 +NULL 2 +drop procedure bug7013| +drop table if exists t4| +create table t4 ( +a mediumint(8) unsigned not null auto_increment, +b smallint(5) unsigned not null, +c char(32) not null, +primary key (a) +) engine=myisam default charset=latin1| +insert into t4 values (1, 2, 'oneword')| +insert into t4 values (2, 2, 'anotherword')| +drop procedure if exists bug7743| +create procedure bug7743 ( searchstring char(28) ) +begin +declare var mediumint(8) unsigned; +select a into var from t4 where b = 2 and c = binary searchstring limit 1; +select var; end| -drop procedure if exists ip| -create procedure ip(m int unsigned) +call bug7743("oneword")| +var +1 +call bug7743("OneWord")| +var +NULL +Warnings: +Warning 1329 No data to FETCH +call bug7743("anotherword")| +var +2 +call bug7743("AnotherWord")| +var +NULL +Warnings: +Warning 1329 No data to FETCH +drop procedure bug7743| +drop table t4| +delete from t3| +insert into t3 values(1)| +drop procedure if exists bug7992_1| +Warnings: +Note 1305 PROCEDURE bug7992_1 does not exist +drop procedure if exists bug7992_2| +Warnings: +Note 1305 PROCEDURE bug7992_2 does not exist +create procedure bug7992_1() begin -declare p bigint unsigned; -declare i int unsigned; -set i=45, p=201; -while i < m do +declare i int; +select max(s1)+1 into i from t3; +end| +create procedure bug7992_2() +insert into t3 (s1) select max(t4.s1)+1 from t3 as t4| +call bug7992_1()| +call bug7992_1()| +call bug7992_2()| +call bug7992_2()| +drop procedure bug7992_1| +drop procedure bug7992_2| +drop table t3| +drop table if exists t3| +create table t3 ( userid bigint(20) not null default 0 )| +drop procedure if exists bug8116| +create procedure bug8116(in _userid int) +select * from t3 where userid = _userid| +call bug8116(42)| +userid +call bug8116(42)| +userid +drop procedure bug8116| +drop table t3| +drop procedure if exists bug6857| +create procedure bug6857(counter int) begin -declare pp bool default 0; -call opp(p, pp); -if pp then -insert into test.primes values (i, p); -set i = i+1; -end if; -set p = p+2; -end; +declare t0, t1 int; +declare plus bool default 0; +set t0 = current_time(); +while counter > 0 do +set counter = counter - 1; end while; -end| -show create procedure opp| -Procedure sql_mode Create Procedure -opp CREATE PROCEDURE `test`.`opp`(n bigint unsigned, out pp bool) +set t1 = current_time(); +if t1 > t0 then +set plus = 1; +end if; +select plus; +end| +drop procedure bug6857| +drop procedure if exists bug8757| +create procedure bug8757() begin -declare r double; -declare b, s bigint unsigned default 0; -set r = sqrt(n); -again: -loop -if s = 45 then -set b = b+200, s = 0; -else +declare x int; +declare c1 cursor for select data from t1 limit 1; begin -declare p bigint unsigned; -select t.p into p from test.primes t where t.i = s; -if b+p > r then -set pp = 1; -leave again; -end if; -if mod(n, b+p) = 0 then -set pp = 0; -leave again; -end if; -set s = s+1; +declare y int; +declare c2 cursor for select i from t2 limit 1; +open c2; +fetch c2 into y; +close c2; +select 2,y; end; -end if; -end loop; -end -show procedure status like '%p%'| -Db Name Type Definer Modified Created Security_type Comment -test ip PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test opp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -call ip(200)| -select * from primes where i=45 or i=100 or i=199| -i p -45 211 -100 557 -199 1229 -drop table primes| -drop procedure opp| -drop procedure ip| -show procedure status like '%p%'| -Db Name Type Definer Modified Created Security_type Comment -drop table if exists fib| -create table fib ( f bigint unsigned not null )| -insert into fib values (1), (1)| -drop procedure if exists fib| -create procedure fib(n int unsigned) -begin -if n > 0 then +open c1; +fetch c1 into x; +close c1; +select 1,x; +end| +delete from t1| +delete from t2| +insert into t1 values ("x", 1)| +insert into t2 values ("y", 2, 0.0)| +call bug8757()| +2 y +2 2 +1 x +1 1 +delete from t1| +delete from t2| +drop procedure bug8757| +drop procedure if exists bug8762| +drop procedure if exists bug8762; create procedure bug8762() begin end| +drop procedure if exists bug8762; create procedure bug8762() begin end| +drop procedure bug8762| +drop function if exists bug5240| +create function bug5240 () returns int begin -declare x, y bigint unsigned; -declare c cursor for select f from fib order by f desc limit 2; +declare x int; +declare c cursor for select data from t1 limit 1; open c; -fetch c into y; fetch c into x; close c; -insert into fib values (x+y); -call fib(n-1); -end; -end if; +return x; end| -call fib(20)| -select * from fib order by f asc| -f -1 -1 -2 -3 -5 -8 -13 -21 -34 -55 -89 -144 -233 -377 -610 -987 -1597 -2584 -4181 -6765 -10946 -17711 -drop table fib| -drop procedure fib| -drop procedure if exists bar| -create procedure bar(x char(16), y int) -comment "111111111111" sql security invoker -insert into test.t1 values (x, y)| -show procedure status like 'bar'| -Db Name Type Definer Modified Created Security_type Comment -test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER 111111111111 -alter procedure bar comment "2222222222" sql security definer| -alter procedure bar comment "3333333333"| -alter procedure bar| -show create procedure bar| -Procedure sql_mode Create Procedure -bar CREATE PROCEDURE `test`.`bar`(x char(16), y int) - COMMENT '3333333333' -insert into test.t1 values (x, y) -show procedure status like 'bar'| -Db Name Type Definer Modified Created Security_type Comment -test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER 3333333333 -drop procedure bar| -drop table t1; -drop table t2; -drop procedure if exists p1; -create procedure p1 () select (select s1 from t1) from t1; -create table t1 (s1 int); -call p1(); -(select s1 from t1) -insert into t1 values (1); -call p1(); -(select s1 from t1) -1 -drop procedure p1; -drop table t1; -drop function if exists foo; -create function `foo` () returns int return 5; -select `foo` (); -`foo` () -5 -drop function `foo`; +delete from t1| +insert into t1 values ("answer", 42)| +select id, bug5240() from t1| +id bug5240() +answer 42 +drop function bug5240| +drop function if exists bug5278| +create function bug5278 () returns char +begin +SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass'); +return 'okay'; +end| +select bug5278()| +ERROR 42000: Can't find any matching row in the user table +select bug5278()| +ERROR 42000: Can't find any matching row in the user table +drop function bug5278| +drop procedure if exists p1| +create table t3(id int)| +insert into t3 values(1)| +create procedure bug7992() +begin +declare i int; +select max(id)+1 into i from t3; +end| +call bug7992()| +call bug7992()| +drop procedure bug7992| +drop table t3| +create table t3 ( +lpitnumber int(11) default null, +lrecordtype int(11) default null +)| +create table t4 ( +lbsiid int(11) not null default '0', +ltradingmodeid int(11) not null default '0', +ltradingareaid int(11) not null default '0', +csellingprice decimal(19,4) default null, +primary key (lbsiid,ltradingmodeid,ltradingareaid) +)| +create table t5 ( +lbsiid int(11) not null default '0', +ltradingareaid int(11) not null default '0', +primary key (lbsiid,ltradingareaid) +)| +drop procedure if exists bug8849| +create procedure bug8849() +begin +insert into t5 +( +t5.lbsiid, +t5.ltradingareaid +) +select distinct t3.lpitnumber, t4.ltradingareaid +from +t4 join t3 on +t3.lpitnumber = t4.lbsiid +and t3.lrecordtype = 1 +left join t4 as price01 on +price01.lbsiid = t4.lbsiid and +price01.ltradingmodeid = 1 and +t4.ltradingareaid = price01.ltradingareaid; +end| +call bug8849()| +call bug8849()| +call bug8849()| +drop procedure bug8849| +drop tables t3,t4,t5| +drop procedure if exists bug8937| +create procedure bug8937() +begin +declare s,x,y,z int; +declare a float; +select sum(data),avg(data),min(data),max(data) into s,x,y,z from t1; +select s,x,y,z; +select avg(data) into a from t1; +select a; +end| +delete from t1| +insert into t1 (data) values (1), (2), (3), (4), (6)| +call bug8937()| +s x y z +16 3 1 6 +a +3.2000 +drop procedure bug8937| +delete from t1| +drop table t1,t2; --- 1.98/mysql-test/t/sp.test 2005-01-13 19:20:51 -08:00 +++ 1.99/mysql-test/t/sp.test 2005-04-05 19:45:08 -07:00 @@ -4,9 +4,22 @@ # Please keep this file free of --error cases and other # things that will not run in a single debugged mysqld # process (e.g. master-slave things). +# +# Test cases for bugs are added at the end. See template there. +# +# Tests that require --error go into sp-error.test +# Tests that require inndb go into sp_trans.test +# Tests that check privilege and security issues go to sp-security.test. +# Tests that require multiple connections, except security/privilege tests, +# go to sp-thread. use test; +# Test tables +# +# t1 and t2 are reused throughout the file, and dropped at the end. +# t3 and up are created and dropped when needed. +# --disable_warnings drop table if exists t1; --enable_warnings @@ -339,16 +352,15 @@ create procedure sub1(id char(16), x int) insert into test.t1 values (id, x)| -# QQ This doesn't work yet -#--disable_warnings -#drop procedure if exists sub2| -#--enable_warnings -#create procedure sub2(id char(16)) -#begin -# declare x int; -# set x = (select sum(t.x) from test.t2 t); -# insert into test.t1 values (id, x); -#end| +--disable_warnings +drop procedure if exists sub2| +--enable_warnings +create procedure sub2(id char(16)) +begin + declare x int; + set x = (select sum(t.i) from test.t2 t); + insert into test.t1 values (id, x); +end| --disable_warnings drop procedure if exists sub3| @@ -360,11 +372,11 @@ call sub1("sub1b", (select max(i) from t2))| call sub1("sub1c", (select i,d from t2 limit 1))| call sub1("sub1d", (select 1 from (select 1) a))| -#call sub2("sub2"); +call sub2("sub2"); select * from t1| select sub3((select max(i) from t2))| drop procedure sub1| -#drop procedure sub2| +drop procedure sub2| drop function sub3| delete from t2| @@ -859,7 +871,7 @@ create procedure create_select(x char(16), y int) begin insert into test.t1 values (x, y); - create table test.t3 select * from test.t1; + create temporary table test.t3 select * from test.t1; insert into test.t3 values (concat(x, "2"), y+2); end| @@ -970,12 +982,12 @@ create procedure hndlr1(val int) begin declare x int default 0; - declare foo condition for 1146; + declare foo condition for 1136; declare bar condition for sqlstate '42S98'; # Just for testing syntax declare zip condition for sqlstate value '42S99'; # Just for testing syntax declare continue handler for foo set x = 1; - insert into test.t666 values ("hndlr1", val); # Non-existing table + insert into test.t1 values ("hndlr1", val, 2); # Too many values if (x) then insert into test.t1 values ("hndlr1", val); # This instead then end if; @@ -994,9 +1006,9 @@ declare x int default 0; begin - declare exit handler for sqlstate '42S02' set x = 1; + declare exit handler for sqlstate '21S01' set x = 1; - insert into test.t666 values ("hndlr2", val); # Non-existing table + insert into test.t1 values ("hndlr2", val, 2); # Too many values end; insert into test.t1 values ("hndlr2", x); @@ -1027,7 +1039,7 @@ declare y int; set y = val + 10; - insert into test.t666 values ("hndlr3", y); # Non-existing table + insert into test.t1 values ("hndlr3", y, 2); # Too many values if x then insert into test.t1 values ("hndlr3", y); end if; @@ -1279,6 +1291,481 @@ # +# Let us test how well new locking scheme works. +# + +# Let us prepare playground +--disable_warnings +drop function if exists f0| +drop function if exists f1| +drop function if exists f2| +drop function if exists f3| +drop function if exists f4| +drop function if exists f5| +drop function if exists f6| +drop function if exists f7| +drop function if exists f8| +drop view if exists v0| +drop view if exists v1| +drop view if exists v2| +--enable_warnings +delete from t1| +delete from t2| +insert into t1 values ("a", 1), ("b", 2) | +insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) | + +# Test the simplest function using tables +create function f1() returns int + return (select sum(data) from t1)| +select f1()| +# This should work too (and give 2 rows as result) +select id, f1() from t1| + +# Function which uses two instances of table simultaneously +create function f2() returns int + return (select data from t1 where data <= (select sum(data) from t1) limit 1)| +select f2()| +select id, f2() from t1| + +# Function which uses the same table twice in different queries +create function f3() returns int +begin + declare n int; + declare m int; + set n:= (select min(data) from t1); + set m:= (select max(data) from t1); + return n < m; +end| +select f3()| +select id, f3() from t1| + +# Calling two functions using same table +select f1(), f3()| +select id, f1(), f3() from t1| + +# Function which uses two different tables +create function f4() returns double + return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")| +select f4()| +select s, f4() from t2| + +# Recursive functions which due to this recursion require simultaneous +# access to several instance of the same table won't work +create function f5(i int) returns int +begin + if i <= 0 then + return 0; + elseif i = 1 then + return (select count(*) from t1 where data = i); + else + return (select count(*) + f5( i - 1) from t1 where data = i); + end if; +end| +select f5(1)| +# This should generate an error about insuficient number of tables locked +--error 1100 +select f5(2)| +# But now it simply miserably fails because we are trying to use the same +# lex on the next iteration :/ It should generate some error too... +# select f5(3)| + +# OTOH this should work +create function f6() returns int +begin + declare n int; + set n:= f1(); + return (select count(*) from t1 where data <= f7() and data <= n); +end| +create function f7() returns int + return (select sum(data) from t1 where data <= f1())| +select f6()| +select id, f6() from t1| + +# TODO Test temporary table handling + +# +# Let us test how new locking work with views +# +# The most trivial view +create view v1 (a) as select f1()| +select * from v1| +select id, a from t1, v1| +select * from v1, v1 as v| +# A bit more complex construction +create view v2 (a) as select a*10 from v1| +select * from v2| +select id, a from t1, v2| +select * from v1, v2| + +# Nice example where the same view is used on +# on different expression levels +create function f8 () returns int + return (select count(*) from v2)| + +select *, f8() from v1| + +# Let us test what will happen if function is missing +drop function f1| +--error 1356 +select * from v1| + +# And what will happen if we have recursion which involves +# views and functions ? +create function f1() returns int + return (select sum(data) from t1) + (select sum(data) from v1)| +# FIXME All these just exceed file limit for me :) +#select f1()| +#select * from v1| +#select * from v2| +# Back to the normal cases +drop function f1| +create function f1() returns int + return (select sum(data) from t1)| + +# Let us also test some weird cases where no real tables is used +create function f0() returns int + return (select * from (select 100) as r)| +select f0()| +select *, f0() from (select 1) as t| +create view v0 as select f0()| +select * from v0| +select *, f0() from v0| + +# +# Let us test how well prelocking works with explicit LOCK TABLES. +# +# Nowdays we have to lock mysql.proc to be able to read SP definitions. +# But Monty was going to fix this. +lock tables t1 read, t1 as t11 read, mysql.proc read| +# These should work well +select f3()| +select id, f3() from t1 as t11| +# Degenerate cases work too :) +select f0()| +select * from v0| +select *, f0() from v0, (select 123) as d1| +# But these should not ! +--error 1100 +select id, f3() from t1| +--error 1100 +select f4()| +unlock tables| + +# Let us test how LOCK TABLES which implicitly depends on functions +# works +lock tables v2 read, mysql.proc read| +select * from v2| +select * from v1| +# These should not work as we have too little instances of tables locked +--error 1100 +select * from v1, v2| +--error 1100 +select f4()| +unlock tables| + + +# TODO We also should test integration with triggers + + +# Cleanup +drop function f0| +drop function f1| +drop function f2| +drop function f3| +drop function f4| +drop function f5| +drop function f6| +drop function f7| +drop function f8| +drop view v0| +drop view v1| +drop view v2| +delete from t1 | +delete from t2 | + +# End of non-bug tests + + +# +# Some "real" examples +# + +# fac + +--disable_warnings +drop table if exists fac| +--enable_warnings +create table fac (n int unsigned not null primary key, f bigint unsigned)| + +--disable_warnings +drop procedure if exists ifac| +--enable_warnings +create procedure ifac(n int unsigned) +begin + declare i int unsigned default 1; + + if n > 20 then + set n = 20; # bigint overflow otherwise + end if; + while i <= n do + begin + insert into test.fac values (i, fac(i)); + set i = i + 1; + end; + end while; +end| + +call ifac(20)| +select * from fac| +drop table fac| +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show function status like '%f%'| +drop procedure ifac| +drop function fac| +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show function status like '%f%'| + + +# primes + +--disable_warnings +drop table if exists primes| +--enable_warnings + +create table primes ( + i int unsigned not null primary key, + p bigint unsigned not null +)| + +insert into primes values + ( 0, 3), ( 1, 5), ( 2, 7), ( 3, 11), ( 4, 13), + ( 5, 17), ( 6, 19), ( 7, 23), ( 8, 29), ( 9, 31), + (10, 37), (11, 41), (12, 43), (13, 47), (14, 53), + (15, 59), (16, 61), (17, 67), (18, 71), (19, 73), + (20, 79), (21, 83), (22, 89), (23, 97), (24, 101), + (25, 103), (26, 107), (27, 109), (28, 113), (29, 127), + (30, 131), (31, 137), (32, 139), (33, 149), (34, 151), + (35, 157), (36, 163), (37, 167), (38, 173), (39, 179), + (40, 181), (41, 191), (42, 193), (43, 197), (44, 199)| + +--disable_warnings +drop procedure if exists opp| +--enable_warnings +create procedure opp(n bigint unsigned, out pp bool) +begin + declare r double; + declare b, s bigint unsigned default 0; + + set r = sqrt(n); + + again: + loop + if s = 45 then + set b = b+200, s = 0; + else + begin + declare p bigint unsigned; + + select t.p into p from test.primes t where t.i = s; + if b+p > r then + set pp = 1; + leave again; + end if; + if mod(n, b+p) = 0 then + set pp = 0; + leave again; + end if; + set s = s+1; + end; + end if; + end loop; +end| + +--disable_warnings +drop procedure if exists ip| +--enable_warnings +create procedure ip(m int unsigned) +begin + declare p bigint unsigned; + declare i int unsigned; + + set i=45, p=201; + + while i < m do + begin + declare pp bool default 0; + + call opp(p, pp); + if pp then + insert into test.primes values (i, p); + set i = i+1; + end if; + set p = p+2; + end; + end while; +end| +show create procedure opp| +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show procedure status like '%p%'| + +# This isn't the fastest way in the world to compute prime numbers, so +# don't be too ambitious. ;-) +call ip(200)| +# We don't want to select the entire table here, just pick a few +# examples. +# The expected result is: +# i p +# --- ---- +# 45 211 +# 100 557 +# 199 1229 +select * from primes where i=45 or i=100 or i=199| +drop table primes| +drop procedure opp| +drop procedure ip| +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show procedure status like '%p%'| + + +# Fibonacci, for recursion test. (Yet Another Numerical series :) + +--disable_warnings +drop table if exists fib| +--enable_warnings +create table fib ( f bigint unsigned not null )| + +insert into fib values (1), (1)| + +# We deliberately do it the awkward way, fetching the last two +# values from the table, in order to exercise various statements +# and table accesses at each turn. +--disable_warnings +drop procedure if exists fib| +--enable_warnings +create procedure fib(n int unsigned) +begin + if n > 0 then + begin + declare x, y bigint unsigned; + declare c cursor for select f from fib order by f desc limit 2; + + open c; + fetch c into y; + fetch c into x; + close c; + insert into fib values (x+y); + call fib(n-1); + end; + end if; +end| + +call fib(20)| + +select * from fib order by f asc| +drop table fib| +drop procedure fib| + + +# +# Comment & suid +# + +--disable_warnings +drop procedure if exists bar| +--enable_warnings +create procedure bar(x char(16), y int) + comment "111111111111" sql security invoker + insert into test.t1 values (x, y)| +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show procedure status like 'bar'| +alter procedure bar comment "2222222222" sql security definer| +alter procedure bar comment "3333333333"| +alter procedure bar| +show create procedure bar| +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show procedure status like 'bar'| +drop procedure bar| + +# +# rexecution +# +--disable_warnings +drop procedure if exists p1| +--enable_warnings +create procedure p1 () + select (select s1 from t3) from t3| + +create table t3 (s1 int)| + +call p1()| +insert into t3 values (1)| +call p1()| +drop procedure p1| +drop table t3| + +# +# backticks +# +--disable_warnings +drop function if exists foo| +--enable_warnings +create function `foo` () returns int + return 5| +select `foo` ()| +drop function `foo`| + +# +# Implicit LOCK/UNLOCK TABLES for table access in functions +# + +--disable_warning +drop function if exists t1max| +--enable_warnings +create function t1max() returns int +begin + declare x int; + select max(data) into x from t1; + return x; +end| + +insert into t1 values ("foo", 3), ("bar", 2), ("zip", 5), ("zap", 1)| +select t1max()| +drop function t1max| + +--disable_warnings +drop table if exists t3| +--enable_warnings +create table t3 ( + v char(16) not null primary key, + c int unsigned not null +)| + +create function getcount(s char(16)) returns int +begin + declare x int; + + select count(*) into x from t3 where v = s; + if x = 0 then + insert into t3 values (s, 1); + else + update t3 set c = c+1 where v = s; + end if; + return x; +end| + +select * from t1 where data = getcount("bar")| +select * from t3| +select getcount("zip")| +select getcount("zip")| +select * from t3| +select getcount(id) from t1 where data = 3| +select getcount(id) from t1 where data = 5| +select * from t3| +drop table t3| +drop function getcount| + + +# # Test cases for old bugs # @@ -1297,6 +1784,7 @@ end if; end| +delete from t1| call bug822('foo', 42)| call bug822('foo', 42)| call bug822('bar', 666)| @@ -1453,49 +1941,56 @@ # # BUG#2267 # ---disable_warnings -drop procedure if exists bug2267_1| ---enable_warnings -create procedure bug2267_1() -begin - show procedure status; -end| - ---disable_warnings -drop procedure if exists bug2267_2| ---enable_warnings -create procedure bug2267_2() -begin - show function status; -end| - ---disable_warnings -drop procedure if exists bug2267_3| ---enable_warnings -create procedure bug2267_3() -begin - show create procedure bug2267_1; -end| - ---disable_warnings -drop procedure if exists bug2267_4| ---enable_warnings -create procedure bug2267_4() -begin - show create function fac; -end| - ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' -call bug2267_1()| ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' -call bug2267_2()| -call bug2267_3()| -call bug2267_4()| - -drop procedure bug2267_1| -drop procedure bug2267_2| -drop procedure bug2267_3| -drop procedure bug2267_4| +# NOTE: This test case will be fixed as soon as Monty +# will allow to open mysql.proc table under LOCK TABLES +# without mentioning in lock list. +# +# FIXME: Other solution would be to use preopened proc table +# instead of opening it anew. +# +#--disable_warnings +#drop procedure if exists bug2267_1| +#--enable_warnings +#create procedure bug2267_1() +#begin +# show procedure status; +#end| +# +#--disable_warnings +#drop procedure if exists bug2267_2| +#--enable_warnings +#create procedure bug2267_2() +#begin +# show function status; +#end| +# +#--disable_warnings +#drop procedure if exists bug2267_3| +#--enable_warnings +#create procedure bug2267_3() +#begin +# show create procedure bug2267_1; +#end| +# +#--disable_warnings +#drop procedure if exists bug2267_4| +#--enable_warnings +#create procedure bug2267_4() +#begin +# show create function fac; +#end| +# +#--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +#call bug2267_1()| +#--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +#call bug2267_2()| +#call bug2267_3()| +#call bug2267_4()| +# +#drop procedure bug2267_1| +#drop procedure bug2267_2| +#drop procedure bug2267_3| +#drop procedure bug2267_4| # # BUG#2227 @@ -1517,23 +2012,30 @@ # # BUG#2614 # ---disable_warnings -drop procedure if exists bug2614| ---enable_warnings -create procedure bug2614() -begin - drop table if exists t3; - create table t3 (id int default '0' not null); - insert into t3 select 12; - insert into t3 select * from t3; -end| - ---disable_warnings -call bug2614()| ---enable_warnings -call bug2614()| -drop table t3| -drop procedure bug2614| +# QQ The second insert doesn't work with temporary tables (it was an +# QQ ordinary table before we changed the locking scheme). It results +# QQ in an error: 1137: Can't reopen table: 't3' +# QQ which is a known limit with temporary tables. +# QQ For this reason we can't run this test any more (i.e., if we modify +# QQ it, it's no longer a test case for the bug), but we keep it here +# QQ anyway, for tracability. +#--disable_warnings +#drop procedure if exists bug2614| +#--enable_warnings +#create procedure bug2614() +#begin +# drop temporary table if exists t3; +# create temporary table t3 (id int default '0' not null); +# insert into t3 select 12; +# insert into t3 select * from t3; +#end| +# +#--disable_warnings +#call bug2614()| +#--enable_warnings +#call bug2614()| +#drop temporary table t3| +#drop procedure bug2614| # # BUG#2674 @@ -1673,7 +2175,7 @@ declare rc int default 0; declare continue handler for 1065 set rc = 1; - drop table if exists temp_t1; + drop temporary table if exists temp_t1; create temporary table temp_t1 ( f1 int auto_increment, f2 varchar(20), primary key (f1) ); @@ -1695,6 +2197,7 @@ select * from t4| drop procedure bug1863| +drop temporary table temp_t1; drop table t3, t4| # @@ -1912,7 +2415,7 @@ create procedure bug2460_2() begin drop table if exists t3; - create table t3 (s1 int); + create temporary table t3 (s1 int); insert into t3 select 1 union select 1; end| @@ -2056,6 +2559,35 @@ drop procedure bug4579_2| drop table t3| +# +# BUG#2773: Function's data type ignored in stored procedures +# +--disable_warnings +drop table if exists t3| +drop procedure if exists bug2773| +--enable_warnings + +create function bug2773() returns int return null| +create table t3 as select bug2773()| +show create table t3| +drop table t3| +drop function bug2773| + +# +# BUG#3788: Stored procedure packet error +# +--disable_warnings +drop procedure if exists bug3788| +--enable_warnings + +create function bug3788() returns date return cast("2005-03-04" as date)| +select bug3788()| +drop function bug3788| + +create function bug3788() returns binary(5) return 5| +select bug3788()| +drop function bug3788| + # # BUG#4726 @@ -2093,27 +2625,28 @@ # # BUG#4318 # ---disable_warnings -drop table if exists t3| ---enable_warnings - -create table t3 (s1 int)| -insert into t3 values (3), (4)| - ---disable_warnings -drop procedure if exists bug4318| ---enable_warnings -create procedure bug4318() - handler t3 read next| - -handler t3 open| -# Expect no results, as tables are closed, but there shouldn't be any errors -call bug4318()| -call bug4318()| -handler t3 close| - -drop procedure bug4318| -drop table t3| +#QQ Don't know if HANDLER commands can work with SPs, or at all... +#--disable_warnings +#drop table if exists t3| +#--enable_warnings +# +#create table t3 (s1 int)| +#insert into t3 values (3), (4)| +# +#--disable_warnings +#drop procedure if exists bug4318| +#--enable_warnings +#create procedure bug4318() +# handler t3 read next| +# +#handler t3 open| +## Expect no results, as tables are closed, but there shouldn't be any errors +#call bug4318()| +#call bug4318()| +#handler t3 close| +# +#drop procedure bug4318| +#drop table t3| # # BUG#4902: Stored procedure with SHOW WARNINGS leads to packet error @@ -2512,230 +3045,370 @@ drop procedure bug6029| drop table t3| +# +# BUG#8540: Local variable overrides an alias +# +--disable_warnings +drop procedure if exists bug8540| +--enable_warnings + +create procedure bug8540() +begin + declare x int default 1; + select x as y, x+0 as z; +end| + +call bug8540()| +drop procedure bug8540| # -# Some "real" examples +# BUG#6642: Stored procedure crash if expression with set function # +--disable_warnings +drop table if exists t3| +--enable_warnings +create table t3 (s1 int)| -# fac +--disable_warnings +drop procedure if exists bug6642| +--enable_warnings +create procedure bug6642() + select abs(count(s1)) from t3| + +call bug6642()| +call bug6642()| +drop procedure bug6642| + +# +# BUG#7013: Stored procedure crash if group by ... with rollup +# +insert into t3 values (0),(1)| --disable_warnings -drop table if exists fac| +drop procedure if exists bug7013| --enable_warnings -create table fac (n int unsigned not null primary key, f bigint unsigned)| +create procedure bug7013() + select s1,count(s1) from t3 group by s1 with rollup| +call bug7013()| +call bug7013()| +drop procedure bug7013| +# +# BUG#7743: 'Lost connection to MySQL server during query' on Stored Procedure +# --disable_warnings -drop procedure if exists ifac| +drop table if exists t4| --enable_warnings -create procedure ifac(n int unsigned) -begin - declare i int unsigned default 1; +create table t4 ( + a mediumint(8) unsigned not null auto_increment, + b smallint(5) unsigned not null, + c char(32) not null, + primary key (a) +) engine=myisam default charset=latin1| +insert into t4 values (1, 2, 'oneword')| +insert into t4 values (2, 2, 'anotherword')| - if n > 20 then - set n = 20; # bigint overflow otherwise - end if; - while i <= n do - begin - insert into test.fac values (i, fac(i)); - set i = i + 1; - end; - end while; +--disable_warnings +drop procedure if exists bug7743| +--enable_warnings +create procedure bug7743 ( searchstring char(28) ) +begin + declare var mediumint(8) unsigned; + select a into var from t4 where b = 2 and c = binary searchstring limit 1; + select var; end| -call ifac(20)| -select * from fac| -drop table fac| ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' -show function status like '%f%'| -drop procedure ifac| -drop function fac| ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' -show function status like '%f%'| +call bug7743("oneword")| +call bug7743("OneWord")| +call bug7743("anotherword")| +call bug7743("AnotherWord")| +drop procedure bug7743| +drop table t4| +# +# BUG#7992: SELECT .. INTO variable .. within Stored Procedure crashes +# the server +# +delete from t3| +insert into t3 values(1)| +drop procedure if exists bug7992_1| +drop procedure if exists bug7992_2| +create procedure bug7992_1() +begin + declare i int; + select max(s1)+1 into i from t3; +end| +create procedure bug7992_2() + insert into t3 (s1) select max(t4.s1)+1 from t3 as t4| + +call bug7992_1()| +call bug7992_1()| +call bug7992_2()| +call bug7992_2()| -# primes +drop procedure bug7992_1| +drop procedure bug7992_2| +drop table t3| +# +# BUG#8116: calling simple stored procedure twice in a row results +# in server crash +# --disable_warnings -drop table if exists primes| +drop table if exists t3| --enable_warnings +create table t3 ( userid bigint(20) not null default 0 )| -create table primes ( - i int unsigned not null primary key, - p bigint unsigned not null -)| +--disable_warnings +drop procedure if exists bug8116| +--enable_warnings +create procedure bug8116(in _userid int) + select * from t3 where userid = _userid| -insert into primes values - ( 0, 3), ( 1, 5), ( 2, 7), ( 3, 11), ( 4, 13), - ( 5, 17), ( 6, 19), ( 7, 23), ( 8, 29), ( 9, 31), - (10, 37), (11, 41), (12, 43), (13, 47), (14, 53), - (15, 59), (16, 61), (17, 67), (18, 71), (19, 73), - (20, 79), (21, 83), (22, 89), (23, 97), (24, 101), - (25, 103), (26, 107), (27, 109), (28, 113), (29, 127), - (30, 131), (31, 137), (32, 139), (33, 149), (34, 151), - (35, 157), (36, 163), (37, 167), (38, 173), (39, 179), - (40, 181), (41, 191), (42, 193), (43, 197), (44, 199)| +call bug8116(42)| +call bug8116(42)| +drop procedure bug8116| +drop table t3| +# +# BUG#6857: current_time() in STORED PROCEDURES +# --disable_warnings -drop procedure if exists opp| +drop procedure if exists bug6857| --enable_warnings -create procedure opp(n bigint unsigned, out pp bool) +create procedure bug6857(counter int) begin - declare r double; - declare b, s bigint unsigned default 0; + declare t0, t1 int; + declare plus bool default 0; - set r = sqrt(n); + set t0 = current_time(); + while counter > 0 do + set counter = counter - 1; + end while; + set t1 = current_time(); + if t1 > t0 then + set plus = 1; + end if; + select plus; +end| - again: - loop - if s = 45 then - set b = b+200, s = 0; - else - begin - declare p bigint unsigned; +# QQ: This is currently disabled. Not only does it slow down a normal test +# run, it makes running with valgrind (or similar tools) extremely +# painful. +# Make sure this takes at least one second on all machines in all builds. +# 30000 makes it about 3 seconds on an old 1.1GHz linux. +#call bug6857(300000)| - select t.p into p from test.primes t where t.i = s; - if b+p > r then - set pp = 1; - leave again; - end if; - if mod(n, b+p) = 0 then - set pp = 0; - leave again; - end if; - set s = s+1; - end; - end if; - end loop; -end| +drop procedure bug6857| +# +# BUG#8757: Stored Procedures: Scope of Begin and End Statements do not +# work properly. --disable_warnings -drop procedure if exists ip| +drop procedure if exists bug8757| --enable_warnings -create procedure ip(m int unsigned) +create procedure bug8757() begin - declare p bigint unsigned; - declare i int unsigned; - - set i=45, p=201; + declare x int; + declare c1 cursor for select data from t1 limit 1; - while i < m do - begin - declare pp bool default 0; + begin + declare y int; + declare c2 cursor for select i from t2 limit 1; - call opp(p, pp); - if pp then - insert into test.primes values (i, p); - set i = i+1; - end if; - set p = p+2; - end; - end while; + open c2; + fetch c2 into y; + close c2; + select 2,y; + end; + open c1; + fetch c1 into x; + close c1; + select 1,x; end| -show create procedure opp| ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' -show procedure status like '%p%'| -# This isn't the fastest way in the world to compute prime numbers, so -# don't be too ambitious. ;-) -call ip(200)| -# We don't want to select the entire table here, just pick a few -# examples. -# The expected result is: -# i p -# --- ---- -# 45 211 -# 100 557 -# 199 1229 -select * from primes where i=45 or i=100 or i=199| -drop table primes| -drop procedure opp| -drop procedure ip| ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' -show procedure status like '%p%'| +delete from t1| +delete from t2| +insert into t1 values ("x", 1)| +insert into t2 values ("y", 2, 0.0)| +call bug8757()| + +delete from t1| +delete from t2| +drop procedure bug8757| -# Fibonacci, for recursion test. (Yet Another Numerical series :) +# +# BUG#8762: Stored Procedures: Inconsistent behavior +# of DROP PROCEDURE IF EXISTS statement. --disable_warnings -drop table if exists fib| +drop procedure if exists bug8762| --enable_warnings -create table fib ( f bigint unsigned not null )| +# Doesn't exist +drop procedure if exists bug8762; create procedure bug8762() begin end| +# Does exist +drop procedure if exists bug8762; create procedure bug8762() begin end| +drop procedure bug8762| -insert into fib values (1), (1)| -# We deliberately do it the awkward way, fetching the last two -# values from the table, in order to exercise various statements -# and table accesses at each turn. +# +# BUG#5240: Stored procedure crash if function has cursor declaration +# +# The following test case fails in --ps-protocol mode due to some bugs +# in algorithm which calculates list of tables to be locked for queries +# using Stored Functions. It is disabled until Dmitri fixes this. +# +--disable_ps_protocol + --disable_warnings -drop procedure if exists fib| +drop function if exists bug5240| --enable_warnings -create procedure fib(n int unsigned) +create function bug5240 () returns int begin - if n > 0 then - begin - declare x, y bigint unsigned; - declare c cursor for select f from fib order by f desc limit 2; + declare x int; + declare c cursor for select data from t1 limit 1; - open c; - fetch c into y; - fetch c into x; - close c; - insert into fib values (x+y); - call fib(n-1); - end; - end if; + open c; + fetch c into x; + close c; + return x; end| -call fib(20)| - -select * from fib order by f asc| -drop table fib| -drop procedure fib| +delete from t1| +insert into t1 values ("answer", 42)| +select id, bug5240() from t1| +drop function bug5240| +--enable_ps_protocol # -# Comment & suid +# BUG#5278: Stored procedure packets out of order if SET PASSWORD. # +--disable_warnings +drop function if exists bug5278| +--enable_warnings +create function bug5278 () returns char +begin + SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass'); + return 'okay'; +end| + +--error 1133 +select bug5278()| +--error 1133 +select bug5278()| +drop function bug5278| +# +# BUG#7992: rolling back temporary Item tree changes in SP +# --disable_warnings -drop procedure if exists bar| +drop procedure if exists p1| --enable_warnings -create procedure bar(x char(16), y int) - comment "111111111111" sql security invoker - insert into test.t1 values (x, y)| ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' -show procedure status like 'bar'| -alter procedure bar comment "2222222222" sql security definer| -alter procedure bar comment "3333333333"| -alter procedure bar| -show create procedure bar| ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' -show procedure status like 'bar'| -drop procedure bar| +create table t3(id int)| +insert into t3 values(1)| +create procedure bug7992() +begin + declare i int; + select max(id)+1 into i from t3; +end| + +call bug7992()| +call bug7992()| +drop procedure bug7992| +drop table t3| delimiter ;| -drop table t1; -drop table t2; # -# rexecution +# BUG#8849: problem with insert statement with table alias's # +# Rolling back changes to AND/OR structure of ON and WHERE clauses in SP +# + +delimiter |; +create table t3 ( + lpitnumber int(11) default null, + lrecordtype int(11) default null +)| + +create table t4 ( + lbsiid int(11) not null default '0', + ltradingmodeid int(11) not null default '0', + ltradingareaid int(11) not null default '0', + csellingprice decimal(19,4) default null, + primary key (lbsiid,ltradingmodeid,ltradingareaid) +)| + +create table t5 ( + lbsiid int(11) not null default '0', + ltradingareaid int(11) not null default '0', + primary key (lbsiid,ltradingareaid) +)| + --disable_warnings -drop procedure if exists p1; +drop procedure if exists bug8849| --enable_warnings -create procedure p1 () select (select s1 from t1) from t1; -create table t1 (s1 int); -call p1(); -insert into t1 values (1); -call p1(); -drop procedure p1; -drop table t1; +create procedure bug8849() +begin + insert into t5 + ( + t5.lbsiid, + t5.ltradingareaid + ) + select distinct t3.lpitnumber, t4.ltradingareaid + from + t4 join t3 on + t3.lpitnumber = t4.lbsiid + and t3.lrecordtype = 1 + left join t4 as price01 on + price01.lbsiid = t4.lbsiid and + price01.ltradingmodeid = 1 and + t4.ltradingareaid = price01.ltradingareaid; +end| + +call bug8849()| +call bug8849()| +call bug8849()| +drop procedure bug8849| +drop tables t3,t4,t5| # -# backticks +# BUG#8937: Stored Procedure: AVG() works as SUM() in SELECT ... INTO statement # --disable_warnings -drop function if exists foo; +drop procedure if exists bug8937| --enable_warnings -create function `foo` () returns int return 5; -select `foo` (); -drop function `foo`; +create procedure bug8937() +begin + declare s,x,y,z int; + declare a float; + + select sum(data),avg(data),min(data),max(data) into s,x,y,z from t1; + select s,x,y,z; + select avg(data) into a from t1; + select a; +end| + +delete from t1| +insert into t1 (data) values (1), (2), (3), (4), (6)| +call bug8937()| + +drop procedure bug8937| +delete from t1| + + +# +# BUG#NNNN: New bug synopsis +# +#--disable_warnings +#drop procedure if exists bugNNNN| +#--enable_warnings +#create procedure bugNNNN... + + +# Add bugs above this line. Use existing tables t1 and t2 when +# practical, or create table t3, t3 etc temporarily (and drop them). +delimiter ;| +drop table t1,t2; + --- 1.28/mysql-test/r/rpl000001.result 2005-01-14 11:30:05 -08:00 +++ 1.29/mysql-test/r/rpl000001.result 2005-04-05 19:45:07 -07:00 @@ -33,27 +33,12 @@ sum(length(word)) 1022 drop table t1,t3; +create table t1 (n int) engine=myisam; reset master; stop slave; reset slave; -create table t1(n int); -select get_lock("hold_slave",10); -get_lock("hold_slave",10) -1 -explain extended select get_lock("hold_slave",10); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used -Warnings: -Note 1003 select sql_no_cache get_lock(_latin1'hold_slave',10) AS `get_lock("hold_slave",10)` +lock tables t1 read; start slave; -select release_lock("hold_slave"); -release_lock("hold_slave") -1 -explain extended select release_lock("hold_slave"); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used -Warnings: -Note 1003 select sql_no_cache release_lock(_latin1'hold_slave') AS `release_lock("hold_slave")` unlock tables; create table t2(id int); insert into t2 values(connection_id()); --- 1.30/mysql-test/r/warnings.result 2005-01-14 11:30:26 -08:00 +++ 1.31/mysql-test/r/warnings.result 2005-04-05 19:45:08 -07:00 @@ -1,6 +1,33 @@ drop table if exists t1, t2; SET SQL_WARNINGS=1; create table t1 (a int); +create table t1 (a int); +ERROR 42S01: Table 't1' already exists +show count(*) errors; +@@session.error_count +1 +show errors; +Level Code Message +Error 1050 Table 't1' already exists +show warnings; +Level Code Message +Error 1050 Table 't1' already exists +create table t2(a int) default charset qwerty; +ERROR 42000: Unknown character set: 'qwerty' +show count(*) errors; +@@session.error_count +1 +show errors; +Level Code Message +Error 1115 Unknown character set: 'qwerty' +create table t (i); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 +show count(*) errors; +@@session.error_count +1 +show errors; +Level Code Message +Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 insert into t1 values (1); insert into t1 values ("hej"); Warnings: @@ -117,6 +144,25 @@ select @@warning_count; @@warning_count 50 +set max_error_count=0; +show variables like 'max_error_count'; +Variable_name Value +max_error_count 0 +update t1 set b='hi'; +Warnings: +select @@warning_count; +@@warning_count +50 +show warnings; +Level Code Message +set max_error_count=65535; +show variables like 'max_error_count'; +Variable_name Value +max_error_count 65535 +set max_error_count=10; +show variables like 'max_error_count'; +Variable_name Value +max_error_count 10 drop table t1; create table t1 (id int) engine=isam; Warnings: --- 1.17/mysql-test/r/grant2.result 2005-01-14 11:29:47 -08:00 +++ 1.18/mysql-test/r/grant2.result 2005-04-05 19:45:07 -07:00 @@ -1,17 +1,33 @@ SET NAMES binary; drop database if exists mysqltest; +drop database if exists mysqltest_1; +delete from mysql.user where user like 'mysqltest\_%'; +delete from mysql.db where user like 'mysqltest\_%'; +delete from mysql.tables_priv where user like 'mysqltest\_%'; +delete from mysql.columns_priv where user like 'mysqltest\_%'; +flush privileges; +grant all privileges on `my\_1`.* to mysqltest_1@localhost with grant option; +grant create user on *.* to mysqltest_1@localhost; +create user mysqltest_2@localhost; +grant select on `my\_1`.* to mysqltest_2@localhost; +grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass'; +ERROR 42000: You must have privileges to update tables in the mysql database to be able to change passwords for others +grant update on mysql.* to mysqltest_1@localhost; +grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass'; +grant select on `my\_1`.* to mysqltest_3@localhost; +grant insert on mysql.* to mysqltest_1@localhost; +grant select on `my\_1`.* to mysqltest_3@localhost; +grant select on `my\_1`.* to mysqltest_4@localhost identified by 'pass'; delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; delete from mysql.tables_priv where user like 'mysqltest\_%'; delete from mysql.columns_priv where user like 'mysqltest\_%'; flush privileges; grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option; +grant create user on *.* to mysqltest_1@localhost; select current_user(); current_user() mysqltest_1@localhost -select current_user; -current_user -mysqltest_1@localhost grant all privileges on `my\_1`.* to mysqltest_2@localhost with grant option; grant all privileges on `my_%`.* to mysqltest_3@localhost with grant option; ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'my_%' @@ -20,12 +36,12 @@ @@sql_mode NO_AUTO_CREATE_USER grant select on `my\_1`.* to mysqltest_4@localhost with grant option; -ERROR 42000: 'mysqltest_1'@'localhost' is not allowed to create new users +ERROR 42000: Can't find any matching row in the user table grant select on `my\_1`.* to mysqltest_4@localhost identified by 'mypass' with grant option; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT CREATE USER ON *.* TO 'mysqltest_1'@'localhost' GRANT ALL PRIVILEGES ON `my\_%`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION show grants for mysqltest_2@localhost; Grants for mysqltest_2@localhost @@ -36,6 +52,26 @@ delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; flush privileges; +create database mysqltest_1; +grant all privileges on `mysqltest\_1`.* to mysqltest_1@localhost with grant option; +select current_user(); +current_user() +mysqltest_1@localhost +show databases; +Database +information_schema +mysqltest_1 +test +grant all privileges on `mysqltest_1`.* to mysqltest_1@localhost with grant option; +ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest_1' +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT ALL PRIVILEGES ON `mysqltest\_1`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +delete from mysql.user where user like 'mysqltest\_%'; +delete from mysql.db where user like 'mysqltest\_%'; +drop database mysqltest_1; +flush privileges; create database mysqltest; grant INSERT, SELECT on mysqltest.* to mysqltest_1@localhost; flush privileges; @@ -147,8 +183,6 @@ GRANT UPDATE (c2) ON "test"."t2" TO 'mysqltest_1'@'%' GRANT UPDATE ON "test"."t1" TO 'mysqltest_1'@'%' drop user 'mysqltest_1', 'mysqltest_3'; -grant all on test.t1 to 'mysqltest_1'; -ERROR 42000: 'root'@'localhost' is not allowed to create new users drop user 'mysqltest_1'; ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%' drop table t1, t2; @@ -211,7 +245,7 @@ GRANT SELECT ON "mysql".* TO '%@a'@'a' drop user '%@a'@'a'; create user mysqltest_2@localhost; -grant usage on *.* to mysqltest_2@localhost with grant option; +grant create user on *.* to mysqltest_2@localhost; select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 'user' create user mysqltest_A@'%'; @@ -219,21 +253,36 @@ drop user mysqltest_B@'%'; drop user mysqltest_2@localhost; create user mysqltest_3@localhost; -grant all privileges on mysql.* to mysqltest_3@localhost; +grant INSERT,DELETE,UPDATE on mysql.* to mysqltest_3@localhost; +show grants; +Grants for mysqltest_3@localhost +GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost' +GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO 'mysqltest_3'@'localhost' select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; -host user password -% mysqltest_2 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 -localhost mysqltest_3 +ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 'user' insert into mysql.user set host='%', user='mysqltest_B'; Warnings: Warning 1364 Field 'ssl_cipher' doesn't have a default value Warning 1364 Field 'x509_issuer' doesn't have a default value Warning 1364 Field 'x509_subject' doesn't have a default value create user mysqltest_A@'%'; -ERROR 42000: Access denied for user 'mysqltest_3'@'localhost' to database 'mysql' rename user mysqltest_B@'%' to mysqltest_C@'%'; -ERROR 42000: Access denied for user 'mysqltest_3'@'localhost' to database 'mysql' -drop user mysqltest_B@'%'; -ERROR 42000: Access denied for user 'mysqltest_3'@'localhost' to database 'mysql' -drop user mysqltest_B@'%'; +drop user mysqltest_C@'%'; drop user mysqltest_3@localhost; +set @@sql_mode=''; +create database mysqltest_1; +create table mysqltest_1.t1 (i int); +insert into mysqltest_1.t1 values (1),(2),(3); +GRANT ALL ON mysqltest_1.t1 TO mysqltest_1@'127.0.0.0/255.0.0.0'; +show grants for current_user(); +Grants for mysqltest_1@stripped/255.0.0.0 +GRANT USAGE ON *.* TO 'mysqltest_1'@'127.0.0.0/255.0.0.0' +GRANT ALL PRIVILEGES ON `mysqltest_1`.`t1` TO 'mysqltest_1'@'127.0.0.0/255.0.0.0' +select * from t1; +i +1 +2 +3 +REVOKE ALL ON mysqltest_1.t1 FROM mysqltest_1@'127.0.0.0/255.0.0.0'; +drop table mysqltest_1.t1; +drop database mysqltest_1;
| Thread | ||
|---|---|---|
| • bk commit into 5.0 tree (jimw:1.1876) | Jim Winstead | 6 Apr |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 2.130 sec. using MySQL 5.1.14-beta-log