#
# Bug #30584: delete with order by and limit clauses does not use
#             limit efficiently
#
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25);
CREATE TABLE t2(a INT, i INT PRIMARY KEY);
INSERT INTO t2 (i) SELECT i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
a	i
NULL	11
NULL	12
NULL	13
NULL	14
NULL	15
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	1
Handler_read_last	0
Handler_read_next	4
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	1
Handler_read_last	0
Handler_read_next	4
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i;
a	i
NULL	16
NULL	17
NULL	18
DROP TABLE t2;
#
# index on field prefix:
#
CREATE TABLE t2(a INT, i CHAR(2), INDEX(i(1)));
INSERT INTO t2 (i) SELECT i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
a	i
NULL	11
NULL	12
NULL	13
NULL	14
NULL	15
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	5
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
FLUSH STATUS;
DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	8
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	5
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i;
a	i
NULL	16
NULL	17
NULL	18
DROP TABLE t2;
#
# constant inside ORDER BY list, should use filesort
# on a small table
#
CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
a	b	c	d
10	10	10	NULL
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	1
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
FLUSH STATUS;
DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	1
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	1
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
## should be 5 (previous LIMIT)
SELECT 1 - COUNT(*) FROM t2 WHERE b = 10;
1 - COUNT(*)
1
DROP TABLE t2;
#
# same test as above (constant inside ORDER BY list), but with
# a larger table - should not use filesort
#
CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2;
FLUSH STATUS;
SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
a	b	c	d
10	10	10	NULL
10	10	10	NULL
10	10	10	NULL
10	10	10	NULL
10	10	10	NULL
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	1
Handler_read_key	0
Handler_read_last	0
Handler_read_next	4
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	1
Handler_read_key	0
Handler_read_last	0
Handler_read_next	4
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
## should be 5 (previous LIMIT)
SELECT 257 - COUNT(*) FROM t2 WHERE b = 10;
257 - COUNT(*)
5
DROP TABLE t2;
#
# as above + partial index, should use filesort
#
CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c));
INSERT INTO t2 SELECT i, i, i, i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
a	b	c	d
10	10	10	10
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	1
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
FLUSH STATUS;
DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	1
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	1
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
SELECT * FROM t2 WHERE b = 10 ORDER BY a, c;
a	b	c	d
DROP TABLE t2;
#
# as above but index is without HA_READ_ORDER flag, should use filesort
#
CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c)) ENGINE=HEAP;
INSERT INTO t2 SELECT i, i, i, i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
a	b	c	d
10	10	10	10
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	1
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	1
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
FLUSH STATUS;
DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	1
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	1
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
SELECT * FROM t2 WHERE b = 10 ORDER BY a, c;
a	b	c	d
DROP TABLE t2;
#
# quick select is Index Merge, should use filesort
#
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
i	key1	key2
NULL	10	10
NULL	11	11
NULL	12	12
NULL	13	13
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	1
Sort_rows	4
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	2
Handler_read_last	0
Handler_read_next	7
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	4
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
x	x	x	x	x	x	x	x	x	x	Using sort_union(key1,key2); Using where; Using filesort
Warnings:
x	x	x
FLUSH STATUS;
DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	1
Sort_rows	4
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	2
Handler_read_last	0
Handler_read_next	7
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	8
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
i	key1	key2
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
x	x	x	x	x	x	x	x	x	x	Using sort_union(key1,key2); Using where; Using filesort
Warnings:
x	x	x
DROP TABLE t2;
#
# reverse quick select, should not use filesort
#
CREATE TABLE t2(a INT, i INT PRIMARY KEY);
INSERT INTO t2 (i) SELECT i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5;
a	i
NULL	18
NULL	17
NULL	16
NULL	15
NULL	14
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	1
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	4
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	1
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	4
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i;
a	i
NULL	11
NULL	12
NULL	13
DROP TABLE t2;
#
# mixed sorting direction, should use filesort
#
CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b));
INSERT INTO t2 SELECT i, i, i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5;
a	b	c
10	10	10
11	11	11
12	12	12
13	13	13
14	14	14
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	5
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
FLUSH STATUS;
DELETE FROM t2 ORDER BY a, b DESC LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	16
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	5
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
SELECT * FROM t2 ORDER BY a, b DESC;
a	b	c
15	15	15
16	16	16
17	17	17
18	18	18
19	19	19
20	20	20
21	21	21
22	22	22
23	23	23
24	24	24
25	25	25
DROP TABLE t2;
#
# LIMIT with no WHERE and DESC direction, should not use filesort
#
CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b));
INSERT INTO t2 (a, b) SELECT i, i FROM t1;
INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2;
FLUSH STATUS;
SELECT * FROM t2 ORDER BY a, b LIMIT 5;
a	b	c
10	10	NULL
10	10	NULL
10	10	NULL
10	10	NULL
10	10	NULL
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	1
Handler_read_key	0
Handler_read_last	0
Handler_read_next	4
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5;
a	b	c
25	25	NULL
25	25	NULL
25	25	NULL
25	25	NULL
25	25	NULL
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
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	4
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
DELETE FROM t2 ORDER BY a DESC, b DESC LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
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	4
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
SELECT * FROM t2 WHERE c = 10 ORDER BY a DESC, b DESC;
a	b	c
DROP TABLE t1, t2;
#
# Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort
#             even if not required
#
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25);
CREATE TABLE t2(a INT, i INT PRIMARY KEY);
INSERT INTO t2 (i) SELECT i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
a	i
NULL	11
NULL	12
NULL	13
NULL	14
NULL	15
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	1
Handler_read_last	0
Handler_read_next	4
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	1
Handler_read_last	0
Handler_read_next	4
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	5
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i;
a	i
10	11
10	12
10	13
10	14
10	15
NULL	16
NULL	17
NULL	18
DROP TABLE t2;
#
# index on field prefix:
#
CREATE TABLE t2(a INT, i CHAR(2), INDEX(i(1)));
INSERT INTO t2 (i) SELECT i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
a	i
NULL	11
NULL	12
NULL	13
NULL	14
NULL	15
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	5
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
FLUSH STATUS;
UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	5
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	5
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i;
a	i
10	11
10	12
10	13
10	14
10	15
NULL	16
NULL	17
NULL	18
DROP TABLE t2;
#
# constant inside ORDER BY list, should use filesort
# on a small table
#
CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
a	b	c	d
10	10	10	NULL
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	1
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
FLUSH STATUS;
UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	1
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	1
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
## should be 5 (previous LIMIT)
SELECT COUNT(*) FROM t2 WHERE b = 10 AND d = 10 ORDER BY a, c;
COUNT(*)
1
DROP TABLE t2;
#
# same test as above (constant inside ORDER BY list), but with
# a larger table - should not use filesort
#
CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2;
FLUSH STATUS;
SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
a	b	c	d
10	10	10	NULL
10	10	10	NULL
10	10	10	NULL
10	10	10	NULL
10	10	10	NULL
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	1
Handler_read_key	0
Handler_read_last	0
Handler_read_next	4
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	1
Handler_read_key	0
Handler_read_last	0
Handler_read_next	4
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	5
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
## should be 5 (previous LIMIT)
SELECT COUNT(*) FROM t2 WHERE b = 10 AND d = 10 ORDER BY a, c;
COUNT(*)
5
DROP TABLE t2;
#
# as above + partial index, should use filesort
#
CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c));
INSERT INTO t2 SELECT i, i, i, i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
a	b	c	d
10	10	10	10
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	1
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
FLUSH STATUS;
UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	1
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	1
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
SELECT * FROM t2 WHERE b = 10 ORDER BY a, c;
a	b	c	d
10	10	10	10
DROP TABLE t2;
#
# as above but index is without HA_READ_ORDER flag, should use filesort
#
CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c)) ENGINE=HEAP;
INSERT INTO t2 SELECT i, i, i, i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
a	b	c	d
10	10	10	10
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	1
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	1
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
FLUSH STATUS;
UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	1
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	1
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
SELECT * FROM t2 WHERE b = 10 ORDER BY a, c;
a	b	c	d
10	10	10	10
DROP TABLE t2;
#
# quick select is Index Merge, should use filesort
#
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
i	key1	key2
NULL	10	10
NULL	11	11
NULL	12	12
NULL	13	13
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	1
Sort_rows	4
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	2
Handler_read_last	0
Handler_read_next	7
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	4
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
x	x	x	x	x	x	x	x	x	x	Using sort_union(key1,key2); Using where; Using filesort
Warnings:
x	x	x
FLUSH STATUS;
UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	1
Sort_rows	4
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	2
Handler_read_last	0
Handler_read_next	7
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	8
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
i	key1	key2
123	10	10
123	11	11
123	12	12
123	13	13
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
x	x	x	x	x	x	x	x	x	x	Using sort_union(key1,key2); Using where; Using filesort
Warnings:
x	x	x
DROP TABLE t2;
#
# reverse quick select, should not use filesort
#
CREATE TABLE t2(a INT, i INT PRIMARY KEY);
INSERT INTO t2 (i) SELECT i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5;
a	i
NULL	18
NULL	17
NULL	16
NULL	15
NULL	14
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	1
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	4
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	1
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	4
Handler_read_retry	0
Handler_read_rnd	5
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i;
a	i
NULL	11
NULL	12
NULL	13
10	14
10	15
10	16
10	17
10	18
DROP TABLE t2;
#
# mixed sorting direction, should use filesort
#
CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b));
INSERT INTO t2 SELECT i, i, i FROM t1;
FLUSH STATUS;
SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5;
a	b	c
10	10	10
11	11	11
12	12	12
13	13	13
14	14	14
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	5
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
FLUSH STATUS;
UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	5
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	5
Handler_read_rnd_deleted	0
Handler_read_rnd_next	17
SELECT * FROM t2 WHERE c = 10 ORDER BY a, b DESC;
a	b	c
10	10	10
11	11	10
12	12	10
13	13	10
14	14	10
DROP TABLE t2;
#
# LIMIT with no WHERE and DESC direction, should not use filesort
#
CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b));
INSERT INTO t2 (a, b) SELECT i, i FROM t1;
INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2;
FLUSH STATUS;
SELECT * FROM t2 ORDER BY a, b LIMIT 5;
a	b	c
10	10	NULL
10	10	NULL
10	10	NULL
10	10	NULL
10	10	NULL
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
SHOW STATUS LIKE 'Handler_read_%';
Variable_name	Value
Handler_read_first	1
Handler_read_key	0
Handler_read_last	0
Handler_read_next	4
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5;
a	b	c
25	25	NULL
25	25	NULL
25	25	NULL
25	25	NULL
25	25	NULL
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
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	4
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
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	4
Handler_read_retry	0
Handler_read_rnd	5
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
SELECT * FROM t2 WHERE c = 10 ORDER BY a DESC, b DESC;
a	b	c
25	25	10
25	25	10
25	25	10
25	25	10
25	25	10
DROP TABLE t1, t2;