oceanbase

1
# owner: yibo.tyf
2
# owner group: SQL3
3
# tags: optimizer
4

5
--source mysql_test/test_suite/executor/include/cleanup.inc
6
--source mysql_test/test_suite/executor/include/load_data.inc
7

8
--result_format 4
9
--explain_protocol 2
10
--disable_abort_on_error
11

12
################################
13
# basic
14
# all kinds of receive.
15

16
# fifo receive
17
select b, sum(a) from t_h3_01_20 group by b having sum(a) between 5 and 16 order by b;
18
select b, sum(a) from t_h5_09_28 group by b having sum(a) between 13 and 24 order by b desc;
19
select b, sum(a) from t_r4_01_20 group by b having sum(a) between 5 and 16 order by b;
20
select b, sum(a) from t_r5_09_28 group by b having sum(a) between 13 and 24 order by b desc;
21
select b, sum(a) from t_h3_r4_01_20 group by b having sum(a) between 5 and 16 order by b;
22
select b, sum(a) from t_h3_r5_09_28 group by b having sum(a) between 13 and 24 order by b desc;
23
select b, sum(a) from t_h5_r4_01_20 group by b having sum(a) between 5 and 16 order by b;
24
select b, sum(a) from t_h5_r5_09_28 group by b having sum(a) between 13 and 24 order by b desc;
25
# task order receive
26
select a, b from t_r4_01_20 where a between 5 and 16 order by a;
27
select a, b from t_r4_09_28 where a between 13 and 24 order by a desc;
28
select a, b from t_r5_01_20 where a between 5 and 16 order by a;
29
select a, b from t_r5_09_28 where a between 13 and 24 order by a desc;
30
select a, b from t_h3_r4_01_20 where a = 12 order by b;
31
select a, b from t_h3_r5_09_28 where a = 12 order by b desc;
32
select a, b from t_h5_r4_01_20 where a = 12 order by b;
33
select a, b from t_h5_r5_09_28 where a = 12 order by b desc;
34
# merge sort receive
35
select a, b from t_h3_01_20 where a between 5 and 16 order by b;
36
select a, b from t_h5_09_28 where a between 13 and 24 order by b desc;
37
select a, b from t_r4_01_20 where a between 5 and 16 order by b;
38
select a, b from t_r4_09_28 where a between 13 and 24 order by b desc;
39
select a, b from t_h3_r4_01_20 where a between 5 and 16 order by b;
40
select a, b from t_h3_r5_09_28 where a between 13 and 24 order by b desc;
41
select a, b from t_h5_r4_01_20 where a between 5 and 16 order by b;
42
select a, b from t_h5_r5_09_28 where a between 13 and 24 order by b desc;
43

44
## check if result_type_classes of join_keys are the same when repart
45
select * from t_h5_int t1, t_refered as t2 where t1.a = t2.aa;
46
select * from t_h5_int t1, t_refered as t2 where t1.a = t2.a;
47
select * from t_h5_int t1, t_refered as t2 where t1.a = t2.b;
48
select * from t_h5_int t1, t_refered as t2 where t1.a = t2.c;
49
select * from t_h5_int t1, t_refered as t2 where t1.a = t2.d;
50
select * from t_h5_int t1, t_refered as t2 where t1.a = t2.e;
51

52

53
select * from t_r4_int t1, t_refered as t2 where t1.a = t2.aa;
54
select * from t_r4_int t1, t_refered as t2 where t1.a = t2.a;
55
select * from t_r4_int t1, t_refered as t2 where t1.a = t2.b;
56
select * from t_r4_int t1, t_refered as t2 where t1.a = t2.c;
57
select * from t_r4_int t1, t_refered as t2 where t1.a = t2.d;
58
select * from t_r4_int t1, t_refered as t2 where t1.a = t2.e;
59

60
select * from t_r4_date t1, t_refered as t2 where t1.c = t2.a;
61
select * from t_r4_date t1, t_refered as t2 where t1.c = t2.b;
62
--sorted_result
63
select * from t_r4_date t1, t_refered as t2 where t1.c = t2.c;
64
--sorted_result
65
select * from t_r4_date t1, t_refered as t2 where t1.c = t2.d;
66
select * from t_r4_date t1, t_refered as t2 where t1.c = t2.e;
67

68
select * from t_r4_datetime t1, t_refered as t2 where t1.c = t2.a;
69
select * from t_r4_datetime t1, t_refered as t2 where t1.c = t2.b;
70
--sorted_result
71
select * from t_r4_datetime t1, t_refered as t2 where t1.c = t2.c;
72
--sorted_result
73
select * from t_r4_datetime t1, t_refered as t2 where t1.c = t2.d;
74
select * from t_r4_datetime t1, t_refered as t2 where t1.c = t2.e;
75

