1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
--source include/have_innodb_16k.inc
let $engine=innodb;
--source include/group_min_max_ext_data.inc
--source include/group_min_max_ext_test.inc
# Test for table having an index with mixed keyparts(asc/desc)
ALTER TABLE t ADD KEY k2(a DESC, b, c DESC, d);
ANALYZE TABLE t;
--sorted_result
SELECT a, b, MIN(d) FROM t
WHERE (a > 4) AND (c = 3 or c = 40) AND (d > 7) GROUP BY a, b;
--sorted_result
SELECT a, b, MIN(d) FROM t
WHERE (a > 4) AND (c = 3 or c = 40) AND (d > 7) GROUP BY a, b;
ALTER TABLE t DROP KEY k2;
# Tests below shouldn't use LIS due to non-equality prefix
# ranges.
EXPLAIN SELECT a, b FROM t WHERE (a > 4) AND (c = 3 OR c > 6) GROUP BY a, b;
EXPLAIN SELECT a, b FROM t WHERE (a > 4) AND (c = 3 OR c = 40) AND
(d = -1 OR d = -2 OR d > 7) GROUP BY a, b;
# Test if result is correct if only min_max key part is descending.
ALTER TABLE t DROP KEY k1;
ALTER TABLE t ADD KEY k1(a, b, c, d DESC);
ANALYZE TABLE t;
EXPLAIN SELECT a, b, MIN(d) FROM t WHERE (a > 9) AND (c = 3) GROUP BY a, b;
SELECT a, b, MIN(d) FROM t WHERE (a > 9) AND (c = 3) GROUP BY a, b;
EXPLAIN SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3) GROUP BY a, b;
SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3) GROUP BY a, b;
ALTER TABLE t DROP KEY k1;
ALTER TABLE t ADD KEY k1(a DESC, b, c DESC, d);
ANALYZE TABLE t;
--source include/group_min_max_ext_test.inc
ALTER TABLE t DROP KEY k1;
ALTER TABLE t ADD KEY k1(a, b DESC, c, d DESC);
ANALYZE TABLE t;
--source include/group_min_max_ext_test.inc
DROP TABLE t;
let $engine=myisam;
--source include/group_min_max_ext_data.inc
--source include/group_min_max_ext_test.inc
DROP TABLE t;
|
