MySQL Lists: commits: bk commit into 4.1 tree (istruewing:1.2608) BUG#26231

List:	Commits	« Previous Message Next Message »
From:	ingo	Date:	March 16 2007 10:28am
Subject:	bk commit into 4.1 tree (istruewing:1.2608) BUG#26231
View as plain text
Below is the list of changes that have just been committed into a local
4.1 repository of istruewing. When istruewing does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-03-16 10:28:48+01:00, istruewing@stripped +4 -0
  Bug#26231 - select count(*) on myisam table returns wrong value
              when index is used
  
  When the table contained TEXT columns with empty contents
  ('', zero length, but not NULL) _and_ strings starting with
  control characters like tabulator or newline, the empty values
  were not found in a "records in range" estimate. Hence count(*)
  missed these records.
  
  The reason was a different set of search flags used for key
  insert and key range estimation.
  
  I decided to fix the set of flags used in range estimation.
  Otherwise millions of databases around the world would require
  a repair after an upgrade.
  
  The consequence is that the manual must be fixed, which claims
  that TEXT columns are compared with "end space padding". This
  is true for CHAR/VARCHAR but wrong for TEXT. See also bug 21335.

  myisam/mi_range.c@stripped, 2007-03-16 10:28:45+01:00, istruewing@stripped +35 -1
    Bug#26231 - select count(*) on myisam table returns wrong value
                when index is used
    Added SEARCH_UPDATE to the search flags so that it compares
    like write/update/delete operations do. Only so it expects
    the keys at the place where they have been inserted.

  myisam/mi_search.c@stripped, 2007-03-16 10:28:46+01:00, istruewing@stripped +35 -7
    Bug#26231 - select count(*) on myisam table returns wrong value
                when index is used
    Added some comments to explain how _mi_get_binary_pack_key()
    works.

  mysql-test/r/myisam.result@stripped, 2007-03-16 10:28:46+01:00, istruewing@stripped +150
-0
    Bug#26231 - select count(*) on myisam table returns wrong value
                when index is used
    Added a test.

  mysql-test/t/myisam.test@stripped, 2007-03-16 10:28:46+01:00, istruewing@stripped +144
-0
    Bug#26231 - select count(*) on myisam table returns wrong value
                when index is used
    Added test result.

# 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:	istruewing
# Host:	chilla.local
# Root:	/home/mydev/mysql-4.1-bug26231