76
#bug:
77
select * from t_h5_r4_int_int_datetime t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a and t1.c = t2.a;
78
select * from t_h5_r4_int_int_datetime t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a and t1.c = t2.aa;
79
select * from t_h5_r4_int_int_datetime t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a and t1.c = t2.b;
80
select * from t_h5_r4_int_int_datetime t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a and t1.c = t2.bb;
81
select * from t_h5_r4_int_int_datetime t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a and t1.c = t2.c;
82
select * from t_h5_r4_int_int_datetime t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a and t1.c = t2.d;
83
select * from t_h5_r4_int_int_datetime t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a and t1.c = t2.e;
84

85

86
select * from t_r4_int t1, t_r4_int as t2 where t1.a = t2.a;
87
select * from t_r4_int t1, t_r4_date as t2 where t1.a = t2.c;
88
select * from t_r4_int t1, t_r4_datetime as t2 where t1.a = t2.c;
89
select * from t_r4_int t1, t_r4_timestamp as t2 where t1.a = t2.c;
90

91

92
select * from t_r4_date t1, t_r4_int as t2 where t1.c = t2.a;
93
select * from t_r4_date t1, t_r4_date as t2 where t1.c = t2.c;
94
select * from t_r4_date t1, t_r4_datetime as t2 where t1.c = t2.c;
95
select * from t_r4_date t1, t_r4_timestamp as t2 where t1.c = t2.c;
96

97
select * from t_r4_datetime t1, t_r4_int as t2 where t1.c = t2.a;
98
select * from t_r4_datetime t1, t_r4_date as t2 where t1.c = t2.c;
99
select * from t_r4_datetime t1, t_r4_datetime as t2 where t1.c = t2.c;
100
select * from t_r4_datetime t1, t_r4_timestamp as t2 where t1.c = t2.c;
101

102
select * from t_h5_int t1, t_refered as t2 where t1.a = t2.aa;
103
select * from t_h5_int t1, t_refered as t2 where t1.a = t2.a;
104
select * from t_h5_int t1, t_refered as t2 where t1.a = t2.b;
105
select * from t_h5_int t1, t_refered as t2 where t1.a = t2.c;
106
select * from t_h5_int t1, t_refered as t2 where t1.a = t2.d;
107
select * from t_h5_int t1, t_refered as t2 where t1.a = t2.e;
108

109
select * from t_r4_int t1, t_refered as t2 where t1.a = t2.aa;
110
select * from t_r4_int t1, t_refered as t2 where t1.a = t2.a;
111
select * from t_r4_int t1, t_refered as t2 where t1.a = t2.b;
112
select * from t_r4_int t1, t_refered as t2 where t1.a = t2.c;
113
select * from t_r4_int t1, t_refered as t2 where t1.a = t2.d;
114
select * from t_r4_int t1, t_refered as t2 where t1.a = t2.e;
115

116
select * from t_r4_date t1, t_refered as t2 where t1.c = t2.a;
117
select * from t_r4_date t1, t_refered as t2 where t1.c = t2.b;
118
--sorted_result
119
select * from t_r4_date t1, t_refered as t2 where t1.c = t2.c;
120
--sorted_result
121
select * from t_r4_date t1, t_refered as t2 where t1.c = t2.d;
122
select * from t_r4_date t1, t_refered as t2 where t1.c = t2.e;
123

124
select * from t_r4_datetime t1, t_refered as t2 where t1.c = t2.a;
125
select * from t_r4_datetime t1, t_refered as t2 where t1.c = t2.b;
126
--sorted_result
127
select * from t_r4_datetime t1, t_refered as t2 where t1.c = t2.c;
128
--sorted_result
129
select * from t_r4_datetime t1, t_refered as t2 where t1.c = t2.d;
130
select * from t_r4_datetime t1, t_refered as t2 where t1.c = t2.e;
131

132
select * from t_h5_r4_int_int t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a;
133
select * from t_h5_r4_int_int t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.aa;
134
select * from t_h5_r4_int_int t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.b;
135
select * from t_h5_r4_int_int t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.c;
136
select * from t_h5_r4_int_int t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.d;
137
select * from t_h5_r4_int_int t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.e;
138

