MySQL Bugs: #37235: Incorrect count in MyISAM partitioned table when using index

Bug #37235	Incorrect count in MyISAM partitioned table when using index
Submitted:	5 Jun 2008 18:16	Modified:	23 Jun 2008 10:23
Reporter:	Manuel Duran Aguete	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.1.24-rc	OS:	Linux (Ubuntu LTS 8.04 server, Mysql binaries)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
A reply of mysql session showing the problem

mysql:sms> select count(*) from enviados_operador;                 
+----------+
| count(*) |
+----------+
|  6186914 | 
+----------+
1 row in set (0.00 sec)

mysql:sms> select count(*) from enviados_operador where id_recibido = 0;
+----------+
| count(*) |
+----------+
|  6134608 | 
+----------+
1 row in set (5.35 sec)

mysql:sms> select count(*) from enviados_operador where id_recibido <> 0;
+----------+
| count(*) |
+----------+
|    52306 | 
+----------+
1 row in set (5.28 sec)

mysql:sms> CREATE INDEX eo_idrecibido_idx on enviados_operador(id_recibido);      
Query OK, 6186914 rows affected (1 min 11.45 sec)
Registros: 6186914  Duplicados: 0  Peligros: 0

mysql:sms> select count(*) from enviados_operador;                 
+----------+
| count(*) |
+----------+
|  6186914 | 
+----------+
1 row in set (0.00 sec)

mysql:sms> select count(*) from enviados_operador where id_recibido = 0;
+----------+
| count(*) |
+----------+
|  6134608 | 
+----------+
1 row in set (14.34 sec)

mysql:sms> select count(*) from enviados_operador where id_recibido <> 0;
+----------+
| count(*) |
+----------+
|   104612 | 
+----------+
1 row in set (3.24 sec)

Well, if i've want to copy all records with id_recibido <> 0
 create table temporal as select * from enviados_operador where id_recibido <> 0;
Query OK, 104612 rows affected (22.23 sec)
Registros: 104612  Duplicados: 0  Peligros: 0

mysql:sms> select count(*) from temporal;
+----------+
| count(*) |
+----------+
|   104612 | 
+----------+
1 row in set (0.11 sec)

Again testing droping the index.

mysql:sms>  create table temporal2  as select * from enviados_operador where id_recibido <> 0;
Query OK, 52306 rows affected (6.47 sec)
Registros: 52306  Duplicados: 0  Peligros: 0

mysql:sms> select count(*) from temporal2;
+----------+
| count(*) |
+----------+
|    52306 | 
+----------+
1 row in set (0.05 sec)

It works without the index.

I'm going to test without in a table not partitioned.
First with index created.
mysql> select count(*) from enviados_operador; 
+----------+
| count(*) |
+----------+
|  6186914 | 
+----------+
1 row in set (0.04 sec)

mysql> select count(*) from enviados_operador where id_recibido = 0;
+----------+
| count(*) |
+----------+
|  6134608 | 
+----------+
1 row in set (5.30 sec)

mysql> select count(*) from enviados_operador where id_recibido <> 0;
+----------+
| count(*) |
+----------+
|    52306 | 
+----------+
1 row in set (0.08 sec)

Now copy data to another table,

mysql> create table temporal as select * from enviados_operador where id_recibido <> 0;
Query OK, 52306 rows affected (8.13 sec)
Records: 52306  Duplicates: 0  Warnings: 0

Work's... .:)

How to repeat:
Tryin to reply the error with a few rows not works.

Suggested fix:
Should return the same result, with and without the index and with/without partitioned table.

Thank you for the report.

Please provide output of SHOW CREATE TABLE and dump of table enviados_operador.

Hello,

The same error in UBUNTU LTS 8.04 desktop with same binary.

Uploaded  bug-data-37235.zip to ftp://ftp.mysql.com/pub/mysql/upload/

The show create table statement 

CREATE TABLE `enviados_operador` (
  `id` int(11) NOT NULL,
  `n_destino` varchar(20) COLLATE utf8_spanish_ci DEFAULT NULL,
  `n_origen` varchar(20) COLLATE utf8_spanish_ci DEFAULT NULL,
  `fecha` datetime DEFAULT NULL,
  `operador` varchar(20) COLLATE utf8_spanish_ci DEFAULT NULL,
  `operador_real` varchar(20) COLLATE utf8_spanish_ci DEFAULT NULL,
  `id_recibido` int(11) DEFAULT NULL,
  `contenido` varchar(1024) COLLATE utf8_spanish_ci DEFAULT NULL,
  `tipo_contenido` varchar(10) COLLATE utf8_spanish_ci DEFAULT NULL,
  `n_partido` int(11) DEFAULT NULL,
  `estado` varchar(20) COLLATE utf8_spanish_ci DEFAULT NULL,
  `smsid` int(11) DEFAULT NULL,
  `descripcion_error` varchar(30) COLLATE utf8_spanish_ci DEFAULT NULL,
  `fecha_i` date NOT NULL,
  `hora_i` time NOT NULL,
  PRIMARY KEY (`id`,`fecha_i`) USING BTREE,
  KEY `eo_idrecibido_idx` (`id_recibido`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci /*!50100 PARTITION BY RANGE (TO_DAYS(fecha_i)) (PARTITION p_eo_0801 VALUES LESS THAN (733438) ENGINE = MyISAM, PARTITION p_eo_0802 VALUES LESS THAN (733467) ENGINE = MyISAM, PARTITION p_eo_0803 VALUES LESS THAN (733498) ENGINE = MyISAM, PARTITION p_eo_0804 VALUES LESS THAN (733528) ENGINE = MyISAM, PARTITION p_eo_0805 VALUES LESS THAN (733559) ENGINE = MyISAM, PARTITION p_eo_0806 VALUES LESS THAN (733589) ENGINE = MyISAM, PARTITION p_eo_0807 VALUES LESS THAN (733620) ENGINE = MyISAM, PARTITION p_eo_0808 VALUES LESS THAN (733651) ENGINE = MyISAM, PARTITION p_eo_0809 VALUES LESS THAN (733681) ENGINE = MyISAM, PARTITION p_eo_0810 VALUES LESS THAN (733712) ENGINE = MyISAM, PARTITION p_eo_0811 VALUES LESS THAN (733742) ENGINE = MyISAM, PARTITION p_eo_0812 VALUES LESS THAN (733773) ENGINE = MyISAM) */

Thank you for the report.

Verified as described. Can be duplicate of bug #37219 and bug #35745

Duplicate of bug#35931, verified that the fix for that bug also fixes this.