oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: yibo.tyf
5
# owner group: SQL3
6
# tags: optimizer
7
# description:
8
# 1. test desc for merge join.
9

10
--disable_warnings
11
drop table if exists aa;
12
drop table if exists bb;
13
drop table if exists cc;
14
--enable_warnings
15
create table aa(a1 int primary key auto_increment, a2 int, a3 int);
16
create table bb(b1 int primary key auto_increment, b2 int, b3 int);
17
create table cc(c1 int primary key auto_increment, c2 int, c3 int);
18
#single layer merge join.
19
delete from aa;
20
insert into aa(a2, a3) values (10, 15), (10, 14), (10, 12),           (11, 13), (12, 12),           (12, 10);
21
delete from bb;
22
insert into bb(b2, b3) values           (10, 14),           (11, 14), (11, 13),           (12, 11), (12, 10);
23
--source mysql_test/include/minor_merge_tenant.inc
24
--disable_query_log
25
call dbms_stats.gather_table_stats('test','aa', method_opt=>'FOR ALL COLUMNS SIZE AUTO');
26
call dbms_stats.gather_table_stats('test','bb', method_opt=>'FOR ALL COLUMNS SIZE AUTO');
27
--enable_query_log
28
--sleep 1
29
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3;
30
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3;
31
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3 desc;
32
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3 desc;
33
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3;
34
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3;
35
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3 desc;
36
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3 desc;
37
delete from aa;
38
insert into aa(a2, a3) values (10, 15), (10, 14), (10, 12),           (11, 13), (12, 12),           (12, 10);
39
delete from bb;
40
insert into bb(b2, b3) values           (10, 14),           (11, 14), (11, 13),           (12, 11), (12, 10);
41
--sleep 1
42
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3;
43
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3;
44
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3 desc;
45
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3 desc;
46
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3;
47
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3;
48
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3 desc;
49
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3 desc;
50
#double layers merge join.
51
delete from cc;
52
insert into cc(c2, c3) values (10, 15), (10, 14), (10, 12),           (12, 10);
53
--sleep 1
54
select /*+ leading(aa bb cc) use_merge(aa, bb, cc) */ c2, a3 from aa, bb, cc where a2 = b2 and a3 = b3 and b2 = c2 and b3 = c3 order by c2, c3 desc;
55
explain select /*+ leading(aa bb cc) use_merge(aa, bb, cc) */ c2, a3 from aa, bb, cc where a2 = b2 and a3 = b3 and b2 = c2 and b3 = c3 order by c2, c3 desc;
56
select /*+ leading(aa bb cc) use_merge(aa, bb, cc) */ c2, a3 from aa, bb, cc where a2 = b2 and a3 = b3 and a2 = c2 and b3 = c3 order by c2, c3 desc;
57
explain select /*+ leading(aa bb cc) use_merge(aa, bb, cc) */ c2, a3 from aa, bb, cc where a2 = b2 and a3 = b3 and a2 = c2 and b3 = c3 order by c2, c3 desc;
58
--disable_warnings
59
drop table if exists tt1,tt2;
60
--enable_warnings
61
CREATE TABLE `tt1` (
62
  `a` int(11) NOT NULL,
63
  `b` int(11) DEFAULT NULL,
64
  `c` varchar(20) DEFAULT NULL,
65
  `d` datetime NOT NULL,
66
  PRIMARY KEY (`a`)
67
);
68
CREATE TABLE `tt2` (
69
  `a` int(11) NOT NULL,
70
  `b` int(11) DEFAULT NULL,
71
  `c` varchar(20) DEFAULT NULL,
72
  `d` datetime NOT NULL,
73
  PRIMARY KEY (`a`)
74
);
75
explain basic select /*+ use_merge(tt1, tt2) */ * from tt1,tt2 where tt1.a=tt2.a order by tt1.d desc;
76
explain basic select /*+ use_merge(tt1, tt2) */ * from tt1,tt2 where tt1.a=tt2.a order by tt1.a desc;
77
explain basic select /*+ use_merge(tt1, tt2) */ * from tt1,tt2 where tt1.a=tt2.a order by tt1.c desc;
78
--disable_warnings
79
drop table if exists tt1,tt2;
80
--enable_warnings
81
--disable_warnings
82