--- 1.14/myisam/mi_range.c	2007-03-16 10:28:51 +01:00
+++ 1.15/myisam/mi_range.c	2007-03-16 10:28:51 +01:00
@@ -145,8 +145,42 @@ static ha_rows _mi_record_pos(MI_INFO *i
   if (!(nextflag & (SEARCH_FIND | SEARCH_NO_FIND | SEARCH_LAST)))
     key_len=USE_WHOLE_KEY;
 
+  /*
+    my_handler.c:mi_compare_text() has a flag 'skip_end_space'.
+    This is set in my_handler.c:ha_key_cmp() in dependence on the
+    compare flags 'nextflag' and the column type.
+
+    TEXT columns are of type HA_KEYTYPE_VARTEXT. In this case the
+    condition is skip_end_space= ((nextflag & (SEARCH_FIND |
+    SEARCH_UPDATE)) == SEARCH_FIND).
+
+    SEARCH_FIND is used for an exact key search. The combination
+    SEARCH_FIND | SEARCH_UPDATE is used in write/update/delete
+    operations with a comment like "Not real duplicates", whatever this
+    means. From the condition above we can see that 'skip_end_space' is
+    always false for these operations. The result is that trailing space
+    counts in key comparison and hence, emtpy strings ('', string length
+    zero, but not NULL) compare less that strings starting with control
+    characters and these in turn compare less than strings starting with
+    blanks.
+
+    When estimating the number of records in a key range, we request an
+    exact search for the minimum key. This translates into a plain
+    SEARCH_FIND flag. Using this alone would lead to a 'skip_end_space'
+    compare. Empty strings would be expected above control characters.
+    Their keys would not be found because they are located below control
+    characters.
+
+    This is the reason that we add the SEARCH_UPDATE flag here. It makes
+    the key estimation compare in the same way like key write operations
+    do. Olny so we will find the keys where they have been inserted.
+
+    Adding the flag unconditionally does not hurt as it is used in the
+    above mentioned condition only. So it can safely be used together
+    with other flags.
+  */
   pos=_mi_search_pos(info,keyinfo,key_buff,key_len,
-		     nextflag | SEARCH_SAVE_BUFF,
+		     nextflag | SEARCH_SAVE_BUFF | SEARCH_UPDATE,
 		     info->s->state.key_root[inx]);
   if (pos >= 0.0)
   {

--- 1.56/myisam/mi_search.c	2007-03-16 10:28:51 +01:00
+++ 1.57/myisam/mi_search.c	2007-03-16 10:28:51 +01:00
@@ -918,11 +918,16 @@ uint _mi_get_binary_pack_key(register MI
   /*
     Keys are compressed the following way:
 
-    prefix length    Packed length of prefix for the prev key. (1 or 3 bytes)
+    prefix length    Packed length of prefix common with prev key (1 or 3 bytes)
     for each key segment:
       [is null]        Null indicator if can be null (1 byte, zero means null)
       [length]         Packed length if varlength (1 or 3 bytes)
+      key segment      'length' bytes of key segment value
     pointer          Reference to the data file (last_keyseg->length).
+
+    get_key_length() is a macro. It gets the prefix length from 'page'
+    and puts it into 'length'. It increments 'page' by 1 or 3, depending
+    on the packed length of the prefix length.
   */
   get_key_length(length,page);
   if (length)
@@ -935,34 +940,44 @@ uint _mi_get_binary_pack_key(register MI
       my_errno=HA_ERR_CRASHED;
       DBUG_RETURN(0);                                 /* Wrong key */
     }
-    from=key;  from_end=key+length;
+    /* Key is packed against prev key, take prefix from prev key. */
+    from= key;
+    from_end= key + length;
   }
   else
   {
-    from=page; from_end=page_end;               /* Not packed key */
+    /* Key is not packed against prev key, take all from page buffer. */
+    from= page;
+    from_end= page_end;
   }
 
   /*
-    The trouble is that key is split in two parts:
-     The first part is in from ...from_end-1.
-     The second part starts at page
+    The trouble is that key can be split in two parts:
+      The first part (prefix) is in from .. from_end - 1.
+      The second part starts at page.
+    The split can be at every byte position. So we need to check for
+    the end of the first part before using every byte.
   */
   for (keyseg=keyinfo->seg ; keyseg->type ;keyseg++)
   {
     if (keyseg->flag & HA_NULL_PART)
     {
+      /* If prefix is used up, switch to rest. */
       if (from == from_end) { from=page;  from_end=page_end; }
       if (!(*key++ = *from++))
         continue;                               /* Null part */
     }
     if (keyseg->flag & (HA_VAR_LENGTH | HA_BLOB_PART | HA_SPACE_PACK))
     {
-      /* Get length of dynamic length key part */
+      /* If prefix is used up, switch to rest. */
       if (from == from_end) { from=page;  from_end=page_end; }
+      /* Get length of dynamic length key part */
       if ((length= (*key++ = *from++)) == 255)
       {
+        /* If prefix is used up, switch to rest. */
         if (from == from_end) { from=page;  from_end=page_end; }
         length= (uint) ((*key++ = *from++)) << 8;
+        /* If prefix is used up, switch to rest. */
         if (from == from_end) { from=page;  from_end=page_end; }
         length+= (uint) ((*key++ = *from++));
       }
@@ -982,20 +997,33 @@ uint _mi_get_binary_pack_key(register MI
     key+=length;
     from+=length;
   }
+  /*
+    Last segment (type == 0) contains length of data pointer.
+    If we have mixed key blocks with data pointer and key block pointer,
+    we have to copy both.
+  */
   length=keyseg->length+nod_flag;
   if ((tmp=(uint) (from_end-from)) <= length)
   {
+    /* Remaining length is less or equal max possible length. */
     memcpy(key+tmp,page,length-tmp);            /* Get last part of key */
     *page_pos= page+length-tmp;
   }
   else
   {
+    /*
+      Remaining length is greater than max possible length.
+      This can happen only if we switched to the new key bytes already.
+      'page_end' is calculated with MI_MAX_KEY_BUFF. So it can be far
+      behind the real end of the key.
+    */
     if (from_end != page_end)
     {
       DBUG_PRINT("error",("Error when unpacking key"));
       my_errno=HA_ERR_CRASHED;
       DBUG_RETURN(0);                                 /* Error */
     }
+    /* Copy data pointer and, if appropriate, key block pointer. */
     memcpy((byte*) key,(byte*) from,(size_t) length);
     *page_pos= from+length;
   }

--- 1.72/mysql-test/r/myisam.result	2007-03-16 10:28:51 +01:00
+++ 1.73/mysql-test/r/myisam.result	2007-03-16 10:28:51 +01:00
@@ -944,4 +944,154 @@ 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	X	X	X	72057594037927935	X	X	X	X	X	X	latin1_swedish_ci	X	max_rows=4100100100
avg_row_length=70100	
 DROP TABLE t1;
+CREATE TABLE t1 (c1 TEXT NOT NULL, KEY c1 (c1(10))) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+(''), (''), (''), (''),
+(' B'), (' B'), (' B'), (' B');
+SELECT DISTINCT COUNT(*) FROM t1 WHERE c1 = '';
+COUNT(*)
+4
+SELECT DISTINCT length(c1), c1 FROM t1 WHERE c1 = '';
+length(c1)	c1
+0	
+SELECT DISTINCT COUNT(*) FROM t1 IGNORE INDEX (c1) WHERE c1 = '';
+COUNT(*)
+4
+SELECT DISTINCT length(c1), c1 FROM t1 IGNORE INDEX (c1) WHERE c1 = '';
+length(c1)	c1
+0	
+SELECT DISTINCT length(c1), c1 FROM t1 ORDER BY c1;
+length(c1)	c1
+0	
+2		A
+2	 B
+DROP TABLE t1;
 End of 4.1 tests

--- 1.58/mysql-test/t/myisam.test	2007-03-16 10:28:51 +01:00
+++ 1.59/mysql-test/t/myisam.test	2007-03-16 10:28:51 +01:00
@@ -882,4 +882,148 @@ CREATE TABLE t1 (c1 TEXT) AVG_ROW_LENGTH
 SHOW TABLE STATUS LIKE 't1';
 DROP TABLE t1;
 
+#
+# Bug#26231 - select count(*) on myisam table returns wrong value
+#             when index is used
+#
+CREATE TABLE t1 (c1 TEXT NOT NULL, KEY c1 (c1(10))) ENGINE=MyISAM;
+# Fill at least two key blocks. "Tab, A" must be in both blocks. 
+INSERT INTO t1 VALUES
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)),
+  (''), (''), (''), (''),
+  (' B'), (' B'), (' B'), (' B');
+SELECT DISTINCT COUNT(*) FROM t1 WHERE c1 = '';
+SELECT DISTINCT length(c1), c1 FROM t1 WHERE c1 = '';
+SELECT DISTINCT COUNT(*) FROM t1 IGNORE INDEX (c1) WHERE c1 = '';
+SELECT DISTINCT length(c1), c1 FROM t1 IGNORE INDEX (c1) WHERE c1 = '';
+SELECT DISTINCT length(c1), c1 FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
 --echo End of 4.1 tests
Thread
• bk commit into 4.1 tree (istruewing:1.2608) BUG#26231	ingo	16 Mar