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)
• Status: Closed
• 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