| List: | Internals | « Previous MessageNext Message » | |
| From: | Sergey Petrunia | Date: | October 20 2005 5:47pm |
| Subject: | bk commit - 4.1 tree (sergefp:1.2485) BUG#9622 | ||
| View as plain text | |||
ChangeSet 1.2485 05/10/20 19:42:32 sergefp@stripped +12 -0 BUG#9622, stage 2, work together with fix for BUG12232: added "nulls_ignored" index statistics collection method for MyISAM tables. (notification trigger: this is about BUG#9622). sql/mysqld.cc 1.603 05/10/20 19:42:23 sergefp@stripped +16 -5 BUG#9622: Added MI_STATS_METHOD_IGNORE_NULLS statistics collection method. sql/ha_myisam.cc 1.162 05/10/20 19:42:23 sergefp@stripped +1 -1 BUG#9622: Added MI_STATS_METHOD_IGNORE_NULLS statistics collection method. mysys/my_handler.c 1.19 05/10/20 19:42:22 sergefp@stripped +97 -2 BUG#9622: ha_key_cmp() now supports new SEARCH_RETURN_B_POS flag, added ha_find_null() mysql-test/t/myisam.test 1.42 05/10/20 19:42:22 sergefp@stripped +19 -0 Testcase for BUG9622 mysql-test/r/myisam.result 1.54 05/10/20 19:42:22 sergefp@stripped +32 -0 Testcase for BUG9622 myisam/sort.c 1.45 05/10/20 19:42:22 sergefp@stripped +2 -2 BUG#9622: nulls_ignored index statistics collection method for MyISAM Now 2 arrays are used to collect index statistics. myisam/myisamdef.h 1.81 05/10/20 19:42:22 sergefp@stripped +3 -0 BUG#9622: nulls_ignored index statistics collection method for MyISAM Now 2 arrays are used to collect index statistics. myisam/myisamchk.c 1.129 05/10/20 19:42:22 sergefp@stripped +20 -5 BUG#9622: Added nulls_ignored index statistics collection method for MyISAM myisam/mi_check.c 1.152 05/10/20 19:42:22 sergefp@stripped +150 -13 BUG#9622: Added MI_STATS_METHOD_IGNORE_NULLS statistics collection method functions. include/myisam.h 1.65 05/10/20 19:42:22 sergefp@stripped +9 -2 BUG#9622: nulls_ignored index statistics collection method for MyISAM Added MI_STATS_METHOD_IGNORE_NULLS, now 2 arrays are used to collect index statistics. include/my_handler.h 1.6 05/10/20 19:42:22 sergefp@stripped +2 -0 BUG#9622: nulls_ignored index statistics collection method for MyISAM Added ha_fund_null(). include/my_base.h 1.63 05/10/20 19:42:22 sergefp@stripped +2 -0 BUG#9622: nulls_ignored index statistics collection method for MyISAM Added SEARCH_RETURN_B_POS flag to ha_key_cmp # 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: sergefp # Host: newbox.mylan # Root: /home/psergey/mysql-4.1-nulls-stats-r2 --- 1.62/include/my_base.h 2005-02-18 15:10:05 +03:00 +++ 1.63/include/my_base.h 2005-10-20 19:42:22 +04:00 @@ -319,6 +319,8 @@ #define SEARCH_NULL_ARE_EQUAL 32768 /* NULL in keys are equal */ #define SEARCH_NULL_ARE_NOT_EQUAL 65536 /* NULL in keys are not equal */ +#define SEARCH_RETURN_B_POS (65536*2) /* see ha_key_cmp for description */ + /* bits in opt_flag */ #define QUICK_USED 1 #define READ_CACHE_USED 2 --- 1.64/include/myisam.h 2005-09-21 02:18:25 +04:00 +++ 1.65/include/myisam.h 2005-10-20 19:42:22 +04:00 @@ -322,7 +322,9 @@ /* Treat NULLs as inequal when collecting statistics (default for 4.1/5.0) */ MI_STATS_METHOD_NULLS_NOT_EQUAL, /* Treat NULLs as equal when collecting statistics (like 4.0 did) */ - MI_STATS_METHOD_NULLS_EQUAL + MI_STATS_METHOD_NULLS_EQUAL, + /* Ignore NULLs - count tuples without NULLs only */ + MI_STATS_METHOD_IGNORE_NULLS } enum_mi_stats_method; typedef struct st_mi_check_param @@ -349,7 +351,11 @@ int tmpfile_createflag; myf myf_rw; IO_CACHE read_cache; + + /*The next two are used to collect statistics */ ulonglong unique_count[MI_MAX_KEY_SEG+1]; + ulonglong notnull_count[MI_MAX_KEY_SEG+1]; + ha_checksum key_crc[MI_MAX_POSSIBLE_KEY]; ulong rec_per_key_part[MI_MAX_KEY_SEG*MI_MAX_POSSIBLE_KEY]; void *thd; @@ -409,7 +415,8 @@ my_bool repair); int update_state_info(MI_CHECK *param, MI_INFO *info,uint update); void update_key_parts(MI_KEYDEF *keyinfo, ulong *rec_per_key_part, - ulonglong *unique, ulonglong records); + ulonglong *unique, ulonglong *notnull, + ulonglong records); int filecopy(MI_CHECK *param, File to,File from,my_off_t start, my_off_t length, const char *type); int movepoint(MI_INFO *info,byte *record,my_off_t oldpos, --- 1.151/myisam/mi_check.c 2005-09-24 01:39:46 +04:00 +++ 1.152/myisam/mi_check.c 2005-10-20 19:42:22 +04:00 @@ -391,7 +391,10 @@ found_keys++; param->record_checksum=init_checksum; + bzero((char*) ¶m->unique_count,sizeof(param->unique_count)); + bzero((char*) ¶m->notnull_count,sizeof(param->notnull_count)); + if ((!(param->testflag & T_SILENT))) printf ("- check data record references index: %d\n",key+1); if (keyinfo->flag & HA_FULLTEXT) @@ -496,7 +499,9 @@ if (param->testflag & T_STATISTICS) update_key_parts(keyinfo, rec_per_key_part, param->unique_count, - (ulonglong) info->state->records); + param->stats_method == MI_STATS_METHOD_IGNORE_NULLS? + param->notnull_count: NULL, + (ulonglong)info->state->records); } if (param->testflag & T_INFO) { @@ -552,6 +557,81 @@ return 1; } +/* + "Ignore NULLs" statistics collection method: process first index tuple. + + SYNOPSIS + ha_collect_stats_ignore_nulls_first() + keyseg IN Array of key part descriptions + notnull OUT Array, notnull[i] = (number of {keypart1...keypart_i} + tuples that don't contain NULLs). + key IN Key tuple + + DESCRIPTION + Find first NULL value, all prefixes that doesn't include it are not-null + tuples, other tuples aren't. +*/ + +static +void mi_collect_stats_nonulls_first(HA_KEYSEG *keyseg, ulonglong *notnull, + uchar *key) +{ + uint first_null, kp; + first_null= ha_find_null(keyseg, key) - keyseg; + for (kp= 0; kp < first_null; kp++) + notnull[kp]++; +} + + +/* + "Ignore NULLs" statistics collection method: process next index tuple. + + SYNOPSIS + ha_collect_stats_ignore_nulls_first() + keyseg IN Array of key part descriptions + notnull OUT Array, notnull[i] = (number of {keypart1...keypart_i} + tuples that don't contain NULLs). + key IN Key tuple + + IMPLEMENTATION + Find first keypart K1 where the values are different or next_tuple's value + is NULL; + + Tuple {keypart1, ..., keypart_K1-1} and its prefixes are not-NULL, + identical tuples. + Find first keypart K2 in next tuple that has NULL value; + + Tuples from {keypart1, ..., key_part_K1} to {keypart1, ..., key_part_K2}, + not including the latter, are not-NULL, different tuples. + + Ignore all other tuples. + + RETURN + 1 + number of first keypart where values differ. +*/ + +static +int mi_collect_stats_nonulls_next(HA_KEYSEG *keyseg, ulonglong *notnull, + uchar *prev_key, uchar *last_key) +{ + uint diffs[2]; + uint first_null_seg, kp; + + /* Get first keypart where values are different or either of them is null */ + ha_key_cmp(keyseg, prev_key, last_key, USE_WHOLE_KEY, + SEARCH_FIND | SEARCH_NULL_ARE_NOT_EQUAL | SEARCH_RETURN_B_POS, + diffs); + + /* Get description for this key part */ + HA_KEYSEG *seg= keyseg + diffs[0] - 1; + first_null_seg= ha_find_null(seg, last_key + diffs[1]) - keyseg; + for (kp= 0; kp < first_null_seg; kp++) + notnull[kp]++; + + return diffs[0]; +} + + /* Check if index is ok */ static int chk_index(MI_CHECK *param, MI_INFO *info, MI_KEYDEF *keyinfo, @@ -641,8 +721,20 @@ ha_key_cmp(keyinfo->seg,info->lastkey,key,USE_WHOLE_KEY, SEARCH_FIND | SEARCH_NULL_ARE_NOT_EQUAL, &diff_pos); + else if (param->stats_method == MI_STATS_METHOD_IGNORE_NULLS) + { + diff_pos= mi_collect_stats_nonulls_next(keyinfo->seg, + param->notnull_count, + info->lastkey, key); + } param->unique_count[diff_pos-1]++; } + else + { + if (param->stats_method == MI_STATS_METHOD_IGNORE_NULLS) + mi_collect_stats_nonulls_first(keyinfo->seg, param->notnull_count, + key); + } } (*key_checksum)+= mi_byte_checksum((byte*) key, key_length- info->s->rec_reflength); @@ -2088,7 +2180,8 @@ if (param->testflag & T_STATISTICS) update_key_parts(sort_param.keyinfo, rec_per_key_part, sort_param.unique, - (ulonglong) info->state->records); + param->stats_method == MI_STATS_METHOD_IGNORE_NULLS? + sort_param.notnull: NULL,(ulonglong) info->state->records); share->state.key_map|=(ulonglong) 1 << sort_param.key; if (sort_param.fix_datafile) @@ -3255,11 +3348,21 @@ ha_key_cmp(sort_param->seg,sort_info->key_block->lastkey, (uchar*) a, USE_WHOLE_KEY, SEARCH_FIND | SEARCH_NULL_ARE_NOT_EQUAL, &diff_pos); + else if (param->stats_method == MI_STATS_METHOD_IGNORE_NULLS) + { + diff_pos= mi_collect_stats_nonulls_next(sort_param->seg, + sort_param->notnull, + sort_info->key_block->lastkey, + (uchar*)a); + } sort_param->unique[diff_pos-1]++; } else { cmp= -1; + if (param->stats_method == MI_STATS_METHOD_IGNORE_NULLS) + mi_collect_stats_nonulls_first(sort_param->seg, sort_param->notnull, + (uchar*)a); } if ((sort_param->keyinfo->flag & HA_NOSAME) && cmp == 0) { @@ -3981,22 +4084,36 @@ SYNOPSIS update_key_parts() - keyinfo Index information (only key->keysegs used) + keyinfo IN Index information (only key->keysegs used) rec_per_key_part OUT Store statistics here - unique IN Array of #distinct values collected over index - run. + unique IN Array of (#distinct tuples) + notnull_tuples IN Array of (#tuples), or NULL records Number of records in the table NOTES + This function currently handles all index statistics collection methods. + Unique is an array: unique[0]= (#different values of {keypart1}) - 1 - unique[1]= (#different values of {keypart2,keypart1} tuple) - unique[0] - 1 + unique[1]= (#different values of {keypart1,keypart2} tuple) - unique[0] - 1 ... + + notnull_tuples is either an array: + notnull_tuples[0] = + (# of {keypart1} tuples such that keypart1 is not NULL) + notnull_tuples[1] = + (# of {keypart1,keypart2} tuples such that all keypart{i} are not NULL) + + or notnull_tuples == NULL which is interpeted as + for any i (not_null_tuples[i] == #records_in_table). + The 'unique' array is collected in one sequential scan through the entire index. This is done in two places: in chk_index() and in sort_key_write(). Statistics collection may consider NULLs as either equal or unequal (see SEARCH_NULL_ARE_NOT_EQUAL, MI_STATS_METHOD_*). + notnull_tuples, if present, is collected during the same index scan. + Output is an array: rec_per_key_part[k] = = E(#records in the table such that keypart_1=c_1 AND ... AND @@ -4010,22 +4127,42 @@ */ void update_key_parts(MI_KEYDEF *keyinfo, ulong *rec_per_key_part, - ulonglong *unique, ulonglong records) + ulonglong *unique, ulonglong *notnull, + ulonglong records) { - ulonglong count=0,tmp; + ulonglong count=0,tmp, unique_tuples; + ulonglong tuples= records; uint parts; for (parts=0 ; parts < keyinfo->keysegs ; parts++) { count+=unique[parts]; - if (count == 0) - tmp=records; + unique_tuples= count + 1; + if (notnull) + { + tuples= notnull[parts]; + /* + #(unique_tuples not counting tuples with NULLs) = + #(unique_tuples counting tuples with NULLs as different) - + #(tuples with NULLs) + */ + unique_tuples -= (records - notnull[parts]); + } + + if (unique_tuples == 0) + tmp= 1; + else if (count == 0) + tmp= tuples; /* 1 unique tuple */ else - tmp= (records + (count+1)/2) / (count+1); - /* for some weird keys (e.g. FULLTEXT) tmp can be <1 here. - let's ensure it is not */ + tmp= (tuples + unique_tuples/2) / unique_tuples; + + /* + for some weird keys (e.g. FULLTEXT) tmp can be <1 here. + let's ensure it is not + */ set_if_bigger(tmp,1); if (tmp >= (ulonglong) ~(ulong) 0) tmp=(ulonglong) ~(ulong) 0; + *rec_per_key_part=(ulong) tmp; rec_per_key_part++; } --- 1.128/myisam/myisamchk.c 2005-09-24 01:39:46 +04:00 +++ 1.129/myisam/myisamchk.c 2005-10-20 19:42:22 +04:00 @@ -339,7 +339,8 @@ REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {"stats_method", OPT_STATS_METHOD, "Specifies how index statistics collection code should threat NULLs. " - "Possible values of name are \"nulls_unequal\" (default behavior for 4.1/5.0), and \"nulls_equal\" (emulate 4.0 behavior).", + "Possible values of name are \"nulls_unequal\" (default behavior for 4.1/5.0), " + "\"nulls_equal\" (emulate 4.0 behavior), and \"nulls_ignored\".", (gptr*) &myisam_stats_method_str, (gptr*) &myisam_stats_method_str, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, { 0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0} @@ -451,6 +452,10 @@ -a, --analyze Analyze distribution of keys. Will make some joins in\n\ MySQL faster. You can check the calculated distribution\n\ by using '--description --verbose table_name'.\n\ + --stats_method=name Specifies how index statistics collection code should\n\ + threat NULLs. Possible values of name are \"nulls_unequal\"\n\ + (default for 4.1/5.0), \"nulls_equal\" (emulate 4.0), and \n\ + \"nulls_ignored\".\n\ -d, --description Prints some information about table.\n\ -A, --set-auto-increment[=value]\n\ Force auto_increment to start at this or higher value\n\ @@ -472,7 +477,7 @@ #include <help_end.h> const char *myisam_stats_method_names[] = {"nulls_unequal", "nulls_equal", - NullS}; + "nulls_ignored", NullS}; TYPELIB myisam_stats_method_typelib= { array_elements(myisam_stats_method_names) - 1, "", myisam_stats_method_names, NULL}; @@ -698,15 +703,25 @@ break; case OPT_STATS_METHOD: { - int method; + int method, method_conv; myisam_stats_method_str= argument; if ((method=find_type(argument, &myisam_stats_method_typelib, 2)) <= 0) { fprintf(stderr, "Invalid value of stats_method: %s.\n", argument); exit(1); } - check_param.stats_method= test(method-1)? MI_STATS_METHOD_NULLS_EQUAL : - MI_STATS_METHOD_NULLS_NOT_EQUAL; + switch (method-1) { + case 0: + method_conv= MI_STATS_METHOD_NULLS_EQUAL; + break; + case 1: + method_conv= MI_STATS_METHOD_NULLS_NOT_EQUAL; + break; + case 2: + method_conv= MI_STATS_METHOD_IGNORE_NULLS; + break; + } + check_param.stats_method= method_conv; break; } #ifdef DEBUG /* Only useful if debugging */ --- 1.80/myisam/myisamdef.h 2005-09-23 12:44:57 +04:00 +++ 1.81/myisam/myisamdef.h 2005-10-20 19:42:22 +04:00 @@ -297,7 +297,10 @@ pthread_t thr; IO_CACHE read_cache, tempfile, tempfile_for_exceptions; DYNAMIC_ARRAY buffpek; + ulonglong unique[MI_MAX_KEY_SEG+1]; + ulonglong notnull[MI_MAX_KEY_SEG+1]; + my_off_t pos,max_pos,filepos,start_recpos; uint key, key_length,real_key_length,sortbuff_size; uint maxbuffers, keys, find_length, sort_keys_length; --- 1.44/myisam/sort.c 2005-08-02 11:57:23 +04:00 +++ 1.45/myisam/sort.c 2005-10-20 19:42:22 +04:00 @@ -481,8 +481,8 @@ { share->state.key_map|=(ulonglong) 1 << sinfo->key; if (param->testflag & T_STATISTICS) - update_key_parts(sinfo->keyinfo, rec_per_key_part, - sinfo->unique, (ulonglong) info->state->records); + update_key_parts(sinfo->keyinfo, rec_per_key_part, sinfo->unique, + sinfo->notnull, (ulonglong) info->state->records); if (!sinfo->buffpek.elements) { if (param->testflag & T_VERBOSE) --- 1.161/sql/ha_myisam.cc 2005-09-24 01:39:47 +04:00 +++ 1.162/sql/ha_myisam.cc 2005-10-20 19:42:23 +04:00 @@ -40,7 +40,7 @@ myisam_recover_names, NULL}; const char *myisam_stats_method_names[] = {"nulls_unequal", "nulls_equal", - NullS}; + "nulls_ignored", NullS}; TYPELIB myisam_stats_method_typelib= { array_elements(myisam_stats_method_names) - 1, "", myisam_stats_method_names, NULL}; --- 1.602/sql/mysqld.cc 2005-09-29 04:07:38 +04:00 +++ 1.603/sql/mysqld.cc 2005-10-20 19:42:23 +04:00 @@ -5212,7 +5212,8 @@ GET_ULONG, REQUIRED_ARG, 8192*1024, 4, ~0L, 0, 1, 0}, {"myisam_stats_method", OPT_MYISAM_STATS_METHOD, "Specifies how MyISAM index statistics collection code should threat NULLs. " - "Possible values of name are \"nulls_unequal\" (default behavior for 4.1/5.0), and \"nulls_equal\" (emulate 4.0 behavior).", + "Possible values of name are \"nulls_unequal\" (default behavior for 4.1/5.0), " + "\"nulls_equal\" (emulate 4.0 behavior), and \"nulls_ignored\".", (gptr*) &myisam_stats_method_str, (gptr*) &myisam_stats_method_str, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {"net_buffer_length", OPT_NET_BUFFER_LENGTH, @@ -6405,16 +6406,26 @@ } case OPT_MYISAM_STATS_METHOD: { - myisam_stats_method_str= argument; int method; + ulong method_conv; + myisam_stats_method_str= argument; if ((method=find_type(argument, &myisam_stats_method_typelib, 2)) <= 0) { fprintf(stderr, "Invalid value of myisam_stats_method: %s.\n", argument); exit(1); } - global_system_variables.myisam_stats_method= - test(method-1)? MI_STATS_METHOD_NULLS_EQUAL : - MI_STATS_METHOD_NULLS_NOT_EQUAL; + switch (method-1) { + case 0: + method_conv= MI_STATS_METHOD_NULLS_EQUAL; + break; + case 1: + method_conv= MI_STATS_METHOD_NULLS_NOT_EQUAL; + break; + case 2: + method_conv= MI_STATS_METHOD_IGNORE_NULLS; + break; + } + global_system_variables.myisam_stats_method= method_conv; break; } case OPT_SQL_MODE: --- 1.5/include/my_handler.h 2004-03-25 16:04:58 +03:00 +++ 1.6/include/my_handler.h 2005-10-20 19:42:22 +04:00 @@ -63,4 +63,6 @@ register uchar *b, uint key_length, uint nextflag, uint *diff_pos); +extern HA_KEYSEG *ha_find_null(HA_KEYSEG *keyseg, uchar *a); + #endif /* _my_handler_h */ --- 1.18/mysys/my_handler.c 2005-02-01 17:27:00 +03:00 +++ 1.19/mysys/my_handler.c 2005-10-20 19:42:22 +04:00 @@ -75,7 +75,7 @@ SYNOPSIS ha_key_cmp() - keyseg Key segments of key to compare + keyseg Array of key segments of key to compare a First key to compare, in format from _mi_pack_key() This is normally key specified by user b Second key to compare. This is always from a row @@ -84,10 +84,20 @@ next_flag How keys should be compared If bit SEARCH_FIND is not set the keys includes the row position and this should also be compared - + diff_pos OUT Number of first keypart where values differ, counting + from one. + NOTES Number-keys can't be splited + DESCRIPTION + + If SEARCH_RETURN_B_POS flag is set, diff_pos must point to array of 2 + values, first value has the meaning described above, second value is: + + diff_pos[1] OUT (b + diff_pos[1]) points to first value in tuple b + that is different from corresponding value in tuple a. + RETURN VALUES <0 If a < b 0 If a == b @@ -107,6 +117,7 @@ float f_1,f_2; double d_1,d_2; uint next_key_length; + uchar *orig_b= b; *diff_pos=0; for ( ; (int) key_length >0 ; key_length=next_key_length, keyseg++) @@ -115,6 +126,9 @@ uint piks=! (keyseg->flag & HA_NO_SORT); (*diff_pos)++; + if (nextflag & SEARCH_RETURN_B_POS) + diff_pos[1]= (uint)(b - orig_b); + /* Handle NULL part */ if (keyseg->null_bit) { @@ -448,3 +462,84 @@ } return 0; } /* ha_key_cmp */ + + +/* + Find first NULL value in key-suffix values tuple. + + SYNOPSIS + ha_find_null() + keyseg Array of keyparts for key suffix + a Key suffix value tuple. + + DESCRIPTION + TODO Consider optimizing this so it doesn't walk over completely + NOT NULL suffixes. + + RETURN + First key part that has NULL as value in values tuple, or + the last key part (with keyseg->type==HA_TYPE_END) if values tuple + doesn't contain NULLs. +*/ + +HA_KEYSEG *ha_find_null(HA_KEYSEG *keyseg, uchar *a) +{ + for (; (enum ha_base_keytype) keyseg->type != HA_KEYTYPE_END; keyseg++) + { + uchar *end; + if (keyseg->null_bit) + { + if (!*a++) + return keyseg; + } + end= a+ keyseg->length; + + switch ((enum ha_base_keytype) keyseg->type) { + case HA_KEYTYPE_TEXT: + case HA_KEYTYPE_BINARY: + if (keyseg->flag & HA_SPACE_PACK) + { + int a_length; + get_key_length(a_length, a); + a += a_length; + break; + } + else + a= end; + break; + case HA_KEYTYPE_VARTEXT: + case HA_KEYTYPE_VARBINARY: + { + int a_length; + get_key_length(a_length, a); + a+= a_length; + break; + } + case HA_KEYTYPE_NUM: + if (keyseg->flag & HA_SPACE_PACK) + { + int alength= *a++; + end= a+alength; + } + a= end; + break; + case HA_KEYTYPE_INT8: + case HA_KEYTYPE_SHORT_INT: + case HA_KEYTYPE_USHORT_INT: + case HA_KEYTYPE_LONG_INT: + case HA_KEYTYPE_ULONG_INT: + case HA_KEYTYPE_INT24: + case HA_KEYTYPE_UINT24: +#ifdef HAVE_LONG_LONG + case HA_KEYTYPE_LONGLONG: + case HA_KEYTYPE_ULONGLONG: +#endif + case HA_KEYTYPE_FLOAT: + case HA_KEYTYPE_DOUBLE: + a= end; + break; + } + } + return keyseg; +} + --- 1.53/mysql-test/r/myisam.result 2005-09-24 01:39:46 +04:00 +++ 1.54/mysql-test/r/myisam.result 2005-10-20 19:42:22 +04:00 @@ -670,3 +670,35 @@ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 1 a 1 a A 10 NULL NULL YES BTREE drop table t1; +set myisam_stats_method=nulls_ignored; +show variables like 'myisam_stats_method'; +Variable_name Value +myisam_stats_method nulls_ignored +create table t1 ( +a char(3), b char(4), c char(5), d char(6), +key(a,b,c,d) +); +insert into t1 values ('bcd','def1', NULL, 'zz'); +insert into t1 values ('bcd','def2', NULL, 'zz'); +insert into t1 values ('bce','def1', 'yuu', NULL); +insert into t1 values ('bce','def2', NULL, 'quux'); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A 2 NULL NULL YES BTREE +t1 1 a 2 b A 4 NULL NULL YES BTREE +t1 1 a 3 c A 4 NULL NULL YES BTREE +t1 1 a 4 d A 4 NULL NULL YES BTREE +delete from t1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A 0 NULL NULL YES BTREE +t1 1 a 2 b A 0 NULL NULL YES BTREE +t1 1 a 3 c A 0 NULL NULL YES BTREE +t1 1 a 4 d A 0 NULL NULL YES BTREE +set myisam_stats_method=DEFAULT; --- 1.41/mysql-test/t/myisam.test 2005-09-21 02:18:26 +04:00 +++ 1.42/mysql-test/t/myisam.test 2005-10-20 19:42:22 +04:00 @@ -637,4 +637,23 @@ drop table t1; +# WL#2609, CSC#XXXX: MyISAM +set myisam_stats_method=nulls_ignored; +show variables like 'myisam_stats_method'; + +create table t1 ( + a char(3), b char(4), c char(5), d char(6), + key(a,b,c,d) +); +insert into t1 values ('bcd','def1', NULL, 'zz'); +insert into t1 values ('bcd','def2', NULL, 'zz'); +insert into t1 values ('bce','def1', 'yuu', NULL); +insert into t1 values ('bce','def2', NULL, 'quux'); +analyze table t1; +show index from t1; +delete from t1; +analyze table t1; +show index from t1; + +set myisam_stats_method=DEFAULT; # End of 4.1 tests
| Thread | ||
|---|---|---|
| • bk commit - 4.1 tree (sergefp:1.2485) BUG#9622 | Sergey Petrunia | 20 Oct |
© 1995-2008 MySQL AB. All rights reserved.
Page generated in 0.677 sec. using MySQL 5.1.14-beta-log