| List: | Commits | « Previous MessageNext Message » | |
| From: | gluh | Date: | February 2 2007 7:25am |
| Subject: | bk commit into 5.1 tree (gluh:1.2419) | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 5.1 repository of gluh. When gluh 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@stripped, 2007-02-02 10:25:45+04:00, gluh@eagle.(none) +33 -0 Merge mysql.com:/home/gluh/MySQL/Merge/5.0-opt into mysql.com:/home/gluh/MySQL/Merge/5.1-opt MERGE: 1.1810.2372.76 mysql-test/r/distinct.result@stripped, 2007-02-02 10:19:31+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.46.1.5 mysql-test/r/func_time.result@stripped, 2007-02-02 10:25:43+04:00, gluh@eagle.(none) +0 -0 manual merge MERGE: 1.47.1.33 mysql-test/r/information_schema.result@stripped, 2007-02-02 10:25:43+04:00, gluh@eagle.(none) +0 -0 manual merge MERGE: 1.87.1.31 mysql-test/r/join_nested.result@stripped, 2007-02-02 10:19:31+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.23.1.2 mysql-test/r/null_key.result@stripped, 2007-02-02 10:19:31+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.35.1.2 mysql-test/r/select.result@stripped, 2007-02-02 10:19:31+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.121.1.26 mysql-test/r/subselect.result@stripped, 2007-02-02 10:19:31+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.134.1.38 mysql-test/r/subselect3.result@stripped, 2007-02-02 10:19:31+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.1.1.3 mysql-test/r/trigger.result@stripped, 2007-02-02 10:19:31+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.29.11.6 mysql-test/t/func_time.test@stripped, 2007-02-02 10:25:43+04:00, gluh@eagle.(none) +0 -0 manual merge MERGE: 1.41.1.26 mysql-test/t/information_schema.test@stripped, 2007-02-02 10:25:43+04:00, gluh@eagle.(none) +0 -0 manual merge MERGE: 1.65.1.24 mysql-test/t/select.test@stripped, 2007-02-02 10:19:31+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.102.1.19 mysql-test/t/subselect.test@stripped, 2007-02-02 10:19:31+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.129.1.10 mysql-test/t/trigger.test@stripped, 2007-02-02 10:19:31+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.34.11.7 sql/filesort.cc@stripped, 2007-02-02 10:19:31+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.105.1.8 sql/item.cc@stripped, 2007-02-02 10:19:31+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.113.1.139 sql/item.h@stripped, 2007-02-02 10:19:31+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.183.1.36 sql/item_cmpfunc.cc@stripped, 2007-02-02 10:19:32+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.187.1.44 sql/item_cmpfunc.h@stripped, 2007-02-02 10:19:32+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.122.2.15 sql/item_func.cc@stripped, 2007-02-02 10:19:32+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.270.1.55 sql/item_subselect.cc@stripped, 2007-02-02 10:19:32+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.113.1.38 sql/item_timefunc.cc@stripped, 2007-02-02 10:19:32+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.100.1.38 sql/mysql_priv.h@stripped, 2007-02-02 10:19:32+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.290.1.142 sql/opt_range.cc@stripped, 2007-02-02 10:25:43+04:00, gluh@eagle.(none) +1 -1 manual merge MERGE: 1.159.1.79 sql/opt_sum.cc@stripped, 2007-02-02 10:19:32+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.44.1.17 sql/sql_base.cc@stripped, 2007-02-02 10:19:32+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.235.1.130 sql/sql_delete.cc@stripped, 2007-02-02 10:25:43+04:00, gluh@eagle.(none) +0 -5 manual merge MERGE: 1.144.1.47 sql/sql_lex.h@stripped, 2007-02-02 10:25:43+04:00, gluh@eagle.(none) +1 -0 manual merge MERGE: 1.175.1.65 sql/sql_parse.cc@stripped, 2007-02-02 10:19:32+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.426.1.176 sql/sql_select.cc@stripped, 2007-02-02 10:19:33+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.312.1.176 sql/sql_show.cc@stripped, 2007-02-02 10:19:33+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.253.1.85 sql/sql_yacc.yy@stripped, 2007-02-02 10:19:33+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.371.1.133 sql/table.cc@stripped, 2007-02-02 10:19:33+04:00, gluh@eagle.(none) +0 -0 Auto merged MERGE: 1.160.1.82 # 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: gluh # Host: eagle.(none) # Root: /home/gluh/MySQL/Merge/5.1-opt/RESYNC --- 1.122/sql/filesort.cc 2007-01-10 14:06:17 +04:00 +++ 1.123/sql/filesort.cc 2007-02-02 10:19:31 +04:00 @@ -434,7 +434,8 @@ static ha_rows find_all_keys(SORTPARAM * byte *ref_pos,*next_pos,ref_buff[MAX_REFLENGTH]; my_off_t record; TABLE *sort_form; - volatile THD::killed_state *killed= ¤t_thd->killed; + THD *thd= current_thd; + volatile THD::killed_state *killed= &thd->killed; handler *file; MY_BITMAP *save_read_set, *save_write_set; DBUG_ENTER("find_all_keys"); @@ -547,6 +548,9 @@ static ha_rows find_all_keys(SORTPARAM * } else file->unlock_row(); + /* It does not make sense to read more keys in case of a fatal error */ + if (thd->net.report_error) + DBUG_RETURN(HA_POS_ERROR); } if (quick_select) { --- 1.232/sql/item.cc 2007-01-24 20:12:28 +04:00 +++ 1.233/sql/item.cc 2007-02-02 10:19:31 +04:00 @@ -1270,7 +1270,10 @@ void Item::split_sum_func2(THD *thd, Ite if (type() == SUM_FUNC_ITEM && skip_registered && ((Item_sum *) this)->ref_by) return; - if (type() != SUM_FUNC_ITEM && with_sum_func) + if ((type() != SUM_FUNC_ITEM && with_sum_func) || + (type() == FUNC_ITEM && + (((Item_func *) this)->functype() == Item_func::ISNOTNULLTEST_FUNC || + ((Item_func *) this)->functype() == Item_func::TRIG_COND_FUNC))) { /* Will split complicated items and ignore simple ones */ split_sum_func(thd, ref_pointer_array, fields); --- 1.152/sql/item_timefunc.cc 2007-01-17 22:45:39 +04:00 +++ 1.153/sql/item_timefunc.cc 2007-02-02 10:19:32 +04:00 @@ -1007,7 +1007,8 @@ longlong Item_func_quarter::val_int() { DBUG_ASSERT(fixed == 1); TIME ltime; - (void) get_arg0_date(<ime, TIME_FUZZY_DATE); + if (get_arg0_date(<ime, TIME_FUZZY_DATE)) + return 0; return (longlong) ((ltime.month+2)/3); } @@ -1647,6 +1648,7 @@ String *Item_func_sec_to_time::val_str(S { DBUG_ASSERT(fixed == 1); TIME ltime; + longlong arg_val= args[0]->val_int(); if ((null_value=args[0]->null_value) || str->alloc(19)) { @@ -1654,7 +1656,7 @@ String *Item_func_sec_to_time::val_str(S return (String*) 0; } - sec_to_time(args[0]->val_int(), args[0]->unsigned_flag, <ime); + sec_to_time(arg_val, args[0]->unsigned_flag, <ime); make_time((DATE_TIME_FORMAT *) 0, <ime, str); return str; @@ -1665,11 +1667,12 @@ longlong Item_func_sec_to_time::val_int( { DBUG_ASSERT(fixed == 1); TIME ltime; + longlong arg_val= args[0]->val_int(); if ((null_value=args[0]->null_value)) return 0; - sec_to_time(args[0]->val_int(), args[0]->unsigned_flag, <ime); + sec_to_time(arg_val, args[0]->unsigned_flag, <ime); return (ltime.neg ? -1 : 1) * ((ltime.hour)*10000 + ltime.minute*100 + ltime.second); --- 1.473/sql/mysql_priv.h 2007-01-24 23:24:49 +04:00 +++ 1.474/sql/mysql_priv.h 2007-02-02 10:19:32 +04:00 @@ -1151,7 +1151,8 @@ bool push_new_name_resolution_context(TH TABLE_LIST *left_op, TABLE_LIST *right_op); void add_join_on(TABLE_LIST *b,Item *expr); -void add_join_natural(TABLE_LIST *a,TABLE_LIST *b,List<String> *using_fields); +void add_join_natural(TABLE_LIST *a,TABLE_LIST *b,List<String> *using_fields, + SELECT_LEX *lex); bool add_proc_to_list(THD *thd, Item *item); TABLE *unlink_open_table(THD *thd,TABLE *list,TABLE *find); void update_non_unique_table_error(TABLE_LIST *update, --- 1.258/sql/opt_range.cc 2007-01-25 01:39:47 +04:00 +++ 1.259/sql/opt_range.cc 2007-02-02 10:25:43 +04:00 @@ -219,6 +219,8 @@ public: } inline void merge_flags(SEL_ARG *arg) { maybe_flag|=arg->maybe_flag; } inline void maybe_smaller() { maybe_flag=1; } + /* Return true iff it's a single-point null interval */ + inline bool is_null_interval() { return maybe_null && max_value[0] == 1; } inline int cmp_min_to_min(SEL_ARG* arg) { return sel_cmp(field,min_value, arg->min_value, min_flag, arg->min_flag); @@ -560,7 +562,8 @@ public: bool is_ror_scan; /* Number of ranges in the last checked tree->key */ uint n_ranges; -}; + uint8 first_null_comp; /* first null component if any, 0 - otherwise */ +} PARAM; class TABLE_READ_PLAN; class TRP_RANGE; @@ -7016,6 +7019,7 @@ check_quick_select(PARAM *param,uint idx DBUG_ENTER("check_quick_select"); param->is_ror_scan= FALSE; + param->first_null_comp= 0; if (!tree) DBUG_RETURN(HA_POS_ERROR); // Can't use it @@ -7116,6 +7120,7 @@ check_quick_keys(PARAM *param,uint idx,S ha_rows records=0, tmp; uint tmp_min_flag, tmp_max_flag, keynr, min_key_length, max_key_length; char *tmp_min_key, *tmp_max_key; + uint8 save_first_null_comp= param->first_null_comp; param->max_key_part=max(param->max_key_part,key_tree->part); if (key_tree->left != &null_element) @@ -7153,6 +7158,9 @@ check_quick_keys(PARAM *param,uint idx,S param->is_ror_scan= FALSE; } + if (!param->first_null_comp && key_tree->is_null_interval()) + param->first_null_comp= key_tree->part+1; + if (key_tree->next_key_part && key_tree->next_key_part->part == key_tree->part+1 && key_tree->next_key_part->type == SEL_ARG::KEY_RANGE) @@ -7196,7 +7204,8 @@ check_quick_keys(PARAM *param,uint idx,S (param->table->key_info[keynr].flags & (HA_NOSAME | HA_END_SPACE_KEY)) == HA_NOSAME && min_key_length == max_key_length && - !memcmp(param->min_key,param->max_key,min_key_length)) + !memcmp(param->min_key,param->max_key,min_key_length) && + !param->first_null_comp) { tmp=1; // Max one record param->n_ranges++; @@ -7271,6 +7280,7 @@ check_quick_keys(PARAM *param,uint idx,S return tmp; records+=tmp; } + param->first_null_comp= save_first_null_comp; return records; } --- 1.59/sql/opt_sum.cc 2006-12-23 23:19:51 +04:00 +++ 1.60/sql/opt_sum.cc 2007-02-02 10:19:32 +04:00 @@ -97,9 +97,9 @@ static ulonglong get_exact_record_count( GROUP BY part. RETURN VALUES - 0 No errors - 1 if all items were resolved - -1 on impossible conditions + 0 no errors + 1 if all items were resolved + HA_ERR_KEY_NOT_FOUND on impossible conditions OR an error number from my_base.h HA_ERR_... if a deadlock or a lock wait timeout happens, for example */ @@ -267,7 +267,7 @@ int opt_sum_query(TABLE_LIST *tables, Li if (error) { if (error == HA_ERR_KEY_NOT_FOUND || error == HA_ERR_END_OF_FILE) - return -1; // No rows matching WHERE + return HA_ERR_KEY_NOT_FOUND; // No rows matching WHERE /* HA_ERR_LOCK_DEADLOCK or some other error */ table->file->print_error(error, MYF(0)); return(error); @@ -354,7 +354,7 @@ int opt_sum_query(TABLE_LIST *tables, Li if (error) { if (error == HA_ERR_KEY_NOT_FOUND || error == HA_ERR_END_OF_FILE) - return -1; // No rows matching WHERE + return HA_ERR_KEY_NOT_FOUND; // No rows matching WHERE /* HA_ERR_LOCK_DEADLOCK or some other error */ table->file->print_error(error, MYF(0)); return(error); --- 1.373/sql/sql_base.cc 2007-01-29 18:07:08 +04:00 +++ 1.374/sql/sql_base.cc 2007-02-02 10:19:32 +04:00 @@ -3764,7 +3764,7 @@ find_field_in_natural_join(THD *thd, TAB { List_iterator_fast<Natural_join_column> field_it(*(table_ref->join_columns)); - Natural_join_column *nj_col; + Natural_join_column *nj_col, *curr_nj_col; Field *found_field; Query_arena *arena, backup; DBUG_ENTER("find_field_in_natural_join"); @@ -3776,14 +3776,21 @@ find_field_in_natural_join(THD *thd, TAB LINT_INIT(arena); LINT_INIT(found_field); - for (;;) + for (nj_col= NULL, curr_nj_col= field_it++; curr_nj_col; + curr_nj_col= field_it++) { - if (!(nj_col= field_it++)) - DBUG_RETURN(NULL); - - if (!my_strcasecmp(system_charset_info, nj_col->name(), name)) - break; + if (!my_strcasecmp(system_charset_info, curr_nj_col->name(), name)) + { + if (nj_col) + { + my_error(ER_NON_UNIQ_ERROR, MYF(0), name, thd->where); + DBUG_RETURN(NULL); + } + nj_col= curr_nj_col; + } } + if (!nj_col) + DBUG_RETURN(NULL); if (nj_col->view_field) { @@ -4684,9 +4691,16 @@ mark_common_columns(THD *thd, TABLE_LIST { bool found= FALSE; const char *field_name_1; + /* true if field_name_1 is a member of using_fields */ + bool is_using_column_1; if (!(nj_col_1= it_1.get_or_create_column_ref(leaf_1))) goto err; field_name_1= nj_col_1->name(); + is_using_column_1= using_fields && + test_if_string_in_list(field_name_1, using_fields); + DBUG_PRINT ("info", ("field_name_1=%s.%s", + nj_col_1->table_name() ? nj_col_1->table_name() : "", + field_name_1)); /* Find a field with the same name in table_ref_2. @@ -4703,6 +4717,10 @@ mark_common_columns(THD *thd, TABLE_LIST if (!(cur_nj_col_2= it_2.get_or_create_column_ref(leaf_2))) goto err; cur_field_name_2= cur_nj_col_2->name(); + DBUG_PRINT ("info", ("cur_field_name_2=%s.%s", + cur_nj_col_2->table_name() ? + cur_nj_col_2->table_name() : "", + cur_field_name_2)); /* Compare the two columns and check for duplicate common fields. @@ -4710,10 +4728,16 @@ mark_common_columns(THD *thd, TABLE_LIST table_ref_2 (then found == TRUE), or if a field in table_ref_2 was already matched by some previous field in table_ref_1 (then cur_nj_col_2->is_common == TRUE). + Note that it is too early to check the columns outside of the + USING list for ambiguity because they are not actually "referenced" + here. These columns must be checked only on unqualified reference + by name (e.g. in SELECT list). */ if (!my_strcasecmp(system_charset_info, field_name_1, cur_field_name_2)) { - if (found || cur_nj_col_2->is_common) + DBUG_PRINT ("info", ("match c1.is_common=%d", nj_col_1->is_common)); + if (cur_nj_col_2->is_common || + (found && (!using_fields || is_using_column_1))) { my_error(ER_NON_UNIQ_ERROR, MYF(0), field_name_1, thd->where); goto err; @@ -4739,9 +4763,7 @@ mark_common_columns(THD *thd, TABLE_LIST clause (if present), mark them as common fields, and add a new equi-join condition to the ON clause. */ - if (nj_col_2 && - (!using_fields || - test_if_string_in_list(field_name_1, using_fields))) + if (nj_col_2 && (!using_fields ||is_using_column_1)) { Item *item_1= nj_col_1->create_item(thd); Item *item_2= nj_col_2->create_item(thd); @@ -4796,6 +4818,13 @@ mark_common_columns(THD *thd, TABLE_LIST eq_cond); nj_col_1->is_common= nj_col_2->is_common= TRUE; + DBUG_PRINT ("info", ("%s.%s and %s.%s are common", + nj_col_1->table_name() ? + nj_col_1->table_name() : "", + nj_col_1->name(), + nj_col_2->table_name() ? + nj_col_2->table_name() : "", + nj_col_2->name())); if (field_1) { --- 1.206/sql/sql_delete.cc 2007-01-23 13:56:53 +04:00 +++ 1.207/sql/sql_delete.cc 2007-02-02 10:25:43 +04:00 @@ -69,14 +69,14 @@ bool mysql_delete(THD *thd, TABLE_LIST * Test if the user wants to delete all rows and deletion doesn't have any side-effects (because of triggers), so we can use optimized handler::delete_all_rows() method. - - If row-based replication is used, we also delete the table row by - row. + We implement fast TRUNCATE for InnoDB even if triggers are present. + TRUNCATE ignores triggers. */ if (!using_limit && const_cond && (!conds || conds->val_int()) && !(specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE)) && - !(table->triggers && table->triggers->has_delete_triggers()) && - !thd->current_stmt_binlog_row_based) + (thd->lex->sql_command == SQLCOM_TRUNCATE || + !(table->triggers && table->triggers->has_delete_triggers())) + ) { /* Update the table->file->stats.records number */ table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); --- 1.258/sql/sql_lex.h 2007-01-24 23:24:49 +04:00 +++ 1.259/sql/sql_lex.h 2007-02-02 10:25:43 +04:00 @@ -620,6 +620,21 @@ public: /* index in the select list of the expression currently being fixed */ int cur_pos_in_select_list; + List<udf_func> udf_list; /* udf function calls stack */ + /* + This is a copy of the original JOIN USING list that comes from + the parser. The parser : + 1. Sets the natural_join of the second TABLE_LIST in the join + and the st_select_lex::prev_join_using. + 2. Makes a parent TABLE_LIST and sets its is_natural_join/ + join_using_fields members. + 3. Uses the wrapper TABLE_LIST as a table in the upper level. + We cannot assign directly to join_using_fields in the parser because + at stage (1.) the parent TABLE_LIST is not constructed yet and + the assignment will override the JOIN USING fields of the lower level + joins on the right. + */ + List<String> *prev_join_using; void init_query(); void init_select(); st_select_lex_unit* master_unit(); --- 1.621/sql/sql_parse.cc 2007-02-01 12:59:05 +04:00 +++ 1.622/sql/sql_parse.cc 2007-02-02 10:19:32 +04:00 @@ -6666,11 +6666,8 @@ TABLE_LIST *st_select_lex::nest_last_joi If this is a JOIN ... USING, move the list of joined fields to the table reference that describes the join. */ - if (table->join_using_fields) - { - ptr->join_using_fields= table->join_using_fields; - table->join_using_fields= NULL; - } + if (prev_join_using) + ptr->join_using_fields= prev_join_using; } } join_list->push_front(ptr); @@ -6926,6 +6923,7 @@ void add_join_on(TABLE_LIST *b, Item *ex a Left join argument b Right join argument using_fields Field names from USING clause + lex The current st_select_lex IMPLEMENTATION This function marks that table b should be joined with a either via @@ -6954,10 +6952,11 @@ void add_join_on(TABLE_LIST *b, Item *ex None */ -void add_join_natural(TABLE_LIST *a, TABLE_LIST *b, List<String> *using_fields) +void add_join_natural(TABLE_LIST *a, TABLE_LIST *b, List<String> *using_fields, + SELECT_LEX *lex) { b->natural_join= a; - b->join_using_fields= using_fields; + lex->prev_join_using= using_fields; } --- 1.486/sql/sql_select.cc 2007-01-29 18:07:08 +04:00 +++ 1.487/sql/sql_select.cc 2007-02-02 10:19:33 +04:00 @@ -717,11 +717,20 @@ JOIN::optimize() { int res; /* - opt_sum_query() returns -1 if no rows match to the WHERE conditions, - or 1 if all items were resolved, or 0, or an error number HA_ERR_... + opt_sum_query() returns HA_ERR_KEY_NOT_FOUND if no rows match + to the WHERE conditions, + or 1 if all items were resolved, + or 0, or an error number HA_ERR_... */ if ((res=opt_sum_query(select_lex->leaf_tables, all_fields, conds))) { + if (res == HA_ERR_KEY_NOT_FOUND) + { + DBUG_PRINT("info",("No matching min/max row")); + zero_result_cause= "No matching min/max row"; + error=0; + DBUG_RETURN(0); + } if (res > 1) { thd->fatal_error(); @@ -729,13 +738,6 @@ JOIN::optimize() DBUG_PRINT("error",("Error from opt_sum_query")); DBUG_RETURN(1); } - if (res < 0) - { - DBUG_PRINT("info",("No matching min/max row")); - zero_result_cause= "No matching min/max row"; - error=0; - DBUG_RETURN(0); - } DBUG_PRINT("info",("Select tables optimized away")); zero_result_cause= "Select tables optimized away"; tables_list= 0; // All tables resolved @@ -865,6 +867,13 @@ JOIN::optimize() { ORDER *org_order= order; order=remove_const(this, order,conds,1, &simple_order); + if (thd->net.report_error) + { + error= 1; + DBUG_PRINT("error",("Error from remove_const")); + DBUG_RETURN(1); + } + /* If we are using ORDER BY NULL or ORDER BY const_expression, return result in any order (even if we are using a GROUP BY) @@ -874,10 +883,11 @@ JOIN::optimize() } /* Check if we can optimize away GROUP BY/DISTINCT. - We can do that if there are no aggregate functions and the + We can do that if there are no aggregate functions, the fields in DISTINCT clause (if present) and/or columns in GROUP BY (if present) contain direct references to all key parts of - an unique index (in whatever order). + an unique index (in whatever order) and if the key parts of the + unique index cannot contain NULLs. Note that the unique keys for DISTINCT and GROUP BY should not be the same (as long as they are unique). @@ -972,6 +982,12 @@ JOIN::optimize() group_list= remove_const(this, (old_group_list= group_list), conds, rollup.state == ROLLUP::STATE_NONE, &simple_group); + if (thd->net.report_error) + { + error= 1; + DBUG_PRINT("error",("Error from remove_const")); + DBUG_RETURN(1); + } if (old_group_list && !group_list) select_distinct= 0; } @@ -988,6 +1004,12 @@ JOIN::optimize() { group_list= procedure->group= remove_const(this, procedure->group, conds, 1, &simple_group); + if (thd->net.report_error) + { + error= 1; + DBUG_PRINT("error",("Error from remove_const")); + DBUG_RETURN(1); + } calc_group_buffer(this, group_list); } @@ -6578,6 +6600,8 @@ remove_const(JOIN *join,ORDER *first_ord *simple_order=0; // Must do a temp table to sort else if (!(order_tables & not_const_tables)) { + if (order->item[0]->with_subselect) + order->item[0]->val_str(&order->item[0]->str_value); DBUG_PRINT("info",("removing: %s", order->item[0]->full_name())); continue; // skip const item } @@ -12107,7 +12131,7 @@ test_if_subkey(ORDER *order, TABLE *tabl /* - Check if GROUP BY/DISTINCT can be optimized away because the set is + Check if GROUP BY/DISTINCT can be optimized away because the set is already known to be distinct. SYNOPSIS @@ -12115,7 +12139,7 @@ test_if_subkey(ORDER *order, TABLE *tabl table The table to operate on. find_func function to iterate over the list and search for a field - + DESCRIPTION Used in removing the GROUP BY/DISTINCT of the following types of statements: @@ -12126,12 +12150,13 @@ test_if_subkey(ORDER *order, TABLE *tabl then <any combination of a,b,c>,{whatever} is also distinct This function checks if all the key parts of any of the unique keys - of the table are referenced by a list : either the select list + of the table are referenced by a list : either the select list through find_field_in_item_list or GROUP BY list through find_field_in_order_list. - If the above holds then we can safely remove the GROUP BY/DISTINCT, + If the above holds and the key parts cannot contain NULLs then we + can safely remove the GROUP BY/DISTINCT, as no result set can be more distinct than an unique key. - + RETURN VALUE 1 found 0 not found. @@ -12154,7 +12179,8 @@ list_contains_unique_index(TABLE *table, key_part < key_part_end; key_part++) { - if (!find_func(key_part->field, data)) + if (key_part->field->maybe_null() || + !find_func(key_part->field, data)) break; } if (key_part == key_part_end) --- 1.387/sql/sql_show.cc 2007-01-24 19:51:06 +04:00 +++ 1.388/sql/sql_show.cc 2007-02-02 10:19:33 +04:00 @@ -4985,6 +4985,7 @@ bool get_schema_tables_result(JOIN *join if (is_subselect) // is subselect { + table_list->table->file->extra(HA_EXTRA_NO_CACHE); table_list->table->file->extra(HA_EXTRA_RESET_STATE); table_list->table->file->delete_all_rows(); free_io_cache(table_list->table); --- 1.535/sql/sql_yacc.yy 2007-01-25 01:43:05 +04:00 +++ 1.536/sql/sql_yacc.yy 2007-02-02 10:19:33 +04:00 @@ -7116,11 +7116,11 @@ join_table: YYERROR_UNLESS($1 && $3); } '(' using_list ')' - { add_join_natural($1,$3,$7); $$=$3; } + { add_join_natural($1,$3,$7,Select); $$=$3; } | table_ref NATURAL JOIN_SYM table_factor { YYERROR_UNLESS($1 && ($$=$4)); - add_join_natural($1,$4,NULL); + add_join_natural($1,$4,NULL,Select); } /* LEFT JOIN variants */ @@ -7147,11 +7147,15 @@ join_table: YYERROR_UNLESS($1 && $5); } USING '(' using_list ')' - { add_join_natural($1,$5,$9); $5->outer_join|=JOIN_TYPE_LEFT; $$=$5; } + { + add_join_natural($1,$5,$9,Select); + $5->outer_join|=JOIN_TYPE_LEFT; + $$=$5; + } | table_ref NATURAL LEFT opt_outer JOIN_SYM table_factor { YYERROR_UNLESS($1 && $6); - add_join_natural($1,$6,NULL); + add_join_natural($1,$6,NULL,Select); $6->outer_join|=JOIN_TYPE_LEFT; $$=$6; } @@ -7185,12 +7189,12 @@ join_table: LEX *lex= Lex; if (!($$= lex->current_select->convert_right_join())) YYABORT; - add_join_natural($$,$5,$9); + add_join_natural($$,$5,$9,Select); } | table_ref NATURAL RIGHT opt_outer JOIN_SYM table_factor { YYERROR_UNLESS($1 && $6); - add_join_natural($6,$1,NULL); + add_join_natural($6,$1,NULL,Select); LEX *lex= Lex; if (!($$= lex->current_select->convert_right_join())) YYABORT; --- 1.270/sql/table.cc 2007-01-25 01:48:10 +04:00 +++ 1.271/sql/table.cc 2007-02-02 10:19:33 +04:00 @@ -3430,6 +3430,7 @@ Field *Natural_join_column::field() const char *Natural_join_column::table_name() { + DBUG_ASSERT(table_ref); return table_ref->alias; } --- 1.163/mysql-test/r/subselect.result 2007-01-25 11:00:05 +04:00 +++ 1.164/mysql-test/r/subselect.result 2007-02-02 10:19:31 +04:00 @@ -3046,6 +3046,80 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE DROP TABLE t1; +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (2), (4), (1), (3); +CREATE TABLE t2 (b int, c int); +INSERT INTO t2 VALUES +(2,1), (1,3), (2,1), (4,4), (2,2), (1,4); +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 ); +a +2 +4 +1 +3 +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1); +ERROR 21000: Subquery returns more than 1 row +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a; +a +1 +2 +3 +4 +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a; +ERROR 21000: Subquery returns more than 1 row +SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2); +b MAX(c) +1 4 +2 2 +4 4 +SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1); +ERROR 21000: Subquery returns more than 1 row +SELECT a FROM t1 GROUP BY a +HAVING IFNULL((SELECT b FROM t2 WHERE b > 2), +(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; +a +1 +2 +3 +4 +SELECT a FROM t1 GROUP BY a +HAVING IFNULL((SELECT b FROM t2 WHERE b > 1), +(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; +ERROR 21000: Subquery returns more than 1 row +SELECT a FROM t1 GROUP BY a +HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), +(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; +a +4 +SELECT a FROM t1 GROUP BY a +HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), +(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3; +ERROR 21000: Subquery returns more than 1 row +SELECT a FROM t1 +ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2), +(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); +a +2 +4 +1 +3 +SELECT a FROM t1 +ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1), +(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); +ERROR 21000: Subquery returns more than 1 row +SELECT a FROM t1 +ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), +(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); +a +2 +1 +3 +4 +SELECT a FROM t1 +ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), +(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1,t2; create table t1 (df decimal(5,1)); insert into t1 values(1.1); insert into t1 values(2.2); --- 1.142/mysql-test/t/subselect.test 2007-01-23 14:34:34 +04:00 +++ 1.143/mysql-test/t/subselect.test 2007-02-02 10:19:31 +04:00 @@ -2000,6 +2000,65 @@ SELECT a FROM t1 WHERE (SELECT 1 FROM DU EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL; DROP TABLE t1; + +# +# Bug 24653: sorting by expressions containing subselects +# that return more than one row +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (2), (4), (1), (3); + +CREATE TABLE t2 (b int, c int); +INSERT INTO t2 VALUES + (2,1), (1,3), (2,1), (4,4), (2,2), (1,4); + +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 ); +--error 1242 +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1); +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a; +--error 1242 +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a; + +SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2); +--error 1242 +SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1); + + +SELECT a FROM t1 GROUP BY a + HAVING IFNULL((SELECT b FROM t2 WHERE b > 2), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; +--error 1242 +SELECT a FROM t1 GROUP BY a + HAVING IFNULL((SELECT b FROM t2 WHERE b > 1), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; + +SELECT a FROM t1 GROUP BY a + HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; +--error 1242 +SELECT a FROM t1 GROUP BY a + HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), + (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3; + +SELECT a FROM t1 + ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); +--error 1242 +SELECT a FROM t1 + ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1), + (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); + +SELECT a FROM t1 + ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); +--error 1242 +SELECT a FROM t1 + ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), + (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); + +DROP TABLE t1,t2; + # End of 4.1 tests # --- 1.142/sql/item_subselect.cc 2007-01-24 23:28:24 +04:00 +++ 1.143/sql/item_subselect.cc 2007-02-02 10:19:32 +04:00 @@ -238,6 +238,10 @@ bool Item_subselect::exec() { int res; + if (thd->net.report_error) + /* Do not execute subselect in case of a fatal error */ + return 1; + res= engine->exec(); if (engine_changed) --- 1.56/mysql-test/r/trigger.result 2007-01-24 19:51:04 +04:00 +++ 1.57/mysql-test/r/trigger.result 2007-02-02 10:19:31 +04:00 @@ -1241,6 +1241,31 @@ i j 2 2 13 13 drop table t1; +CREATE TABLE t1 (a INT PRIMARY KEY); +CREATE TABLE t2 (a INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW +INSERT INTO t2 VALUES (OLD.a); +FLUSH STATUS; +TRUNCATE t1; +SHOW STATUS LIKE 'handler_delete'; +Variable_name Value +Handler_delete 0 +SELECT COUNT(*) FROM t2; +COUNT(*) +0 +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +DELETE FROM t2; +FLUSH STATUS; +DELETE FROM t1; +SHOW STATUS LIKE 'handler_delete'; +Variable_name Value +Handler_delete 8 +SELECT COUNT(*) FROM t2; +COUNT(*) +8 +DROP TRIGGER trg_t1; +DROP TABLE t1,t2; drop table if exists t1; drop function if exists f1; create table t1 (i int); --- 1.66/mysql-test/t/trigger.test 2007-01-24 20:12:28 +04:00 +++ 1.67/mysql-test/t/trigger.test 2007-02-02 10:19:31 +04:00 @@ -1505,6 +1505,31 @@ update t1 set i= i+ 10 where j > 2; select * from t1; drop table t1; +# +# Bug#23556 TRUNCATE TABLE still maps to DELETE +# +CREATE TABLE t1 (a INT PRIMARY KEY); +CREATE TABLE t2 (a INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); + +CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW + INSERT INTO t2 VALUES (OLD.a); + +FLUSH STATUS; +TRUNCATE t1; +SHOW STATUS LIKE 'handler_delete'; +SELECT COUNT(*) FROM t2; + +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +DELETE FROM t2; + +FLUSH STATUS; +DELETE FROM t1; +SHOW STATUS LIKE 'handler_delete'; +SELECT COUNT(*) FROM t2; + +DROP TRIGGER trg_t1; +DROP TABLE t1,t2; # # Bug #23651 "Server crashes when trigger which uses stored function --- 1.144/mysql-test/r/information_schema.result 2006-12-09 07:27:52 +04:00 +++ 1.145/mysql-test/r/information_schema.result 2007-02-02 10:25:43 +04:00 @@ -1342,6 +1342,16 @@ id select_type table type possible_keys 1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 2 DERIVED tables ALL NULL NULL NULL NULL 2 drop view v1; +create table t1 (f1 int(11)); +create table t2 (f1 int(11), f2 int(11)); +select table_name from information_schema.tables +where table_schema = 'test' and table_name not in +(select table_name from information_schema.columns +where table_schema = 'test' and column_name = 'f3'); +table_name +t1 +t2 +drop table t1,t2; End of 5.0 tests. select * from information_schema.engines WHERE ENGINE="MyISAM"; ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS --- 1.92/mysql-test/t/information_schema.test 2006-12-04 23:02:30 +04:00 +++ 1.93/mysql-test/t/information_schema.test 2007-02-02 10:25:43 +04:00 @@ -988,6 +988,18 @@ explain select * from v1; explain select * from (select table_name from information_schema.tables) as a; drop view v1; +# +# Bug#23299 Some queries against INFORMATION_SCHEMA with subqueries fail +# +create table t1 (f1 int(11)); +create table t2 (f1 int(11), f2 int(11)); + +select table_name from information_schema.tables +where table_schema = 'test' and table_name not in +(select table_name from information_schema.columns + where table_schema = 'test' and column_name = 'f3'); +drop table t1,t2; + --echo End of 5.0 tests. # # Show engines --- 1.25/mysql-test/r/join_nested.result 2007-01-10 14:06:17 +04:00 +++ 1.26/mysql-test/r/join_nested.result 2007-02-02 10:19:31 +04:00 @@ -1605,3 +1605,31 @@ WHERE t1.id='5'; id ct pc nm 5 NULL NULL NULL DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT, c INT); +CREATE TABLE t4 (a INT, c INT); +CREATE TABLE t5 (a INT, c INT); +SELECT b FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) +LEFT JOIN t5 USING (a)) USING (a); +b +SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) +LEFT JOIN t5 USING (a)) USING (a); +ERROR 23000: Column 'c' in field list is ambiguous +SELECT b FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a) +JOIN t5 USING (a)) USING (a); +b +SELECT c FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a) +JOIN t5 USING (a)) USING (a); +ERROR 23000: Column 'c' in field list is ambiguous +DROP TABLE t1,t2,t3,t4,t5; +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (a INT, b INT); +CREATE TABLE t3 (a INT, b INT); +INSERT INTO t1 VALUES (1,1); +INSERT INTO t2 VALUES (1,1); +INSERT INTO t3 VALUES (1,1); +SELECT * FROM t1 JOIN (t2 JOIN t3 USING (b)) USING (a); +ERROR 23000: Column 'a' in from clause is ambiguous +DROP TABLE t1,t2,t3; +End of 5.0 tests --- 1.50/mysql-test/r/distinct.result 2007-01-08 14:30:57 +04:00 +++ 1.51/mysql-test/r/distinct.result 2007-02-02 10:19:31 +04:00 @@ -530,7 +530,8 @@ id select_type table type possible_keys EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -CREATE TABLE t2(a INT, b INT, c INT, d INT, PRIMARY KEY (a,b)); +CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT, +PRIMARY KEY (a,b)); INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4); EXPLAIN SELECT DISTINCT a FROM t2; id select_type table type possible_keys key key_len ref rows Extra @@ -644,3 +645,26 @@ SELECT COUNT(*) FROM COUNT(*) 2 DROP TABLE t1, t2; +CREATE TABLE t1 (a INT, UNIQUE (a)); +INSERT INTO t1 VALUES (4),(null),(2),(1),(null),(3); +EXPLAIN SELECT DISTINCT a FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 6 Using index +SELECT DISTINCT a FROM t1; +a +NULL +1 +2 +3 +4 +EXPLAIN SELECT a FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 6 Using index +SELECT a FROM t1 GROUP BY a; +a +NULL +1 +2 +3 +4 +DROP TABLE t1; --- 1.92/mysql-test/r/func_time.result 2006-11-30 00:30:58 +04:00 +++ 1.93/mysql-test/r/func_time.result 2007-02-02 10:25:43 +04:00 @@ -1183,6 +1183,20 @@ set time_zone= @@global.time_zone; select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE; str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE NULL +CREATE TABLE t1 (a int, t1 time, t2 time, d date, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (1, '10:00:00', NULL, NULL), +(2, '11:00:00', '11:15:00', '1972-02-06'); +SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) +FROM t1; +t1 t2 SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ) QUARTER(d) +10:00:00 NULL NULL NULL +11:00:00 11:15:00 00:15:00 1 +SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) +FROM t1 ORDER BY a DESC; +t1 t2 SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ) QUARTER(d) +11:00:00 11:15:00 00:15:00 1 +10:00:00 NULL NULL NULL +DROP TABLE t1; End of 5.0 tests select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND) --- 1.37/mysql-test/r/null_key.result 2006-08-12 02:06:20 +05:00 +++ 1.38/mysql-test/r/null_key.result 2007-02-02 10:19:31 +04:00 @@ -30,7 +30,7 @@ id select_type table type possible_keys 1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b a 5 const 2 Using where; Using index +1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index explain select * from t1 where a > 1 and a < 3 limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index @@ -258,7 +258,7 @@ INSERT INTO t1 VALUES (1,NULL),(2,NULL), INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); explain select id from t1 where uniq_id is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1 idx1 5 const 1 Using where +1 SIMPLE t1 ref idx1 idx1 5 const 5 Using where explain select id from t1 where uniq_id =1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const idx1 idx1 5 const 1 --- 1.145/mysql-test/r/select.result 2007-01-24 20:12:28 +04:00 +++ 1.146/mysql-test/r/select.result 2007-02-02 10:19:31 +04:00 @@ -3642,3 +3642,89 @@ INSERT into t1 values (1), (2), (3); SELECT * FROM t1 LIMIT 2, -1; 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 '-1' at line 1 DROP TABLE t1; +CREATE TABLE t1 ( +ID_with_null int NULL, +ID_better int NOT NULL, +INDEX idx1 (ID_with_null), +INDEX idx2 (ID_better) +); +INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3); +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL; +COUNT(*) +128 +SELECT COUNT(*) FROM t1 WHERE ID_better=1; +COUNT(*) +2 +EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +DROP INDEX idx1 ON t1; +CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); +EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +DROP TABLE t1; +CREATE TABLE t1 ( +ID1_with_null int NULL, +ID2_with_null int NULL, +ID_better int NOT NULL, +INDEX idx1 (ID1_with_null, ID2_with_null), +INDEX idx2 (ID_better) +); +INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3), +(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3); +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; +COUNT(*) +24 +SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL; +COUNT(*) +24 +SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL; +COUNT(*) +192 +SELECT COUNT(*) FROM t1 WHERE ID_better=1; +COUNT(*) +2 +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +DROP INDEX idx1 ON t1; +CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND +(ID2_with_null=1 OR ID2_with_null=2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +DROP TABLE t1; --- 1.77/mysql-test/t/func_time.test 2006-11-30 00:30:58 +04:00 +++ 1.78/mysql-test/t/func_time.test 2007-02-02 10:25:43 +04:00 @@ -692,6 +692,18 @@ set time_zone= @@global.time_zone; # select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE; + +# +# Bug #25643: SEC_TO_TIME function problem +# +CREATE TABLE t1 (a int, t1 time, t2 time, d date, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (1, '10:00:00', NULL, NULL), + (2, '11:00:00', '11:15:00', '1972-02-06'); +SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) + FROM t1; +SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) + FROM t1 ORDER BY a DESC; +DROP TABLE t1; --echo End of 5.0 tests # --- 1.116/mysql-test/t/select.test 2007-01-24 20:12:28 +04:00 +++ 1.117/mysql-test/t/select.test 2007-02-02 10:19:31 +04:00 @@ -3134,3 +3134,77 @@ SELECT * FROM t1 LIMIT 2, -1; DROP TABLE t1; +# +# 25407: wrong estimate of NULL keys for unique indexes +# + +CREATE TABLE t1 ( + ID_with_null int NULL, + ID_better int NOT NULL, + INDEX idx1 (ID_with_null), + INDEX idx2 (ID_better) +); + +INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3); +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; + +SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL; +SELECT COUNT(*) FROM t1 WHERE ID_better=1; + +EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; + +DROP INDEX idx1 ON t1; +CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); + +EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; + +DROP TABLE t1; + +CREATE TABLE t1 ( + ID1_with_null int NULL, + ID2_with_null int NULL, + ID_better int NOT NULL, + INDEX idx1 (ID1_with_null, ID2_with_null), + INDEX idx2 (ID_better) +); + +INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3), + (3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3); + +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; + +SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; +SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL; +SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL; +SELECT COUNT(*) FROM t1 WHERE ID_better=1; + +EXPLAIN SELECT * FROM t1 + WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; +EXPLAIN SELECT * FROM t1 + WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; +EXPLAIN SELECT * FROM t1 + WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; + +DROP INDEX idx1 ON t1; +CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); + +EXPLAIN SELECT * FROM t1 + WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; +EXPLAIN SELECT * FROM t1 + WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; +EXPLAIN SELECT * FROM t1 + WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; +EXPLAIN SELECT * FROM t1 + WHERE ID_better=1 AND ID1_with_null IS NULL AND + (ID2_with_null=1 OR ID2_with_null=2); + +DROP TABLE t1; --- 1.4/mysql-test/r/subselect3.result 2007-01-25 11:00:05 +04:00 +++ 1.5/mysql-test/r/subselect3.result 2007-02-02 10:19:31 +04:00 @@ -629,3 +629,19 @@ cc NULL NULL aa 1 1 bb NULL NULL drop table t1,t2; +create table t1 (a int, b int); +insert into t1 values (0,0), (2,2), (3,3); +create table t2 (a int, b int); +insert into t2 values (1,1), (3,3); +select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; +a b Z +0 0 0 +2 2 0 +3 3 1 +insert into t2 values (NULL,4); +select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; +a b Z +0 0 0 +2 2 0 +3 3 1 +drop table t1,t2;
| Thread | ||
|---|---|---|
| • bk commit into 5.1 tree (gluh:1.2419) | gluh | 2 Feb |
© 1995-2008 MySQL AB. All rights reserved.
Page generated in 0.949 sec. using MySQL 5.1.14-beta-log