Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.0, 5.5.29, 5.3.12
-
None
Description
Test case:
--source include/have_innodb.inc
|
|
|
CREATE TABLE t (
|
id int not null auto_increment,
|
x int not null,
|
primary key(id)
|
)engine=innodb;
|
|
|
insert into t (x) values(0),(0),(0);
|
insert into t (x) select 0 from t as t1,t as t2;
|
insert into t (x) select 0 from t as t1,t as t2;
|
insert into t (x) select 0 from t as t1,t as t2;
|
|
|
SELECT (SELECT MAX(id) - 1000 FROM t) INTO @a;
|
FLUSH STATUS;
|
SELECT x FROM t WHERE id > @a ORDER BY x LIMIT 1;
|
SHOW STATUS LIKE 'handler_read%';
|
FLUSH STATUS;
|
SELECT x FROM t WHERE id > (SELECT MAX(id) - 1000 FROM t) ORDER BY x LIMIT 1;
|
SHOW STATUS LIKE 'handler_read%';
|
DROP TABLE t;
|
output on mariadb (tested 5.3.5, 5.5.28, 5.5.29 and 10.0.0):
mysql [localhost] {msandbox} (test) > SELECT x FROM t WHERE id > @a ORDER BY x LIMIT 1;
|
+---+
|
| x |
|
+---+
|
| 0 |
|
+---+
|
1 row in set (0.00 sec)
|
|
|
mysql [localhost] {msandbox} (test) > SHOW STATUS LIKE 'handler_read%';
|
+--------------------------+-------+
|
| Variable_name | Value |
|
+--------------------------+-------+
|
| Handler_read_first | 0 |
|
| Handler_read_key | 1 |
|
| Handler_read_last | 0 |
|
| Handler_read_next | 1000 |
|
| Handler_read_prev | 0 |
|
| Handler_read_rnd | 0 |
|
| Handler_read_rnd_deleted | 0 |
|
| Handler_read_rnd_next | 0 |
|
+--------------------------+-------+
|
8 rows in set (0.00 sec)
|
|
|
mysql [localhost] {msandbox} (test) > FLUSH STATUS;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql [localhost] {msandbox} (test) > SELECT x FROM t WHERE id > (SELECT MAX(id) - 1000 FROM t) ORDER BY x DESC LIMIT 1;
|
+---+
|
| x |
|
+-- +
|
| 0 |
|
+---+
|
1 row in set (0.00 sec)
|
|
|
mysql [localhost] {msandbox} (test) > SHOW STATUS LIKE 'handler_read%';
|
+--------------------------+-------+
|
| Variable_name | Value |
|
+--------------------------+-------+
|
| Handler_read_first | 0 |
|
| Handler_read_key | 0 |
|
| Handler_read_last | 1 |
|
| Handler_read_next | 0 |
|
| Handler_read_prev | 0 |
|
| Handler_read_rnd | 0 |
|
| Handler_read_rnd_deleted | 0 |
|
| Handler_read_rnd_next | 24493 |
|
+--------------------------+-------+
|
output on mysql (tested 5.0 and 5.1):
mysql> SELECT x FROM t WHERE id > @a ORDER BY x LIMIT 1;
|
+---+
|
| x |
|
+---+
|
| 0 |
|
+---+
|
1 row in set (0.00 sec)
|
|
|
mysql> SHOW STATUS LIKE 'handler_read%';
|
+-----------------------+-------+
|
| Variable_name | Value |
|
+-----------------------+-------+
|
| Handler_read_first | 0 |
|
| Handler_read_key | 3 |
|
| Handler_read_next | 1000 |
|
| Handler_read_prev | 0 |
|
| Handler_read_rnd | 0 |
|
| Handler_read_rnd_next | 0 |
|
+-----------------------+-------+
|
6 rows in set (0.00 sec)
|
|
|
mysql> FLUSH STATUS;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql> SELECT x FROM t WHERE id > (SELECT MAX(id) - 1000 FROM t) ORDER BY x LIMIT 1;
|
+---+
|
| x |
|
+---+
|
| 0 |
|
+---+
|
1 row in set (0.00 sec)
|
|
|
mysql> SHOW STATUS LIKE 'handler_read%';
|
+-----------------------+-------+
|
| Variable_name | Value |
|
+-----------------------+-------+
|
| Handler_read_first | 0 |
|
| Handler_read_key | 5 |
|
| Handler_read_next | 1000 |
|
| Handler_read_prev | 0 |
|
| Handler_read_rnd | 0 |
|
| Handler_read_rnd_next | 0 |
|
+-----------------------+-------+
|