139
select * from t_h5_r4_int_int_datetime t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a and t1.c = t2.a;
140
select * from t_h5_r4_int_int_datetime t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a and t1.c = t2.aa;
141
select * from t_h5_r4_int_int_datetime t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a and t1.c = t2.b;
142
select * from t_h5_r4_int_int_datetime t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a and t1.c = t2.bb;
143
select * from t_h5_r4_int_int_datetime t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a and t1.c = t2.c;
144
select * from t_h5_r4_int_int_datetime t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a and t1.c = t2.d;
145
select * from t_h5_r4_int_int_datetime t1, t_refered as t2 where t1.a = t2.a and t1.b=t2.a and t1.c = t2.e;
146

147
select * from t_r4_int t1, t_r4_int  as t2 where t1.a = t2.a;
148
select * from t_r4_int t1, t_r4_date  as t2 where t1.a = t2.c;
149
select * from t_r4_int t1, t_r4_datetime  as t2 where t1.a = t2.c;
150
select * from t_r4_int t1, t_r4_timestamp  as t2 where t1.a = t2.c;
151

152

153
select * from t_r4_date t1, t_r4_int  as t2 where t1.c = t2.a;
154
select * from t_r4_date t1, t_r4_date  as t2 where t1.c = t2.c;
155
select * from t_r4_date t1, t_r4_datetime  as t2 where t1.c = t2.c;
156
select * from t_r4_date t1, t_r4_timestamp  as t2 where t1.c = t2.c;
157

158
select * from t_r4_datetime t1, t_r4_int  as t2 where t1.c = t2.a;
159
select * from t_r4_datetime t1, t_r4_date  as t2 where t1.c = t2.c;
160
select * from t_r4_datetime t1, t_r4_datetime  as t2 where t1.c = t2.c;
161
select * from t_r4_datetime t1, t_r4_timestamp  as t2 where t1.c = t2.c;
162

163

164
# additional case for task order with many range part.
165

166
select min(a) from t_many_range_part where a >=  1;
167
select min(a) from t_many_range_part where a >=  6;
168
select min(a) from t_many_range_part where a >= 11;
169
select min(a) from t_many_range_part where a >= 16;
170
select min(a) from t_many_range_part where a >= 21;
171
select min(a) from t_many_range_part where a >= 26;
172
select min(a) from t_many_range_part where a >= 31;
173
select min(a) from t_many_range_part where a >= 36;
174
select min(a) from t_many_range_part where a >= 41;
175
select min(a) from t_many_range_part where a >= 46;
176

177
select max(a) from t_many_range_part where a >=  1;
178
select max(a) from t_many_range_part where a >=  6;
179
select max(a) from t_many_range_part where a >= 11;
180
select max(a) from t_many_range_part where a >= 16;
181
select max(a) from t_many_range_part where a >= 21;
182
select max(a) from t_many_range_part where a >= 26;
183
select max(a) from t_many_range_part where a >= 31;
184
select max(a) from t_many_range_part where a >= 36;
185
select max(a) from t_many_range_part where a >= 41;
186
select max(a) from t_many_range_part where a >= 46;
187

188
select a from t_many_range_part where a >=  1 order by a asc limit 10;
189
select a from t_many_range_part where a >=  6 order by a asc limit 10;
190
select a from t_many_range_part where a >= 11 order by a asc limit 10;
191
select a from t_many_range_part where a >= 16 order by a asc limit 10;
192
select a from t_many_range_part where a >= 21 order by a asc limit 10;
193
select a from t_many_range_part where a >= 26 order by a asc limit 10;
194
select a from t_many_range_part where a >= 31 order by a asc limit 10;
195
select a from t_many_range_part where a >= 36 order by a asc limit 10;
196
select a from t_many_range_part where a >= 41 order by a asc limit 10;
197
select a from t_many_range_part where a >= 46 order by a asc limit 10;
198

199
select a from t_many_range_part where a >=  1 order by a desc limit 10;
200
select a from t_many_range_part where a >=  6 order by a desc limit 10;
201
select a from t_many_range_part where a >= 11 order by a desc limit 10;
202
select a from t_many_range_part where a >= 16 order by a desc limit 10;
203
select a from t_many_range_part where a >= 21 order by a desc limit 10;
204
select a from t_many_range_part where a >= 26 order by a desc limit 10;
205
select a from t_many_range_part where a >= 31 order by a desc limit 10;
206
select a from t_many_range_part where a >= 36 order by a desc limit 10;
207
select a from t_many_range_part where a >= 41 order by a desc limit 10;
208
select a from t_many_range_part where a >= 46 order by a desc limit 10;
209

210

211
--source mysql_test/test_suite/executor/include/cleanup.inc
212