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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
|
#
# group_min_max related tests that result in an different output when run
# under --ps.
#
#
--source include/have_ps_protocol.inc
#
# Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar statements
#
CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6),
(5,1), (5,2), (5,3), (5,4), (5,5);
--replace_column 10 X
EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
FLUSH STATUS;
SELECT max(b), a FROM t1 GROUP BY a;
--skip_if_hypergraph # Depends on the query plan.
SHOW STATUS LIKE 'handler_read__e%';
--replace_column 10 X
EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
FLUSH STATUS;
CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;
--skip_if_hypergraph # Depends on the query plan.
SHOW STATUS LIKE 'handler_read__e%';
FLUSH STATUS;
SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
--skip_if_hypergraph # Depends on the query plan.
SHOW STATUS LIKE 'handler_read__e%';
FLUSH STATUS;
(SELECT max(b), a FROM t1 GROUP BY a) UNION
(SELECT max(b), a FROM t1 GROUP BY a);
--skip_if_hypergraph # Depends on the query plan.
SHOW STATUS LIKE 'handler_read__e%';
--replace_column 10 X
EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION
(SELECT max(b), a FROM t1 GROUP BY a);
--echo # In queries below "index for group-by" should be used for subqueries
--replace_column 10 X
EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
FROM t1 AS t1_outer;
--replace_column 10 X
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
--replace_column 10 X
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
--replace_column 10 X
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
--replace_column 10 X
EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
--replace_column 10 X
EXPLAIN SELECT 1 FROM t1 AS t1_outer1 LEFT JOIN t1 AS t1_outer2
ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2)
AND t1_outer1.b = t1_outer2.b;
--replace_column 10 X
EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
CREATE TABLE t3 LIKE t1;
--echo # Ensure that t3 is loaded into data-dictionary cache.
--disable_query_log
--disable_result_log
SHOW CREATE TABLE t3;
--enable_result_log
--enable_query_log
FLUSH STATUS;
INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
--skip_if_hypergraph # Depends on the query plan.
SHOW STATUS LIKE 'handler_read__e%';
DELETE FROM t3;
FLUSH STATUS;
INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2)
FROM t1 LIMIT 1;
--skip_if_hypergraph # Depends on the query plan.
SHOW STATUS LIKE 'handler_read__e%';
FLUSH STATUS;
DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
--skip_if_hypergraph # Depends on the query plan.
SHOW STATUS LIKE 'handler_read__e%';
FLUSH STATUS;
--error ER_SUBQUERY_NO_1_ROW
DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x
FROM t1) > 10000;
--skip_if_hypergraph # Depends on the query plan.
SHOW STATUS LIKE 'handler_read__e%';
DROP TABLE t1,t2,t3;
|