83
--echo == test partitioned table merge join ==
84
--disable_warnings
85
drop table if exists aa;
86
drop table if exists bb;
87
drop table if exists cc;
88
--enable_warnings
89
create table aa(a1 int, a2 int, a3 int) partition by hash(a1) partitions 5;
90
create table bb(b1 int, b2 int, b3 int) partition by hash(b1) partitions 5;
91
create table cc(c1 int, c2 int, c3 int) partition by hash(c1) partitions 5;
92
#single layer merge join.
93
delete from aa;
94
insert into aa values (1, 10, 15), (2, 10, 14), (3, 10, 12), (4, 11, 13), (5, 12, 12), (6, 12, 10);
95
delete from bb;
96
insert into bb values (1, 10, 14), (2, 11, 14), (3, 11, 13), (4, 12, 11), (5, 12, 10);
97
--sleep 1
98
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3;
99
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3;
100
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3 desc;
101
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3 desc;
102
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3;
103
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3;
104
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3 desc;
105
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3 desc;
106
delete from aa;
107
insert into aa values (1, 10, 15), (2, 10, 14), (3, 10, 12), (4, 11, 13), (5, 12, 12), (6, 12, 10);
108
delete from bb;
109
insert into bb values (1, 10, 14), (2, 11, 14), (3, 11, 13), (4, 12, 11), (5, 12, 10);
110
--sleep 1
111
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3;
112
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3;
113
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3 desc;
114
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2, a3 desc;
115
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3;
116
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3;
117
select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3 desc;
118
explain select /*+ use_merge(aa, bb) */ a2, b3 from aa, bb where a2 = b2 and a3 = b3 order by a2 desc, a3 desc;
119
#double layers merge join.
120
delete from cc;
121
insert into cc values (1, 10, 15), (2, 10, 14), (3, 10, 12), (4, 12, 10);
122
--sleep 1
123
select /*+ leading(aa bb cc) use_merge(aa, bb, cc) */ c2, a3 from aa, bb, cc where a2 = b2 and a3 = b3 and b2 = c2 and b3 = c3 order by c2, c3 desc;
124
explain select /*+ leading(aa bb cc) use_merge(aa, bb, cc) */ c2, a3 from aa, bb, cc where a2 = b2 and a3 = b3 and b2 = c2 and b3 = c3 order by c2, c3 desc;
125
select /*+ leading(aa bb cc) use_merge(aa, bb, cc) */ c2, a3 from aa, bb, cc where a2 = b2 and a3 = b3 and a2 = c2 and b3 = c3 order by c2, c3 desc;
126
explain select /*+ leading(aa bb cc) use_merge(aa, bb, cc) */ c2, a3 from aa, bb, cc where a2 = b2 and a3 = b3 and a2 = c2 and b3 = c3 order by c2, c3 desc;
127
--disable_warnings
128
drop table if exists tt1,tt2;
129
--enable_warnings
130
CREATE TABLE `tt1` (
131
  `a` int(11) NOT NULL,
132
  `b` int(11) DEFAULT NULL,
133
  `c` varchar(20) DEFAULT NULL,
134
  `d` datetime NOT NULL,
135
  PRIMARY KEY (`a`)
136
) partition by hash(a) partitions 5;
137
CREATE TABLE `tt2` (
138
  `a` int(11) NOT NULL,
139
  `b` int(11) DEFAULT NULL,
140
  `c` varchar(20) DEFAULT NULL,
141
  `d` datetime NOT NULL,
142
  PRIMARY KEY (`a`)
143
) partition by hash(a) partitions 5;
144
explain basic select /*+ use_merge(tt1, tt2) */ * from tt1,tt2 where tt1.a=tt2.a order by tt1.d desc;
145
explain basic select /*+ use_merge(tt1, tt2) */ * from tt1,tt2 where tt1.a=tt2.a order by tt1.a desc;
146
explain basic select /*+ use_merge(tt1, tt2) */ * from tt1,tt2 where tt1.a=tt2.a order by tt1.c desc;
147
--disable_warnings
148
drop table if exists tt1,tt2;
149
--enable_warnings
150
--disable_warnings
151
drop table if exists aa;
152
drop table if exists bb;
153
drop table if exists cc;
154
--enable_warnings
155

156
--disable_warnings
157
drop table if exists t_r4_01_20;
158
--enable_warnings
159

