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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
|
#
# Run join_nested.test with BKA enabled
#
set @save_optimizer_switch_jcl6=@@optimizer_switch;
set @@optimizer_switch='optimize_join_buffer_size=on';
set @@optimizer_switch='semijoin_with_cache=on';
set @@optimizer_switch='outer_join_with_cache=on';
set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
set join_cache_level=6;
set optimizer_search_depth=62;
set @optimizer_switch_for_join_nested_test=@@optimizer_switch;
set @join_cache_level_for_join_nested_test=@@join_cache_level;
--source join_nested.test
#
# BUG#35835: queries with nested outer joins with BKA enabled
#
CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
CREATE TABLE t8 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
INSERT INTO t5 VALUES (1,1,0), (2,2,0), (3,3,0);
INSERT INTO t6 VALUES (1,2,0), (3,2,0), (6,1,0);
INSERT INTO t7 VALUES (1,1,0), (2,2,0);
INSERT INTO t8 VALUES (0,2,0), (1,2,0);
INSERT INTO t6 VALUES (-1,12,0), (-3,13,0), (-6,11,0), (-4,14,0);
INSERT INTO t7 VALUES (-1,11,0), (-2,12,0), (-3,13,0), (-4,14,0), (-5,15,0);
INSERT INTO t8 VALUES (-3,13,0), (-1,12,0), (-2,14,0), (-5,15,0), (-4,16,0);
EXPLAIN
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5
LEFT JOIN
(
(t6, t7)
LEFT JOIN
t8
ON t7.b=t8.b AND t6.b < 10
)
ON t6.b >= 2 AND t5.b=t7.b AND
(t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0;
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5
LEFT JOIN
(
(t6, t7)
LEFT JOIN
t8
ON t7.b=t8.b AND t6.b < 10
)
ON t6.b >= 2 AND t5.b=t7.b AND
(t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0;
DELETE FROM t5;
DELETE FROM t6;
DELETE FROM t7;
DELETE FROM t8;
INSERT INTO t5 VALUES (1,3,0), (3,2,0);
INSERT INTO t6 VALUES (3,3,0);
INSERT INTO t7 VALUES (1,2,0);
INSERT INTO t8 VALUES (1,1,0);
EXPLAIN
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5 LEFT JOIN
(t6 LEFT JOIN t7 ON t7.a=1, t8)
ON (t5.b=t8.b);
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5 LEFT JOIN
(t6 LEFT JOIN t7 ON t7.a=1, t8)
ON (t5.b=t8.b);
EXPLAIN
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5 LEFT JOIN
(t6 LEFT JOIN t7 ON t7.b=2, t8)
ON (t5.b=t8.b);
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5 LEFT JOIN
(t6 LEFT JOIN t7 ON t7.b=2, t8)
ON (t5.b=t8.b);
EXPLAIN
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5 LEFT JOIN
(t8, t6 LEFT JOIN t7 ON t7.a=1)
ON (t5.b=t8.b);
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5 LEFT JOIN
(t8, t6 LEFT JOIN t7 ON t7.a=1)
ON (t5.b=t8.b);
DROP TABLE t5,t6,t7,t8;
set join_cache_level=default;
set @@optimizer_switch=@save_optimizer_switch_jcl6;
set @optimizer_switch_for_join_nested_test=NULL;
set @join_cache_level_for_join_nested_test=NULL;
|
