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
--source include/big_test.inc
# As per discussion with Olav, skipping this test from valgrind runs
--source include/not_valgrind.inc
--echo #
--echo # Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT.
--echo # MEMORY LEADING TO SYSTEM CRASH
--echo #
# This test will cause the server to allocate many GBs of memory and most
# likely run out of memory if this bug gets re-introduced.
# A reduced version of this test is include in include/mrr_tests.inc
CREATE TABLE ten (a INTEGER);
INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE hundred (a INTEGER);
INSERT INTO hundred
SELECT a1.a + 10 * a2.a FROM ten a1, ten a2;
CREATE TABLE thousand (a INTEGER);
INSERT INTO thousand
SELECT a1.a + 10 * a2.a + 100 * a3.a FROM ten a1, ten a2, ten a3;
CREATE TABLE t1 (
pk INTEGER NOT NULL,
i1 INTEGER NOT NULL,
c1 VARCHAR(10) NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t1
SELECT a, 1, 'MySQL' FROM thousand;
CREATE TABLE t2 (
pk INTEGER NOT NULL,
c1 VARCHAR(10) NOT NULL,
c2 varchar(10) NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t2
SELECT a, 'MySQL', 'MySQL' FROM ten;
CREATE TABLE t3 (
pk INTEGER NOT NULL,
c1 VARCHAR(10) NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t3
SELECT a, 'MySQL' FROM hundred;
CREATE TABLE t4 (
pk int(11) NOT NULL,
c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
c2 varchar(10) NOT NULL,
c3 varchar(10) NOT NULL,
PRIMARY KEY (pk),
KEY k1 (c1_key)
);
# t4 is empty
CREATE TABLE t5 (
pk INTEGER NOT NULL,
c1 VARCHAR(10) NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t5
SELECT a, 'MySQL' FROM ten;
set optimizer_switch=`mrr=on,mrr_cost_based=off`;
#set optimizer_switch='mrr=off';
let query=
SELECT STRAIGHT_JOIN *
FROM
(t1 LEFT JOIN
(t2 LEFT JOIN
(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
ON t2.c1 = t4.c3)
ON t1.c1 = t4.c2)
RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
WHERE t1.i1 = 1;
eval EXPLAIN $query;
eval $query;
DROP TABLE ten, hundred, thousand;
DROP TABLE t1, t2, t3, t4, t5;