MySQL Bugs: #26231: select count(*) on myisam table returns wrong value when index is used

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

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.

doesn't affect 5.0.36

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.

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.

Queued to 5.1-engines, 5.0-engines, 4.1-engines.

Pushed into 5.1.18-beta

Pushed into 5.0.40

Pushed to 4.1.23

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.