160
create table t_r4_01_20(a int, b int, c datetime, primary key (a,b))
161
partition by range columns(a)
162
(
163
  partition p6 values less than (6),
164
  partition p11 values less than (11),
165
  partition p16 values less than (16),
166
  partition pm values less than (MAXVALUE)
167
);
168
insert into t_r4_01_20 values ( 1,  1, 20161101), ( 2,  2, 20161102), ( 3,  3, 20161103), ( 4,  4, 20161104),
169
                              ( 5,  5, 20161105), ( 6,  6, 20161106), ( 7,  7, 20161107), ( 8,  8, 20161108),
170
                              ( 9,  9, 20161109), (10, 10, 20161110), (11, 11, 20161111), (12, 12, 20161112),
171
                              (13, 13, 20161113), (14, 14, 20161114), (15, 15, 20161115), (16, 16, 20161116),
172
                              (17, 17, 20161117), (18, 18, 20161118), (19, 19, 20161119), (20, 20, 20161120);
173
--sleep 1
174
 
175

176
select /*+use_merge(t1 t2)*/ t1.a, t2.a from t_r4_01_20 t1 inner join t_r4_01_20 t2 on t1.a = t2.a order by t1.a desc, t2.a desc;
177
explain basic select /*+use_merge(t1 t2)*/ t1.a, t2.a from t_r4_01_20 t1 inner join t_r4_01_20 t2 on t1.a = t2.a order by t1.a desc, t2.a desc;
178

179
explain select /*+use_merge(t1 t2)*/ t1.a, t2.a from t_r4_01_20 t1 inner join t_r4_01_20 t2 on t1.a = t2.a order by t1.a asc, t2.a asc;
180
select /*+use_merge(t1 t2)*/ t1.a, t2.a from t_r4_01_20 t1 inner join t_r4_01_20 t2 on t1.a = t2.a order by t1.a asc, t2.a asc;
181
--disable_warnings
182
drop table if exists t_r4_01_20;
183
--enable_warnings
184

185
## bug:
186
--disable_warnings
187
drop table if exists t2, t7;
188
--enable_warnings
189
create table t2(a int, b varchar(20), c decimal(20,10));
190
create table t7(a int(10), b varchar(10), c char(20), d decimal(20,10));
191
insert into t2 values(1, "222", 3.33),(2,"222",3.33),(2,"333",2.22),(3,"3333",1.11), (5, "555", 5.55), (6, "6666", 5.5),(1, '1', 1), (1, '2', 2), (2, '2.00', 2.00001), (3, '3.0', 3.00001), (4, "A", 4.33), (5, "B", 5), (6, "AB", 6);
192
insert into t7 values(65, '222', '333', 5), (65, '444', '5.550', 1), (66, '3.0', '555', 3.00001), (66, '2', 'B', 2), (67, 'A', '1', 1);
193
--sleep 1
194

195
--disable_warnings
196
drop table if exists t8, t9;
197
--enable_warnings
198
create table t8(a int primary key, b int, c int, index idx(b,c));
199
create table t9(a int primary key, b int, c int, index idx(b,c));
200
--disable_query_log
201
insert into t8 values (1, 2, 1);
202
insert into t8 values (2, 2, 2);
203
insert into t8 values (3, 2, 3);
204
insert into t8 values (4, 4, 1);
205
insert into t8 values (5, 4, 2);
206
insert into t8 values (6, 4, 3);
207

208
insert into t9 values (1, 2, 1);
209
insert into t9 values (2, 2, 2);
210
insert into t9 values (3, 2, 3);
211
insert into t9 values (4, 3, 1);
212
insert into t9 values (5, 4, 1);
213
insert into t9 values (6, 4, 2);
214
insert into t9 values (7, 4, 3);
215
--sleep 1
216
--enable_query_log
217

218
--echo
219
--echo ************ 测试full join
220
explain select /*+use_merge(t2,t7)*/* from t2 full join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
221
select /*+use_merge(t2,t7)*/* from t2 full join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
222
--echo
223
explain select /*+use_hash(t2,t7)*/* from t2 full join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
224
select /*+use_hash(t2,t7)*/* from t2 full join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
225
--echo
226
explain select /*+use_nl(t2,t7)*/* from t2 full join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
227
select /*+use_nl(t2,t7)*/* from t2 full join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
228

