| List: | Internals | « Previous MessageNext Message » | |
| From: | jani | Date: | October 27 2005 10:43pm |
| Subject: | bk commit into 5.0 tree (jani:1.1945) | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 5.0 repository of jani. When jani 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.1945 05/10/27 23:43:20 jani@stripped +44 -0 Merge ua141d10.elisa.omakaista.fi:/home/my/bk/mysql-4.1 into ua141d10.elisa.omakaista.fi:/home/my/bk/mysql-5.0-tmp tests/mysql_client_test.c 1.161 05/10/27 23:43:14 jani@stripped +14 -14 Merged from 4.1 sql/sql_update.cc 1.176 05/10/27 23:43:14 jani@stripped +6 -8 Merged from 4.1 sql/mysqld.cc 1.519 05/10/27 23:43:14 jani@stripped +13 -15 Merged from 4.1 sql/item.cc 1.193 05/10/27 23:43:13 jani@stripped +7 -6 Merged from 4.1 netware/mysql_test_run.c 1.16 05/10/27 23:43:13 jani@stripped +0 -2 Merged from 4.1 mysql-test/t/myisam.test 1.50 05/10/27 23:43:13 jani@stripped +0 -0 Merged from 4.1 mysql-test/t/insert_select.test 1.27 05/10/27 23:43:13 jani@stripped +10 -0 Merged from 4.1 mysql-test/r/select.result 1.108 05/10/27 23:43:13 jani@stripped +298 -0 Merged from 4.1 mysql-test/r/myisam.result 1.69 05/10/27 23:43:13 jani@stripped +0 -0 Merged from 4.1 mysql-test/r/insert_select.result 1.34 05/10/27 23:43:13 jani@stripped +0 -0 Merged from 4.1 mysql-test/mysql-test-run.pl 1.56 05/10/27 23:43:13 jani@stripped +0 -1 Merged from 4.1 myisam/myisamchk.c 1.121 05/10/27 23:43:13 jani@stripped +0 -1 Merged from 4.1 Makefile.am 1.81 05/10/27 23:43:13 jani@stripped +0 -0 Merged from 4.1 strings/ctype-win1250ch.c 1.54 05/10/27 20:04:33 jani@stripped +0 -0 Auto merged strings/conf_to_src.c 1.15 05/10/27 20:04:33 jani@stripped +0 -0 Auto merged sql/structs.h 1.52 05/10/27 20:04:33 jani@stripped +0 -0 Auto merged sql/sql_select.cc 1.367 05/10/27 20:04:33 jani@stripped +0 -0 Auto merged sql/sql_load.cc 1.88 05/10/27 20:04:33 jani@stripped +0 -0 Auto merged sql/records.cc 1.42 05/10/27 20:04:33 jani@stripped +0 -0 Auto merged sql/item_timefunc.cc 1.96 05/10/27 20:04:32 jani@stripped +0 -0 Auto merged sql/ha_myisam.cc 1.164 05/10/27 20:04:32 jani@stripped +0 -0 Auto merged scripts/make_binary_distribution.sh 1.101 05/10/27 20:04:32 jani@stripped +0 -0 Auto merged mysys/my_handler.c 1.23 05/10/27 20:04:32 jani@stripped +0 -0 Auto merged mysys/my_getopt.c 1.55 05/10/27 20:04:32 jani@stripped +0 -0 Auto merged mysql-test/t/update.test 1.25 05/10/27 20:04:32 jani@stripped +0 -0 Auto merged mysql-test/t/select.test 1.89 05/10/27 20:04:32 jani@stripped +0 -0 Auto merged mysql-test/t/date_formats.test 1.14 05/10/27 20:04:32 jani@stripped +0 -0 Auto merged mysql-test/r/update.result 1.27 05/10/27 20:04:32 jani@stripped +0 -0 Auto merged mysql-test/r/date_formats.result 1.18 05/10/27 20:04:31 jani@stripped +0 -0 Auto merged mysql-test/mysql-test-run.sh 1.279 05/10/27 20:04:31 jani@stripped +0 -3 Auto merged myisam/sort.c 1.49 05/10/27 20:04:31 jani@stripped +0 -0 Auto merged myisam/myisamdef.h 1.83 05/10/27 20:04:31 jani@stripped +0 -0 Auto merged myisam/mi_write.c 1.55 05/10/27 20:04:31 jani@stripped +0 -0 Auto merged myisam/mi_search.c 1.60 05/10/27 20:04:31 jani@stripped +0 -0 Auto merged myisam/mi_rnext_same.c 1.18 05/10/27 20:04:31 jani@stripped +0 -0 Auto merged myisam/mi_delete.c 1.36 05/10/27 20:04:31 jani@stripped +0 -0 Auto merged myisam/mi_check.c 1.139 05/10/27 20:04:31 jani@stripped +0 -0 Auto merged innobase/include/Makefile.am 1.12 05/10/27 20:04:31 jani@stripped +0 -0 Auto merged include/myisam.h 1.70 05/10/27 20:04:31 jani@stripped +0 -0 Auto merged include/my_handler.h 1.10 05/10/27 20:04:31 jani@stripped +0 -0 Auto merged include/my_base.h 1.71 05/10/27 20:04:31 jani@stripped +0 -0 Auto merged include/config-netware.h 1.11 05/10/27 20:04:31 jani@stripped +0 -1 Auto merged heap/hp_create.c 1.21 05/10/27 20:04:31 jani@stripped +0 -0 Auto merged heap/_check.c 1.18 05/10/27 20:04:31 jani@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: jani # Host: ua141d10.elisa.omakaista.fi # Root: /home/my/bk/mysql-5.0-tmp/RESYNC --- 1.17/heap/_check.c 2004-12-18 05:19:14 +02:00 +++ 1.18/heap/_check.c 2005-10-27 20:04:31 +03:00 @@ -167,7 +167,7 @@ ulong found= 0; byte *key, *recpos; uint key_length; - uint not_used; + uint not_used[2]; if ((key= tree_search_edge(&keydef->rb_tree, info->parents, &info->last_pos, offsetof(TREE_ELEMENT, left)))) @@ -177,7 +177,7 @@ memcpy(&recpos, key + (*keydef->get_key_length)(keydef,key), sizeof(byte*)); key_length= hp_rb_make_key(keydef, info->recbuf, recpos, 0); if (ha_key_cmp(keydef->seg, (uchar*) info->recbuf, (uchar*) key, - key_length, SEARCH_FIND | SEARCH_SAME, ¬_used)) + key_length, SEARCH_FIND | SEARCH_SAME, not_used)) { error= 1; DBUG_PRINT("error",("Record in wrong link: key: %d Record: 0x%lx\n", --- 1.20/heap/hp_create.c 2005-03-17 17:44:19 +02:00 +++ 1.21/heap/hp_create.c 2005-10-27 20:04:31 +03:00 @@ -203,9 +203,9 @@ static int keys_compare(heap_rb_param *param, uchar *key1, uchar *key2) { - uint not_used; + uint not_used[2]; return ha_key_cmp(param->keyseg, key1, key2, param->key_length, - param->search_flag, ¬_used); + param->search_flag, not_used); } static void init_block(HP_BLOCK *block, uint reclength, ulong min_records, --- 1.69/include/myisam.h 2005-09-21 18:38:03 +03:00 +++ 1.70/include/myisam.h 2005-10-27 20:04:31 +03:00 @@ -392,7 +392,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 only tuples without NULLs in the index components */ + MI_STATS_METHOD_IGNORE_NULLS } enum_mi_stats_method; typedef struct st_mi_check_param @@ -419,7 +421,14 @@ int tmpfile_createflag; myf myf_rw; IO_CACHE read_cache; + + /* + The next two are used to collect statistics, see update_key_parts for + description. + */ 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; @@ -479,7 +488,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.138/myisam/mi_check.c 2005-10-26 15:01:47 +03:00 +++ 1.139/myisam/mi_check.c 2005-10-27 20:04:31 +03:00 @@ -392,7 +392,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) @@ -497,7 +500,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) { @@ -553,6 +558,96 @@ return 1; } + +/* + "Ignore NULLs" statistics collection method: process first index tuple. + + SYNOPSIS + mi_collect_stats_nonulls_first() + keyseg IN Array of key part descriptions + notnull INOUT Array, notnull[i] = (number of {keypart1...keypart_i} + tuples that don't contain NULLs) + key IN Key values tuple + + DESCRIPTION + Process the first index tuple - find out which prefix tuples don't + contain NULLs, and update the array of notnull counters accordingly. +*/ + +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; + /* + All prefix tuples that don't include keypart_{first_null} are not-null + tuples (and all others aren't), increment counters for them. + */ + for (kp= 0; kp < first_null; kp++) + notnull[kp]++; +} + + +/* + "Ignore NULLs" statistics collection method: process next index tuple. + + SYNOPSIS + mi_collect_stats_nonulls_next() + keyseg IN Array of key part descriptions + notnull INOUT Array, notnull[i] = (number of {keypart1...keypart_i} + tuples that don't contain NULLs) + prev_key IN Previous key values tuple + last_key IN Next key values tuple + + DESCRIPTION + Process the next index tuple: + 1. Find out which prefix tuples of last_key don't contain NULLs, and + update the array of notnull counters accordingly. + 2. Find the first keypart number where the prev_key and last_key tuples + are different(A), or last_key has NULL value(B), and return it, so the + caller can count number of unique tuples for each key prefix. We don't + need (B) to be counted, and that is compensated back in + update_key_parts(). + + RETURN + 1 + number of first keypart where values differ or last_key tuple has NULL +*/ + +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; + HA_KEYSEG *seg; + + /* + Find the first keypart where values are different or either of them is + NULL. We get results in diffs array: + diffs[0]= 1 + number of first different keypart + diffs[1]=offset: (last_key + diffs[1]) points to first value in + last_key that is NULL or different from corresponding + value in prev_key. + */ + ha_key_cmp(keyseg, prev_key, last_key, USE_WHOLE_KEY, + SEARCH_FIND | SEARCH_NULL_ARE_NOT_EQUAL, diffs); + seg= keyseg + diffs[0] - 1; + + /* Find first NULL in last_key */ + first_null_seg= ha_find_null(seg, last_key + diffs[1]) - keyseg; + for (kp= 0; kp < first_null_seg; kp++) + notnull[kp]++; + + /* + Return 1+ number of first key part where values differ. Don't care if + these were NULLs and not .... We compensate for that in + update_key_parts. + */ + return diffs[0]; +} + + /* Check if index is ok */ static int chk_index(MI_CHECK *param, MI_INFO *info, MI_KEYDEF *keyinfo, @@ -564,7 +659,7 @@ uchar key[MI_MAX_POSSIBLE_KEY_BUFF],*temp_buff,*keypos,*old_keypos,*endpos; my_off_t next_page,record; char llbuff[22]; - uint diff_pos; + uint diff_pos[2]; DBUG_ENTER("chk_index"); DBUG_DUMP("buff",(byte*) buff,mi_getint(buff)); @@ -622,7 +717,7 @@ } if ((*keys)++ && (flag=ha_key_cmp(keyinfo->seg,info->lastkey,key,key_length, - comp_flag, &diff_pos)) >=0) + comp_flag, diff_pos)) >=0) { DBUG_DUMP("old",(byte*) info->lastkey, info->lastkey_length); DBUG_DUMP("new",(byte*) key, key_length); @@ -641,8 +736,20 @@ if (param->stats_method == MI_STATS_METHOD_NULLS_NOT_EQUAL) ha_key_cmp(keyinfo->seg,info->lastkey,key,USE_WHOLE_KEY, SEARCH_FIND | SEARCH_NULL_ARE_NOT_EQUAL, - &diff_pos); - param->unique_count[diff_pos-1]++; + diff_pos); + else if (param->stats_method == MI_STATS_METHOD_IGNORE_NULLS) + { + diff_pos[0]= mi_collect_stats_nonulls_next(keyinfo->seg, + param->notnull_count, + info->lastkey, key); + } + param->unique_count[diff_pos[0]-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, @@ -1740,9 +1847,10 @@ _mi_kpointer(info,keypos-nod_flag,param->new_file_pos); /* Save new pos */ if (sort_one_index(param,info,keyinfo,next_page,new_file)) { - DBUG_PRINT("error",("From page: %ld, keyoffset: %d used_length: %d", - (ulong) pagepos, (int) (keypos - buff), - (int) used_length)); + DBUG_PRINT("error", + ("From page: %ld, keyoffset: 0x%lx used_length: %d", + (ulong) pagepos, (ulong) (keypos - buff), + (int) used_length)); DBUG_DUMP("buff",(byte*) buff,used_length); goto err; } @@ -2097,7 +2205,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); mi_set_key_active(share->state.key_map, sort_param.key); if (sort_param.fix_datafile) @@ -3241,15 +3350,15 @@ static int sort_key_cmp(MI_SORT_PARAM *sort_param, const void *a, const void *b) { - uint not_used; + uint not_used[2]; return (ha_key_cmp(sort_param->seg, *((uchar**) a), *((uchar**) b), - USE_WHOLE_KEY, SEARCH_SAME,¬_used)); + USE_WHOLE_KEY, SEARCH_SAME, not_used)); } /* sort_key_cmp */ static int sort_key_write(MI_SORT_PARAM *sort_param, const void *a) { - uint diff_pos; + uint diff_pos[2]; char llbuff[22],llbuff2[22]; SORT_INFO *sort_info=sort_param->sort_info; MI_CHECK *param= sort_info->param; @@ -3259,16 +3368,26 @@ { cmp=ha_key_cmp(sort_param->seg,sort_info->key_block->lastkey, (uchar*) a, USE_WHOLE_KEY,SEARCH_FIND | SEARCH_UPDATE, - &diff_pos); + diff_pos); if (param->stats_method == MI_STATS_METHOD_NULLS_NOT_EQUAL) 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); - sort_param->unique[diff_pos-1]++; + SEARCH_FIND | SEARCH_NULL_ARE_NOT_EQUAL, diff_pos); + else if (param->stats_method == MI_STATS_METHOD_IGNORE_NULLS) + { + diff_pos[0]= mi_collect_stats_nonulls_next(sort_param->seg, + sort_param->notnull, + sort_info->key_block->lastkey, + (uchar*)a); + } + sort_param->unique[diff_pos[0]-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) { @@ -3986,24 +4105,34 @@ /* Update statistics for each part of an index - + 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 + + DESCRIPTION + This function is called produce index statistics values from unique and + notnull_tuples arrays after these arrays were produced with sequential + index scan (the scan is done in two places: chk_index() and + sort_key_write()). + + This function handles all 3 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 - ... - 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_*). + unique[0]= (#different values of {keypart1}) - 1 + unique[1]= (#different values of {keypart1,keypart2} tuple)-unique[0]-1 + ... + + For MI_STATS_METHOD_IGNORE_NULLS method, notnull_tuples is an array too: + 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) + ... + For all other statistics collection methods notnull_tuples==NULL. Output is an array: rec_per_key_part[k] = @@ -4015,25 +4144,53 @@ index tuples} = #tuples-in-the-index / #distinct-tuples-in-the-index. + + The #tuples-in-the-index and #distinct-tuples-in-the-index have different + meaning depending on which statistics collection method is used: + + MI_STATS_METHOD_* how are nulls compared? which tuples are counted? + NULLS_EQUAL NULL == NULL all tuples in table + NULLS_NOT_EQUAL NULL != NULL all tuples in table + IGNORE_NULLS n/a tuples that don't have NULLs */ 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.35/myisam/mi_delete.c 2005-09-27 21:10:55 +03:00 +++ 1.36/myisam/mi_delete.c 2005-10-27 20:04:31 +03:00 @@ -407,7 +407,8 @@ MYISAM_SHARE *share=info->s; MI_KEY_PARAM s_temp; DBUG_ENTER("del"); - DBUG_PRINT("enter",("leaf_page: %ld keypos: %lx",leaf_page,keypos)); + DBUG_PRINT("enter",("leaf_page: %ld keypos: 0x%lx", leaf_page, + (ulong) keypos)); DBUG_DUMP("leaf_buff",(byte*) leaf_buff,mi_getint(leaf_buff)); endpos=leaf_buff+mi_getint(leaf_buff); @@ -512,7 +513,8 @@ MI_KEY_PARAM s_temp; MYISAM_SHARE *share=info->s; DBUG_ENTER("underflow"); - DBUG_PRINT("enter",("leaf_page: %ld keypos: %lx",(long) leaf_page,keypos)); + DBUG_PRINT("enter",("leaf_page: %ld keypos: 0x%lx",(long) leaf_page, + (ulong) keypos)); DBUG_DUMP("anc_buff",(byte*) anc_buff,mi_getint(anc_buff)); DBUG_DUMP("leaf_buff",(byte*) leaf_buff,mi_getint(leaf_buff)); --- 1.17/myisam/mi_rnext_same.c 2005-06-07 18:23:38 +03:00 +++ 1.18/myisam/mi_rnext_same.c 2005-10-27 20:04:31 +03:00 @@ -28,7 +28,7 @@ int mi_rnext_same(MI_INFO *info, byte *buf) { int error; - uint inx,not_used; + uint inx,not_used[2]; MI_KEYDEF *keyinfo; DBUG_ENTER("mi_rnext_same"); @@ -69,7 +69,7 @@ info->s->state.key_root[inx]))) break; if (ha_key_cmp(keyinfo->seg, info->lastkey, info->lastkey2, - info->last_rkey_length, SEARCH_FIND, ¬_used)) + info->last_rkey_length, SEARCH_FIND, not_used)) { error=1; my_errno=HA_ERR_END_OF_FILE; --- 1.59/myisam/mi_search.c 2005-10-13 17:36:41 +03:00 +++ 1.60/myisam/mi_search.c 2005-10-27 20:04:31 +03:00 @@ -128,13 +128,13 @@ if ((nextflag & (SEARCH_SMALLER | SEARCH_LAST)) && flag != 0) { - uint not_used; + uint not_used[2]; if (_mi_get_prev_key(info,keyinfo, buff, info->lastkey, keypos, &info->lastkey_length)) goto err; if (!(nextflag & SEARCH_SMALLER) && ha_key_cmp(keyinfo->seg, info->lastkey, key, key_len, SEARCH_FIND, - ¬_used)) + not_used)) { my_errno=HA_ERR_KEY_NOT_FOUND; /* Didn't find key */ goto err; @@ -179,7 +179,7 @@ { reg4 int start,mid,end,save_end; int flag; - uint totlength,nod_flag,not_used; + uint totlength,nod_flag,not_used[2]; DBUG_ENTER("_mi_bin_search"); LINT_INIT(flag); @@ -193,7 +193,7 @@ { mid= (start+end)/2; if ((flag=ha_key_cmp(keyinfo->seg,page+(uint) mid*totlength,key,key_len, - comp_flag,¬_used)) + comp_flag, not_used)) >= 0) end=mid; else @@ -201,7 +201,7 @@ } if (mid != start) flag=ha_key_cmp(keyinfo->seg,page+(uint) start*totlength,key,key_len, - comp_flag,¬_used); + comp_flag, not_used); if (flag < 0) start++; /* point at next, bigger key */ *ret_pos=page+(uint) start*totlength; @@ -242,7 +242,7 @@ uchar *buff, my_bool *last_key) { int flag; - uint nod_flag,length,not_used; + uint nod_flag,length,not_used[2]; uchar t_buff[MI_MAX_KEY_BUFF],*end; DBUG_ENTER("_mi_seq_search"); @@ -264,7 +264,7 @@ DBUG_RETURN(MI_FOUND_WRONG_KEY); } if ((flag=ha_key_cmp(keyinfo->seg,t_buff,key,key_len,comp_flag, - ¬_used)) >= 0) + not_used)) >= 0) break; #ifdef EXTRA_DEBUG DBUG_PRINT("loop",("page: %lx key: '%s' flag: %d", (long) page, t_buff, @@ -507,9 +507,9 @@ cmp_rest: if (key_len_left>0) { - uint not_used; + uint not_used[2]; if ((flag = ha_key_cmp(keyinfo->seg+1,vseg, - k,key_len_left,nextflag,¬_used)) >= 0) + k, key_len_left, nextflag, not_used)) >= 0) break; } else --- 1.54/myisam/mi_write.c 2005-09-27 21:10:55 +03:00 +++ 1.55/myisam/mi_write.c 2005-10-27 20:04:31 +03:00 @@ -898,10 +898,10 @@ static int keys_compare(bulk_insert_param *param, uchar *key1, uchar *key2) { - uint not_used; + uint not_used[2]; return ha_key_cmp(param->info->s->keyinfo[param->keynr].seg, - key1, key2, USE_WHOLE_KEY, SEARCH_SAME, - ¬_used); + key1, key2, USE_WHOLE_KEY, SEARCH_SAME, + not_used); } --- 1.120/myisam/myisamchk.c 2005-09-30 13:56:24 +03:00 +++ 1.121/myisam/myisamchk.c 2005-10-27 23:43:13 +03: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}; @@ -699,13 +704,25 @@ case OPT_STATS_METHOD: { int method; + enum_mi_stats_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= (enum_mi_stats_method) (method-1); + 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.82/myisam/myisamdef.h 2005-10-08 17:39:38 +03:00 +++ 1.83/myisam/myisamdef.h 2005-10-27 20:04:31 +03:00 @@ -298,7 +298,14 @@ pthread_t thr; IO_CACHE read_cache, tempfile, tempfile_for_exceptions; DYNAMIC_ARRAY buffpek; + + /* + The next two are used to collect statistics, see update_key_parts for + description. + */ 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.48/myisam/sort.c 2005-10-08 17:39:38 +03:00 +++ 1.49/myisam/sort.c 2005-10-27 20:04:31 +03:00 @@ -481,8 +481,12 @@ { mi_set_key_active(share->state.key_map, 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, + param->stats_method == MI_STATS_METHOD_IGNORE_NULLS? + sinfo->notnull: NULL, + (ulonglong) info->state->records); + + if (!sinfo->buffpek.elements) { if (param->testflag & T_VERBOSE) --- 1.163/sql/ha_myisam.cc 2005-10-26 15:01:47 +03:00 +++ 1.164/sql/ha_myisam.cc 2005-10-27 20:04:32 +03: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.192/sql/item.cc 2005-10-27 00:09:17 +03:00 +++ 1.193/sql/item.cc 2005-10-27 23:43:13 +03:00 @@ -5084,8 +5084,15 @@ bool Item_insert_value::fix_fields(THD *thd, Item **items) { DBUG_ASSERT(fixed == 0); + /* We should only check that arg is in first table */ + st_table_list *orig_next_table= context->last_name_resolution_table; + context->last_name_resolution_table= context->first_name_resolution_table; if (!arg->fixed && arg->fix_fields(thd, &arg)) + { + context->last_name_resolution_table= orig_next_table; return TRUE; + } + context->last_name_resolution_table= orig_next_table; if (arg->type() == REF_ITEM) { @@ -5097,6 +5104,7 @@ arg= ref->ref[0]; } Item_field *field_arg= (Item_field *)arg; + if (field_arg->field->table->insert_values) { Field *def_field= (Field*) sql_alloc(field_arg->field->size_of()); --- 1.95/sql/item_timefunc.cc 2005-10-14 01:32:28 +03:00 +++ 1.96/sql/item_timefunc.cc 2005-10-27 20:04:32 +03:00 @@ -1610,6 +1610,16 @@ if (args[1]->type() == STRING_ITEM) { // Optimize the normal case fixed_length=1; + + /* + Force case sensitive collation on format string. + This needed because format modifiers with different case, + for example %m and %M, have different meaning. Thus eq() + will distinguish them. + */ + args[1]->collation.set( + get_charset_by_csname(args[1]->collation.collation->csname, + MY_CS_BINSORT,MYF(0)), DERIVATION_COERCIBLE); /* The result is a binary string (no reason to use collation->mbmaxlen This is becasue make_date_time() only returns binary strings --- 1.518/sql/mysqld.cc 2005-10-26 18:04:49 +03:00 +++ 1.519/sql/mysqld.cc 2005-10-27 23:43:14 +03:00 @@ -5608,7 +5608,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, @@ -6863,6 +6864,7 @@ } case OPT_MYISAM_STATS_METHOD: { + ulong method_conv; myisam_stats_method_str= argument; int method; if ((method=find_type(argument, &myisam_stats_method_typelib, 2)) <= 0) @@ -6870,7 +6872,18 @@ fprintf(stderr, "Invalid value of myisam_stats_method: %s.\n", argument); exit(1); } - global_system_variables.myisam_stats_method= method-1; + 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.41/sql/records.cc 2005-10-19 00:43:13 +03:00 +++ 1.42/sql/records.cc 2005-10-27 20:04:33 +03:00 @@ -28,11 +28,10 @@ static int rr_from_cache(READ_RECORD *info); static int init_rr_cache(THD *thd, READ_RECORD *info); static int rr_cmp(uchar *a,uchar *b); - +static int rr_index_first(READ_RECORD *info); static int rr_index(READ_RECORD *info); - /* Initialize READ_RECORD structure to perform full index scan @@ -58,26 +57,19 @@ bool print_error, uint idx) { bzero((char*) info,sizeof(*info)); - info->thd=thd; - info->table=table; - info->file= table->file; - info->forms= &info->table; /* Only one table */ - + info->table= table; + info->file= table->file; info->record= table->record[0]; - info->ref_length= table->file->ref_length; + info->print_error= print_error; - info->select=NULL; - info->print_error=print_error; - info->ignore_not_found_rows= 0; table->status=0; /* And it's always found */ - if (!table->file->inited) { table->file->ha_index_init(idx); table->file->extra(HA_EXTRA_RETRIEVE_PRIMARY_KEY); } - info->read_record= rr_index; - info->first= TRUE; + /* read_record will be changed to rr_index in rr_index_first */ + info->read_record= rr_index_first; } @@ -209,6 +201,21 @@ } } +static int rr_handle_error(READ_RECORD *info, int error) +{ + if (error == HA_ERR_END_OF_FILE) + error= -1; + else + { + if (info->print_error) + info->table->file->print_error(error, MYF(0)); + if (error < 0) // Fix negative BDB errno + error= 1; + } + return error; +} + + /* Read a record from head-database */ static int rr_quick(READ_RECORD *info) @@ -223,15 +230,7 @@ } if (tmp != HA_ERR_RECORD_DELETED) { - if (tmp == HA_ERR_END_OF_FILE) - tmp= -1; - else - { - if (info->print_error) - info->file->print_error(tmp,MYF(0)); - if (tmp < 0) // Fix negative BDB errno - tmp=1; - } + tmp= rr_handle_error(info, tmp); break; } } @@ -240,7 +239,31 @@ /* - A READ_RECORD::read_record implementation that reads index sequentially + Reads first row in an index scan + + SYNOPSIS + rr_index_first() + info Scan info + + RETURN + 0 Ok + -1 End of records + 1 Error +*/ + + +static int rr_index_first(READ_RECORD *info) +{ + int tmp= info->file->index_first(info->record); + info->read_record= rr_index; + if (tmp) + tmp= rr_handle_error(info, tmp); + return tmp; +} + + +/* + Reads index sequentially after first row SYNOPSIS rr_index() @@ -256,43 +279,16 @@ 1 Error */ + static int rr_index(READ_RECORD *info) { - int tmp; - while (1) - { - if (info->first) - { - info->first= FALSE; - tmp= info->file->index_first(info->record); - } - else - tmp= info->file->index_next(info->record); - - if (!tmp) - break; - if (info->thd->killed) - { - my_error(ER_SERVER_SHUTDOWN,MYF(0)); - return 1; - } - if (tmp != HA_ERR_RECORD_DELETED) - { - if (tmp == HA_ERR_END_OF_FILE) - tmp= -1; - else - { - if (info->print_error) - info->table->file->print_error(tmp,MYF(0)); - if (tmp < 0) // Fix negative BDB errno - tmp=1; - } - break; - } - } + int tmp= info->file->index_next(info->record); + if (tmp) + tmp= rr_handle_error(info, tmp); return tmp; } + static int rr_sequential(READ_RECORD *info) { int tmp; @@ -303,17 +299,13 @@ info->thd->send_kill_message(); return 1; } + /* + rnd_next can return RECORD_DELETED for MyISAM when one thread is + reading and another deleting without locks. + */ if (tmp != HA_ERR_RECORD_DELETED) { - if (tmp == HA_ERR_END_OF_FILE) - tmp= -1; - else - { - if (info->print_error) - info->table->file->print_error(tmp,MYF(0)); - if (tmp < 0) // Fix negative BDB errno - tmp=1; - } + tmp= rr_handle_error(info, tmp); break; } } @@ -324,23 +316,18 @@ static int rr_from_tempfile(READ_RECORD *info) { int tmp; -tryNext: - if (my_b_read(info->io_cache,info->ref_pos,info->ref_length)) - return -1; /* End of file */ - if ((tmp=info->file->rnd_pos(info->record,info->ref_pos))) - { - if (tmp == HA_ERR_END_OF_FILE) - tmp= -1; - else if (tmp == HA_ERR_RECORD_DELETED || - (tmp == HA_ERR_KEY_NOT_FOUND && info->ignore_not_found_rows)) - goto tryNext; - else - { - if (info->print_error) - info->file->print_error(tmp,MYF(0)); - if (tmp < 0) // Fix negative BDB errno - tmp=1; - } + for (;;) + { + if (my_b_read(info->io_cache,info->ref_pos,info->ref_length)) + return -1; /* End of file */ + if (!(tmp=info->file->rnd_pos(info->record,info->ref_pos))) + break; + /* The following is extremely unlikely to happen */ + if (tmp == HA_ERR_RECORD_DELETED || + (tmp == HA_ERR_KEY_NOT_FOUND && info->ignore_not_found_rows)) + continue; + tmp= rr_handle_error(info, tmp); + break; } return tmp; } /* rr_from_tempfile */ @@ -378,26 +365,23 @@ { int tmp; byte *cache_pos; -tryNext: - if (info->cache_pos == info->cache_end) - return -1; /* End of file */ - cache_pos=info->cache_pos; - info->cache_pos+=info->ref_length; - - if ((tmp=info->file->rnd_pos(info->record,cache_pos))) - { - if (tmp == HA_ERR_END_OF_FILE) - tmp= -1; - else if (tmp == HA_ERR_RECORD_DELETED || - (tmp == HA_ERR_KEY_NOT_FOUND && info->ignore_not_found_rows)) - goto tryNext; - else - { - if (info->print_error) - info->file->print_error(tmp,MYF(0)); - if (tmp < 0) // Fix negative BDB errno - tmp=1; - } + + for (;;) + { + if (info->cache_pos == info->cache_end) + return -1; /* End of file */ + cache_pos= info->cache_pos; + info->cache_pos+= info->ref_length; + + if (!(tmp=info->file->rnd_pos(info->record,cache_pos))) + break; + + /* The following is extremely unlikely to happen */ + if (tmp == HA_ERR_RECORD_DELETED || + (tmp == HA_ERR_KEY_NOT_FOUND && info->ignore_not_found_rows)) + continue; + tmp= rr_handle_error(info, tmp); + break; } return tmp; } --- 1.87/sql/sql_load.cc 2005-10-27 00:09:17 +03:00 +++ 1.88/sql/sql_load.cc 2005-10-27 20:04:33 +03:00 @@ -1026,11 +1026,8 @@ *to++ = (byte) unescape((char) chr); continue; } - else - { - PUSH(chr); - chr= escape_char; - } + PUSH(chr); + chr= escape_char; } #ifdef ALLOW_LINESEPARATOR_IN_STRINGS if (chr == line_term_char) --- 1.366/sql/sql_select.cc 2005-10-24 09:36:18 +03:00 +++ 1.367/sql/sql_select.cc 2005-10-27 20:04:33 +03:00 @@ -825,6 +825,7 @@ { order=0; // The output has only one row simple_order=1; + select_distinct= 0; // No need in distinct for 1 row } calc_group_buffer(this, group_list); --- 1.175/sql/sql_update.cc 2005-10-19 00:52:02 +03:00 +++ 1.176/sql/sql_update.cc 2005-10-27 23:43:14 +03:00 @@ -244,7 +244,7 @@ } if (!select && limit != HA_POS_ERROR) { - if (MAX_KEY != (used_index= get_index_for_order(table, order, limit))) + if ((used_index= get_index_for_order(table, order, limit)) != MAX_KEY) need_sort= FALSE; } /* If running in safe sql mode, don't allow updates without keys */ @@ -267,14 +267,14 @@ used_key_is_modified= (!select->quick->unique_key_range() && select->quick->check_if_keys_used(&fields)); } - else if (used_index != MAX_KEY) + else { - used_key_is_modified= check_if_key_used(table, used_index, fields); + used_key_is_modified= 0; + if (used_index == MAX_KEY) // no index for sort order + used_index= table->file->key_used_on_scan; + if (used_index != MAX_KEY) + used_key_is_modified= check_if_key_used(table, used_index, fields); } - else if ((used_index=table->file->key_used_on_scan) < MAX_KEY) - used_key_is_modified=check_if_key_used(table, used_index, fields); - else - used_key_is_modified=0; if (used_key_is_modified || order) { @@ -286,11 +286,11 @@ if (used_index < MAX_KEY && old_used_keys.is_set(used_index)) { table->key_read=1; - table->file->extra(HA_EXTRA_KEYREAD); //todo: psergey: check + table->file->extra(HA_EXTRA_KEYREAD); } /* note: can actually avoid sorting below.. */ - if (order && need_sort) + if (order && (need_sort || used_key_is_modified)) { /* Doing an ORDER BY; Let filesort find and sort the rows we are going @@ -300,6 +300,7 @@ SORT_FIELD *sortorder; ha_rows examined_rows; + used_index= MAX_KEY; // For call to init_read_record() table->sort.io_cache = (IO_CACHE *) my_malloc(sizeof(IO_CACHE), MYF(MY_FAE | MY_ZEROFILL)); if (!(sortorder=make_unireg_sortorder(order, &length)) || @@ -366,10 +367,7 @@ error= 1; // Aborted limit= tmp_limit; end_read_record(&info); - - /* if we got here we must not use index in the main update loop below */ - used_index= MAX_KEY; - + /* Change select to use tempfile */ if (select) { --- 1.51/sql/structs.h 2005-10-19 00:43:14 +03:00 +++ 1.52/sql/structs.h 2005-10-27 20:04:33 +03:00 @@ -135,7 +135,6 @@ byte *cache,*cache_pos,*cache_end,*read_positions; IO_CACHE *io_cache; bool print_error, ignore_not_found_rows; - bool first; /* used only with rr_index_read */ } READ_RECORD; --- 1.14/strings/conf_to_src.c 2005-06-24 21:19:54 +03:00 +++ 1.15/strings/conf_to_src.c 2005-10-27 20:04:33 +03:00 @@ -171,50 +171,68 @@ return FALSE; } +static int +is_case_sensitive(CHARSET_INFO *cs) +{ + return (cs->sort_order && + cs->sort_order['A'] < cs->sort_order['a'] && + cs->sort_order['a'] < cs->sort_order['B']) ? 1 : 0; +} + void dispcset(FILE *f,CHARSET_INFO *cs) { fprintf(f,"{\n"); fprintf(f," %d,%d,%d,\n",cs->number,0,0); - fprintf(f," MY_CS_COMPILED%s%s,\n", + fprintf(f," MY_CS_COMPILED%s%s%s,\n", cs->state & MY_CS_BINSORT ? "|MY_CS_BINSORT" : "", - cs->state & MY_CS_PRIMARY ? "|MY_CS_PRIMARY" : ""); + cs->state & MY_CS_PRIMARY ? "|MY_CS_PRIMARY" : "", + is_case_sensitive(cs) ? "|MY_CS_CSSORT" : ""); if (cs->name) { - fprintf(f," \"%s\",\n",cs->csname); - fprintf(f," \"%s\",\n",cs->name); - fprintf(f," \"\",\n"); - fprintf(f," ctype_%s,\n",cs->name); - fprintf(f," to_lower_%s,\n",cs->name); - fprintf(f," to_upper_%s,\n",cs->name); + fprintf(f," \"%s\", /* cset name */\n",cs->csname); + fprintf(f," \"%s\", /* coll name */\n",cs->name); + fprintf(f," \"\", /* comment */\n"); + fprintf(f," NULL, /* tailoring */\n"); + fprintf(f," ctype_%s, /* ctype */\n",cs->name); + fprintf(f," to_lower_%s, /* lower */\n",cs->name); + fprintf(f," to_upper_%s, /* upper */\n",cs->name); if (cs->sort_order) - fprintf(f," sort_order_%s,\n",cs->name); + fprintf(f," sort_order_%s, /* sort_order */\n",cs->name); else - fprintf(f," NULL,\n"); - fprintf(f," to_uni_%s,\n",cs->name); - fprintf(f," from_uni_%s,\n",cs->name); + fprintf(f," NULL, /* sort_order */\n"); + fprintf(f," NULL, /* contractions */\n"); + fprintf(f," NULL, /* sort_order_big*/\n"); + fprintf(f," to_uni_%s, /* to_uni */\n",cs->name); } else { - fprintf(f," NULL,\n"); - fprintf(f," NULL,\n"); - fprintf(f," NULL,\n"); - fprintf(f," NULL,\n"); - fprintf(f," NULL,\n"); - fprintf(f," NULL,\n"); - fprintf(f," NULL,\n"); - fprintf(f," NULL,\n"); - fprintf(f," NULL,\n"); + fprintf(f," NULL, /* cset name */\n"); + fprintf(f," NULL, /* coll name */\n"); + fprintf(f," NULL, /* comment */\n"); + fprintf(f," NULL, /* tailoging */\n"); + fprintf(f," NULL, /* ctype */\n"); + fprintf(f," NULL, /* lower */\n"); + fprintf(f," NULL, /* upper */\n"); + fprintf(f," NULL, /* sort order */\n"); + fprintf(f," NULL, /* contractions */\n"); + fprintf(f," NULL, /* sort_order_big*/\n"); + fprintf(f," NULL, /* to_uni */\n"); } - - fprintf(f," \"\",\n"); - fprintf(f," \"\",\n"); - fprintf(f," 0,\n"); - fprintf(f," 0,\n"); - fprintf(f," 0,\n"); + + fprintf(f," NULL, /* from_uni */\n"); + fprintf(f," NULL, /* state map */\n"); + fprintf(f," NULL, /* ident map */\n"); + fprintf(f," 1, /* strxfrm_multiply*/\n"); + fprintf(f," 1, /* mbminlen */\n"); + fprintf(f," 1, /* mbmaxlen */\n"); + fprintf(f," 0, /* min_sort_char */\n"); + fprintf(f," 255, /* max_sort_char */\n"); + fprintf(f," 0, /* escape_with_backslash_is_dangerous */\n"); + fprintf(f," &my_charset_8bit_handler,\n"); if (cs->state & MY_CS_BINSORT) - fprintf(f," &my_collation_bin_handler,\n"); + fprintf(f," &my_collation_8bit_bin_handler,\n"); else fprintf(f," &my_collation_8bit_simple_ci_handler,\n"); fprintf(f,"}\n"); @@ -252,6 +270,11 @@ } } } + + + fprintf(f,"#include <my_global.h>\n"); + fprintf(f,"#include <m_ctype.h>\n\n"); + for (cs=all_charsets; cs < all_charsets+256; cs++) { --- 1.9/include/my_handler.h 2005-05-05 15:11:23 +03:00 +++ 1.10/include/my_handler.h 2005-10-27 20:04:31 +03:00 @@ -88,4 +88,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.22/mysys/my_handler.c 2005-06-06 19:34:46 +03:00 +++ 1.23/mysys/my_handler.c 2005-10-27 20:04:32 +03: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,26 @@ 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. + diff_pos[1] OUT (b + diff_pos[1]) points to first value in tuple b + that is different from corresponding value in tuple a. + + EXAMPLES + Example1: if the function is called for tuples + ('aaa','bbb') and ('eee','fff'), then + diff_pos[0] = 1 (as 'aaa' != 'eee') + diff_pos[1] = 0 (offset from beggining of tuple b to 'eee' keypart). + + Example2: if the index function is called for tuples + ('aaa','bbb') and ('aaa','fff'), + diff_pos[0] = 2 (as 'aaa' != 'eee') + diff_pos[1] = 3 (offset from beggining of tuple b to 'fff' keypart, + here we assume that first key part is CHAR(3) NOT NULL) NOTES Number-keys can't be splited - + RETURN VALUES <0 If a < b 0 If a == b @@ -107,6 +123,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++) @@ -114,6 +131,7 @@ uchar *end; uint piks=! (keyseg->flag & HA_NO_SORT); (*diff_pos)++; + diff_pos[1]= (uint)(b - orig_b); /* Handle NULL part */ if (keyseg->null_bit) @@ -451,3 +469,84 @@ } return 0; } /* ha_key_cmp */ + + +/* + Find the first NULL value in index-suffix values tuple + + SYNOPSIS + ha_find_null() + keyseg Array of keyparts for key suffix + a Key suffix value tuple + + DESCRIPTION + Find the first NULL value in index-suffix values tuple. + TODO Consider optimizing this fuction or its use so we don't search for + NULL values in completely NOT NULL index 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/strings/ctype-win1250ch.c 2005-10-13 17:10:16 +03:00 +++ 1.54/strings/ctype-win1250ch.c 2005-10-27 20:04:33 +03:00 @@ -514,42 +514,84 @@ #ifdef REAL_MYSQL -static uchar NEAR like_range_prefix_min_win1250ch[] = +static uchar NEAR like_range_prefix_min_win1250ch[]= { -0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, -48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, -64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, -80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, -96, 54, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, -80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 165, 124, 125, 126, 0, -0, 0, 130, 0, 132, 133, 134, 135, 0, 137, 138, 139, 83, 84, 142, 90, -0, 145, 146, 147, 148, 149, 150, 151, 0, 153, 138, 155, 83, 84, 142, 90, -32, 161, 162, 76, 164, 165, 166, 167, 168, 65, 83, 171, 172, 173, 174, 90, -176, 177, 178, 76, 180, 181, 0, 183, 184, 65, 83, 187, 76, 189, 76, 90, -82, 65, 65, 65, 65, 76, 67, 67, 200, 69, 69, 69, 69, 73, 73, 68, -68, 78, 78, 79, 79, 79, 79, 215, 216, 85, 85, 85, 85, 89, 84, 223, -82, 65, 65, 65, 65, 76, 67, 67, 200, 69, 69, 69, 69, 73, 73, 68, -68, 78, 78, 79, 79, 79, 79, 247, 216, 85, 85, 85, 85, 89, 84, 255, + 0x00, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, + 0x08, 0x09, 0x0A, 0x0B, 0x0C, 0x0D, 0x0E, 0x0F, + 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, + 0x18, 0x19, 0x1A, 0x1B, 0x1C, 0x1D, 0x1E, 0x1F, + 0x20, 0x21, 0x22, 0x23, 0x24, 0x25, 0x26, 0x27, + 0x28, 0x29, 0x2A, 0x2B, 0x2C, 0x2D, 0x2E, 0x2F, + 0x30, 0x31, 0x32, 0x33, 0x34, 0x35, 0x36, 0x37, + 0x38, 0x39, 0x3A, 0x3B, 0x3C, 0x3D, 0x3E, 0x3F, + 0x40, 0x41, 0x42, 0x43, 0x44, 0x45, 0x46, 0x47, + 0x48, 0x49, 0x4A, 0x4B, 0x4C, 0x4D, 0x4E, 0x4F, + 0x50, 0x51, 0x52, 0x53, 0x54, 0x55, 0x56, 0x57, + 0x58, 0x59, 0x5A, 0x5B, 0x5C, 0x5D, 0x5E, 0x5F, + 0x60, 0x61, 0x62, 0x63, 0x64, 0x65, 0x66, 0x67, + 0x68, 0x69, 0x6A, 0x6B, 0x6C, 0x6D, 0x6E, 0x6F, + 0x70, 0x71, 0x72, 0x73, 0x74, 0x75, 0x76, 0x77, + 0x78, 0x79, 0x7A, 0x7B, 0x7C, 0x7D, 0x7E, 0x7F, + 0x80, 0x81, 0x82, 0x83, 0x84, 0x85, 0x86, 0x87, + 0x88, 0x89, 0x8A, 0x8B, 0x8C, 0x8D, 0x8E, 0x8F, + 0x90, 0x91, 0x92, 0x93, 0x94, 0x95, 0x96, 0x97, + 0x98, 0x99, 0x9A, 0x9B, 0x9C, 0x9D, 0x9E, 0x9F, + 0xA0, 0xA1, 0xA2, 0xA3, 0xA4, 0xA5, 0xA6, 0xA7, + 0xA8, 0xA9, 0xAA, 0xAB, 0xAC, 0xAD, 0xAE, 0xAF, + 0xB0, 0xB1, 0xB2, 0xB3, 0xB4, 0xB5, 0xB6, 0xB7, + 0xB8, 0xB9, 0xBA, 0xBB, 0xBC, 0xBD, 0xBE, 0xBF, + 0xC0, 0xC1, 0xC2, 0xC3, 0xC4, 0xC5, 0xC6, 0xC7, + 0xC8, 0xC9, 0xCA, 0xCB, 0xCC, 0xCD, 0xCE, 0xCF, + 0xD0, 0xD1, 0xD2, 0xD3, 0xD4, 0xD5, 0xD6, 0xD7, + 0xD8, 0xD9, 0xDA, 0xDB, 0xDC, 0xDD, 0xDE, 0xDF, + 0xE0, 0xE1, 0xE2, 0xE3, 0xE4, 0xE5, 0xE6, 0xE7, + 0xE8, 0xE9, 0xEA, 0xEB, 0xEC, 0xED, 0xEE, 0xEF, + 0xF0, 0xF1, 0xF2, 0xF3, 0xF4, 0xF5, 0xF6, 0xF7, + 0xF8, 0xF9, 0xFA, 0xFB, 0xFC, 0xFD, 0xFE, 0xFF }; -static uchar NEAR like_range_prefix_max_win1250ch[] = { -182, 182, 182, 182, 182, 182, 182, 182, 182, 182, 182, 182, 182, 182, 182, 182, -182, 182, 182, 182, 182, 182, 182, 182, 182, 182, 182, 182, 182, 182, 182, 182, -160, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, -48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, -64, 185, 98, 73, 240, 234, 102, 103, 104, 238, 106, 107, 179, 109, 242, 245, -112, 113, 224, 186, 254, 251, 118, 119, 120, 253, 159, 91, 92, 93, 94, 95, -96, 54, 98, 73, 240, 234, 102, 103, 104, 238, 106, 107, 179, 109, 242, 245, -112, 113, 224, 186, 254, 251, 118, 119, 120, 253, 159, 165, 124, 125, 126, 182, -182, 182, 130, 182, 132, 133, 134, 135, 182, 137, 154, 139, 186, 254, 158, 159, -182, 145, 146, 147, 148, 149, 150, 151, 182, 153, 154, 155, 186, 254, 158, 159, -32, 161, 162, 179, 164, 165, 166, 167, 168, 185, 186, 171, 172, 173, 174, 159, -176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 179, 189, 179, 159, -224, 185, 185, 185, 185, 179, 238, 238, 200, 234, 234, 234, 234, 238, 238, 240, -240, 242, 242, 245, 245, 245, 245, 215, 248, 251, 251, 251, 251, 253, 254, 223, -224, 185, 185, 185, 185, 179, 238, 238, 200, 234, 234, 234, 234, 238, 238, 240, -240, 242, 242, 245, 245, 245, 245, 247, 248, 251, 251, 251, 251, 253, 254, 255, + +/* + The letter "C" is a special case: + "CH" is sorted between "H" and "I". + prefix_max for "C" is "I": prefix_max[0x43] == 0x49 + prefix_max for "c" is "i": prefix_max[0x63] == 0x69 + For all other characters: prefix_max[i] == i +*/ + +static uchar NEAR like_range_prefix_max_win1250ch[]= +{ + 0x00, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, + 0x08, 0x09, 0x0A, 0x0B, 0x0C, 0x0D, 0x0E, 0x0F, + 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, + 0x18, 0x19, 0x1A, 0x1B, 0x1C, 0x1D, 0x1E, 0x1F, + 0x20, 0x21, 0x22, 0x23, 0x24, 0x25, 0x26, 0x27, + 0x28, 0x29, 0x2A, 0x2B, 0x2C, 0x2D, 0x2E, 0x2F, + 0x30, 0x31, 0x32, 0x33, 0x34, 0x35, 0x36, 0x37, + 0x38, 0x39, 0x3A, 0x3B, 0x3C, 0x3D, 0x3E, 0x3F, + 0x40, 0x41, 0x42, 0x49, 0x44, 0x45, 0x46, 0x47, + 0x48, 0x49, 0x4A, 0x4B, 0x4C, 0x4D, 0x4E, 0x4F, + 0x50, 0x51, 0x52, 0x53, 0x54, 0x55, 0x56, 0x57, + 0x58, 0x59, 0x5A, 0x5B, 0x5C, 0x5D, 0x5E, 0x5F, + 0x60, 0x61, 0x62, 0x69, 0x64, 0x65, 0x66, 0x67, + 0x68, 0x69, 0x6A, 0x6B, 0x6C, 0x6D, 0x6E, 0x6F, + 0x70, 0x71, 0x72, 0x73, 0x74, 0x75, 0x76, 0x77, + 0x78, 0x79, 0x7A, 0x7B, 0x7C, 0x7D, 0x7E, 0x7F, + 0x80, 0x81, 0x82, 0x83, 0x84, 0x85, 0x86, 0x87, + 0x88, 0x89, 0x8A, 0x8B, 0x8C, 0x8D, 0x8E, 0x8F, + 0x90, 0x91, 0x92, 0x93, 0x94, 0x95, 0x96, 0x97, + 0x98, 0x99, 0x9A, 0x9B, 0x9C, 0x9D, 0x9E, 0x9F, + 0xA0, 0xA1, 0xA2, 0xA3, 0xA4, 0xA5, 0xA6, 0xA7, + 0xA8, 0xA9, 0xAA, 0xAB, 0xAC, 0xAD, 0xAE, 0xAF, + 0xB0, 0xB1, 0xB2, 0xB3, 0xB4, 0xB5, 0xB6, 0xB7, + 0xB8, 0xB9, 0xBA, 0xBB, 0xBC, 0xBD, 0xBE, 0xBF, + 0xC0, 0xC1, 0xC2, 0xC3, 0xC4, 0xC5, 0xC6, 0xC7, + 0xC8, 0xC9, 0xCA, 0xCB, 0xCC, 0xCD, 0xCE, 0xCF, + 0xD0, 0xD1, 0xD2, 0xD3, 0xD4, 0xD5, 0xD6, 0xD7, + 0xD8, 0xD9, 0xDA, 0xDB, 0xDC, 0xDD, 0xDE, 0xDF, + 0xE0, 0xE1, 0xE2, 0xE3, 0xE4, 0xE5, 0xE6, 0xE7, + 0xE8, 0xE9, 0xEA, 0xEB, 0xEC, 0xED, 0xEE, 0xEF, + 0xF0, 0xF1, 0xF2, 0xF3, 0xF4, 0xF5, 0xF6, 0xF7, + 0xF8, 0xF9, 0xFA, 0xFB, 0xFC, 0xFD, 0xFE, 0xFF }; #define min_sort_char '\x20' --- 1.17/mysql-test/r/date_formats.result 2005-07-20 05:53:52 +03:00 +++ 1.18/mysql-test/r/date_formats.result 2005-10-27 20:04:31 +03:00 @@ -495,3 +495,11 @@ Warnings: Warning 1292 Truncated incorrect date value: '2003-04-05 g' Warning 1292 Truncated incorrect datetime value: '2003-04-05 10:11:12.101010234567' +create table t1 (f1 datetime); +insert into t1 (f1) values ("2005-01-01"); +insert into t1 (f1) values ("2005-02-01"); +select date_format(f1, "%m") as d1, date_format(f1, "%M") as d2 from t1 order by date_format(f1, "%M"); +d1 d2 +02 February +01 January +drop table t1; --- 1.13/mysql-test/t/date_formats.test 2005-07-28 16:12:32 +03:00 +++ 1.14/mysql-test/t/date_formats.test 2005-10-27 20:04:32 +03:00 @@ -261,4 +261,12 @@ str_to_date("2003-04-05 10:11:12.101010234567", "%Y-%m-%d %H:%i:%S.%f") as f2; --enable_ps_protocol +# +# Bug #14016 +# +create table t1 (f1 datetime); +insert into t1 (f1) values ("2005-01-01"); +insert into t1 (f1) values ("2005-02-01"); +select date_format(f1, "%m") as d1, date_format(f1, "%M") as d2 from t1 order by date_format(f1, "%M"); +drop table t1; # End of 4.1 tests --- 1.54/mysys/my_getopt.c 2005-10-06 22:09:05 +03:00 +++ 1.55/mysys/my_getopt.c 2005-10-27 20:04:32 +03:00 @@ -723,10 +723,10 @@ ulonglong block_size= (optp->block_size ? (ulonglong) optp->block_size : 1L); num= eval_num_suffix(arg, err, (char*) optp->name); - if (num > 0 && (ulonglong) num > (ulonglong) (ulong) optp->max_value && + if (num > 0 && (ulonglong) num > (ulonglong) optp->max_value && optp->max_value) /* if max value is not set -> no upper limit */ - num= (longlong) (ulong) optp->max_value; - num= ((num - (longlong) optp->sub_size) / block_size); + num= (ulonglong) optp->max_value; + num= ((num - optp->sub_size) / block_size); num= (longlong) (num * block_size); return max(num, optp->min_value); } --- 1.55/mysql-test/mysql-test-run.pl 2005-10-21 00:23:29 +03:00 +++ 1.56/mysql-test/mysql-test-run.pl 2005-10-27 23:43:13 +03:00 @@ -473,6 +473,13 @@ my $im_mysqld1_port= 9312; my $im_mysqld2_port= 9314; + if ( $ENV{'MTR_BUILD_THREAD'} ) + { + $opt_master_myport= $ENV{'MTR_BUILD_THREAD'} * 40 + 8120; + $opt_slave_myport= $opt_master_myport + 16; + $opt_ndbcluster_port= $opt_master_myport + 24; + } + # Read the command line # Note: Keep list, and the order, in sync with usage at end of this file @@ -882,7 +889,9 @@ { $path_client_bindir= mtr_path_exists("$glob_basedir/client_release", "$glob_basedir/bin"); - $exe_mysqld= mtr_exe_exists ("$path_client_bindir/mysqld-nt"); + $exe_mysqld= mtr_exe_exists ("$path_client_bindir/mysqld-nt", + "$path_client_bindir/mysqld", + "$path_client_bindir/mysqld-debug",); $path_language= mtr_path_exists("$glob_basedir/share/english/"); $path_charsetsdir= mtr_path_exists("$glob_basedir/share/charsets"); } @@ -944,9 +953,18 @@ "$glob_basedir/share/english/"); $path_charsetsdir= mtr_path_exists("$glob_basedir/share/mysql/charsets", "$glob_basedir/share/charsets"); - $exe_mysqld= mtr_exe_exists ("$glob_basedir/libexec/mysqld", - "$glob_basedir/bin/mysqld"); + if ( $glob_win32 ) + { + $exe_mysqld= mtr_exe_exists ("$glob_basedir/bin/mysqld-nt", + "$glob_basedir/bin/mysqld", + "$glob_basedir/bin/mysqld-debug",); + } + else + { + $exe_mysqld= mtr_exe_exists ("$glob_basedir/libexec/mysqld", + "$glob_basedir/bin/mysqld"); + } $exe_im= mtr_exe_exists("$glob_basedir/libexec/mysqlmanager", "$glob_basedir/bin/mysqlmanager"); if ( $glob_use_embedded_server ) @@ -1040,6 +1058,13 @@ chomp($ENV{$key}); } } + + # We are nice and report a bit about our settings + print "Using MTR_BUILD_THREAD = ",$ENV{MTR_BUILD_THREAD} || 0,"\n"; + print "Using MASTER_MYPORT = $ENV{MASTER_MYPORT}\n"; + print "Using MASTER_MYPORT1 = $ENV{MASTER_MYPORT1}\n"; + print "Using SLAVE_MYPORT = $ENV{SLAVE_MYPORT}\n"; + print "Using NDBCLUSTER_PORT = $opt_ndbcluster_port\n"; } --- 1.33/mysql-test/r/insert_select.result 2005-09-15 22:05:29 +03:00 +++ 1.34/mysql-test/r/insert_select.result 2005-10-27 23:43:13 +03:00 @@ -668,3 +668,11 @@ insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b; ERROR 42S22: Unknown column 't2.b' in 'field list' drop table t1,t2,t3; +create table t1(x int, y int); +create table t2(x int, z int); +insert into t1(x,y) select x,z from t2 on duplicate key update x=values(x); +insert into t1(x,y) select x,z from t2 on duplicate key update x=values(z); +ERROR 42S22: Unknown column 'z' in 'field list' +insert into t1(x,y) select x,z from t2 on duplicate key update x=values(t2.x); +ERROR 42S02: Unknown table 't2' in field list +drop table t1,t2; --- 1.107/mysql-test/r/select.result 2005-10-25 09:01:42 +03:00 +++ 1.108/mysql-test/r/select.result 2005-10-27 23:43:13 +03:00 @@ -2801,6 +2801,304 @@ 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 1 SIMPLE t2 ref a a 23 test.t1.a 2 DROP TABLE t1, t2; +CREATE TABLE t1 ( city char(30) ); +INSERT INTO t1 VALUES ('London'); +INSERT INTO t1 VALUES ('Paris'); +SELECT * FROM t1 WHERE city='London'; +city +London +SELECT * FROM t1 WHERE city='london'; +city +London +EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE city='London' AND city='london'; +city +London +EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; +city +London +DROP TABLE t1; +create table t1 (a int(11) unsigned, b int(11) unsigned); +insert into t1 values (1,0), (1,1), (1,2); +select a-b from t1 order by 1; +a-b +0 +1 +18446744073709551615 +select a-b , (a-b < 0) from t1 order by 1; +a-b (a-b < 0) +0 0 +1 0 +18446744073709551615 0 +select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0; +d (a-b >= 0) b +1 1 0 +0 1 1 +18446744073709551615 1 2 +select cast((a - b) as unsigned) from t1 order by 1; +cast((a - b) as unsigned) +0 +1 +18446744073709551615 +drop table t1; +create table t1 (a int(11)); +select all all * from t1; +a +select distinct distinct * from t1; +a +select all distinct * from t1; +ERROR HY000: Incorrect usage of ALL and DISTINCT +select distinct all * from t1; +ERROR HY000: Incorrect usage of ALL and DISTINCT +drop table t1; +CREATE TABLE t1 ( +kunde_intern_id int(10) unsigned NOT NULL default '0', +kunde_id int(10) unsigned NOT NULL default '0', +FK_firma_id int(10) unsigned NOT NULL default '0', +aktuell enum('Ja','Nein') NOT NULL default 'Ja', +vorname varchar(128) NOT NULL default '', +nachname varchar(128) NOT NULL default '', +geloescht enum('Ja','Nein') NOT NULL default 'Nein', +firma varchar(128) NOT NULL default '' +); +INSERT INTO t1 VALUES +(3964,3051,1,'Ja','Vorname1','1Nachname','Nein','Print Schau XXXX'), +(3965,3051111,1,'Ja','Vorname1111','1111Nachname','Nein','Print Schau XXXX'); +SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM t1 +WHERE +( +( +( '' != '' AND firma LIKE CONCAT('%', '', '%')) +OR +(vorname LIKE CONCAT('%', 'Vorname1', '%') AND +nachname LIKE CONCAT('%', '1Nachname', '%') AND +'Vorname1' != '' AND 'xxxx' != '') +) +AND +( +aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2 +) +) +; +kunde_id FK_firma_id aktuell vorname nachname geloescht +SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, +geloescht FROM t1 +WHERE +( +( +aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2 +) +AND +( +( '' != '' AND firma LIKE CONCAT('%', '', '%') ) +OR +( vorname LIKE CONCAT('%', 'Vorname1', '%') AND +nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND +'xxxx' != '') +) +) +; +kunde_id FK_firma_id aktuell vorname nachname geloescht +SELECT COUNT(*) FROM t1 WHERE +( 0 OR (vorname LIKE '%Vorname1%' AND nachname LIKE '%1Nachname%' AND 1)) +AND FK_firma_id = 2; +COUNT(*) +0 +drop table t1; +CREATE TABLE t1 (b BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (b)); +INSERT INTO t1 VALUES (0x8000000000000000); +SELECT b FROM t1 WHERE b=0x8000000000000000; +b +9223372036854775808 +DROP TABLE t1; +CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL); +CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL); +INSERT INTO `t2` VALUES (0,'READ'); +CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL); +INSERT INTO `t3` VALUES (1,'fs'); +select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0); +id name gid uid ident level +1 fs NULL NULL 0 READ +drop table t1,t2,t3; +CREATE TABLE t1 ( +acct_id int(11) NOT NULL default '0', +profile_id smallint(6) default NULL, +UNIQUE KEY t1$acct_id (acct_id), +KEY t1$profile_id (profile_id) +); +INSERT INTO t1 VALUES (132,17),(133,18); +CREATE TABLE t2 ( +profile_id smallint(6) default NULL, +queue_id int(11) default NULL, +seq int(11) default NULL, +KEY t2$queue_id (queue_id) +); +INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1); +CREATE TABLE t3 ( +id int(11) NOT NULL default '0', +qtype int(11) default NULL, +seq int(11) default NULL, +warn_lvl int(11) default NULL, +crit_lvl int(11) default NULL, +rr1 tinyint(4) NOT NULL default '0', +rr2 int(11) default NULL, +default_queue tinyint(4) NOT NULL default '0', +KEY t3$qtype (qtype), +KEY t3$id (id) +); +INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0), +(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0); +SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q +WHERE +(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND +(pq.queue_id = q.id) AND (q.rr1 <> 1); +COUNT(*) +4 +drop table t1,t2,t3; +create table t1 (f1 int); +insert into t1 values (1),(NULL); +create table t2 (f2 int, f3 int, f4 int); +create index idx1 on t2 (f4); +insert into t2 values (1,2,3),(2,4,6); +select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3) +from t2 C where A.f4 = C.f4) or A.f3 IS NULL; +f2 +1 +NULL +drop table t1,t2; +create table t2 (a tinyint unsigned); +create index t2i on t2(a); +insert into t2 values (0), (254), (255); +explain select * from t2 where a > -1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index +select * from t2 where a > -1; +a +0 +254 +255 +drop table t2; +CREATE TABLE t1 (a int, b int, c int); +INSERT INTO t1 +SELECT 50, 3, 3 FROM DUAL +WHERE NOT EXISTS +(SELECT * FROM t1 WHERE a = 50 AND b = 3); +SELECT * FROM t1; +a b c +50 3 3 +INSERT INTO t1 +SELECT 50, 3, 3 FROM DUAL +WHERE NOT EXISTS +(SELECT * FROM t1 WHERE a = 50 AND b = 3); +select found_rows(); +found_rows() +0 +SELECT * FROM t1; +a b c +50 3 3 +select count(*) from t1; +count(*) +1 +select found_rows(); +found_rows() +1 +select count(*) from t1 limit 2,3; +count(*) +select found_rows(); +found_rows() +0 +select SQL_CALC_FOUND_ROWS count(*) from t1 limit 2,3; +count(*) +select found_rows(); +found_rows() +1 +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT); +(SELECT a, b AS c FROM t1) ORDER BY c+1; +a c +(SELECT a, b AS c FROM t1) ORDER BY b+1; +a c +SELECT a, b AS c FROM t1 ORDER BY c+1; +a c +SELECT a, b AS c FROM t1 ORDER BY b+1; +a c +drop table t1; +create table t1(f1 int, f2 int); +create table t2(f3 int); +select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,1)); +f1 +select f1 from t1,t2 where f1=f2 and (f1,NULL) = ((1,1)); +f1 +select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,NULL)); +f1 +insert into t1 values(1,1),(2,null); +insert into t2 values(2); +select * from t1,t2 where f1=f3 and (f1,f2) = (2,null); +f1 f2 f3 +select * from t1,t2 where f1=f3 and (f1,f2) <=> (2,null); +f1 f2 f3 +2 NULL 2 +drop table t1,t2; +create table t1 (f1 int not null auto_increment primary key, f2 varchar(10)); +create table t11 like t1; +insert into t1 values(1,""),(2,""); +show table status like 't1%'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +t1 MyISAM 9 Dynamic 2 20 X X X X X X X X latin1_swedish_ci NULL +t11 MyISAM 9 Dynamic 0 0 X X X X X X X X latin1_swedish_ci NULL +select 123 as a from t1 where f1 is null; +a +drop table t1,t11; +CREATE TABLE t1 (a INT, b INT); +(SELECT a, b AS c FROM t1) ORDER BY c+1; +a c +(SELECT a, b AS c FROM t1) ORDER BY b+1; +a c +SELECT a, b AS c FROM t1 ORDER BY c+1; +a c +SELECT a, b AS c FROM t1 ORDER BY b+1; +a c +drop table t1; +CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) ); +INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4); +CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, c INT ); +INSERT INTO t2 VALUES ( 1,10,1), (1,10,2), (1,11,1), (1,11,2), (1,2,1), (1,2,2), +(1,2,3); +SELECT t2.a, t2.b, IF(t1.b IS NULL,'',c) AS c, COUNT(*) AS d FROM t2 LEFT JOIN +t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; +a b c d +1 2 1 1 +1 2 2 1 +1 2 3 1 +1 10 2 +1 11 2 +SELECT t2.a, t2.b, IF(t1.b IS NULL,'',c) AS c, COUNT(*) AS d FROM t2 LEFT JOIN +t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c; +a b c d +1 10 4 +1 2 1 1 +1 2 2 1 +1 2 3 1 +SELECT t2.a, t2.b, IF(t1.b IS NULL,'',c) AS c, COUNT(*) AS d FROM t2 LEFT JOIN +t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c; +a b c d +1 2 1 1 +1 2 2 1 +1 2 3 1 +1 10 2 +1 11 2 +SELECT t2.a, t2.b, IF(t1.b IS NULL,'',c) AS c, COUNT(*) AS d FROM t2,t1 +WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; +a b c d +1 2 1 1 +1 2 2 1 +1 2 3 1 +DROP TABLE IF EXISTS t1, t2; create table t1 (a int, b int); create table t2 like t1; select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1; @@ -3180,6 +3478,14 @@ from t1 inner join (t2 right join t3 on t2.id = t3.b_id) on t1.id = t3.a_id; count(*) 6 +create table t1 (f1 int primary key, f2 int); +create table t2 (f3 int, f4 int, primary key(f3,f4)); +insert into t1 values (1,1); +insert into t2 values (1,1),(1,2); +select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1; +count(f2) >0 +1 +drop table t1,t2; drop table t1,t2,t3; create table t1 (a int); create table t2 (b int); --- 1.26/mysql-test/r/update.result 2005-10-19 00:43:13 +03:00 +++ 1.27/mysql-test/r/update.result 2005-10-27 20:04:32 +03:00 @@ -263,8 +263,8 @@ delete from t1 where count(*)=1; ERROR HY000: Invalid use of group function drop table t1; -create table t1 ( a int, index (a) ); -insert into t1 values (0),(0),(0),(0),(0),(0),(0),(0); +create table t1 ( a int, b int default 0, index (a) ); +insert into t1 (a) values (0),(0),(0),(0),(0),(0),(0),(0); flush status; select a from t1 order by a limit 1; a @@ -278,15 +278,16 @@ Handler_read_rnd 0 Handler_read_rnd_next 0 flush status; -update t1 set a=unix_timestamp() order by a limit 1; +update t1 set a=9999 order by a limit 1; +update t1 set b=9999 order by a limit 1; show status like 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 0 Handler_read_next 0 Handler_read_prev 0 -Handler_read_rnd 1 -Handler_read_rnd_next 0 +Handler_read_rnd 2 +Handler_read_rnd_next 9 flush status; delete from t1 order by a limit 1; show status like 'handler_read%'; @@ -318,7 +319,21 @@ Handler_read_prev 0 Handler_read_rnd 1 Handler_read_rnd_next 9 -select count(*) from t1; -count(*) -5 +select * from t1; +a b +0 0 +0 0 +0 0 +0 0 +0 0 +update t1 set a=a+10,b=1 order by a limit 3; +update t1 set a=a+11,b=2 order by a limit 3; +update t1 set a=a+12,b=3 order by a limit 3; +select * from t1 order by a; +a b +11 2 +21 2 +22 3 +22 3 +23 3 drop table t1; --- 1.26/mysql-test/t/insert_select.test 2005-09-15 22:05:30 +03:00 +++ 1.27/mysql-test/t/insert_select.test 2005-10-27 23:43:13 +03:00 @@ -202,4 +202,26 @@ insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b; drop table t1,t2,t3; +# +# Bug #12695 Item_func_isnull::update_used_tables() did not update +# const_item_cache +create table t1(f1 varchar(5) key); +insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1; +insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1; +insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1; +select * from t1; +drop table t1; + +# +# Bug #13392 values() fails with 'ambiguous' or returns NULL +# with ON DUPLICATE and SELECT +create table t1(x int, y int); +create table t2(x int, z int); +insert into t1(x,y) select x,z from t2 on duplicate key update x=values(x); +--error 1054 +insert into t1(x,y) select x,z from t2 on duplicate key update x=values(z); +--error 1109 +insert into t1(x,y) select x,z from t2 on duplicate key update x=values(t2.x); +drop table t1,t2; + # End of 4.1 tests --- 1.88/mysql-test/t/select.test 2005-10-25 09:01:42 +03:00 +++ 1.89/mysql-test/t/select.test 2005-10-27 20:04:32 +03:00 @@ -2244,6 +2244,15 @@ WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; DROP TABLE IF EXISTS t1, t2; +# +# Bug #13855 select distinct with group by caused server crash +# +create table t1 (f1 int primary key, f2 int); +create table t2 (f3 int, f4 int, primary key(f3,f4)); +insert into t1 values (1,1); +insert into t2 values (1,1),(1,2); +select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1; +drop table t1,t2; # End of 4.1 tests # --- 1.24/mysql-test/t/update.test 2005-10-19 00:43:13 +03:00 +++ 1.25/mysql-test/t/update.test 2005-10-27 20:04:32 +03:00 @@ -228,15 +228,16 @@ drop table t1; # BUG#12915: Optimize "DELETE|UPDATE ... ORDER BY ... LIMIT n" to use an index -create table t1 ( a int, index (a) ); -insert into t1 values (0),(0),(0),(0),(0),(0),(0),(0); +create table t1 ( a int, b int default 0, index (a) ); +insert into t1 (a) values (0),(0),(0),(0),(0),(0),(0),(0); flush status; select a from t1 order by a limit 1; show status like 'handler_read%'; flush status; -update t1 set a=unix_timestamp() order by a limit 1; +update t1 set a=9999 order by a limit 1; +update t1 set b=9999 order by a limit 1; show status like 'handler_read%'; flush status; @@ -253,7 +254,11 @@ delete from t1 order by a limit 1; show status like 'handler_read%'; -select count(*) from t1; +select * from t1; +update t1 set a=a+10,b=1 order by a limit 3; +update t1 set a=a+11,b=2 order by a limit 3; +update t1 set a=a+12,b=3 order by a limit 3; +select * from t1 order by a; drop table t1; # End of 4.1 tests --- 1.68/mysql-test/r/myisam.result 2005-09-30 00:34:15 +03:00 +++ 1.69/mysql-test/r/myisam.result 2005-10-27 23:43:13 +03:00 @@ -669,6 +669,38 @@ 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; set storage_engine=MyISAM; drop table if exists t1,t2,t3; --- Testing varchar --- --- 1.49/mysql-test/t/myisam.test 2005-09-21 14:53:08 +03:00 +++ 1.50/mysql-test/t/myisam.test 2005-10-27 23:43:13 +03:00 @@ -629,6 +629,25 @@ 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 # --- 1.11/innobase/include/Makefile.am 2004-12-17 15:06:02 +02:00 +++ 1.12/innobase/include/Makefile.am 2005-10-27 20:04:31 +03:00 @@ -28,7 +28,7 @@ ha0ha.h ha0ha.ic hash0hash.h hash0hash.ic \ ibuf0ibuf.h ibuf0ibuf.ic ibuf0types.h lock0lock.h \ lock0lock.ic lock0types.h log0log.h log0log.ic log0recv.h \ - log0recv.ic mach0data.h mach0data.ic makefilewin.i \ + log0recv.ic mach0data.h mach0data.ic \ mem0dbg.h mem0dbg.ic mem0mem.h mem0mem.ic mem0pool.h \ mem0pool.ic mtr0log.h mtr0log.ic mtr0mtr.h mtr0mtr.ic \ mtr0types.h os0file.h os0proc.h os0proc.ic \ --- 1.160/tests/mysql_client_test.c 2005-10-21 14:53:36 +03:00 +++ 1.161/tests/mysql_client_test.c 2005-10-27 23:43:14 +03:00 @@ -59,6 +59,8 @@ static const char *opt_basedir= "./"; +static longlong opt_getopt_ll_test= 0; + static int embedded_server_arg_count= 0; static char *embedded_server_args[MAX_SERVER_ARGS]; @@ -14351,6 +14353,19 @@ rc= mysql_query(mysql, "drop table t1, t2"); myquery(rc); } + + +/* + Bug #12925: Bad handling of maximum values in getopt +*/ +static void test_bug12925() +{ + myheader("test_bug12925"); + if (opt_getopt_ll_test) + DIE_UNLESS(opt_getopt_ll_test == LL(25600*1024*1024)); +} + + /* Read and parse arguments and MySQL options from my.cnf */ @@ -14393,6 +14408,9 @@ {"user", 'u', "User for login if not current user", (char **) &opt_user, (char **) &opt_user, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, #endif + {"getopt-ll-test", 'g', "Option for testing bug in getopt library", + (char **) &opt_getopt_ll_test, (char **) &opt_getopt_ll_test, 0, + GET_LL, REQUIRED_ARG, 0, 0, LONGLONG_MAX, 0, 0, 0}, { 0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0} }; @@ -14598,6 +14616,7 @@ { "test_bug10760", test_bug10760 }, { "test_bug12001", test_bug12001 }, { "test_bug11718", test_bug11718 }, + { "test_bug12925", test_bug12925 }, { "test_bug11909", test_bug11909 }, { "test_bug11901", test_bug11901 }, { "test_bug11904", test_bug11904 },
| Thread | ||
|---|---|---|
| • bk commit into 5.0 tree (jani:1.1945) | jani | 27 Oct |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 1.130 sec. using MySQL 5.1.14-beta-log