MySQL Bugs: #43293: Handler_read_next counter not updated correctly for Falcon tables

Bug #43293	Handler_read_next counter not updated correctly for Falcon tables
Submitted:	1 Mar 2009 17:44	Modified:	26 May 2010 17:52
Reporter:	Sergey Petrunya	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0 bzr	OS:	Any
Assigned to:	Lars-Erik BjÃ¸rk	CPU Architecture:	Any
Tags:	F_HANDLER

Description:
Handler_read_next counter is not updated correctly for Falcon tables - for equality reads it is incremented twice.

How to repeat:
create table t10(a int);
insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t11(a int, filler char(200), key(a)) engine=falcon;
insert into t11 select A.a+10*B.a, 'booooo' from t10 A, t10 B, t10 C;
explain select * from t11 where a=10;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | t11   | ref  | a             | a    | 5       | const |  100 |       | 
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t11 where a=10;
+------+--------+
| a    | filler |
+------+--------+
|   10 | booooo | 
|   10 | booooo | 
|   10 | booooo | 
|   10 | booooo | 
|   10 | booooo | 
|   10 | booooo | 
|   10 | booooo | 
|   10 | booooo | 
|   10 | booooo | 
|   10 | booooo | 
+------+--------+
10 rows in set (0.01 sec)

mysql> show status like 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     | 
| Handler_delete             | 0     | 
| Handler_discover           | 0     | 
| Handler_mrr_init           | 0     | 
| Handler_prepare            | 0     | 
| Handler_read_first         | 0     | 
| Handler_read_key           | 1     | 
| Handler_read_next          | 21    | 
| Handler_read_prev          | 0     | 
| Handler_read_rnd           | 0     | 
| Handler_read_rnd_next      | 0     | 
| Handler_rollback           | 0     | 
| Handler_savepoint          | 0     | 
| Handler_savepoint_rollback | 0     | 
| Handler_update             | 0     | 
| Handler_write              | 0     | 
+----------------------------+-------+
16 rows in set (0.01 sec)

Now, where is the "21" number coming from? It is because StorageInterface::index_next_same() calls StorageInterface::index_next() and both of them increment the counter.

Suggested fix:
Increment the counter twice. Make sure it is also incremented correctly when MRR is used.

Thank you for the report.

Verified as described.

Please re-triage this bug.  We are not reporting the correct number of Handler_read_next calls made into the engine.  I do not know why this is a beta issue.

Clarifying as requested: 

This bug is about incorrect handler values being displayed by the user. Handler counter values do not affect anything in the server itself. Incorrect counter values will lead to:

* Big inconvenience for those who will make attempts to do performance analysis or optimization troubleshooting.

* IIRC some piece of software in MySQL Enterprise analyses handler counters and makes some conclusions. These conclusions might be incorrect as well.