229
--echo ************ 测试right join
230
--echo
231
explain select /*+use_merge(t2,t7)*/* from t2 right join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
232
select /*+use_merge(t2,t7)*/* from t2 right join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
233
--echo
234
explain select /*+use_hash(t2,t7)*/* from t2 right join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
235
select /*+use_hash(t2,t7)*/* from t2 right join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
236
--echo
237
explain select /*+use_nl(t2,t7)*/* from t2 right join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
238
select /*+use_nl(t2,t7)*/* from t2 right join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
239

240
explain select /*+leading(t8, t9)*/ t8.b,t8.c,t9.b,t9.c from t8 right join t9 on t8.b = t9.b;
241
select /*+leading(t8, t9)*/ t8.b,t8.c,t9.b,t9.c from t8 right join t9 on t8.b = t9.b;
242

243
--echo ************ 测试left join
244
--echo
245
explain select /*+use_merge(t2,t7)*/* from t2 left join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
246
select /*+use_merge(t2,t7)*/* from t2 left join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
247
--echo
248
explain select /*+use_hash(t2,t7)*/* from t2 left join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
249
select /*+use_hash(t2,t7)*/* from t2 left join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
250
--echo
251
explain select /*+use_nl(t2,t7)*/* from t2 left join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
252
select /*+use_nl(t2,t7)*/* from t2 left join t7 on t2.b = t7.a and t2.c = t7.d order by t2.a,t2.b,t7.a,t7.b;
253

254

255
## bug: 9211556 end
256

257
--echo ********** join condition 必须和 ordering 排序一致
258
explain select /*+use_merge(t8 t9)*/ * from t8 join t9 on t8.c = t9.c and t8.b = t9.b;
259
select /*+use_merge(t8 t9)*/ * from t8 join t9 on t8.c = t9.c and t8.b = t9.b;
260

261
explain select /*+index(t8 idx), index(t9 idx), use_merge(t8 t9)*/ * from t8 join t9 on t8.a = t9.b and t8.b = t9.a;
262
select /*+index(t8 idx), index(t9 idx), use_merge(t8 t9)*/ * from t8 join t9 on t8.a = t9.b and t8.b = t9.a;
263

264
explain select /*+use_merge(t8 t9)*/ * from t8 left join t9 on t8.c = t9.c and t8.b = t9.b;
265
select /*+use_merge(t8 t9)*/ * from t8 left join t9 on t8.c = t9.c and t8.b = t9.b;
266

267
explain select /*+index(t8 idx), index(t9 idx), use_merge(t8 t9)*/ * from t8 left join t9 on t8.a = t9.b and t8.b = t9.a;
268
select /*+index(t8 idx), index(t9 idx), use_merge(t8 t9)*/ * from t8 left join t9 on t8.a = t9.b and t8.b = t9.a;
269

270
explain select /*+use_merge(t8 t9)*/ * from t8 right join t9 on t8.c = t9.c and t8.b = t9.b;
271
select /*+use_merge(t8 t9)*/ * from t8 right join t9 on t8.c = t9.c and t8.b = t9.b;
272

273
explain select /*+index(t8 idx), index(t9 idx), use_merge(t8 t9)*/ * from t8 right join t9 on t8.a = t9.b and t8.b = t9.a;
274
select /*+index(t8 idx), index(t9 idx), use_merge(t8 t9)*/ * from t8 right join t9 on t8.a = t9.b and t8.b = t9.a;
275

276
explain select /*+use_merge(t8 t9)*/ * from t8 right join t9 on t8.c = t9.c and t8.b = t9.b;
277
select /*+use_merge(t8 t9)*/ * from t8 right join t9 on t8.c = t9.c and t8.b = t9.b;
278

279
explain select /*+index(t8 idx), index(t9 idx), use_merge(t8 t9)*/ * from t8 full join t9 on t8.a = t9.b and t8.b = t9.a;
280
select /*+index(t8 idx), index(t9 idx), use_merge(t8 t9)*/ * from t8 full join t9 on t8.a = t9.b and t8.b = t9.a;
281

282
## misc bug 21243815
283
--disable_warnings
284
drop table if exists BB,CC;
285
--enable_warnings
286
CREATE TABLE BB ( col_varchar_key varchar (1), col_varchar_10 varchar(10), col_varchar varchar (1), col_int_key int, col_varchar_10_key varchar(10), col_varchar_20_key varchar(20), col_int int, col_varchar_20 varchar(20), pk int, /*Indices*/ key idx3(pk, col_varchar_key ), key idx1(pk, col_int_key ), key idx5(pk, col_varchar_10_key ), key idx7(pk, col_varchar_20_key ), primary key (pk) );
287

