# A regression bug?

**URL:** <https://forums.percona.com/t/a-regression-bug/1525>\
**Category:** Other MySQL® Questions\
**Created:** [October 7, 2010, 5:55am UTC](https://forums.percona.com/t/a-regression-bug/1525 "2010-10-07T05:55:50Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![systemsvlex](https://avatars.discourse-cdn.com/v4/letter/s/ac91a4/32.png) [@systemsvlex](https://forums.percona.com/u/systemsvlex)\
**Post date:** [October 7, 2010, 5:55am UTC](https://forums.percona.com/t/a-regression-bug/1525/1 "2010-10-07T05:55:50Z")

</div>

Hi.

One of our mysql servers:  
Server version: 5.1.50-rel11.4-log (Percona Server (GPL), 11.4 , Revision 111)  
64 bits  
OS: GNU/Linux Ubuntu 10.04 Lucid

All tables in our databases are InnoDB.

If we execute this query:

select count(_) from ordenes\_compra where b\_updated=‘S’ and estado=‘O’;  
±---------+  
| count(_) |  
±---------+  
| 0 |  
±---------+

mmm… it isn’t correct!! We’ll try another time:

mysql\> select count(id\_orden) from ordenes\_compra where b\_updated=‘S’ and estado=‘O’;  
±----------------+  
| count(id\_orden) |  
±----------------+  
| 1682 |  
±----------------+

Here it is the EXPLAINs for the querys:

mysql\> explain select count(id\_orden) from ordenes\_compra where b\_updated=‘S’ and estado=‘O’;  
±—±------------±---------------±-----±--------------- -------------±---------------±--------±------±-----±— ---------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±---------------±-----±--------------- -------------±---------------±--------±------±-----±— ---------+  
| 1 | SIMPLE | ordenes\_compra | ref | sys\_c0010069,orden\_modified | orden\_modified | 6 | const | 4928 | Using where |  
±—±------------±---------------±-----±--------------- -------------±---------------±--------±------±-----±— ---------+

mysql\> explain select count(\*) from ordenes\_compra where b\_updated=‘S’ and estado=‘O’;  
±—±------------±---------------±------------±-------- --------------------±----------------------------±-------- ±-----±-----±-------------------------------------------- ---------------------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±---------------±------------±-------- --------------------±----------------------------±-------- ±-----±-----±-------------------------------------------- ---------------------------+  
| 1 | SIMPLE | ordenes\_compra | index\_merge | sys\_c0010069,orden\_modified | orden\_modified,sys\_c0010069 | 6,12 | NULL | 2404 | Using intersect(orden\_modified,sys\_c0010069); Using where; Using index |  
±—±------------±---------------±------------±-------- --------------------±----------------------------±-------- ±-----±-----±-------------------------------------------- ---------------------------+

But if we apply the IGNORE INDEX clause the results are fine:

mysql\> select count(_) from ordenes\_compra IGNORE INDEX(sys\_c0010069) where b\_updated=‘S’ and estado=‘O’;  
±---------+  
| count(_) |  
±---------+  
| 1667 |  
±---------+

There is a bug 14980 in mysql database ([URL][MySQL Bugs: #14980: COUNT(\*) incorrect on MyISAM table with certain INDEX](http://bugs.mysql.com/bug.php?id=14980%5B/URL%5D)) for this, and another numbered 26331 for the same ([URL][MySQL Bugs: #26231: select count(\*) on myisam table returns wrong value when index is used](http://bugs.mysql.com/bug.php?id=26231%5B/URL%5D)).

We are very surprised because these are bugs from three or four years ago.

Please, could you help us with this problem?

Thank you very much.

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [October 7, 2010, 8:36am UTC](https://forums.percona.com/t/a-regression-bug/1525/2 "2010-10-07T08:36:18Z")

</div>

The bugs you linked to are for MyISAM. The bug that is probably biting you is something in the fast index creation in InnoDB. There are open bugs on this functionality. I suggest that you use OPTIMIZE TABLE, if possible, to rebuild the entire table and see if that fixes the problem.

---

<div class="post-metadata">

**Author:** ![systemsvlex](https://avatars.discourse-cdn.com/v4/letter/s/ac91a4/32.png) [@systemsvlex](https://forums.percona.com/u/systemsvlex)\
**Post date:** [October 7, 2010, 8:40am UTC](https://forums.percona.com/t/a-regression-bug/1525/3 "2010-10-07T08:40:05Z")

</div>

| [B]xaprb wrote on Thu, 07 October 2010 16:06[/B] |
| The bugs you linked to are for MyISAM. The bug that is probably biting you is something in the fast index creation in InnoDB. There are open bugs on this functionality. I suggest that you use OPTIMIZE TABLE, if possible, to rebuild the entire table and see if that fixes the problem. |

Ok, I will try to OPTIMIZE this table. I try to maintain you informated about this.

Thank you!!

---

<div class="post-metadata">

**Author:** ![systemsvlex](https://avatars.discourse-cdn.com/v4/letter/s/ac91a4/32.png) [@systemsvlex](https://forums.percona.com/u/systemsvlex)\
**Post date:** [October 7, 2010, 9:00am UTC](https://forums.percona.com/t/a-regression-bug/1525/4 "2010-10-07T09:00:33Z")

</div>

Ok, here it is the results:

mysql\> set sql\_log\_bin=0;  
Query OK, 0 rows affected (0.00 sec)

mysql\> select count(_) from ordenes\_compra where b\_updated=‘S’ and estado=‘O’;  
±---------+  
| count(_) |  
±---------+  
| 0 |  
±---------+  
1 row in set (0.93 sec)

mysql\> optimize table ordenes\_compra;  
±---------------------------±---------±---------±------- -----------------------------------------------------------+  
| Table | Op | Msg\_type | Msg\_text |  
±---------------------------±---------±---------±------- -----------------------------------------------------------+  
| accounts\_db.ordenes\_compra | optimize | note | Table does not support optimize, doing recreate + analyze instead |  
| accounts\_db.ordenes\_compra | optimize | status | OK |  
±---------------------------±---------±---------±------- -----------------------------------------------------------+  
2 rows in set (10.07 sec)

mysql\> select count(_) from ordenes\_compra where b\_updated=‘S’ and estado=‘O’;  
±---------+  
| count(_) |  
±---------+  
| 0 |  
±---------+  
1 row in set (0.05 sec)

I think this doesn’t work as we expected…

Regards.

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [October 7, 2010, 10:12am UTC](https://forums.percona.com/t/a-regression-bug/1525/5 "2010-10-07T10:12:59Z")

</div>

Looks like a bug still. I’d file this bug with MySQL, or hire someone such as Percona to help you with it.