Bug #14980 COUNT(*) incorrect on MyISAM table with certain INDEX
Submitted: 16 Nov 2005 12:42 Modified: 14 Mar 2006 18:17
Reporter: Felix Geerinckx (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.0.15/5.0.17 BK/4.1 BK OS:Windows (Windows/Linux)
Assigned to: Ingo Strüwing CPU Architecture:Any

[16 Nov 2005 12:42] Felix Geerinckx
Description:
SELECT COUNT(*)   FROM foo WHERE id2 = 10; -- incorrect
SELECT COUNT(id1) FROM foo WHERE id2 = 10; -- correct

return different results when a certain index is used on MyISAM table, with the COUNT(*) being wrong.

Bug discovered by "larry" 
See http://forums.mysql.com/read.php?20,54669,54669#msg-54669

How to repeat:
USE test;
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
  id1 int NOT NULL auto_increment,
  id2 int NOT NULL default '0',
  t text NOT NULL,
  PRIMARY KEY  (id1),
  KEY x (id2, t(32))
) ENGINE=MyISAM;

INSERT INTO foo (id2, t) VALUES
(10, 'abc'), (10, 'abc'), (10, 'abc'),
(20, 'abc'), (20, 'abc'), (20, 'def'),
(10, 'abc'), (10, 'abc');

SELECT * FROM foo WHERE id2 = 10; -- 5 records

SELECT COUNT(*)   FROM foo WHERE id2 = 10; -- 2 = NOK
SELECT COUNT(id1) FROM foo WHERE id2 = 10; -- 5 = OK

-- don't use index x
SELECT COUNT(*)   FROM foo IGNORE INDEX (x) WHERE id2 = 10; -- 5 = OK
SELECT COUNT(id1) FROM foo IGNORE INDEX (x) WHERE id2 = 10; -- 5 = OK

-- Try with InnoDB

ALTER TABLE foo ENGINE = InnoDB;

SELECT COUNT(*)   FROM foo WHERE id2 = 10; -- 5 = OK
SELECT COUNT(id1) FROM foo WHERE id2 = 10; -- 5 = OK
[16 Nov 2005 13:21] MySQL Verification Team
Thank you for the bug report.

mysql> SELECT COUNT(*)   FROM foo WHERE id2 = 10;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT COUNT(*)   FROM foo IGNORE INDEX (x) WHERE id2 = 10;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set (0.01 sec)
[16 Nov 2005 13:28] MySQL Verification Team
mysql> SELECT COUNT(*)   FROM foo WHERE id2 = 10;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.16-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*)   FROM foo WHERE id2 = 10;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.0.26-debug-log |
+------------------+
1 row in set (0.01 sec)
[1 Mar 2006 15:55] 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/3337
[10 Mar 2006 14:06] 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/3705
[11 Mar 2006 7:54] Ingo Strüwing
Pushed to 4.1.19, 5.0.20, and 5.1.8.
[14 Mar 2006 18:17] Paul DuBois
Noted in 4.1.19, 5.0.20, 5.1.8 changelogs.

<literal>SELECT COUNT(*)</literal> for a
<literal>MyISAM</literal> table could return different results
depending on whether an index was used. (Bug #14980)
[9 Feb 2007 20:18] MySQL Verification Team
i've opened a similar bug, http://bugs.mysql.com/bug.php?id=26231