bk commit into 4.1 tree (sergefp:1.2263) BUG#9622

[Sergey Petrunia]

Below is the list of changes that have just been committed into a local
4.1 repository of psergey. When psergey 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.2263 05/05/14 00:21:53 sergefp@stripped +3 -0
  Fix for BUG#9622: Make index statistics collection in MyISAM behave the same
  way in ALTER TABLE ... ENABLE KEYS, ANALYZE TABLE and after bulk insert:
  now statistics collection always assumes NULLs are inequal. 

  mysql-test/t/myisam.test
    1.37 05/05/14 00:21:43 sergefp@stripped +15 -0
    Testcase for BUG#9622

  mysql-test/r/myisam.result
    1.49 05/05/14 00:21:43 sergefp@stripped +14 -0
    Testcase for BUG#9622

  myisam/mi_check.c
    1.146 05/05/14 00:21:43 sergefp@stripped +35 -1
    Fix for BUG#9622: Make index statistics collection in MyISAM behave the same
    way in ALTER TABLE ... ENABLE KEYS, ANALYZE TABLE and after bulk insert:
    now statistics collection always assumes NULLs are inequal. 

# 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:	pslp.mylan
# Root:	/home/psergey/mysql-4.1-bug10095

--- 1.145/myisam/mi_check.c	2005-04-11 20:04:42 +02:00
+++ 1.146/myisam/mi_check.c	2005-05-14 00:21:43 +02:00
@@ -3243,6 +3243,9 @@
     cmp=ha_key_cmp(sort_param->seg,sort_info->key_block->lastkey,
 		   (uchar*) a, USE_WHOLE_KEY,SEARCH_FIND | SEARCH_UPDATE,
 		   &diff_pos);
+    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]++;
   }
   else
@@ -3955,7 +3958,38 @@
   return;
 }
 
-    /* calculate unique keys for each part key */
+
+/*
+  Update statistics for each part of an index
+  
+  SYNOPSIS
+    update_key_parts()
+      keyinfo               Index information (only key->keysegs used)
+      rec_per_key_part  OUT Store statistics here
+      unique            IN  Array of #distinct values collected over index
+                            run.
+      records               Number of records in the table
+      
+  NOTES
+    Unique is an array:
+    unique[0]= (#different values of {keypart1}) - 1
+    unique[1]= (#different values of {keypart2,keypart1} tuple) - unique[0] - 1
+    ...
+    Here we assume that NULL != NULL (see SEARCH_NULL_ARE_NOT_EQUAL). 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().
+
+    Output is an array:
+    rec_per_key_part[k] = 
+     = E(#records in the table such that keypart_1=c_1 AND ... AND 
+         keypart_k=c_k for arbitrary constants c_1 ... c_k) 
+     
+     = {assuming that values have uniform distribution and index contains all
+        tuples from the domain (or that {c_1, ..., c_k} tuple is choosen from
+        index tuples}
+     
+     = #tuples-in-the-index / #distinct-tuples-in-the-index.
+*/
 
 void update_key_parts(MI_KEYDEF *keyinfo, ulong *rec_per_key_part,
 			     ulonglong *unique, ulonglong records)

--- 1.48/mysql-test/r/myisam.result	2005-04-08 14:12:19 +02:00
+++ 1.49/mysql-test/r/myisam.result	2005-05-14 00:21:43 +02:00
@@ -581,3 +581,17 @@
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
 drop table t1;
+create table t1 (a int, key(a));
+insert into t1 values (1),(2),(3),(4),(NULL),(NULL),(NULL),(NULL);
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+show keys 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	8	NULL	NULL	YES	BTREE	
+alter table t1 disable keys;
+alter table t1 enable keys;
+show keys 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	8	NULL	NULL	YES	BTREE	
+drop table t1;

--- 1.36/mysql-test/t/myisam.test	2005-04-08 14:12:19 +02:00
+++ 1.37/mysql-test/t/myisam.test	2005-05-14 00:21:43 +02:00
@@ -560,3 +560,18 @@
 check table t1;
 drop table t1;
 
+
+# BUG#9622 - ANALYZE TABLE and ALTER TABLE .. ENABLE INDEX produce
+# different statistics on the same table with NULL values.
+create table t1 (a int, key(a));
+
+insert into t1 values (1),(2),(3),(4),(NULL),(NULL),(NULL),(NULL);
+analyze table t1;
+show keys from t1;
+
+alter table t1 disable keys;
+alter table t1 enable keys;
+show keys from t1;
+
+drop table t1;
+