Bug #26231 select count(*) on myisam table returns wrong value when index is used
Submitted: 9 Feb 2007 17:26 Modified: 4 Apr 2007 18:05
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:4.1.22,4.1BK OS:Any (*)
Assigned to: Ingo Strüwing CPU Architecture:Any
Tags: bfsm_2007_02_15

[9 Feb 2007 17:26] Shane Bester
Description:
When an index is used, wrong data is returned.  See the queries:

mysql> select count(*) from t1 WHERE c1 = '';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from t1 IGNORE INDEX (c1) WHERE c1 = '';
+----------+
| count(*) |
+----------+
| 4843 |
+----------+
1 row in set (1.54 sec)

check table and repair table come back all OK so it's not a corruption problem.
Looks like #14980 but that's fixed, so I'm opening new report now.

How to repeat:
The datafiles are uploaded to our ftp site. See private comment for path.

Suggested fix:
return correct result.
[9 Feb 2007 19:39] MySQL Verification Team
doesn't affect 5.0.36
[9 Feb 2007 20:14] MySQL Verification Team
tested on todays 4.1.23 bk, bug exists there.
dump/reload table has no affect.
innodb doesn't have this bug. seems to be myisam only.
[16 Mar 2007 9:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22104

ChangeSet@1.2608, 2007-03-16 10:28:48+01:00, istruewing@chilla.local +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.
[30 Mar 2007 9:07] Ingo Strüwing
Queued to 5.1-engines, 5.0-engines, 4.1-engines.
[30 Mar 2007 17:28] Bugs System
Pushed into 5.1.18-beta
[30 Mar 2007 17:30] Bugs System
Pushed into 5.0.40
[30 Mar 2007 18:03] Ingo Strüwing
Pushed to 4.1.23
[4 Apr 2007 18:05] Paul DuBois
Noted in 4.1.23, 5.0.40, 5.1.18 changelogs.

For MyISAM tables, COUNT(*) could return an incorrect value if the
WHERE clause compared an indexed TEXT column to the empty string
(''). This happened if the column contained empty strings and also
strings starting with control characters such as tab or newline.