288

289
CREATE TABLE CC ( col_varchar_key varchar (1), col_varchar_20_key varchar(20), col_varchar_20 varchar(20), col_int_key int, col_int int, pk int, col_varchar_10 varchar(10), col_varchar_10_key varchar(10), col_varchar varchar (1), /*Indices*/ key idx3(pk, col_varchar_key ), key idx7(pk, col_varchar_20_key ), key idx1(pk, col_int_key ), primary key (pk) , key idx5(pk, col_varchar_10_key )) ;
290

291
--disable_query_log
292
INSERT INTO BB VALUES ('d', 'n', NULL, NULL, 'm', 'p', 2, 'v', 1) , (NULL, 'i', 'w', 6, 'n', 'r', 9, 'f', 2) , ('k', NULL, 'a', 8, 'p', NULL, NULL, 'j', 3) , (NULL, NULL, NULL, 4, 'b', 'b', 4, 'o', 4) , ('b', 'u', 'z', 7, 'k', 'e', 9, 'z', 5) , ('n', 'm', 's', 5, NULL, 'p', 6, 'y', 6) , ('f', 'e', 'f', 9, 'o', 'i', 9, 't', 7) , ('a', 'u', 's', NULL, 'l', 'g', 6, 'k', 8) , ('s', 'd', NULL, 1, 'r', 'g', NULL, 'j', 9) , ('y', NULL, 'j', 8, 'u', 'v', 7, 'b', 10) , ('w', 'i', NULL, 1, 'v', NULL, 1, NULL, 11) , ('t', NULL, NULL, 6, 'v', 'b', 3, NULL, 12) , ('m', 'i', 'd', 6, 'l', NULL, 3, 't', 13) , ('m', 'l', 'z', 6, 'a', 'b', NULL, 'b', 14) , ('d', 'y', 'c', 9, NULL, 'e', 7, 'g', 15) , ('e', 'm', 'w', 4, 'c', 'u', 6, 'a', 16) , ('a', NULL, 'j', 8, 'd', 'z', NULL, 's', 17) , ('h', 'u', NULL, 5, 't', 'p', 4, NULL, 18) , ('p', 'l', 'u', 8, 'e', 's', 6, 'm', 19) , ('l', 'l', 'h', 1, 'b', 'i', 8, 'z', 20) , ('i', 'f', NULL, NULL, 'g', 'e', NULL, 'p', 21) , ('l', 'w', 'p', 9, 'g', 'b', 4, 'k', 22) , ('j', 'd', 'l', 4, 'x', 'z', NULL, 'u', 23) , ('t', 'a', 'y', 8, 'o', 'e', 8, 'a', 24) , (NULL, 'a', 'a', 0, NULL, 'd', 9, 'm', 25) , (NULL, 'b', NULL, 3, 'i', 'r', NULL, 'i', 26) , ('s', 'v', NULL, 4, 'w', NULL, NULL, 'p', 27) , ('m', 'x', 'y', 0, 'n', 'i', 6, 'k', 28) ;
293

294
INSERT INTO CC VALUES ('x', 'r', 'v', 7, 8, 1, 't', 'j', 't') , (NULL, 'b', 'z', 2, 9, 2, 'p', 'u', 'y') , (NULL, 'q', 'e', 6, NULL, 3, 'u', 's', 'j') , ('d', NULL, 'n', 4, 3, 4, 'n', NULL, 'm') , ('y', 'u', 'b', 8, NULL, 5, 'z', NULL, 't') , ('p', 'f', 'm', 7, 1, 6, NULL, 'k', 'w') , ('m', 'w', 'f', 2, 9, 7, 't', 'w', 'g') , ('w', 'n', 'm', 3, 8, 8, NULL, 't', 'z') , ('o', 'g', 'i', 5, 0, 9, 't', 'a', NULL) , ('m', 'v', 'm', NULL, 7, 10, NULL, 'h', NULL) , ('u', 'm', 'j', 3, 4, 11, 'f', NULL, 'q') , ('h', 'y', NULL, 7, 4, 12, 'k', 't', NULL) , (NULL, 'h', 'g', NULL, 6, 13, 'v', 'd', 'u') , (NULL, NULL, NULL, 4, 5, 14, NULL, 'v', 'm') , ('f', 'x', 'm', 4, 8, 15, 'd', NULL, NULL) , ('s', 'f', 'x', 0, 2, 16, 'l', NULL, 'f') , ('t', 'o', 's', NULL, 8, 17, 'r', NULL, 'r') , ('h', 's', NULL, NULL, 3, 18, 'd', 'r', 'k') , ('u', 'w', 'r', 7, NULL, 19, 'w', NULL, 'c') , ('i', 'o', NULL, 8, 0, 20, 'n', 'i', 'y') , (NULL, NULL, NULL, 9, NULL, 21, 'm', NULL, 'm') , ('p', 'g', 'k', 9, NULL, 22, 'g', 'x', 'v') , (NULL, 'q', NULL, NULL, 2, 23, 'f', 'g', 's') , (NULL, NULL, 'p', 9, 0, 24, 'e', NULL, 'v') , (NULL, 'q', 'm', 3, 9, 25, 'w', 'i', 'm') , ('e', NULL, 'e', 5, NULL, 26, NULL, 'k', 'f') , ('i', 'm', 'n', 0, 4, 27, 'g', NULL, 'a') , ('r', 'g', 'o', 2, NULL, 28, 'c', NULL, 'u') , ('y', NULL, 'o', 2, NULL, 29, 'h', 'n', 'o') ;
295
--enable_query_log
296

297
explain SELECT /*+no_rewrite,leading(table1 table2),use_merge(table1 table2)*/   table1 . col_varchar_10_key AS field1  FROM    BB AS table1, CC AS table2    WHERE  table1 . `col_int_key` = table2 . `pk`  and table2 . `col_int_key` = table1 . `pk` ORDER BY table1 . col_int_key DESC;
298

299
SELECT /*+no_rewrite,leading(table1 table2),use_merge(table1 table2)*/   table1 . col_varchar_10_key AS field1  FROM    BB AS table1, CC AS table2    WHERE  table1 . `col_int_key` = table2 . `pk`  and table2 . `col_int_key` = table1 . `pk` ORDER BY table1 . col_int_key DESC;
300

301
--disable_warnings
302
drop table if exists t1, t2;
303
--enable_warnings
304

305
create table t1 (c1 int);
306
create table t2 (c1 int);
307
insert into t1 values(1);
308
insert into t1 select * from t1;
309
insert into t1 select * from t1;
310
insert into t1 select * from t1;
311
insert into t1 select * from t1;
312
insert into t1 select * from t1;
313
insert into t1 select * from t1;
314
insert into t1 select * from t1;
315
insert into t1 select * from t1;
316
insert into t1 select * from t1;
317
insert into t1 select * from t1;
318
insert into t1 select * from t1;
319
insert into t1 select * from t1;
320
insert into t1 select * from t1;
321

322
insert into t2 select * from t1;
323

324
select count(*) from (select /*+leading(t1 t2) use_merge(t2)*/* from t1 where t1.c1 in (select * from t2));
325
select count(*) from (select /*+leading(t1 t2) use_hash(t2)*/* from t1 where t1.c1 in (select * from t2));
326

327
select count(*) from (select /*+leading(t1 t2) use_merge(t2)*/* from t1 where not exists (select 1 from t2 where t1.c1 = t2.c1));
328
select count(*) from (select /*+leading(t1 t2) use_hash(t2)*/* from t1 where not exists (select 1 from t2 where t1.c1 = t2.c1));
329

330
alter system set _rowsets_enabled = true;
331
set ob_enable_plan_cache=0;
332

333
select count(*) from (select /*+leading(t1 t2) use_merge(t2)*/* from t1 where t1.c1 in (select * from t2));
334
select count(*) from (select /*+leading(t1 t2) use_hash(t2)*/* from t1 where t1.c1 in (select * from t2));
335

336
select count(*) from (select /*+leading(t1 t2) use_merge(t2)*/* from t1 where not exists (select 1 from t2 where t1.c1 = t2.c1));
337
select count(*) from (select /*+leading(t1 t2) use_hash(t2)*/* from t1 where not exists (select 1 from t2 where t1.c1 = t2.c1));
338

339
set ob_enable_plan_cache=1;
340

341
drop table t1;
342
drop table t2;
343

344
--disable_warnings
345
drop table if exists t2, t7, t8, t9, BB, CC;
346
--enable_warnings
347