Debian Codegraph @

mysql-test/suite/innodb/r/optimizer_temporary_table.result

raw
1 drop table if exists t1, t2;
2 Warnings:
3 Note 1051 Unknown table 'test.t1'
4 Note 1051 Unknown table 'test.t2'
5 create table t1 (a int, b varchar(20));
6 create table t2 (a int, b varchar(20));
7 insert into t1 values(1, 'a'), (2, 'b'), (3, 'c'), (3, 'c'), (4, 'c');
8 insert into t2 values(2, 'd'), (3, 'e'), (4, 'f'), (4, 'f'), (5, 'e');
9 drop function if exists func1;
10 Warnings:
11 Note 1305 FUNCTION test.func1 does not exist
12 create function func1(x int) returns int deterministic
13 begin
14 declare z1, z2 int;
15 set z1 = x;
16 set z2 = z1 + 2;
17 return z2;
18 end|
19 drop table if exists t3;
20 Warnings:
21 Note 1051 Unknown table 'test.t3'
22 create table t3 like t1;
23 insert into t3 select 5 + 10000 * rand(), '5' from t3;
24 insert into t3 select 5 + 10000 * rand(), '5' from t3;
25 insert into t3 select 5 + 10000 * rand(), '5' from t3;
26 insert into t3 select 5 + 10000 * rand(), '5' from t3;
27 insert into t3 select 5 + 10000 * rand(), '5' from t3;
28 insert into t3 select 5 + 10000 * rand(), '5' from t3;
29 insert into t3 select 5 + 10000 * rand(), '5' from t3;
30 insert into t3 select 5 + 10000 * rand(), '5' from t3;
31 drop table if exists t4;
32 Warnings:
33 Note 1051 Unknown table 'test.t4'
34 create table t4 (a int, b int, c int, d char, e blob);
35 insert into t4 values (1, 2, 3, '4', '5');
36 drop table if exists t5;
37 Warnings:
38 Note 1051 Unknown table 'test.t5'
39 create table t5 (a int, b int primary key);
40 insert into t5 values (1,7), (1,8);
41 drop table if exists t6;
42 Warnings:
43 Note 1051 Unknown table 'test.t6'
44 create table t6 (a int, b int, c int, d char);
45 insert into t6 values(1, 2, 3, '4');
46 create index t6_indx1 on t6(a);
47 drop table if exists t7;
48 Warnings:
49 Note 1051 Unknown table 'test.t7'
50 create table t7 (a int, b varchar(20));
51 insert into t7 values(1, 'a'), (2, 'b'), (3, 'c'), (3, 'c'), (4, 'c');
52 create index indx7 on t7(b);
53 drop table if exists t8;
54 Warnings:
55 Note 1051 Unknown table 'test.t8'
56 create table t8 (
57 pk integer auto_increment, col_int_key integer,
58 col_varchar_key varchar(1), primary key (pk),
59 key (col_int_key), key (col_varchar_key, col_int_key)) engine = innodb;
60 insert into t8 values (1, 2, 'v'), (2, 150, 'v');
61 drop table if exists t9, t10;
62 Warnings:
63 Note 1051 Unknown table 'test.t9'
64 Note 1051 Unknown table 'test.t10'
65 create table t9 (
66 pk int(11) not null auto_increment,
67 col_int_nokey int(11) default null,
68 col_int_key int(11) default null,
69 col_date_key date default null,
70 col_date_nokey date default null,
71 primary key (pk),
72 key col_int_key (col_int_key),
73 key col_date_key (col_date_key),
74 key col_varchar_key (col_int_key)
75 ) engine=innodb;
76 Warnings:
77 Warning 1681 Integer display width is deprecated and will be removed in a future release.
78 Warning 1681 Integer display width is deprecated and will be removed in a future release.
79 Warning 1681 Integer display width is deprecated and will be removed in a future release.
80 Warning 1831 Duplicate index 'col_varchar_key' defined on the table 'test.t9'. This is deprecated and will be disallowed in a future release.
81 create table t10 (
82 pk int(11) not null auto_increment,
83 col_int_nokey int(11) default null,
84 col_int_key int(11) default null,
85 col_date_key date default null,
86 col_date_nokey date default null,
87 primary key (pk),
88 key col_int_key (col_int_key),
89 key col_date_key (col_date_key),
90 key col_varchar_key (col_int_key)
91 ) engine=innodb;
92 Warnings:
93 Warning 1681 Integer display width is deprecated and will be removed in a future release.
94 Warning 1681 Integer display width is deprecated and will be removed in a future release.
95 Warning 1681 Integer display width is deprecated and will be removed in a future release.
96 Warning 1831 Duplicate index 'col_varchar_key' defined on the table 'test.t10'. This is deprecated and will be disallowed in a future release.
97 insert into t9 values
98 (1, 2, 4, '2008-12-05', '2008-12-05'),
99 (2, 150, 62, '2005-03-27', '2005-03-27'),
100 (3, NULL, 7, '2004-04-09', '2004-04-09'),
101 (4, 2, 1, '2006-05-13', '2006-05-13'),
102 (5, 5, 0, '2001-05-06', '2001-05-06'),
103 (6, 3, 7, '2006-03-03', '2006-03-03'),
104 (7, 1, 7, '2007-12-28', '2007-12-28'),
105 (8, 4, 1, '2004-10-20', '2004-10-20'),
106 (9, NULL, 7, '2008-04-09', '2008-04-09'),
107 (10, 2, 1, '2005-12-25', '2005-12-25'),
108 (11, 6, 5, '1900-01-01', '1900-01-01'),
109 (12, 6, 2, NULL, NULL),
110 (13, 8, 0, '1900-01-01', '1900-01-01'),
111 (14, 2, 1, '2001-01-16', '2001-01-16'),
112 (15, 6, 8, '1900-01-01', '1900-01-01'),
113 (16, 8, 1, '2001-11-23', '2001-11-23'),
114 (17, 3, 1, '2004-11-04', '2004-11-04'),
115 (18, 3, 9, '2003-03-12', '2003-03-12'),
116 (19, 9, 1, '2002-06-22', '2002-06-22'),
117 (20, 6, 5, '2004-10-10', '2004-10-10');
118 insert into t10 values
119 (1, 8, 4, '2002-01-03', '2002-01-03'),
120 (2, 3, 5, '2007-07-08', '2007-07-08'),
121 (3, 3, 8, '2000-08-02', '2000-08-02'),
122 (4, NULL, 4, '2000-03-06', '2000-03-06'),
123 (5, 7, 8, '2002-06-05', '2002-06-05'),
124 (6, 4, 2, '2009-08-09', '2009-08-09'),
125 (7, 7, 9, '2001-03-22', '2001-03-22'),
126 (8, 7, 6, '2002-10-08', '2002-10-08'),
127 (9, 8, NULL, NULL, NULL),
128 (10, 6, NULL, '2007-09-13', '2007-09-13'),
129 (11, 3, 48, '2003-12-08', '2003-12-08'),
130 (12, 210, 228, '2006-06-01', '2006-06-01'),
131 (13, 1, 3, '2001-10-04', '2001-10-04'),
132 (14, 2, 5, '2004-11-16', '2004-11-16'),
133 (15, 251, 39, NULL, NULL),
134 (16, 4, 6, '2005-06-22', '2005-06-22'),
135 (17, 4, 8, '2003-04-19', '2003-04-19'),
136 (18, 9, 3, '2006-03-23', '2006-03-23'),
137 (19, 4, NULL, NULL, NULL),
138 (20, NULL, 2, '2008-10-22', '2008-10-22'),
139 (21, 4, 6, '2009-04-04', '2009-04-04'),
140 (22, NULL, 3, NULL, NULL),
141 (23, 1, 1, '2001-07-15', '2001-07-15'),
142 (24, 6, 4, '2004-09-25', '2004-09-25'),
143 (25, 2, 3, '2001-02-20', '2001-02-20'),
144 (26, NULL, 1, '1900-01-01', '1900-01-01'),
145 (27, 4, NULL, '2004-08-02', '2004-08-02'),
146 (28, 248, 97, '2009-02-26', '2009-02-26'),
147 (29, 4, 0, '2002-11-23', '2002-11-23'),
148 (30, 8, 0, '2008-12-17', '2008-12-17'),
149 (31, 4, 9, '2005-08-26', '2005-08-26'),
150 (32, 5, 5, '2001-03-10', '2001-03-10'),
151 (33, 9, 9, '1900-01-01', '1900-01-01'),
152 (34, 2, 0, '2003-08-04', '2003-08-04'),
153 (35, 4, 2, '2007-11-06', '2007-11-06'),
154 (36, 211, 172, '2009-04-23', '2009-04-23'),
155 (37, 2, NULL, '2002-10-06', '2002-10-06'),
156 (38, 4, 5, '2008-02-12', '2008-02-12'),
157 (39, 125, 119, '2007-09-18', '2007-09-18'),
158 (40, 9, 1, '2007-06-26', '2007-06-26'),
159 (41, 4, 4, NULL, NULL),
160 (42, 8, 8, '2009-05-05', '2009-05-05'),
161 (43, 8, NULL, '2003-05-04', '2003-05-04'),
162 (44, NULL, 6, '2003-11-10', '2003-11-10'),
163 (45, 8, 5, '2009-02-19', '2009-02-19'),
164 (46, 4, 5, '2006-11-17', '2006-11-17'),
165 (47, 8, 1, '2000-02-23', '2000-02-23'),
166 (48, NULL, 7, '1900-01-01', '1900-01-01'),
167 (49, 1, 2, '2003-04-08', '2003-04-08'),
168 (50, 3, 8, '2006-07-08', '2006-07-08'),
169 (51, 5, 9, '2001-05-21', '2001-05-21'),
170 (52, 8, NULL, NULL, NULL),
171 (53, 7, NULL, '2009-01-05', '2009-01-05'),
172 (54, 2, 3, '2001-08-05', '2001-08-05'),
173 (55, NULL, 172, '2000-11-01', '2000-11-01'),
174 (56, 3, NULL, '2004-12-16', '2004-12-16'),
175 (57, NULL, 6, '2003-02-06', '2003-02-06'),
176 (58, 9, 6, '2008-04-23', '2008-04-23'),
177 (59, 1, 5, '2005-12-20', '2005-12-20'),
178 (60, 0, 4, '2002-03-13', '2002-03-13'),
179 (61, 0, 3, NULL, NULL),
180 (62, 8, 2, '2006-07-20', '2006-07-20'),
181 (63, NULL, 7, '2002-12-19', '2002-12-19'),
182 (64, 9, 4, '2001-07-09', '2001-07-09'),
183 (65, 1, 6, '2006-05-27', '2006-05-27'),
184 (66, 9, 0, '2007-02-26', '2007-02-26'),
185 (67, 7, 8, NULL, NULL),
186 (68, 2, 5, '2007-02-07', '2007-02-07'),
187 (69, 1, 8, '2005-01-22', '2005-01-22'),
188 (70, 9, 2, '2006-04-25', '2006-04-25'),
189 (71, 5, 9, '2002-11-13', '2002-11-13'),
190 (72, 4, 7, '2007-10-26', '2007-10-26'),
191 (73, 6, 5, '2003-06-16', '2003-06-16'),
192 (74, 5, 7, '2006-01-18', '2006-01-18'),
193 (75, 2, 0, '1900-01-01', '1900-01-01'),
194 (76, 4, 4, '2000-09-13', '2000-09-13'),
195 (77, 0, 3, '2003-01-26', '2003-01-26'),
196 (78, 3, 1, '2002-09-09', '2002-09-09'),
197 (79, 0, 0, '2001-09-06', '2001-09-06'),
198 (80, 6, 6, '2006-02-23', '2006-02-23'),
199 (81, 1, 2, '2004-06-21', '2004-06-21'),
200 (82, 9, NULL, '2006-12-02', '2006-12-02'),
201 (83, 4, 8, '2005-05-17', '2005-05-17'),
202 (84, 9, NULL, '2001-12-27', '2001-12-27'),
203 (85, 4, NULL, '2008-04-11', '2008-04-11'),
204 (86, 1, NULL, '2000-09-24', '2000-09-24'),
205 (87, 2, 3, '2004-04-20', '2004-04-20'),
206 (88, 8, 7, '2008-07-10', '2008-07-10'),
207 (89, 1, 3, '2007-08-14', '2007-08-14'),
208 (90, 0, 5, '2008-06-08', '2008-06-08'),
209 (91, 2, 5, '2001-07-26', '2001-07-26'),
210 (92, 0, 1, '2008-09-17', '2008-09-17'),
211 (93, 1, 2, NULL, NULL),
212 (94, 2, 1, '2004-02-25', '2004-02-25'),
213 (95, NULL, 7, '2009-11-02', '2009-11-02'),
214 (96, 3, 1, '2001-12-14', '2001-12-14'),
215 (97, 8, 9, NULL, NULL),
216 (98, 4, 9, '1900-01-01', '1900-01-01'),
217 (99, 4, 8, '2002-04-12', '2002-04-12'),
218 (100, NULL, 3, '2000-02-09', '2000-02-09');
219 create table t11 (
220 col_date_key date default null,
221 col_varchar_10_utf8 varchar(10) CHARACTER SET utf8 default null,
222 col_varchar_10_latin1 varchar(10) default null,
223 col_varchar_10_latin1_key varchar(10) default null,
224 col_datetime datetime default null,
225 col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 default null,
226 col_varchar_1024_latin1_key varchar(1024) default null,
227 col_varchar_1024_latin1 varchar(1024) default null,
228 col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 default null,
229 col_int_key int(11) default null,
230 col_date date default null,
231 col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 default null,
232 col_int int(11) default null,
233 pk int(11) not null auto_increment,
234 col_datetime_key datetime default null,
235 primary key (pk),
236 key col_date_key (col_date_key),
237 key col_varchar_10_latin1_key (col_varchar_10_latin1_key),
238 key col_varchar_10_utf8_key (col_varchar_10_utf8_key),
239 key col_varchar_1024_latin1_key (col_varchar_1024_latin1_key(1000)),
240 key col_int_key (col_int_key),
241 key col_varchar_1024_utf8_key (col_varchar_1024_utf8_key(333)),
242 key col_datetime_key (col_datetime_key)
243 ) engine=myisam auto_increment=76 default charset=latin1;
244 Warnings:
245 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
246 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
247 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
248 Warning 1681 Integer display width is deprecated and will be removed in a future release.
249 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
250 Warning 1681 Integer display width is deprecated and will be removed in a future release.
251 Warning 1681 Integer display width is deprecated and will be removed in a future release.
252 insert ignore into t11 values
253 ('2005-03-10','have','o','gryvuwdllyhymuwyypoiuyeqbnaanbur','2004','going','h','well','f',1140785152,'2003','YTLDF',NULL,NULL,'20050908213850.042201'),
254 ('2003', 'g', 'ELTFL', 'got', '20:07:16.048352', 'ryvuwdllyhymuwyypoiuyeqbnaanbursgddnqhyvxnnpadsjiqdkajofferjfslatroorycttbiuhlubvmoikwwnalqyewgthdmfvsedatazrflwenzeiwdxtheqppbmqfydsawvanehgvfvbsqxzkqzhtjkybvikmpexipoddmoulsnzfchusfgkdazecevrpuakfioamyyqyribcnydfxmmzsxcpkydyczmeajaebsaskneaqwxjbpjptiwgiizoxfygnbjojpifpzldsdhrukdbwpzribxgafunywmmcfpjugozduylotsugqocbadhcnxjqlugncbebwsbobhxgghyyphxfqqnpliazusgjswynfotwdonpbkllfdbuoqceirksegypasuuxnrjumrkaryhkgkbdfgoqbdmrdrmpvwitqzswgossxltimkbtrxitnmigcxgfaqmrnvspyaftisgdbkzlevczyrlossygtnatwcobbtsxqhjrdykmthpyzmdgyirxwlidiknxwsufkyexucekyhivscjdcouswuiyltrpmrngriwvrxgovfyewdsvlwirfzkwexxzoccufvuvhsjfvuwjsrrzguntudxxhblzbescayrbbrijnvucxxmbyltlojlgcweddzrfxsmwnxxewigapgrowtmrlqsknuaegzfvdwfdnnbfyubwckwfdrwmrymagyswwvvyeqhaaskgxogthhrzsdlsyqoeuvhwxquwbnivpowtybzehvbsoppuefqngkqohtdeylosjyvtxuziotnfpbqxkpxvzcjbgxokjzitakfevxduhtudsslluhzgcwgrcrtpnczgxchnmmgnubjzvvuklklfhiupbsjzhcqyadhskxtjzabzesulhgpykga', 'she', 'have', 'PYJKD', 9, '2008-06-10', 'yvuwdllyhymuwyypoiuyeqbnaanbursgddnqhyvxnnpadsjiqdkajofferjfslatroorycttbiuhlubvmoikwwnalqyewgthdmfvsedatazrflwenzeiwdxtheqppbmqfydsawvanehgvfvbsqxzkqzhtjkybvikmpexipoddmoulsnzfchusfgkdazecevrpuakfioamyyqyribcnydfxmmzsxcpkydyczmeajaebsaskneaqwxjbpjptiwgiizoxfygnbjojpifpzldsdhrukdbwpzribxgafunywmmcfpjugozduylotsugqocbadhcnxjqlugncbebwsbobhxgghyyphxfqqnpliazusgjswynfotwdonpbkllfdbuoqceirksegypasuuxnrjumrkaryhkgkbdfgoqbdmrdrmpvwitqzswgossxltimkbtrxitnmigcxgfaqmrnv', NULL, NULL, '2008-01-09');
255 create table t12 (t text, c char(10), b blob, d varbinary(10)) engine=innodb;
256 insert into t12 values (NULL, NULL, NULL,NULL);
257 insert into t12 values ("", "", "", "");
258 insert into t12 values ("hello", "hello", "hello", "hello");
259 insert into t12 values ("HELLO", "HELLO","HELLO", "HELLO");
260 insert into t12 values ("HELLO MY", "HELLO MY", "HELLO MY", "HELLO MY");
261 insert into t12 values ("a", "a", "a", "a");
262 insert into t12 values (1,1,1,1);
263 insert into t12 values (NULL,NULL,NULL,NULL);
264 create table t13 (
265 pk int(11) not null auto_increment,
266 col_int_nokey int(11) default null,
267 col_int_key int(11) default null,
268 col_date_key date default null,
269 col_time_key time default null,
270 col_varchar_key varchar(1) default null,
271 col_varchar_nokey varchar(1) default null,
272 primary key (pk),
273 key col_int_key (col_int_key),
274 key col_date_key (col_date_key),
275 key col_time_key (col_time_key),
276 key col_varchar_key (col_varchar_key, col_int_key)
277 ) engine=myisam auto_increment=21;
278 Warnings:
279 Warning 1681 Integer display width is deprecated and will be removed in a future release.
280 Warning 1681 Integer display width is deprecated and will be removed in a future release.
281 Warning 1681 Integer display width is deprecated and will be removed in a future release.
282 insert into t13 values
283 (2, 150, 62, '2005-03-27', '14:26:02', 'v', 'v'),
284 (3, NULL, 7, '2004-04-09', '14:03:03', 'c', 'c'),
285 (4, 2, 1, '2006-05-13', '01:46:09', NULL, NULL),
286 (5, 5, 0, '2001-05-06', '16:21:18', 'x', 'x'),
287 (6, 3, 7, '2006-03-03', '18:56:33', 'i', 'i'),
288 (7, 1, 7, '2007-12-28', NULL, 'e', 'e'),
289 (8, 4, 1, '2004-10-20', '09:29:08', 'p', 'p'),
290 (9, NULL, 7, '2008-04-09', '19:11:10', 's', 's'),
291 (10, 2, 1, '2005-12-25', '11:57:26', 'j', 'j'),
292 (11, 6, 5, '1900-01-01', '00:39:46', 'z', 'z'),
293 (12, 6, 2, NULL, '03:28:15', 'c', 'c'),
294 (13, 8, 0, '1900-01-01', '06:44:18', 'a', 'a'),
295 (14, 2, 1, '2001-01-16', '14:36:39', 'q', 'q'),
296 (15, 6, 8, '1900-01-01', '18:42:45', 'y', 'y'),
297 (16, 8, 1, '2001-11-23', '02:57:29', NULL, NULL),
298 (17, 3, 1, '2004-11-04', '16:46:13', 'r', 'r'),
299 (18, 3, 9, '2003-03-12', '19:39:02', 'v', 'v'),
300 (19, 9, 1, '2002-06-22', NULL, NULL, NULL),
301 (20, 6, 5, '2004-10-10', '20:58:33', 'r', 'r');
302 create table t14 (
303 pk int(11) not null auto_increment,
304 col_int_nokey int(11) default null,
305 col_int_key int(11) default null,
306 col_date_key date default null,
307 col_time_key time default null,
308 col_varchar_key varchar(1) default null,
309 col_varchar_nokey varchar(1) default null,
310 primary key (pk),
311 key col_int_key (col_int_key),
312 key col_date_key (col_date_key),
313 key col_time_key (col_time_key),
314 key col_varchar_key (col_varchar_key,col_int_key)
315 ) ENGINE=myisam auto_increment=30;
316 Warnings:
317 Warning 1681 Integer display width is deprecated and will be removed in a future release.
318 Warning 1681 Integer display width is deprecated and will be removed in a future release.
319 Warning 1681 Integer display width is deprecated and will be removed in a future release.
320 insert into t14 values
321 (10, NULL, 8, '2000-12-03', '22:55:23', 'x', 'x'),
322 (11, 8, 7, '2008-05-03', '10:19:31', 'd', 'd'),
323 (12, 1, 1, '2005-12-06', '14:40:36', 'r', 'r'),
324 (13, 9, 7, '2000-04-10', '04:37:47', 'f', 'f'),
325 (14, 4, 9, '2002-11-05', '19:34:06', 'y', 'y'),
326 (15, 3, NULL, '2000-09-06', '20:35:33', 'u', 'u'),
327 (16, 2, 1, NULL, NULL, 'm', 'm'),
328 (17, NULL, 9, '2007-06-14', '14:43:37', NULL, NULL),
329 (18, 2, 2, '2007-11-17', '02:23:09', 'o', 'o'),
330 (19, NULL, 9, '2009-02-23', '01:22:45', 'w', 'w'),
331 (20, 6, 2, '2007-01-08', '00:00:00', 'm', 'm'),
332 (21, 7, 4, '2008-06-10', '00:13:25', 'q', 'q'),
333 (22, 2, 0, '2002-10-20', '03:47:16', NULL, NULL),
334 (23, 5, 4, '2008-09-12', '01:41:48', 'd', 'd'),
335 (24, 7, 8, '2006-06-16', '00:00:00', 'g', 'g'),
336 (25, 6, NULL, '2004-09-18', '22:32:04', 'x', 'x'),
337 (26, 6, NULL, '1900-01-01', '16:44:14', 'f', 'f'),
338 (27, 2, 0, '2005-09-13', '17:38:37', 'p', 'p'),
339 (28, 9, NULL, '2007-04-09', '08:46:48', 'j', 'j'),
340 (29, 6, 8, '2000-09-20', '14:11:27', 'c', 'c');
341 analyze table t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14;
342 Table Op Msg_type Msg_text
343 test.t1 analyze status OK
344 test.t2 analyze status OK
345 test.t3 analyze status OK
346 test.t4 analyze status OK
347 test.t5 analyze status OK
348 test.t6 analyze status OK
349 test.t7 analyze status OK
350 test.t8 analyze status OK
351 test.t9 analyze status OK
352 test.t10 analyze status OK
353 test.t11 analyze status OK
354 test.t12 analyze status OK
355 test.t13 analyze status OK
356 test.t14 analyze status OK
357 # restart: --big-tables=1
358 SET @optimizer_switch_saved= @@optimizer_switch;
359 SET @@optimizer_switch="derived_merge=off";
360 explain format = json select sql_buffer_result * from t1;
361 EXPLAIN
362 {
363 "query_block": {
364 "select_id": 1,
365 "cost_info": {
366 "query_cost": "0.75"
367 },
368 "buffer_result": {
369 "using_temporary_table": true,
370 "table": {
371 "table_name": "t1",
372 "access_type": "ALL",
373 "rows_examined_per_scan": 5,
374 "rows_produced_per_join": 5,
375 "filtered": "100.00",
376 "cost_info": {
377 "read_cost": "0.25",
378 "eval_cost": "0.50",
379 "prefix_cost": "0.75",
380 "data_read_per_join": "440"
381 },
382 "used_columns": [
383 "a",
384 "b"
385 ]
386 }
387 }
388 }
389 }
390 Warnings:
391 Note 1003 /* select#1 */ select sql_buffer_result `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
392 select sql_buffer_result * from t1;
393 a b
394 1 a
395 2 b
396 3 c
397 3 c
398 4 c
399 explain format = json select sql_buffer_result * from t2;
400 EXPLAIN
401 {
402 "query_block": {
403 "select_id": 1,
404 "cost_info": {
405 "query_cost": "0.75"
406 },
407 "buffer_result": {
408 "using_temporary_table": true,
409 "table": {
410 "table_name": "t2",
411 "access_type": "ALL",
412 "rows_examined_per_scan": 5,
413 "rows_produced_per_join": 5,
414 "filtered": "100.00",
415 "cost_info": {
416 "read_cost": "0.25",
417 "eval_cost": "0.50",
418 "prefix_cost": "0.75",
419 "data_read_per_join": "440"
420 },
421 "used_columns": [
422 "a",
423 "b"
424 ]
425 }
426 }
427 }
428 }
429 Warnings:
430 Note 1003 /* select#1 */ select sql_buffer_result `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`
431 select sql_buffer_result * from t2;
432 a b
433 2 d
434 3 e
435 4 f
436 4 f
437 5 e
438 explain format = json select * from t1 left join t2 on t1.b = t2.b order by t1.a;
439 EXPLAIN
440 {
441 "query_block": {
442 "select_id": 1,
443 "cost_info": {
444 "query_cost": "28.50"
445 },
446 "ordering_operation": {
447 "using_temporary_table": true,
448 "using_filesort": true,
449 "cost_info": {
450 "sort_cost": "25.00"
451 },
452 "nested_loop": [
453 {
454 "table": {
455 "table_name": "t1",
456 "access_type": "ALL",
457 "rows_examined_per_scan": 5,
458 "rows_produced_per_join": 5,
459 "filtered": "100.00",
460 "cost_info": {
461 "read_cost": "0.25",
462 "eval_cost": "0.50",
463 "prefix_cost": "0.75",
464 "data_read_per_join": "440"
465 },
466 "used_columns": [
467 "a",
468 "b"
469 ]
470 }
471 },
472 {
473 "table": {
474 "table_name": "t2",
475 "access_type": "ALL",
476 "rows_examined_per_scan": 5,
477 "rows_produced_per_join": 25,
478 "filtered": "100.00",
479 "using_join_buffer": "hash join",
480 "cost_info": {
481 "read_cost": "0.25",
482 "eval_cost": "2.50",
483 "prefix_cost": "3.50",
484 "data_read_per_join": "2K"
485 },
486 "used_columns": [
487 "a",
488 "b"
489 ],
490 "attached_condition": "<if>(is_not_null_compl(t2), (`test`.`t2`.`b` = `test`.`t1`.`b`), true)"
491 }
492 }
493 ]
494 }
495 }
496 }
497 Warnings:
498 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`b` = `test`.`t1`.`b`)) where true order by `test`.`t1`.`a`
499 select * from t1 left join t2 on t1.b = t2.b order by t1.a;
500 a b a b
501 1 a NULL NULL
502 2 b NULL NULL
503 3 c NULL NULL
504 3 c NULL NULL
505 4 c NULL NULL
506 explain format = json select * from t1 order by func1(a);
507 EXPLAIN
508 {
509 "query_block": {
510 "select_id": 1,
511 "cost_info": {
512 "query_cost": "5.75"
513 },
514 "ordering_operation": {
515 "using_temporary_table": true,
516 "using_filesort": true,
517 "cost_info": {
518 "sort_cost": "5.00"
519 },
520 "table": {
521 "table_name": "t1",
522 "access_type": "ALL",
523 "rows_examined_per_scan": 5,
524 "rows_produced_per_join": 5,
525 "filtered": "100.00",
526 "cost_info": {
527 "read_cost": "0.25",
528 "eval_cost": "0.50",
529 "prefix_cost": "0.75",
530 "data_read_per_join": "440"
531 },
532 "used_columns": [
533 "a",
534 "b"
535 ]
536 }
537 }
538 }
539 }
540 Warnings:
541 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `func1`(`test`.`t1`.`a`)
542 select * from t1 order by func1(a);
543 a b
544 1 a
545 2 b
546 3 c
547 3 c
548 4 c
549 explain format = json select a from t1 group by b order by sum(a);
550 EXPLAIN
551 {
552 "query_block": {
553 "select_id": 1,
554 "cost_info": {
555 "query_cost": "0.75"
556 },
557 "ordering_operation": {
558 "using_filesort": true,
559 "grouping_operation": {
560 "using_temporary_table": true,
561 "using_filesort": false,
562 "table": {
563 "table_name": "t1",
564 "access_type": "ALL",
565 "rows_examined_per_scan": 5,
566 "rows_produced_per_join": 5,
567 "filtered": "100.00",
568 "cost_info": {
569 "read_cost": "0.25",
570 "eval_cost": "0.50",
571 "prefix_cost": "0.75",
572 "data_read_per_join": "440"
573 },
574 "used_columns": [
575 "a",
576 "b"
577 ]
578 }
579 }
580 }
581 }
582 }
583 Warnings:
584 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`b` order by sum(`test`.`t1`.`a`)
585 select a from t1 group by b order by sum(a);
586 a
587 1
588 2
589 3
590 explain format = json select a from t7 group by b order by sum(a);
591 EXPLAIN
592 {
593 "query_block": {
594 "select_id": 1,
595 "cost_info": {
596 "query_cost": "0.75"
597 },
598 "ordering_operation": {
599 "using_temporary_table": true,
600 "using_filesort": true,
601 "grouping_operation": {
602 "using_filesort": false,
603 "table": {
604 "table_name": "t7",
605 "access_type": "index",
606 "possible_keys": [
607 "indx7"
608 ],
609 "key": "indx7",
610 "used_key_parts": [
611 "b"
612 ],
613 "key_length": "83",
614 "rows_examined_per_scan": 5,
615 "rows_produced_per_join": 5,
616 "filtered": "100.00",
617 "cost_info": {
618 "read_cost": "0.25",
619 "eval_cost": "0.50",
620 "prefix_cost": "0.75",
621 "data_read_per_join": "440"
622 },
623 "used_columns": [
624 "a",
625 "b"
626 ]
627 }
628 }
629 }
630 }
631 }
632 Warnings:
633 Note 1003 /* select#1 */ select `test`.`t7`.`a` AS `a` from `test`.`t7` group by `test`.`t7`.`b` order by sum(`test`.`t7`.`a`)
634 select a from t7 group by b order by sum(a);
635 a
636 1
637 2
638 3
639 explain format = json select sql_big_result distinct * from t1, t2;
640 EXPLAIN
641 {
642 "query_block": {
643 "select_id": 1,
644 "cost_info": {
645 "query_cost": "3.50"
646 },
647 "duplicates_removal": {
648 "using_temporary_table": true,
649 "using_filesort": false,
650 "nested_loop": [
651 {
652 "table": {
653 "table_name": "t1",
654 "access_type": "ALL",
655 "rows_examined_per_scan": 5,
656 "rows_produced_per_join": 5,
657 "filtered": "100.00",
658 "cost_info": {
659 "read_cost": "0.25",
660 "eval_cost": "0.50",
661 "prefix_cost": "0.75",
662 "data_read_per_join": "440"
663 },
664 "used_columns": [
665 "a",
666 "b"
667 ]
668 }
669 },
670 {
671 "table": {
672 "table_name": "t2",
673 "access_type": "ALL",
674 "rows_examined_per_scan": 5,
675 "rows_produced_per_join": 25,
676 "filtered": "100.00",
677 "using_join_buffer": "hash join",
678 "cost_info": {
679 "read_cost": "0.25",
680 "eval_cost": "2.50",
681 "prefix_cost": "3.50",
682 "data_read_per_join": "2K"
683 },
684 "used_columns": [
685 "a",
686 "b"
687 ]
688 }
689 }
690 ]
691 }
692 }
693 }
694 Warnings:
695 Note 1003 /* select#1 */ select distinct sql_big_result `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2`
696 select sql_big_result distinct * from t1, t2;
697 a b a b
698 1 a 2 d
699 1 a 3 e
700 1 a 4 f
701 1 a 5 e
702 2 b 2 d
703 2 b 3 e
704 2 b 4 f
705 2 b 5 e
706 3 c 2 d
707 3 c 3 e
708 3 c 4 f
709 3 c 5 e
710 4 c 2 d
711 4 c 3 e
712 4 c 4 f
713 4 c 5 e
714 explain format = json select sql_big_result t1.b from t1, t2 group by t1.b;
715 EXPLAIN
716 {
717 "query_block": {
718 "select_id": 1,
719 "cost_info": {
720 "query_cost": "3.50"
721 },
722 "grouping_operation": {
723 "using_temporary_table": true,
724 "using_filesort": false,
725 "nested_loop": [
726 {
727 "table": {
728 "table_name": "t1",
729 "access_type": "ALL",
730 "rows_examined_per_scan": 5,
731 "rows_produced_per_join": 5,
732 "filtered": "100.00",
733 "cost_info": {
734 "read_cost": "0.25",
735 "eval_cost": "0.50",
736 "prefix_cost": "0.75",
737 "data_read_per_join": "440"
738 },
739 "used_columns": [
740 "b"
741 ]
742 }
743 },
744 {
745 "table": {
746 "table_name": "t2",
747 "access_type": "ALL",
748 "rows_examined_per_scan": 5,
749 "rows_produced_per_join": 25,
750 "filtered": "100.00",
751 "using_join_buffer": "hash join",
752 "cost_info": {
753 "read_cost": "0.25",
754 "eval_cost": "2.50",
755 "prefix_cost": "3.50",
756 "data_read_per_join": "2K"
757 }
758 }
759 }
760 ]
761 }
762 }
763 }
764 Warnings:
765 Note 1003 /* select#1 */ select sql_big_result `test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`b`
766 select sql_big_result t1.b from t1, t2 group by t1.b;
767 b
768 a
769 b
770 c
771 explain format = json select sql_big_result distinct t1.a from t1, t2 group by t1.a, t1.b;
772 EXPLAIN
773 {
774 "query_block": {
775 "select_id": 1,
776 "cost_info": {
777 "query_cost": "3.50"
778 },
779 "duplicates_removal": {
780 "using_filesort": false,
781 "grouping_operation": {
782 "using_temporary_table": true,
783 "using_filesort": false,
784 "nested_loop": [
785 {
786 "table": {
787 "table_name": "t1",
788 "access_type": "ALL",
789 "rows_examined_per_scan": 5,
790 "rows_produced_per_join": 5,
791 "filtered": "100.00",
792 "cost_info": {
793 "read_cost": "0.25",
794 "eval_cost": "0.50",
795 "prefix_cost": "0.75",
796 "data_read_per_join": "440"
797 },
798 "used_columns": [
799 "a",
800 "b"
801 ]
802 }
803 },
804 {
805 "table": {
806 "table_name": "t2",
807 "access_type": "ALL",
808 "rows_examined_per_scan": 5,
809 "rows_produced_per_join": 25,
810 "filtered": "100.00",
811 "using_join_buffer": "hash join",
812 "cost_info": {
813 "read_cost": "0.25",
814 "eval_cost": "2.50",
815 "prefix_cost": "3.50",
816 "data_read_per_join": "2K"
817 }
818 }
819 }
820 ]
821 }
822 }
823 }
824 }
825 Warnings:
826 Note 1003 /* select#1 */ select distinct sql_big_result `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`,`test`.`t1`.`b`
827 select sql_big_result distinct t1.a from t1, t2 group by t1.a, t1.b;
828 a
829 1
830 2
831 3
832 4
833 explain format = json select t1.* from t1 inner join t2 on t1.a = t2.a order by t2.a;
834 EXPLAIN
835 {
836 "query_block": {
837 "select_id": 1,
838 "cost_info": {
839 "query_cost": "8.50"
840 },
841 "ordering_operation": {
842 "using_temporary_table": true,
843 "using_filesort": true,
844 "cost_info": {
845 "sort_cost": "5.00"
846 },
847 "nested_loop": [
848 {
849 "table": {
850 "table_name": "t1",
851 "access_type": "ALL",
852 "rows_examined_per_scan": 5,
853 "rows_produced_per_join": 5,
854 "filtered": "100.00",
855 "cost_info": {
856 "read_cost": "0.25",
857 "eval_cost": "0.50",
858 "prefix_cost": "0.75",
859 "data_read_per_join": "440"
860 },
861 "used_columns": [
862 "a",
863 "b"
864 ]
865 }
866 },
867 {
868 "table": {
869 "table_name": "t2",
870 "access_type": "ALL",
871 "rows_examined_per_scan": 5,
872 "rows_produced_per_join": 5,
873 "filtered": "20.00",
874 "using_join_buffer": "hash join",
875 "cost_info": {
876 "read_cost": "0.25",
877 "eval_cost": "0.50",
878 "prefix_cost": "3.50",
879 "data_read_per_join": "440"
880 },
881 "used_columns": [
882 "a"
883 ],
884 "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
885 }
886 }
887 ]
888 }
889 }
890 }
891 Warnings:
892 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) order by `test`.`t2`.`a`
893 select t1.* from t1 inner join t2 on t1.a = t2.a order by t2.a;
894 a b
895 2 b
896 3 c
897 3 c
898 4 c
899 4 c
900 explain format = json select t1.* from t1, t2 where t1.a = t2.a order by t1.a;
901 EXPLAIN
902 {
903 "query_block": {
904 "select_id": 1,
905 "cost_info": {
906 "query_cost": "8.50"
907 },
908 "ordering_operation": {
909 "using_temporary_table": true,
910 "using_filesort": true,
911 "cost_info": {
912 "sort_cost": "5.00"
913 },
914 "nested_loop": [
915 {
916 "table": {
917 "table_name": "t1",
918 "access_type": "ALL",
919 "rows_examined_per_scan": 5,
920 "rows_produced_per_join": 5,
921 "filtered": "100.00",
922 "cost_info": {
923 "read_cost": "0.25",
924 "eval_cost": "0.50",
925 "prefix_cost": "0.75",
926 "data_read_per_join": "440"
927 },
928 "used_columns": [
929 "a",
930 "b"
931 ]
932 }
933 },
934 {
935 "table": {
936 "table_name": "t2",
937 "access_type": "ALL",
938 "rows_examined_per_scan": 5,
939 "rows_produced_per_join": 5,
940 "filtered": "20.00",
941 "using_join_buffer": "hash join",
942 "cost_info": {
943 "read_cost": "0.25",
944 "eval_cost": "0.50",
945 "prefix_cost": "3.50",
946 "data_read_per_join": "440"
947 },
948 "used_columns": [
949 "a"
950 ],
951 "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
952 }
953 }
954 ]
955 }
956 }
957 }
958 Warnings:
959 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) order by `test`.`t1`.`a`
960 select t1.* from t1, t2 where t1.a = t2.a order by t1.a;
961 a b
962 2 b
963 3 c
964 3 c
965 4 c
966 4 c
967 explain format = json select t1.* from t1 inner join t2 where t1.a = t2.a group by t1.a;
968 EXPLAIN
969 {
970 "query_block": {
971 "select_id": 1,
972 "cost_info": {
973 "query_cost": "3.50"
974 },
975 "grouping_operation": {
976 "using_temporary_table": true,
977 "using_filesort": false,
978 "nested_loop": [
979 {
980 "table": {
981 "table_name": "t1",
982 "access_type": "ALL",
983 "rows_examined_per_scan": 5,
984 "rows_produced_per_join": 5,
985 "filtered": "100.00",
986 "cost_info": {
987 "read_cost": "0.25",
988 "eval_cost": "0.50",
989 "prefix_cost": "0.75",
990 "data_read_per_join": "440"
991 },
992 "used_columns": [
993 "a",
994 "b"
995 ]
996 }
997 },
998 {
999 "table": {
1000 "table_name": "t2",
1001 "access_type": "ALL",
1002 "rows_examined_per_scan": 5,
1003 "rows_produced_per_join": 5,
1004 "filtered": "20.00",
1005 "using_join_buffer": "hash join",
1006 "cost_info": {
1007 "read_cost": "0.25",
1008 "eval_cost": "0.50",
1009 "prefix_cost": "3.50",
1010 "data_read_per_join": "440"
1011 },
1012 "used_columns": [
1013 "a"
1014 ],
1015 "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
1016 }
1017 }
1018 ]
1019 }
1020 }
1021 }
1022 Warnings:
1023 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) group by `test`.`t1`.`a`
1024 select t1.* from t1 inner join t2 where t1.a = t2.a group by t1.a;
1025 a b
1026 2 b
1027 3 c
1028 4 c
1029 explain format = json select t1.* from t1 inner join t2 where t1.a = t2.a group by t2.a;
1030 EXPLAIN
1031 {
1032 "query_block": {
1033 "select_id": 1,
1034 "cost_info": {
1035 "query_cost": "3.50"
1036 },
1037 "grouping_operation": {
1038 "using_temporary_table": true,
1039 "using_filesort": false,
1040 "nested_loop": [
1041 {
1042 "table": {
1043 "table_name": "t1",
1044 "access_type": "ALL",
1045 "rows_examined_per_scan": 5,
1046 "rows_produced_per_join": 5,
1047 "filtered": "100.00",
1048 "cost_info": {
1049 "read_cost": "0.25",
1050 "eval_cost": "0.50",
1051 "prefix_cost": "0.75",
1052 "data_read_per_join": "440"
1053 },
1054 "used_columns": [
1055 "a",
1056 "b"
1057 ]
1058 }
1059 },
1060 {
1061 "table": {
1062 "table_name": "t2",
1063 "access_type": "ALL",
1064 "rows_examined_per_scan": 5,
1065 "rows_produced_per_join": 5,
1066 "filtered": "20.00",
1067 "using_join_buffer": "hash join",
1068 "cost_info": {
1069 "read_cost": "0.25",
1070 "eval_cost": "0.50",
1071 "prefix_cost": "3.50",
1072 "data_read_per_join": "440"
1073 },
1074 "used_columns": [
1075 "a"
1076 ],
1077 "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
1078 }
1079 }
1080 ]
1081 }
1082 }
1083 }
1084 Warnings:
1085 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) group by `test`.`t2`.`a`
1086 select t1.* from t1 inner join t2 where t1.a = t2.a group by t2.a;
1087 a b
1088 2 b
1089 3 c
1090 4 c
1091 explain format = json select a from t1 group by a order by a;
1092 EXPLAIN
1093 {
1094 "query_block": {
1095 "select_id": 1,
1096 "cost_info": {
1097 "query_cost": "5.75"
1098 },
1099 "ordering_operation": {
1100 "using_filesort": false,
1101 "grouping_operation": {
1102 "using_temporary_table": true,
1103 "using_filesort": true,
1104 "cost_info": {
1105 "sort_cost": "5.00"
1106 },
1107 "table": {
1108 "table_name": "t1",
1109 "access_type": "ALL",
1110 "rows_examined_per_scan": 5,
1111 "rows_produced_per_join": 5,
1112 "filtered": "100.00",
1113 "cost_info": {
1114 "read_cost": "0.25",
1115 "eval_cost": "0.50",
1116 "prefix_cost": "0.75",
1117 "data_read_per_join": "440"
1118 },
1119 "used_columns": [
1120 "a"
1121 ]
1122 }
1123 }
1124 }
1125 }
1126 }
1127 Warnings:
1128 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` order by `test`.`t1`.`a`
1129 select a from t1 group by a order by a;
1130 a
1131 1
1132 2
1133 3
1134 4
1135 explain format = json select b from t1 group by b order by a;
1136 EXPLAIN
1137 {
1138 "query_block": {
1139 "select_id": 1,
1140 "cost_info": {
1141 "query_cost": "0.75"
1142 },
1143 "ordering_operation": {
1144 "using_filesort": true,
1145 "grouping_operation": {
1146 "using_temporary_table": true,
1147 "using_filesort": false,
1148 "table": {
1149 "table_name": "t1",
1150 "access_type": "ALL",
1151 "rows_examined_per_scan": 5,
1152 "rows_produced_per_join": 5,
1153 "filtered": "100.00",
1154 "cost_info": {
1155 "read_cost": "0.25",
1156 "eval_cost": "0.50",
1157 "prefix_cost": "0.75",
1158 "data_read_per_join": "440"
1159 },
1160 "used_columns": [
1161 "a",
1162 "b"
1163 ]
1164 }
1165 }
1166 }
1167 }
1168 }
1169 Warnings:
1170 Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` group by `test`.`t1`.`b` order by `test`.`t1`.`a`
1171 select b from t1 group by b order by a;
1172 b
1173 a
1174 b
1175 c
1176 explain format = json select distinct t1.a from t1, t2 where t1.b = t2.b;
1177 EXPLAIN
1178 {
1179 "query_block": {
1180 "select_id": 1,
1181 "cost_info": {
1182 "query_cost": "3.50"
1183 },
1184 "duplicates_removal": {
1185 "using_temporary_table": true,
1186 "using_filesort": false,
1187 "nested_loop": [
1188 {
1189 "table": {
1190 "table_name": "t1",
1191 "access_type": "ALL",
1192 "rows_examined_per_scan": 5,
1193 "rows_produced_per_join": 5,
1194 "filtered": "100.00",
1195 "cost_info": {
1196 "read_cost": "0.25",
1197 "eval_cost": "0.50",
1198 "prefix_cost": "0.75",
1199 "data_read_per_join": "440"
1200 },
1201 "used_columns": [
1202 "a",
1203 "b"
1204 ]
1205 }
1206 },
1207 {
1208 "table": {
1209 "table_name": "t2",
1210 "access_type": "ALL",
1211 "rows_examined_per_scan": 5,
1212 "rows_produced_per_join": 5,
1213 "filtered": "20.00",
1214 "distinct": true,
1215 "using_join_buffer": "hash join",
1216 "cost_info": {
1217 "read_cost": "0.25",
1218 "eval_cost": "0.50",
1219 "prefix_cost": "3.50",
1220 "data_read_per_join": "440"
1221 },
1222 "used_columns": [
1223 "b"
1224 ],
1225 "attached_condition": "(`test`.`t2`.`b` = `test`.`t1`.`b`)"
1226 }
1227 }
1228 ]
1229 }
1230 }
1231 }
1232 Warnings:
1233 Note 1003 /* select#1 */ select distinct `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`b` = `test`.`t1`.`b`)
1234 select distinct t1.a from t1, t2 where t1.b = t2.b;
1235 a
1236 explain format = json select distinct a from t1 group by b,a;
1237 EXPLAIN
1238 {
1239 "query_block": {
1240 "select_id": 1,
1241 "cost_info": {
1242 "query_cost": "0.75"
1243 },
1244 "duplicates_removal": {
1245 "using_filesort": false,
1246 "grouping_operation": {
1247 "using_temporary_table": true,
1248 "using_filesort": false,
1249 "table": {
1250 "table_name": "t1",
1251 "access_type": "ALL",
1252 "rows_examined_per_scan": 5,
1253 "rows_produced_per_join": 5,
1254 "filtered": "100.00",
1255 "cost_info": {
1256 "read_cost": "0.25",
1257 "eval_cost": "0.50",
1258 "prefix_cost": "0.75",
1259 "data_read_per_join": "440"
1260 },
1261 "used_columns": [
1262 "a",
1263 "b"
1264 ]
1265 }
1266 }
1267 }
1268 }
1269 }
1270 Warnings:
1271 Note 1003 /* select#1 */ select distinct `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`b`,`test`.`t1`.`a`
1272 select distinct a from t1 group by b,a;
1273 a
1274 1
1275 2
1276 3
1277 4
1278 set @old_sort_buffer_size = @@sort_buffer_size;
1279 set @@sort_buffer_size = 32804;
1280 explain format = json select distinct b from t3 group by a having count(*) > 1;
1281 EXPLAIN
1282 {
1283 "query_block": {
1284 "select_id": 1,
1285 "cost_info": {
1286 "query_cost": "0.35"
1287 },
1288 "duplicates_removal": {
1289 "using_filesort": false,
1290 "grouping_operation": {
1291 "using_temporary_table": true,
1292 "using_filesort": false,
1293 "table": {
1294 "table_name": "t3",
1295 "access_type": "ALL",
1296 "rows_examined_per_scan": 1,
1297 "rows_produced_per_join": 1,
1298 "filtered": "100.00",
1299 "cost_info": {
1300 "read_cost": "0.25",
1301 "eval_cost": "0.10",
1302 "prefix_cost": "0.35",
1303 "data_read_per_join": "88"
1304 },
1305 "used_columns": [
1306 "a",
1307 "b"
1308 ]
1309 }
1310 }
1311 }
1312 }
1313 }
1314 Warnings:
1315 Note 1003 /* select#1 */ select distinct `test`.`t3`.`b` AS `b` from `test`.`t3` group by `test`.`t3`.`a` having (count(0) > 1)
1316 select distinct b from t3 group by a having count(*) > 1;
1317 b
1318 select distinct b from t3 group by a;
1319 b
1320 set @@sort_buffer_size = @old_sort_buffer_size;
1321 explain format = json select distinct t4.a,e from t4, t1 order by t4.b;
1322 EXPLAIN
1323 {
1324 "query_block": {
1325 "select_id": 1,
1326 "cost_info": {
1327 "query_cost": "6.11"
1328 },
1329 "ordering_operation": {
1330 "using_temporary_table": true,
1331 "using_filesort": true,
1332 "duplicates_removal": {
1333 "using_temporary_table": true,
1334 "using_filesort": false,
1335 "cost_info": {
1336 "sort_cost": "5.00"
1337 },
1338 "nested_loop": [
1339 {
1340 "table": {
1341 "table_name": "t4",
1342 "access_type": "ALL",
1343 "rows_examined_per_scan": 1,
1344 "rows_produced_per_join": 1,
1345 "filtered": "100.00",
1346 "cost_info": {
1347 "read_cost": "0.25",
1348 "eval_cost": "0.10",
1349 "prefix_cost": "0.35",
1350 "data_read_per_join": "32"
1351 },
1352 "used_columns": [
1353 "a",
1354 "b",
1355 "e"
1356 ]
1357 }
1358 },
1359 {
1360 "table": {
1361 "table_name": "t1",
1362 "access_type": "ALL",
1363 "rows_examined_per_scan": 5,
1364 "rows_produced_per_join": 5,
1365 "filtered": "100.00",
1366 "distinct": true,
1367 "using_join_buffer": "hash join",
1368 "cost_info": {
1369 "read_cost": "0.27",
1370 "eval_cost": "0.50",
1371 "prefix_cost": "1.12",
1372 "data_read_per_join": "440"
1373 }
1374 }
1375 }
1376 ]
1377 }
1378 }
1379 }
1380 }
1381 Warnings:
1382 Note 1003 /* select#1 */ select distinct `test`.`t4`.`a` AS `a`,`test`.`t4`.`e` AS `e` from `test`.`t4` join `test`.`t1` order by `test`.`t4`.`b`
1383 select distinct t4.a,e from t4, t1 order by t4.b;
1384 a e
1385 1 5
1386 explain format = json select * from t1 union all select * from t2;
1387 EXPLAIN
1388 {
1389 "query_block": {
1390 "union_result": {
1391 "using_temporary_table": false,
1392 "query_specifications": [
1393 {
1394 "dependent": false,
1395 "cacheable": true,
1396 "query_block": {
1397 "select_id": 1,
1398 "cost_info": {
1399 "query_cost": "0.75"
1400 },
1401 "table": {
1402 "table_name": "t1",
1403 "access_type": "ALL",
1404 "rows_examined_per_scan": 5,
1405 "rows_produced_per_join": 5,
1406 "filtered": "100.00",
1407 "cost_info": {
1408 "read_cost": "0.25",
1409 "eval_cost": "0.50",
1410 "prefix_cost": "0.75",
1411 "data_read_per_join": "440"
1412 },
1413 "used_columns": [
1414 "a",
1415 "b"
1416 ]
1417 }
1418 }
1419 },
1420 {
1421 "dependent": false,
1422 "cacheable": true,
1423 "query_block": {
1424 "select_id": 2,
1425 "cost_info": {
1426 "query_cost": "0.75"
1427 },
1428 "table": {
1429 "table_name": "t2",
1430 "access_type": "ALL",
1431 "rows_examined_per_scan": 5,
1432 "rows_produced_per_join": 5,
1433 "filtered": "100.00",
1434 "cost_info": {
1435 "read_cost": "0.25",
1436 "eval_cost": "0.50",
1437 "prefix_cost": "0.75",
1438 "data_read_per_join": "440"
1439 },
1440 "used_columns": [
1441 "a",
1442 "b"
1443 ]
1444 }
1445 }
1446 }
1447 ]
1448 }
1449 }
1450 }
1451 Warnings:
1452 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union all /* select#2 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`
1453 select * from t1 union all select * from t2;
1454 a b
1455 1 a
1456 2 b
1457 3 c
1458 3 c
1459 4 c
1460 2 d
1461 3 e
1462 4 f
1463 4 f
1464 5 e
1465 explain format = json select * from t1 union select * from t2 order by b;
1466 EXPLAIN
1467 {
1468 "query_block": {
1469 "union_result": {
1470 "using_temporary_table": true,
1471 "select_id": 3,
1472 "table_name": "<union1,2>",
1473 "access_type": "ALL",
1474 "using_filesort": true,
1475 "query_specifications": [
1476 {
1477 "dependent": false,
1478 "cacheable": true,
1479 "query_block": {
1480 "select_id": 1,
1481 "cost_info": {
1482 "query_cost": "0.75"
1483 },
1484 "table": {
1485 "table_name": "t1",
1486 "access_type": "ALL",
1487 "rows_examined_per_scan": 5,
1488 "rows_produced_per_join": 5,
1489 "filtered": "100.00",
1490 "cost_info": {
1491 "read_cost": "0.25",
1492 "eval_cost": "0.50",
1493 "prefix_cost": "0.75",
1494 "data_read_per_join": "440"
1495 },
1496 "used_columns": [
1497 "a",
1498 "b"
1499 ]
1500 }
1501 }
1502 },
1503 {
1504 "dependent": false,
1505 "cacheable": true,
1506 "query_block": {
1507 "select_id": 2,
1508 "cost_info": {
1509 "query_cost": "0.75"
1510 },
1511 "table": {
1512 "table_name": "t2",
1513 "access_type": "ALL",
1514 "rows_examined_per_scan": 5,
1515 "rows_produced_per_join": 5,
1516 "filtered": "100.00",
1517 "cost_info": {
1518 "read_cost": "0.25",
1519 "eval_cost": "0.50",
1520 "prefix_cost": "0.75",
1521 "data_read_per_join": "440"
1522 },
1523 "used_columns": [
1524 "a",
1525 "b"
1526 ]
1527 }
1528 }
1529 }
1530 ]
1531 }
1532 }
1533 }
1534 Warnings:
1535 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` order by `b`
1536 select * from t1 union select * from t2 order by b;
1537 a b
1538 1 a
1539 2 b
1540 3 c
1541 4 c
1542 2 d
1543 3 e
1544 5 e
1545 4 f
1546 explain format = json select tt.* from (select * from t1) as tt;
1547 EXPLAIN
1548 {
1549 "query_block": {
1550 "select_id": 1,
1551 "cost_info": {
1552 "query_cost": "1.00"
1553 },
1554 "table": {
1555 "table_name": "tt",
1556 "access_type": "ALL",
1557 "rows_examined_per_scan": 5,
1558 "rows_produced_per_join": 5,
1559 "filtered": "100.00",
1560 "cost_info": {
1561 "read_cost": "0.50",
1562 "eval_cost": "0.50",
1563 "prefix_cost": "1.00",
1564 "data_read_per_join": "480"
1565 },
1566 "used_columns": [
1567 "a",
1568 "b"
1569 ],
1570 "materialized_from_subquery": {
1571 "using_temporary_table": true,
1572 "dependent": false,
1573 "cacheable": true,
1574 "query_block": {
1575 "select_id": 2,
1576 "cost_info": {
1577 "query_cost": "0.75"
1578 },
1579 "table": {
1580 "table_name": "t1",
1581 "access_type": "ALL",
1582 "rows_examined_per_scan": 5,
1583 "rows_produced_per_join": 5,
1584 "filtered": "100.00",
1585 "cost_info": {
1586 "read_cost": "0.25",
1587 "eval_cost": "0.50",
1588 "prefix_cost": "0.75",
1589 "data_read_per_join": "440"
1590 },
1591 "used_columns": [
1592 "a",
1593 "b"
1594 ]
1595 }
1596 }
1597 }
1598 }
1599 }
1600 }
1601 Warnings:
1602 Note 1003 /* select#1 */ select `tt`.`a` AS `a`,`tt`.`b` AS `b` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) `tt`
1603 select tt.* from (select * from t1) as tt;
1604 a b
1605 1 a
1606 2 b
1607 3 c
1608 3 c
1609 4 c
1610 select * from t5;
1611 a b
1612 1 7
1613 1 8
1614 explain format = json select b from (select b from t5 where b > 5) tt;
1615 EXPLAIN
1616 {
1617 "query_block": {
1618 "select_id": 1,
1619 "cost_info": {
1620 "query_cost": "0.70"
1621 },
1622 "table": {
1623 "table_name": "tt",
1624 "access_type": "ALL",
1625 "rows_examined_per_scan": 2,
1626 "rows_produced_per_join": 2,
1627 "filtered": "100.00",
1628 "cost_info": {
1629 "read_cost": "0.50",
1630 "eval_cost": "0.20",
1631 "prefix_cost": "0.70",
1632 "data_read_per_join": "32"
1633 },
1634 "used_columns": [
1635 "b"
1636 ],
1637 "materialized_from_subquery": {
1638 "using_temporary_table": true,
1639 "dependent": false,
1640 "cacheable": true,
1641 "query_block": {
1642 "select_id": 2,
1643 "cost_info": {
1644 "query_cost": "0.45"
1645 },
1646 "table": {
1647 "table_name": "t5",
1648 "access_type": "index",
1649 "possible_keys": [
1650 "PRIMARY"
1651 ],
1652 "key": "PRIMARY",
1653 "used_key_parts": [
1654 "b"
1655 ],
1656 "key_length": "4",
1657 "rows_examined_per_scan": 2,
1658 "rows_produced_per_join": 2,
1659 "filtered": "100.00",
1660 "using_index": true,
1661 "cost_info": {
1662 "read_cost": "0.25",
1663 "eval_cost": "0.20",
1664 "prefix_cost": "0.45",
1665 "data_read_per_join": "32"
1666 },
1667 "used_columns": [
1668 "b"
1669 ],
1670 "attached_condition": "(`test`.`t5`.`b` > 5)"
1671 }
1672 }
1673 }
1674 }
1675 }
1676 }
1677 Warnings:
1678 Note 1003 /* select#1 */ select `tt`.`b` AS `b` from (/* select#2 */ select `test`.`t5`.`b` AS `b` from `test`.`t5` where (`test`.`t5`.`b` > 5)) `tt`
1679 select b from (select b from t5 where b > 5) tt;
1680 b
1681 7
1682 8
1683 explain format = json select * from (select b from t1) as t1, (select b from t2) as t2;
1684 EXPLAIN
1685 "b"
1686 "b"
1687 "data_read_per_join": "440"
1688 "data_read_per_join": "440"
1689 "eval_cost": "0.50",
1690 "eval_cost": "0.50",
1691 "prefix_cost": "0.75",
1692 "prefix_cost": "0.75",
1693 "read_cost": "0.25",
1694 "read_cost": "0.25",
1695 "access_type": "ALL",
1696 "access_type": "ALL",
1697 "cost_info": {
1698 "cost_info": {
1699 "filtered": "100.00",
1700 "filtered": "100.00",
1701 "query_cost": "0.75"
1702 "query_cost": "0.75"
1703 "rows_examined_per_scan": 5,
1704 "rows_examined_per_scan": 5,
1705 "rows_produced_per_join": 5,
1706 "rows_produced_per_join": 5,
1707 "table_name": "t1",
1708 "table_name": "t2",
1709 "used_columns": [
1710 "used_columns": [
1711 ]
1712 ]
1713 },
1714 },
1715 "cost_info": {
1716 "cost_info": {
1717 "select_id": 2,
1718 "select_id": 3,
1719 "table": {
1720 "table": {
1721 }
1722 }
1723 },
1724 },
1725 "b"
1726 "b"
1727 "cacheable": true,
1728 "cacheable": true,
1729 "data_read_per_join": "2K"
1730 "data_read_per_join": "440"
1731 "dependent": false,
1732 "dependent": false,
1733 "eval_cost": "0.50",
1734 "eval_cost": "2.50",
1735 "prefix_cost": "1.00",
1736 "prefix_cost": "4.00",
1737 "query_block": {
1738 "query_block": {
1739 "read_cost": "0.50",
1740 "read_cost": "0.50",
1741 "using_temporary_table": true,
1742 "using_temporary_table": true,
1743 }
1744 }
1745 "access_type": "ALL",
1746 "access_type": "ALL",
1747 "cost_info": {
1748 "cost_info": {
1749 "filtered": "100.00",
1750 "filtered": "100.00",
1751 "materialized_from_subquery": {
1752 "materialized_from_subquery": {
1753 "rows_examined_per_scan": 5,
1754 "rows_examined_per_scan": 5,
1755 "rows_produced_per_join": 25,
1756 "rows_produced_per_join": 5,
1757 "table_name": "t1",
1758 "table_name": "t2",
1759 "used_columns": [
1760 "used_columns": [
1761 "using_join_buffer": "hash join",
1762 ],
1763 ],
1764 }
1765 }
1766 },
1767 },
1768 "table": {
1769 "table": {
1770 }
1771 }
1772 "query_cost": "4.00"
1773 {
1774 {
1775 }
1776 },
1777 "cost_info": {
1778 "nested_loop": [
1779 "select_id": 1,
1780 ]
1781 },
1782 "query_block": {
1783 }
1784 Note 1003 /* select#1 */ select `t1`.`b` AS `b`,`t2`.`b` AS `b` from (/* select#2 */ select `test`.`t1`.`b` AS `b` from `test`.`t1`) `t1` join (/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t2`
1785 Warnings:
1786 {
1787 }
1788 select * from (select b from t1) as t1, (select b from t2) as t2;
1789 b b
1790 a d
1791 a e
1792 a e
1793 a f
1794 a f
1795 b d
1796 b e
1797 b e
1798 b f
1799 b f
1800 c d
1801 c d
1802 c d
1803 c e
1804 c e
1805 c e
1806 c e
1807 c e
1808 c e
1809 c f
1810 c f
1811 c f
1812 c f
1813 c f
1814 c f
1815 explain format = json select * from t1, (select * from t6) as t where t.a =5;
1816 EXPLAIN
1817 {
1818 "query_block": {
1819 "select_id": 1,
1820 "cost_info": {
1821 "query_cost": "1.95"
1822 },
1823 "nested_loop": [
1824 {
1825 "table": {
1826 "table_name": "t",
1827 "access_type": "ALL",
1828 "rows_examined_per_scan": 2,
1829 "rows_produced_per_join": 2,
1830 "filtered": "100.00",
1831 "cost_info": {
1832 "read_cost": "0.50",
1833 "eval_cost": "0.20",
1834 "prefix_cost": "0.70",
1835 "data_read_per_join": "48"
1836 },
1837 "used_columns": [
1838 "a",
1839 "b",
1840 "c",
1841 "d"
1842 ],
1843 "materialized_from_subquery": {
1844 "using_temporary_table": true,
1845 "dependent": false,
1846 "cacheable": true,
1847 "query_block": {
1848 "select_id": 2,
1849 "cost_info": {
1850 "query_cost": "0.35"
1851 },
1852 "table": {
1853 "table_name": "t6",
1854 "access_type": "ref",
1855 "possible_keys": [
1856 "t6_indx1"
1857 ],
1858 "key": "t6_indx1",
1859 "used_key_parts": [
1860 "a"
1861 ],
1862 "key_length": "5",
1863 "ref": [
1864 "const"
1865 ],
1866 "rows_examined_per_scan": 1,
1867 "rows_produced_per_join": 1,
1868 "filtered": "100.00",
1869 "cost_info": {
1870 "read_cost": "0.25",
1871 "eval_cost": "0.10",
1872 "prefix_cost": "0.35",
1873 "data_read_per_join": "24"
1874 },
1875 "used_columns": [
1876 "a",
1877 "b",
1878 "c",
1879 "d"
1880 ]
1881 }
1882 }
1883 }
1884 }
1885 },
1886 {
1887 "table": {
1888 "table_name": "t1",
1889 "access_type": "ALL",
1890 "rows_examined_per_scan": 5,
1891 "rows_produced_per_join": 10,
1892 "filtered": "100.00",
1893 "using_join_buffer": "hash join",
1894 "cost_info": {
1895 "read_cost": "0.25",
1896 "eval_cost": "1.00",
1897 "prefix_cost": "1.95",
1898 "data_read_per_join": "880"
1899 },
1900 "used_columns": [
1901 "a",
1902 "b"
1903 ]
1904 }
1905 }
1906 ]
1907 }
1908 }
1909 Warnings:
1910 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`t`.`a` AS `a`,`t`.`b` AS `b`,`t`.`c` AS `c`,`t`.`d` AS `d` from `test`.`t1` join (/* select#2 */ select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t6`.`c` AS `c`,`test`.`t6`.`d` AS `d` from `test`.`t6` where (`test`.`t6`.`a` = 5)) `t`
1911 select * from t1, (select * from t6) as t where t.a =5;
1912 a b a b c d
1913 explain format = json select * from t1 where t1.a in (select a from t2) ;
1914 EXPLAIN
1915 {
1916 "query_block": {
1917 "select_id": 1,
1918 "cost_info": {
1919 "query_cost": "3.50"
1920 },
1921 "nested_loop": [
1922 {
1923 "table": {
1924 "table_name": "t1",
1925 "access_type": "ALL",
1926 "rows_examined_per_scan": 5,
1927 "rows_produced_per_join": 5,
1928 "filtered": "100.00",
1929 "cost_info": {
1930 "read_cost": "0.25",
1931 "eval_cost": "0.50",
1932 "prefix_cost": "0.75",
1933 "data_read_per_join": "440"
1934 },
1935 "used_columns": [
1936 "a",
1937 "b"
1938 ],
1939 "attached_condition": "(`test`.`t1`.`a` is not null)"
1940 }
1941 },
1942 {
1943 "table": {
1944 "table_name": "<subquery2>",
1945 "access_type": "eq_ref",
1946 "key": "<auto_distinct_key>",
1947 "key_length": "5",
1948 "ref": [
1949 "test.t1.a"
1950 ],
1951 "rows_examined_per_scan": 1,
1952 "materialized_from_subquery": {
1953 "using_temporary_table": true,
1954 "query_block": {
1955 "table": {
1956 "table_name": "t2",
1957 "access_type": "ALL",
1958 "rows_examined_per_scan": 5,
1959 "rows_produced_per_join": 5,
1960 "filtered": "100.00",
1961 "cost_info": {
1962 "read_cost": "0.25",
1963 "eval_cost": "0.50",
1964 "prefix_cost": "0.75",
1965 "data_read_per_join": "440"
1966 },
1967 "used_columns": [
1968 "a"
1969 ]
1970 }
1971 }
1972 }
1973 }
1974 }
1975 ]
1976 }
1977 }
1978 Warnings:
1979 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where (`<subquery2>`.`a` = `test`.`t1`.`a`)
1980 select * from t1 where t1.a in (select a from t2) ;
1981 a b
1982 2 b
1983 3 c
1984 3 c
1985 4 c
1986 set optimizer_switch='materialization=off';
1987 set optimizer_switch='firstmatch=off';
1988 set optimizer_switch='loosescan=off';
1989 explain format = json select * from t1 where t1.a in (select a from t2) ;
1990 EXPLAIN
1991 {
1992 "query_block": {
1993 "select_id": 1,
1994 "cost_info": {
1995 "query_cost": "5.50"
1996 },
1997 "duplicates_removal": {
1998 "using_temporary_table": true,
1999 "nested_loop": [
2000 {
2001 "table": {
2002 "table_name": "t1",
2003 "access_type": "ALL",
2004 "rows_examined_per_scan": 5,
2005 "rows_produced_per_join": 5,
2006 "filtered": "100.00",
2007 "cost_info": {
2008 "read_cost": "0.25",
2009 "eval_cost": "0.50",
2010 "prefix_cost": "0.75",
2011 "data_read_per_join": "440"
2012 },
2013 "used_columns": [
2014 "a",
2015 "b"
2016 ]
2017 }
2018 },
2019 {
2020 "table": {
2021 "table_name": "t2",
2022 "access_type": "ALL",
2023 "rows_examined_per_scan": 5,
2024 "rows_produced_per_join": 5,
2025 "filtered": "20.00",
2026 "using_join_buffer": "hash join",
2027 "cost_info": {
2028 "read_cost": "0.25",
2029 "eval_cost": "0.50",
2030 "prefix_cost": "5.50",
2031 "data_read_per_join": "440"
2032 },
2033 "used_columns": [
2034 "a"
2035 ],
2036 "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
2037 }
2038 }
2039 ]
2040 }
2041 }
2042 }
2043 Warnings:
2044 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`a` = `test`.`t1`.`a`)
2045 select * from t1 where t1.a in (select a from t2) ;
2046 a b
2047 2 b
2048 3 c
2049 3 c
2050 4 c
2051 set optimizer_switch='default,derived_merge=off';
2052 explain format = json update t1, t2 set t1.a = 9, t2.a = 10 where t1.b = 'a';
2053 EXPLAIN
2054 {
2055 "query_block": {
2056 "select_id": 1,
2057 "cost_info": {
2058 "query_cost": "1.50"
2059 },
2060 "nested_loop": [
2061 {
2062 "table": {
2063 "update": true,
2064 "table_name": "t1",
2065 "access_type": "ALL",
2066 "rows_examined_per_scan": 5,
2067 "rows_produced_per_join": 1,
2068 "filtered": "20.00",
2069 "cost_info": {
2070 "read_cost": "0.65",
2071 "eval_cost": "0.10",
2072 "prefix_cost": "0.75",
2073 "data_read_per_join": "88"
2074 },
2075 "used_columns": [
2076 "a",
2077 "b"
2078 ],
2079 "attached_condition": "(`test`.`t1`.`b` = 'a')"
2080 }
2081 },
2082 {
2083 "table": {
2084 "update": true,
2085 "table_name": "t2",
2086 "access_type": "ALL",
2087 "rows_examined_per_scan": 5,
2088 "rows_produced_per_join": 5,
2089 "filtered": "100.00",
2090 "cost_info": {
2091 "read_cost": "0.25",
2092 "eval_cost": "0.50",
2093 "prefix_cost": "1.50",
2094 "data_read_per_join": "440"
2095 },
2096 "used_columns": [
2097 "a",
2098 "b"
2099 ]
2100 }
2101 }
2102 ]
2103 }
2104 }
2105 Warnings:
2106 Note 1003 update `test`.`t1` join `test`.`t2` set `test`.`t1`.`a` = 9,`test`.`t2`.`a` = 10 where (`test`.`t1`.`b` = 'a')
2107 update t1, t2 set t1.a = 9, t2.a = 10 where t1.b = 'a';
2108 explain format = json select count(distinct a) from t1;
2109 EXPLAIN
2110 {
2111 "query_block": {
2112 "select_id": 1,
2113 "cost_info": {
2114 "query_cost": "0.75"
2115 },
2116 "table": {
2117 "table_name": "t1",
2118 "access_type": "ALL",
2119 "rows_examined_per_scan": 5,
2120 "rows_produced_per_join": 5,
2121 "filtered": "100.00",
2122 "cost_info": {
2123 "read_cost": "0.25",
2124 "eval_cost": "0.50",
2125 "prefix_cost": "0.75",
2126 "data_read_per_join": "440"
2127 },
2128 "used_columns": [
2129 "a"
2130 ]
2131 }
2132 }
2133 }
2134 Warnings:
2135 Note 1003 /* select#1 */ select count(distinct `test`.`t1`.`a`) AS `count(distinct a)` from `test`.`t1`
2136 select count(distinct a) from t1;
2137 count(distinct a)
2138 4
2139 explain format = json select b, group_concat(a) from t1;
2140 EXPLAIN
2141 {
2142 "query_block": {
2143 "select_id": 1,
2144 "cost_info": {
2145 "query_cost": "0.75"
2146 },
2147 "table": {
2148 "table_name": "t1",
2149 "access_type": "ALL",
2150 "rows_examined_per_scan": 5,
2151 "rows_produced_per_join": 5,
2152 "filtered": "100.00",
2153 "cost_info": {
2154 "read_cost": "0.25",
2155 "eval_cost": "0.50",
2156 "prefix_cost": "0.75",
2157 "data_read_per_join": "440"
2158 },
2159 "used_columns": [
2160 "a",
2161 "b"
2162 ]
2163 }
2164 }
2165 }
2166 Warnings:
2167 Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b`,group_concat(`test`.`t1`.`a` separator ',') AS `group_concat(a)` from `test`.`t1`
2168 select b, group_concat(a) from t1;
2169 b group_concat(a)
2170 a 9,2,3,3,4
2171 select * from t1;
2172 a b
2173 9 a
2174 2 b
2175 3 c
2176 3 c
2177 4 c
2178 explain format = json select b, count(a), sum(a), avg(a), std(a),
2179 min(a), max(a), bit_and(a), bit_or(a) from t1 group by b;
2180 EXPLAIN
2181 {
2182 "query_block": {
2183 "select_id": 1,
2184 "cost_info": {
2185 "query_cost": "0.75"
2186 },
2187 "grouping_operation": {
2188 "using_temporary_table": true,
2189 "using_filesort": false,
2190 "table": {
2191 "table_name": "t1",
2192 "access_type": "ALL",
2193 "rows_examined_per_scan": 5,
2194 "rows_produced_per_join": 5,
2195 "filtered": "100.00",
2196 "cost_info": {
2197 "read_cost": "0.25",
2198 "eval_cost": "0.50",
2199 "prefix_cost": "0.75",
2200 "data_read_per_join": "440"
2201 },
2202 "used_columns": [
2203 "a",
2204 "b"
2205 ]
2206 }
2207 }
2208 }
2209 }
2210 Warnings:
2211 Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b`,count(`test`.`t1`.`a`) AS `count(a)`,sum(`test`.`t1`.`a`) AS `sum(a)`,avg(`test`.`t1`.`a`) AS `avg(a)`,std(`test`.`t1`.`a`) AS `std(a)`,min(`test`.`t1`.`a`) AS `min(a)`,max(`test`.`t1`.`a`) AS `max(a)`,bit_and(`test`.`t1`.`a`) AS `bit_and(a)`,bit_or(`test`.`t1`.`a`) AS `bit_or(a)` from `test`.`t1` group by `test`.`t1`.`b`
2212 select b, count(a), sum(a), avg(a), std(a), min(a),
2213 max(a), bit_and(a), bit_or(a) from t1 group by b;
2214 b count(a) sum(a) avg(a) std(a) min(a) max(a) bit_and(a) bit_or(a)
2215 a 1 9 9.0000 0 9 9 9 9
2216 b 1 2 2.0000 0 2 2 2 2
2217 c 3 10 3.3333 0.4714045207910316 3 4 0 7
2218 select sql_big_result * from performance_schema.global_variables where variable_name like 'internal_tmp_mem_storage_engine';
2219 VARIABLE_NAME VARIABLE_VALUE
2220 internal_tmp_mem_storage_engine TempTable
2221 select 1 union distinct select 1 union all select 1;
2222 1
2223 1
2224 1
2225 select alias1.`col_int_key` AS field1 from (t8 as alias1) where (5) in
2226 (select 7 union select 1) order by alias1.`col_varchar_key` asc;
2227 field1
2228 select count(table2.col_date_key) as field1 from
2229 (t10 as table1 inner join
2230 ((t10 as table2 right join
2231 t9 as table3 on
2232 ((table3.pk = table2.pk) or
2233 (table3.pk = table2.col_int_key))))
2234 on ((table3.pk < table2.col_int_nokey)))
2235 order by field1 ASC;
2236 field1
2237 2200
2238 select distinct table1.col_varchar_1024_utf8_key as field1
2239 from t11 as table1 left join t11 as table2 on
2240 table1.col_varchar_1024_utf8 = table2.col_varchar_1024_latin1_key
2241 order by field1;
2242 field1
2243 YTLDF
2244 yvuwdllyhymuwyypoiuyeqbnaanbursgddnqhyvxnnpadsjiqdkajofferjfslatroorycttbiuhlubvmoikwwnalqyewgthdmfvsedatazrflwenzeiwdxtheqppbmqfydsawvanehgvfvbsqxzkqzhtjkybvikmpexipoddmoulsnzfchusfgkdazecevrpuakfioamyyqyribcnydfxmmzsxcpkydyczmeajaebsaskneaqwxjbpjptiwgiizoxfygnbjojpifpzldsdhrukdbwpzribxgafunywmmcfpjugozduylotsugqocbadhcnxjqlugncbebwsbobhxgghyyphxfqqnpliazusgjswynfotwdonpbkllfdbuoqceirksegypasuuxnrjumrkaryhkgkbdfgoqbdmrdrmpvwitqzswgossxltimkbtrxitnmigcxgfaqmrnv
2245 select t from t12 group by c;
2246 t
2247 NULL
2248
2249 1
2250 a
2251 hello
2252 HELLO MY
2253 select count(outr.col_varchar_nokey) AS x
2254 from t13 as outr2 left join
2255 (select * from t13) as outr
2256 on (outr2.col_time_key > outr.col_date_key)
2257 where outr.pk in
2258 (select innr.col_int_nokey AS y from
2259 (select * from t14) as innr2 left join
2260 (select * from t14) AS innr
2261 ON (innr2.col_int_nokey = innr.col_int_key)
2262 where innr.col_varchar_nokey > innr.col_varchar_key
2263 or not outr.pk > 9)
2264 or outr.col_varchar_key is null;
2265 x
2266 102
2267 SET @@optimizer_switch=@optimizer_switch_saved;
2268 SET GLOBAL innodb_fast_shutdown = 0;
2269 # restart: --innodb-read-only --big-tables=1
2270 SET @optimizer_switch_saved= @@optimizer_switch;
2271 SET @@optimizer_switch="derived_merge=off";
2272 explain format = json select sql_buffer_result * from t1;
2273 EXPLAIN
2274 {
2275 "query_block": {
2276 "select_id": 1,
2277 "cost_info": {
2278 "query_cost": "0.75"
2279 },
2280 "buffer_result": {
2281 "using_temporary_table": true,
2282 "table": {
2283 "table_name": "t1",
2284 "access_type": "ALL",
2285 "rows_examined_per_scan": 5,
2286 "rows_produced_per_join": 5,
2287 "filtered": "100.00",
2288 "cost_info": {
2289 "read_cost": "0.25",
2290 "eval_cost": "0.50",
2291 "prefix_cost": "0.75",
2292 "data_read_per_join": "440"
2293 },
2294 "used_columns": [
2295 "a",
2296 "b"
2297 ]
2298 }
2299 }
2300 }
2301 }
2302 Warnings:
2303 Note 1003 /* select#1 */ select sql_buffer_result `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
2304 select sql_buffer_result * from t1;
2305 a b
2306 9 a
2307 2 b
2308 3 c
2309 3 c
2310 4 c
2311 explain format = json select sql_buffer_result * from t2;
2312 EXPLAIN
2313 {
2314 "query_block": {
2315 "select_id": 1,
2316 "cost_info": {
2317 "query_cost": "0.75"
2318 },
2319 "buffer_result": {
2320 "using_temporary_table": true,
2321 "table": {
2322 "table_name": "t2",
2323 "access_type": "ALL",
2324 "rows_examined_per_scan": 5,
2325 "rows_produced_per_join": 5,
2326 "filtered": "100.00",
2327 "cost_info": {
2328 "read_cost": "0.25",
2329 "eval_cost": "0.50",
2330 "prefix_cost": "0.75",
2331 "data_read_per_join": "440"
2332 },
2333 "used_columns": [
2334 "a",
2335 "b"
2336 ]
2337 }
2338 }
2339 }
2340 }
2341 Warnings:
2342 Note 1003 /* select#1 */ select sql_buffer_result `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`
2343 select sql_buffer_result * from t2;
2344 a b
2345 10 d
2346 10 e
2347 10 f
2348 10 f
2349 10 e
2350 explain format = json select * from t1 left join t2 on t1.b = t2.b order by t1.a;
2351 EXPLAIN
2352 {
2353 "query_block": {
2354 "select_id": 1,
2355 "cost_info": {
2356 "query_cost": "28.50"
2357 },
2358 "ordering_operation": {
2359 "using_temporary_table": true,
2360 "using_filesort": true,
2361 "cost_info": {
2362 "sort_cost": "25.00"
2363 },
2364 "nested_loop": [
2365 {
2366 "table": {
2367 "table_name": "t1",
2368 "access_type": "ALL",
2369 "rows_examined_per_scan": 5,
2370 "rows_produced_per_join": 5,
2371 "filtered": "100.00",
2372 "cost_info": {
2373 "read_cost": "0.25",
2374 "eval_cost": "0.50",
2375 "prefix_cost": "0.75",
2376 "data_read_per_join": "440"
2377 },
2378 "used_columns": [
2379 "a",
2380 "b"
2381 ]
2382 }
2383 },
2384 {
2385 "table": {
2386 "table_name": "t2",
2387 "access_type": "ALL",
2388 "rows_examined_per_scan": 5,
2389 "rows_produced_per_join": 25,
2390 "filtered": "100.00",
2391 "using_join_buffer": "hash join",
2392 "cost_info": {
2393 "read_cost": "0.25",
2394 "eval_cost": "2.50",
2395 "prefix_cost": "3.50",
2396 "data_read_per_join": "2K"
2397 },
2398 "used_columns": [
2399 "a",
2400 "b"
2401 ],
2402 "attached_condition": "<if>(is_not_null_compl(t2), (`test`.`t2`.`b` = `test`.`t1`.`b`), true)"
2403 }
2404 }
2405 ]
2406 }
2407 }
2408 }
2409 Warnings:
2410 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`b` = `test`.`t1`.`b`)) where true order by `test`.`t1`.`a`
2411 select * from t1 left join t2 on t1.b = t2.b order by t1.a;
2412 a b a b
2413 2 b NULL NULL
2414 3 c NULL NULL
2415 3 c NULL NULL
2416 4 c NULL NULL
2417 9 a NULL NULL
2418 explain format = json select * from t1 order by func1(a);
2419 EXPLAIN
2420 {
2421 "query_block": {
2422 "select_id": 1,
2423 "cost_info": {
2424 "query_cost": "5.75"
2425 },
2426 "ordering_operation": {
2427 "using_temporary_table": true,
2428 "using_filesort": true,
2429 "cost_info": {
2430 "sort_cost": "5.00"
2431 },
2432 "table": {
2433 "table_name": "t1",
2434 "access_type": "ALL",
2435 "rows_examined_per_scan": 5,
2436 "rows_produced_per_join": 5,
2437 "filtered": "100.00",
2438 "cost_info": {
2439 "read_cost": "0.25",
2440 "eval_cost": "0.50",
2441 "prefix_cost": "0.75",
2442 "data_read_per_join": "440"
2443 },
2444 "used_columns": [
2445 "a",
2446 "b"
2447 ]
2448 }
2449 }
2450 }
2451 }
2452 Warnings:
2453 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `func1`(`test`.`t1`.`a`)
2454 select * from t1 order by func1(a);
2455 a b
2456 2 b
2457 3 c
2458 3 c
2459 4 c
2460 9 a
2461 explain format = json select a from t1 group by b order by sum(a);
2462 EXPLAIN
2463 {
2464 "query_block": {
2465 "select_id": 1,
2466 "cost_info": {
2467 "query_cost": "0.75"
2468 },
2469 "ordering_operation": {
2470 "using_filesort": true,
2471 "grouping_operation": {
2472 "using_temporary_table": true,
2473 "using_filesort": false,
2474 "table": {
2475 "table_name": "t1",
2476 "access_type": "ALL",
2477 "rows_examined_per_scan": 5,
2478 "rows_produced_per_join": 5,
2479 "filtered": "100.00",
2480 "cost_info": {
2481 "read_cost": "0.25",
2482 "eval_cost": "0.50",
2483 "prefix_cost": "0.75",
2484 "data_read_per_join": "440"
2485 },
2486 "used_columns": [
2487 "a",
2488 "b"
2489 ]
2490 }
2491 }
2492 }
2493 }
2494 }
2495 Warnings:
2496 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`b` order by sum(`test`.`t1`.`a`)
2497 select a from t1 group by b order by sum(a);
2498 a
2499 2
2500 9
2501 3
2502 explain format = json select a from t7 group by b order by sum(a);
2503 EXPLAIN
2504 {
2505 "query_block": {
2506 "select_id": 1,
2507 "cost_info": {
2508 "query_cost": "0.75"
2509 },
2510 "ordering_operation": {
2511 "using_temporary_table": true,
2512 "using_filesort": true,
2513 "grouping_operation": {
2514 "using_filesort": false,
2515 "table": {
2516 "table_name": "t7",
2517 "access_type": "index",
2518 "possible_keys": [
2519 "indx7"
2520 ],
2521 "key": "indx7",
2522 "used_key_parts": [
2523 "b"
2524 ],
2525 "key_length": "83",
2526 "rows_examined_per_scan": 5,
2527 "rows_produced_per_join": 5,
2528 "filtered": "100.00",
2529 "cost_info": {
2530 "read_cost": "0.25",
2531 "eval_cost": "0.50",
2532 "prefix_cost": "0.75",
2533 "data_read_per_join": "440"
2534 },
2535 "used_columns": [
2536 "a",
2537 "b"
2538 ]
2539 }
2540 }
2541 }
2542 }
2543 }
2544 Warnings:
2545 Note 1003 /* select#1 */ select `test`.`t7`.`a` AS `a` from `test`.`t7` group by `test`.`t7`.`b` order by sum(`test`.`t7`.`a`)
2546 select a from t7 group by b order by sum(a);
2547 a
2548 1
2549 2
2550 3
2551 explain format = json select sql_big_result distinct * from t1, t2;
2552 EXPLAIN
2553 {
2554 "query_block": {
2555 "select_id": 1,
2556 "cost_info": {
2557 "query_cost": "3.50"
2558 },
2559 "duplicates_removal": {
2560 "using_temporary_table": true,
2561 "using_filesort": false,
2562 "nested_loop": [
2563 {
2564 "table": {
2565 "table_name": "t1",
2566 "access_type": "ALL",
2567 "rows_examined_per_scan": 5,
2568 "rows_produced_per_join": 5,
2569 "filtered": "100.00",
2570 "cost_info": {
2571 "read_cost": "0.25",
2572 "eval_cost": "0.50",
2573 "prefix_cost": "0.75",
2574 "data_read_per_join": "440"
2575 },
2576 "used_columns": [
2577 "a",
2578 "b"
2579 ]
2580 }
2581 },
2582 {
2583 "table": {
2584 "table_name": "t2",
2585 "access_type": "ALL",
2586 "rows_examined_per_scan": 5,
2587 "rows_produced_per_join": 25,
2588 "filtered": "100.00",
2589 "using_join_buffer": "hash join",
2590 "cost_info": {
2591 "read_cost": "0.25",
2592 "eval_cost": "2.50",
2593 "prefix_cost": "3.50",
2594 "data_read_per_join": "2K"
2595 },
2596 "used_columns": [
2597 "a",
2598 "b"
2599 ]
2600 }
2601 }
2602 ]
2603 }
2604 }
2605 }
2606 Warnings:
2607 Note 1003 /* select#1 */ select distinct sql_big_result `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2`
2608 select sql_big_result distinct * from t1, t2;
2609 a b a b
2610 2 b 10 d
2611 2 b 10 e
2612 2 b 10 f
2613 3 c 10 d
2614 3 c 10 e
2615 3 c 10 f
2616 4 c 10 d
2617 4 c 10 e
2618 4 c 10 f
2619 9 a 10 d
2620 9 a 10 e
2621 9 a 10 f
2622 explain format = json select sql_big_result t1.b from t1, t2 group by t1.b;
2623 EXPLAIN
2624 {
2625 "query_block": {
2626 "select_id": 1,
2627 "cost_info": {
2628 "query_cost": "3.50"
2629 },
2630 "grouping_operation": {
2631 "using_temporary_table": true,
2632 "using_filesort": false,
2633 "nested_loop": [
2634 {
2635 "table": {
2636 "table_name": "t1",
2637 "access_type": "ALL",
2638 "rows_examined_per_scan": 5,
2639 "rows_produced_per_join": 5,
2640 "filtered": "100.00",
2641 "cost_info": {
2642 "read_cost": "0.25",
2643 "eval_cost": "0.50",
2644 "prefix_cost": "0.75",
2645 "data_read_per_join": "440"
2646 },
2647 "used_columns": [
2648 "b"
2649 ]
2650 }
2651 },
2652 {
2653 "table": {
2654 "table_name": "t2",
2655 "access_type": "ALL",
2656 "rows_examined_per_scan": 5,
2657 "rows_produced_per_join": 25,
2658 "filtered": "100.00",
2659 "using_join_buffer": "hash join",
2660 "cost_info": {
2661 "read_cost": "0.25",
2662 "eval_cost": "2.50",
2663 "prefix_cost": "3.50",
2664 "data_read_per_join": "2K"
2665 }
2666 }
2667 }
2668 ]
2669 }
2670 }
2671 }
2672 Warnings:
2673 Note 1003 /* select#1 */ select sql_big_result `test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`b`
2674 select sql_big_result t1.b from t1, t2 group by t1.b;
2675 b
2676 a
2677 b
2678 c
2679 explain format = json select sql_big_result distinct t1.a from t1, t2 group by t1.a, t1.b;
2680 EXPLAIN
2681 {
2682 "query_block": {
2683 "select_id": 1,
2684 "cost_info": {
2685 "query_cost": "3.50"
2686 },
2687 "duplicates_removal": {
2688 "using_filesort": false,
2689 "grouping_operation": {
2690 "using_temporary_table": true,
2691 "using_filesort": false,
2692 "nested_loop": [
2693 {
2694 "table": {
2695 "table_name": "t1",
2696 "access_type": "ALL",
2697 "rows_examined_per_scan": 5,
2698 "rows_produced_per_join": 5,
2699 "filtered": "100.00",
2700 "cost_info": {
2701 "read_cost": "0.25",
2702 "eval_cost": "0.50",
2703 "prefix_cost": "0.75",
2704 "data_read_per_join": "440"
2705 },
2706 "used_columns": [
2707 "a",
2708 "b"
2709 ]
2710 }
2711 },
2712 {
2713 "table": {
2714 "table_name": "t2",
2715 "access_type": "ALL",
2716 "rows_examined_per_scan": 5,
2717 "rows_produced_per_join": 25,
2718 "filtered": "100.00",
2719 "using_join_buffer": "hash join",
2720 "cost_info": {
2721 "read_cost": "0.25",
2722 "eval_cost": "2.50",
2723 "prefix_cost": "3.50",
2724 "data_read_per_join": "2K"
2725 }
2726 }
2727 }
2728 ]
2729 }
2730 }
2731 }
2732 }
2733 Warnings:
2734 Note 1003 /* select#1 */ select distinct sql_big_result `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`,`test`.`t1`.`b`
2735 select sql_big_result distinct t1.a from t1, t2 group by t1.a, t1.b;
2736 a
2737 2
2738 3
2739 4
2740 9
2741 explain format = json select t1.* from t1 inner join t2 on t1.a = t2.a order by t2.a;
2742 EXPLAIN
2743 {
2744 "query_block": {
2745 "select_id": 1,
2746 "cost_info": {
2747 "query_cost": "8.50"
2748 },
2749 "ordering_operation": {
2750 "using_temporary_table": true,
2751 "using_filesort": true,
2752 "cost_info": {
2753 "sort_cost": "5.00"
2754 },
2755 "nested_loop": [
2756 {
2757 "table": {
2758 "table_name": "t1",
2759 "access_type": "ALL",
2760 "rows_examined_per_scan": 5,
2761 "rows_produced_per_join": 5,
2762 "filtered": "100.00",
2763 "cost_info": {
2764 "read_cost": "0.25",
2765 "eval_cost": "0.50",
2766 "prefix_cost": "0.75",
2767 "data_read_per_join": "440"
2768 },
2769 "used_columns": [
2770 "a",
2771 "b"
2772 ]
2773 }
2774 },
2775 {
2776 "table": {
2777 "table_name": "t2",
2778 "access_type": "ALL",
2779 "rows_examined_per_scan": 5,
2780 "rows_produced_per_join": 5,
2781 "filtered": "20.00",
2782 "using_join_buffer": "hash join",
2783 "cost_info": {
2784 "read_cost": "0.25",
2785 "eval_cost": "0.50",
2786 "prefix_cost": "3.50",
2787 "data_read_per_join": "440"
2788 },
2789 "used_columns": [
2790 "a"
2791 ],
2792 "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
2793 }
2794 }
2795 ]
2796 }
2797 }
2798 }
2799 Warnings:
2800 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) order by `test`.`t2`.`a`
2801 select t1.* from t1 inner join t2 on t1.a = t2.a order by t2.a;
2802 a b
2803 explain format = json select t1.* from t1, t2 where t1.a = t2.a order by t1.a;
2804 EXPLAIN
2805 {
2806 "query_block": {
2807 "select_id": 1,
2808 "cost_info": {
2809 "query_cost": "8.50"
2810 },
2811 "ordering_operation": {
2812 "using_temporary_table": true,
2813 "using_filesort": true,
2814 "cost_info": {
2815 "sort_cost": "5.00"
2816 },
2817 "nested_loop": [
2818 {
2819 "table": {
2820 "table_name": "t1",
2821 "access_type": "ALL",
2822 "rows_examined_per_scan": 5,
2823 "rows_produced_per_join": 5,
2824 "filtered": "100.00",
2825 "cost_info": {
2826 "read_cost": "0.25",
2827 "eval_cost": "0.50",
2828 "prefix_cost": "0.75",
2829 "data_read_per_join": "440"
2830 },
2831 "used_columns": [
2832 "a",
2833 "b"
2834 ]
2835 }
2836 },
2837 {
2838 "table": {
2839 "table_name": "t2",
2840 "access_type": "ALL",
2841 "rows_examined_per_scan": 5,
2842 "rows_produced_per_join": 5,
2843 "filtered": "20.00",
2844 "using_join_buffer": "hash join",
2845 "cost_info": {
2846 "read_cost": "0.25",
2847 "eval_cost": "0.50",
2848 "prefix_cost": "3.50",
2849 "data_read_per_join": "440"
2850 },
2851 "used_columns": [
2852 "a"
2853 ],
2854 "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
2855 }
2856 }
2857 ]
2858 }
2859 }
2860 }
2861 Warnings:
2862 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) order by `test`.`t1`.`a`
2863 select t1.* from t1, t2 where t1.a = t2.a order by t1.a;
2864 a b
2865 explain format = json select t1.* from t1 inner join t2 where t1.a = t2.a group by t1.a;
2866 EXPLAIN
2867 {
2868 "query_block": {
2869 "select_id": 1,
2870 "cost_info": {
2871 "query_cost": "3.50"
2872 },
2873 "grouping_operation": {
2874 "using_temporary_table": true,
2875 "using_filesort": false,
2876 "nested_loop": [
2877 {
2878 "table": {
2879 "table_name": "t1",
2880 "access_type": "ALL",
2881 "rows_examined_per_scan": 5,
2882 "rows_produced_per_join": 5,
2883 "filtered": "100.00",
2884 "cost_info": {
2885 "read_cost": "0.25",
2886 "eval_cost": "0.50",
2887 "prefix_cost": "0.75",
2888 "data_read_per_join": "440"
2889 },
2890 "used_columns": [
2891 "a",
2892 "b"
2893 ]
2894 }
2895 },
2896 {
2897 "table": {
2898 "table_name": "t2",
2899 "access_type": "ALL",
2900 "rows_examined_per_scan": 5,
2901 "rows_produced_per_join": 5,
2902 "filtered": "20.00",
2903 "using_join_buffer": "hash join",
2904 "cost_info": {
2905 "read_cost": "0.25",
2906 "eval_cost": "0.50",
2907 "prefix_cost": "3.50",
2908 "data_read_per_join": "440"
2909 },
2910 "used_columns": [
2911 "a"
2912 ],
2913 "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
2914 }
2915 }
2916 ]
2917 }
2918 }
2919 }
2920 Warnings:
2921 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) group by `test`.`t1`.`a`
2922 select t1.* from t1 inner join t2 where t1.a = t2.a group by t1.a;
2923 a b
2924 explain format = json select t1.* from t1 inner join t2 where t1.a = t2.a group by t2.a;
2925 EXPLAIN
2926 {
2927 "query_block": {
2928 "select_id": 1,
2929 "cost_info": {
2930 "query_cost": "3.50"
2931 },
2932 "grouping_operation": {
2933 "using_temporary_table": true,
2934 "using_filesort": false,
2935 "nested_loop": [
2936 {
2937 "table": {
2938 "table_name": "t1",
2939 "access_type": "ALL",
2940 "rows_examined_per_scan": 5,
2941 "rows_produced_per_join": 5,
2942 "filtered": "100.00",
2943 "cost_info": {
2944 "read_cost": "0.25",
2945 "eval_cost": "0.50",
2946 "prefix_cost": "0.75",
2947 "data_read_per_join": "440"
2948 },
2949 "used_columns": [
2950 "a",
2951 "b"
2952 ]
2953 }
2954 },
2955 {
2956 "table": {
2957 "table_name": "t2",
2958 "access_type": "ALL",
2959 "rows_examined_per_scan": 5,
2960 "rows_produced_per_join": 5,
2961 "filtered": "20.00",
2962 "using_join_buffer": "hash join",
2963 "cost_info": {
2964 "read_cost": "0.25",
2965 "eval_cost": "0.50",
2966 "prefix_cost": "3.50",
2967 "data_read_per_join": "440"
2968 },
2969 "used_columns": [
2970 "a"
2971 ],
2972 "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
2973 }
2974 }
2975 ]
2976 }
2977 }
2978 }
2979 Warnings:
2980 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) group by `test`.`t2`.`a`
2981 select t1.* from t1 inner join t2 where t1.a = t2.a group by t2.a;
2982 a b
2983 explain format = json select a from t1 group by a order by a;
2984 EXPLAIN
2985 {
2986 "query_block": {
2987 "select_id": 1,
2988 "cost_info": {
2989 "query_cost": "5.75"
2990 },
2991 "ordering_operation": {
2992 "using_filesort": false,
2993 "grouping_operation": {
2994 "using_temporary_table": true,
2995 "using_filesort": true,
2996 "cost_info": {
2997 "sort_cost": "5.00"
2998 },
2999 "table": {
3000 "table_name": "t1",
3001 "access_type": "ALL",
3002 "rows_examined_per_scan": 5,
3003 "rows_produced_per_join": 5,
3004 "filtered": "100.00",
3005 "cost_info": {
3006 "read_cost": "0.25",
3007 "eval_cost": "0.50",
3008 "prefix_cost": "0.75",
3009 "data_read_per_join": "440"
3010 },
3011 "used_columns": [
3012 "a"
3013 ]
3014 }
3015 }
3016 }
3017 }
3018 }
3019 Warnings:
3020 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` order by `test`.`t1`.`a`
3021 select a from t1 group by a order by a;
3022 a
3023 2
3024 3
3025 4
3026 9
3027 explain format = json select b from t1 group by b order by a;
3028 EXPLAIN
3029 {
3030 "query_block": {
3031 "select_id": 1,
3032 "cost_info": {
3033 "query_cost": "0.75"
3034 },
3035 "ordering_operation": {
3036 "using_filesort": true,
3037 "grouping_operation": {
3038 "using_temporary_table": true,
3039 "using_filesort": false,
3040 "table": {
3041 "table_name": "t1",
3042 "access_type": "ALL",
3043 "rows_examined_per_scan": 5,
3044 "rows_produced_per_join": 5,
3045 "filtered": "100.00",
3046 "cost_info": {
3047 "read_cost": "0.25",
3048 "eval_cost": "0.50",
3049 "prefix_cost": "0.75",
3050 "data_read_per_join": "440"
3051 },
3052 "used_columns": [
3053 "a",
3054 "b"
3055 ]
3056 }
3057 }
3058 }
3059 }
3060 }
3061 Warnings:
3062 Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` group by `test`.`t1`.`b` order by `test`.`t1`.`a`
3063 select b from t1 group by b order by a;
3064 b
3065 b
3066 c
3067 a
3068 explain format = json select distinct t1.a from t1, t2 where t1.b = t2.b;
3069 EXPLAIN
3070 {
3071 "query_block": {
3072 "select_id": 1,
3073 "cost_info": {
3074 "query_cost": "3.50"
3075 },
3076 "duplicates_removal": {
3077 "using_temporary_table": true,
3078 "using_filesort": false,
3079 "nested_loop": [
3080 {
3081 "table": {
3082 "table_name": "t1",
3083 "access_type": "ALL",
3084 "rows_examined_per_scan": 5,
3085 "rows_produced_per_join": 5,
3086 "filtered": "100.00",
3087 "cost_info": {
3088 "read_cost": "0.25",
3089 "eval_cost": "0.50",
3090 "prefix_cost": "0.75",
3091 "data_read_per_join": "440"
3092 },
3093 "used_columns": [
3094 "a",
3095 "b"
3096 ]
3097 }
3098 },
3099 {
3100 "table": {
3101 "table_name": "t2",
3102 "access_type": "ALL",
3103 "rows_examined_per_scan": 5,
3104 "rows_produced_per_join": 5,
3105 "filtered": "20.00",
3106 "distinct": true,
3107 "using_join_buffer": "hash join",
3108 "cost_info": {
3109 "read_cost": "0.25",
3110 "eval_cost": "0.50",
3111 "prefix_cost": "3.50",
3112 "data_read_per_join": "440"
3113 },
3114 "used_columns": [
3115 "b"
3116 ],
3117 "attached_condition": "(`test`.`t2`.`b` = `test`.`t1`.`b`)"
3118 }
3119 }
3120 ]
3121 }
3122 }
3123 }
3124 Warnings:
3125 Note 1003 /* select#1 */ select distinct `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`b` = `test`.`t1`.`b`)
3126 select distinct t1.a from t1, t2 where t1.b = t2.b;
3127 a
3128 explain format = json select distinct a from t1 group by b,a;
3129 EXPLAIN
3130 {
3131 "query_block": {
3132 "select_id": 1,
3133 "cost_info": {
3134 "query_cost": "0.75"
3135 },
3136 "duplicates_removal": {
3137 "using_filesort": false,
3138 "grouping_operation": {
3139 "using_temporary_table": true,
3140 "using_filesort": false,
3141 "table": {
3142 "table_name": "t1",
3143 "access_type": "ALL",
3144 "rows_examined_per_scan": 5,
3145 "rows_produced_per_join": 5,
3146 "filtered": "100.00",
3147 "cost_info": {
3148 "read_cost": "0.25",
3149 "eval_cost": "0.50",
3150 "prefix_cost": "0.75",
3151 "data_read_per_join": "440"
3152 },
3153 "used_columns": [
3154 "a",
3155 "b"
3156 ]
3157 }
3158 }
3159 }
3160 }
3161 }
3162 Warnings:
3163 Note 1003 /* select#1 */ select distinct `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`b`,`test`.`t1`.`a`
3164 select distinct a from t1 group by b,a;
3165 a
3166 2
3167 3
3168 4
3169 9
3170 set @old_sort_buffer_size = @@sort_buffer_size;
3171 set @@sort_buffer_size = 32804;
3172 explain format = json select distinct b from t3 group by a having count(*) > 1;
3173 EXPLAIN
3174 {
3175 "query_block": {
3176 "select_id": 1,
3177 "cost_info": {
3178 "query_cost": "0.35"
3179 },
3180 "duplicates_removal": {
3181 "using_filesort": false,
3182 "grouping_operation": {
3183 "using_temporary_table": true,
3184 "using_filesort": false,
3185 "table": {
3186 "table_name": "t3",
3187 "access_type": "ALL",
3188 "rows_examined_per_scan": 1,
3189 "rows_produced_per_join": 1,
3190 "filtered": "100.00",
3191 "cost_info": {
3192 "read_cost": "0.25",
3193 "eval_cost": "0.10",
3194 "prefix_cost": "0.35",
3195 "data_read_per_join": "88"
3196 },
3197 "used_columns": [
3198 "a",
3199 "b"
3200 ]
3201 }
3202 }
3203 }
3204 }
3205 }
3206 Warnings:
3207 Note 1003 /* select#1 */ select distinct `test`.`t3`.`b` AS `b` from `test`.`t3` group by `test`.`t3`.`a` having (count(0) > 1)
3208 select distinct b from t3 group by a having count(*) > 1;
3209 b
3210 select distinct b from t3 group by a;
3211 b
3212 set @@sort_buffer_size = @old_sort_buffer_size;
3213 explain format = json select distinct t4.a,e from t4, t1 order by t4.b;
3214 EXPLAIN
3215 {
3216 "query_block": {
3217 "select_id": 1,
3218 "cost_info": {
3219 "query_cost": "6.11"
3220 },
3221 "ordering_operation": {
3222 "using_temporary_table": true,
3223 "using_filesort": true,
3224 "duplicates_removal": {
3225 "using_temporary_table": true,
3226 "using_filesort": false,
3227 "cost_info": {
3228 "sort_cost": "5.00"
3229 },
3230 "nested_loop": [
3231 {
3232 "table": {
3233 "table_name": "t4",
3234 "access_type": "ALL",
3235 "rows_examined_per_scan": 1,
3236 "rows_produced_per_join": 1,
3237 "filtered": "100.00",
3238 "cost_info": {
3239 "read_cost": "0.25",
3240 "eval_cost": "0.10",
3241 "prefix_cost": "0.35",
3242 "data_read_per_join": "32"
3243 },
3244 "used_columns": [
3245 "a",
3246 "b",
3247 "e"
3248 ]
3249 }
3250 },
3251 {
3252 "table": {
3253 "table_name": "t1",
3254 "access_type": "ALL",
3255 "rows_examined_per_scan": 5,
3256 "rows_produced_per_join": 5,
3257 "filtered": "100.00",
3258 "distinct": true,
3259 "using_join_buffer": "hash join",
3260 "cost_info": {
3261 "read_cost": "0.27",
3262 "eval_cost": "0.50",
3263 "prefix_cost": "1.12",
3264 "data_read_per_join": "440"
3265 }
3266 }
3267 }
3268 ]
3269 }
3270 }
3271 }
3272 }
3273 Warnings:
3274 Note 1003 /* select#1 */ select distinct `test`.`t4`.`a` AS `a`,`test`.`t4`.`e` AS `e` from `test`.`t4` join `test`.`t1` order by `test`.`t4`.`b`
3275 select distinct t4.a,e from t4, t1 order by t4.b;
3276 a e
3277 1 5
3278 explain format = json select * from t1 union all select * from t2;
3279 EXPLAIN
3280 {
3281 "query_block": {
3282 "union_result": {
3283 "using_temporary_table": false,
3284 "query_specifications": [
3285 {
3286 "dependent": false,
3287 "cacheable": true,
3288 "query_block": {
3289 "select_id": 1,
3290 "cost_info": {
3291 "query_cost": "0.75"
3292 },
3293 "table": {
3294 "table_name": "t1",
3295 "access_type": "ALL",
3296 "rows_examined_per_scan": 5,
3297 "rows_produced_per_join": 5,
3298 "filtered": "100.00",
3299 "cost_info": {
3300 "read_cost": "0.25",
3301 "eval_cost": "0.50",
3302 "prefix_cost": "0.75",
3303 "data_read_per_join": "440"
3304 },
3305 "used_columns": [
3306 "a",
3307 "b"
3308 ]
3309 }
3310 }
3311 },
3312 {
3313 "dependent": false,
3314 "cacheable": true,
3315 "query_block": {
3316 "select_id": 2,
3317 "cost_info": {
3318 "query_cost": "0.75"
3319 },
3320 "table": {
3321 "table_name": "t2",
3322 "access_type": "ALL",
3323 "rows_examined_per_scan": 5,
3324 "rows_produced_per_join": 5,
3325 "filtered": "100.00",
3326 "cost_info": {
3327 "read_cost": "0.25",
3328 "eval_cost": "0.50",
3329 "prefix_cost": "0.75",
3330 "data_read_per_join": "440"
3331 },
3332 "used_columns": [
3333 "a",
3334 "b"
3335 ]
3336 }
3337 }
3338 }
3339 ]
3340 }
3341 }
3342 }
3343 Warnings:
3344 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union all /* select#2 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`
3345 select * from t1 union all select * from t2;
3346 a b
3347 9 a
3348 2 b
3349 3 c
3350 3 c
3351 4 c
3352 10 d
3353 10 e
3354 10 f
3355 10 f
3356 10 e
3357 explain format = json select * from t1 union select * from t2 order by b;
3358 EXPLAIN
3359 {
3360 "query_block": {
3361 "union_result": {
3362 "using_temporary_table": true,
3363 "select_id": 3,
3364 "table_name": "<union1,2>",
3365 "access_type": "ALL",
3366 "using_filesort": true,
3367 "query_specifications": [
3368 {
3369 "dependent": false,
3370 "cacheable": true,
3371 "query_block": {
3372 "select_id": 1,
3373 "cost_info": {
3374 "query_cost": "0.75"
3375 },
3376 "table": {
3377 "table_name": "t1",
3378 "access_type": "ALL",
3379 "rows_examined_per_scan": 5,
3380 "rows_produced_per_join": 5,
3381 "filtered": "100.00",
3382 "cost_info": {
3383 "read_cost": "0.25",
3384 "eval_cost": "0.50",
3385 "prefix_cost": "0.75",
3386 "data_read_per_join": "440"
3387 },
3388 "used_columns": [
3389 "a",
3390 "b"
3391 ]
3392 }
3393 }
3394 },
3395 {
3396 "dependent": false,
3397 "cacheable": true,
3398 "query_block": {
3399 "select_id": 2,
3400 "cost_info": {
3401 "query_cost": "0.75"
3402 },
3403 "table": {
3404 "table_name": "t2",
3405 "access_type": "ALL",
3406 "rows_examined_per_scan": 5,
3407 "rows_produced_per_join": 5,
3408 "filtered": "100.00",
3409 "cost_info": {
3410 "read_cost": "0.25",
3411 "eval_cost": "0.50",
3412 "prefix_cost": "0.75",
3413 "data_read_per_join": "440"
3414 },
3415 "used_columns": [
3416 "a",
3417 "b"
3418 ]
3419 }
3420 }
3421 }
3422 ]
3423 }
3424 }
3425 }
3426 Warnings:
3427 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` order by `b`
3428 select * from t1 union select * from t2 order by b;
3429 a b
3430 9 a
3431 2 b
3432 3 c
3433 4 c
3434 10 d
3435 10 e
3436 10 f
3437 explain format = json select tt.* from (select * from t1) as tt;
3438 EXPLAIN
3439 {
3440 "query_block": {
3441 "select_id": 1,
3442 "cost_info": {
3443 "query_cost": "1.00"
3444 },
3445 "table": {
3446 "table_name": "tt",
3447 "access_type": "ALL",
3448 "rows_examined_per_scan": 5,
3449 "rows_produced_per_join": 5,
3450 "filtered": "100.00",
3451 "cost_info": {
3452 "read_cost": "0.50",
3453 "eval_cost": "0.50",
3454 "prefix_cost": "1.00",
3455 "data_read_per_join": "480"
3456 },
3457 "used_columns": [
3458 "a",
3459 "b"
3460 ],
3461 "materialized_from_subquery": {
3462 "using_temporary_table": true,
3463 "dependent": false,
3464 "cacheable": true,
3465 "query_block": {
3466 "select_id": 2,
3467 "cost_info": {
3468 "query_cost": "0.75"
3469 },
3470 "table": {
3471 "table_name": "t1",
3472 "access_type": "ALL",
3473 "rows_examined_per_scan": 5,
3474 "rows_produced_per_join": 5,
3475 "filtered": "100.00",
3476 "cost_info": {
3477 "read_cost": "0.25",
3478 "eval_cost": "0.50",
3479 "prefix_cost": "0.75",
3480 "data_read_per_join": "440"
3481 },
3482 "used_columns": [
3483 "a",
3484 "b"
3485 ]
3486 }
3487 }
3488 }
3489 }
3490 }
3491 }
3492 Warnings:
3493 Note 1003 /* select#1 */ select `tt`.`a` AS `a`,`tt`.`b` AS `b` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) `tt`
3494 select tt.* from (select * from t1) as tt;
3495 a b
3496 9 a
3497 2 b
3498 3 c
3499 3 c
3500 4 c
3501 select * from t5;
3502 a b
3503 1 7
3504 1 8
3505 explain format = json select b from (select b from t5 where b > 5) tt;
3506 EXPLAIN
3507 {
3508 "query_block": {
3509 "select_id": 1,
3510 "cost_info": {
3511 "query_cost": "0.70"
3512 },
3513 "table": {
3514 "table_name": "tt",
3515 "access_type": "ALL",
3516 "rows_examined_per_scan": 2,
3517 "rows_produced_per_join": 2,
3518 "filtered": "100.00",
3519 "cost_info": {
3520 "read_cost": "0.50",
3521 "eval_cost": "0.20",
3522 "prefix_cost": "0.70",
3523 "data_read_per_join": "32"
3524 },
3525 "used_columns": [
3526 "b"
3527 ],
3528 "materialized_from_subquery": {
3529 "using_temporary_table": true,
3530 "dependent": false,
3531 "cacheable": true,
3532 "query_block": {
3533 "select_id": 2,
3534 "cost_info": {
3535 "query_cost": "0.45"
3536 },
3537 "table": {
3538 "table_name": "t5",
3539 "access_type": "index",
3540 "possible_keys": [
3541 "PRIMARY"
3542 ],
3543 "key": "PRIMARY",
3544 "used_key_parts": [
3545 "b"
3546 ],
3547 "key_length": "4",
3548 "rows_examined_per_scan": 2,
3549 "rows_produced_per_join": 2,
3550 "filtered": "100.00",
3551 "using_index": true,
3552 "cost_info": {
3553 "read_cost": "0.25",
3554 "eval_cost": "0.20",
3555 "prefix_cost": "0.45",
3556 "data_read_per_join": "32"
3557 },
3558 "used_columns": [
3559 "b"
3560 ],
3561 "attached_condition": "(`test`.`t5`.`b` > 5)"
3562 }
3563 }
3564 }
3565 }
3566 }
3567 }
3568 Warnings:
3569 Note 1003 /* select#1 */ select `tt`.`b` AS `b` from (/* select#2 */ select `test`.`t5`.`b` AS `b` from `test`.`t5` where (`test`.`t5`.`b` > 5)) `tt`
3570 select b from (select b from t5 where b > 5) tt;
3571 b
3572 7
3573 8
3574 explain format = json select * from (select b from t1) as t1, (select b from t2) as t2;
3575 EXPLAIN
3576 "b"
3577 "b"
3578 "data_read_per_join": "440"
3579 "data_read_per_join": "440"
3580 "eval_cost": "0.50",
3581 "eval_cost": "0.50",
3582 "prefix_cost": "0.75",
3583 "prefix_cost": "0.75",
3584 "read_cost": "0.25",
3585 "read_cost": "0.25",
3586 "access_type": "ALL",
3587 "access_type": "ALL",
3588 "cost_info": {
3589 "cost_info": {
3590 "filtered": "100.00",
3591 "filtered": "100.00",
3592 "query_cost": "0.75"
3593 "query_cost": "0.75"
3594 "rows_examined_per_scan": 5,
3595 "rows_examined_per_scan": 5,
3596 "rows_produced_per_join": 5,
3597 "rows_produced_per_join": 5,
3598 "table_name": "t1",
3599 "table_name": "t2",
3600 "used_columns": [
3601 "used_columns": [
3602 ]
3603 ]
3604 },
3605 },
3606 "cost_info": {
3607 "cost_info": {
3608 "select_id": 2,
3609 "select_id": 3,
3610 "table": {
3611 "table": {
3612 }
3613 }
3614 },
3615 },
3616 "b"
3617 "b"
3618 "cacheable": true,
3619 "cacheable": true,
3620 "data_read_per_join": "2K"
3621 "data_read_per_join": "440"
3622 "dependent": false,
3623 "dependent": false,
3624 "eval_cost": "0.50",
3625 "eval_cost": "2.50",
3626 "prefix_cost": "1.00",
3627 "prefix_cost": "4.00",
3628 "query_block": {
3629 "query_block": {
3630 "read_cost": "0.50",
3631 "read_cost": "0.50",
3632 "using_temporary_table": true,
3633 "using_temporary_table": true,
3634 }
3635 }
3636 "access_type": "ALL",
3637 "access_type": "ALL",
3638 "cost_info": {
3639 "cost_info": {
3640 "filtered": "100.00",
3641 "filtered": "100.00",
3642 "materialized_from_subquery": {
3643 "materialized_from_subquery": {
3644 "rows_examined_per_scan": 5,
3645 "rows_examined_per_scan": 5,
3646 "rows_produced_per_join": 25,
3647 "rows_produced_per_join": 5,
3648 "table_name": "t1",
3649 "table_name": "t2",
3650 "used_columns": [
3651 "used_columns": [
3652 "using_join_buffer": "hash join",
3653 ],
3654 ],
3655 }
3656 }
3657 },
3658 },
3659 "table": {
3660 "table": {
3661 }
3662 }
3663 "query_cost": "4.00"
3664 {
3665 {
3666 }
3667 },
3668 "cost_info": {
3669 "nested_loop": [
3670 "select_id": 1,
3671 ]
3672 },
3673 "query_block": {
3674 }
3675 Note 1003 /* select#1 */ select `t1`.`b` AS `b`,`t2`.`b` AS `b` from (/* select#2 */ select `test`.`t1`.`b` AS `b` from `test`.`t1`) `t1` join (/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t2`
3676 Warnings:
3677 {
3678 }
3679 select * from (select b from t1) as t1, (select b from t2) as t2;
3680 b b
3681 a d
3682 a e
3683 a e
3684 a f
3685 a f
3686 b d
3687 b e
3688 b e
3689 b f
3690 b f
3691 c d
3692 c d
3693 c d
3694 c e
3695 c e
3696 c e
3697 c e
3698 c e
3699 c e
3700 c f
3701 c f
3702 c f
3703 c f
3704 c f
3705 c f
3706 explain format = json select * from t1, (select * from t6) as t where t.a =5;
3707 EXPLAIN
3708 {
3709 "query_block": {
3710 "select_id": 1,
3711 "cost_info": {
3712 "query_cost": "1.95"
3713 },
3714 "nested_loop": [
3715 {
3716 "table": {
3717 "table_name": "t",
3718 "access_type": "ALL",
3719 "rows_examined_per_scan": 2,
3720 "rows_produced_per_join": 2,
3721 "filtered": "100.00",
3722 "cost_info": {
3723 "read_cost": "0.50",
3724 "eval_cost": "0.20",
3725 "prefix_cost": "0.70",
3726 "data_read_per_join": "48"
3727 },
3728 "used_columns": [
3729 "a",
3730 "b",
3731 "c",
3732 "d"
3733 ],
3734 "materialized_from_subquery": {
3735 "using_temporary_table": true,
3736 "dependent": false,
3737 "cacheable": true,
3738 "query_block": {
3739 "select_id": 2,
3740 "cost_info": {
3741 "query_cost": "0.35"
3742 },
3743 "table": {
3744 "table_name": "t6",
3745 "access_type": "ref",
3746 "possible_keys": [
3747 "t6_indx1"
3748 ],
3749 "key": "t6_indx1",
3750 "used_key_parts": [
3751 "a"
3752 ],
3753 "key_length": "5",
3754 "ref": [
3755 "const"
3756 ],
3757 "rows_examined_per_scan": 1,
3758 "rows_produced_per_join": 1,
3759 "filtered": "100.00",
3760 "cost_info": {
3761 "read_cost": "0.25",
3762 "eval_cost": "0.10",
3763 "prefix_cost": "0.35",
3764 "data_read_per_join": "24"
3765 },
3766 "used_columns": [
3767 "a",
3768 "b",
3769 "c",
3770 "d"
3771 ]
3772 }
3773 }
3774 }
3775 }
3776 },
3777 {
3778 "table": {
3779 "table_name": "t1",
3780 "access_type": "ALL",
3781 "rows_examined_per_scan": 5,
3782 "rows_produced_per_join": 10,
3783 "filtered": "100.00",
3784 "using_join_buffer": "hash join",
3785 "cost_info": {
3786 "read_cost": "0.25",
3787 "eval_cost": "1.00",
3788 "prefix_cost": "1.95",
3789 "data_read_per_join": "880"
3790 },
3791 "used_columns": [
3792 "a",
3793 "b"
3794 ]
3795 }
3796 }
3797 ]
3798 }
3799 }
3800 Warnings:
3801 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`t`.`a` AS `a`,`t`.`b` AS `b`,`t`.`c` AS `c`,`t`.`d` AS `d` from `test`.`t1` join (/* select#2 */ select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t6`.`c` AS `c`,`test`.`t6`.`d` AS `d` from `test`.`t6` where (`test`.`t6`.`a` = 5)) `t`
3802 select * from t1, (select * from t6) as t where t.a =5;
3803 a b a b c d
3804 explain format = json select * from t1 where t1.a in (select a from t2) ;
3805 EXPLAIN
3806 {
3807 "query_block": {
3808 "select_id": 1,
3809 "cost_info": {
3810 "query_cost": "3.50"
3811 },
3812 "nested_loop": [
3813 {
3814 "table": {
3815 "table_name": "t1",
3816 "access_type": "ALL",
3817 "rows_examined_per_scan": 5,
3818 "rows_produced_per_join": 5,
3819 "filtered": "100.00",
3820 "cost_info": {
3821 "read_cost": "0.25",
3822 "eval_cost": "0.50",
3823 "prefix_cost": "0.75",
3824 "data_read_per_join": "440"
3825 },
3826 "used_columns": [
3827 "a",
3828 "b"
3829 ],
3830 "attached_condition": "(`test`.`t1`.`a` is not null)"
3831 }
3832 },
3833 {
3834 "table": {
3835 "table_name": "<subquery2>",
3836 "access_type": "eq_ref",
3837 "key": "<auto_distinct_key>",
3838 "key_length": "5",
3839 "ref": [
3840 "test.t1.a"
3841 ],
3842 "rows_examined_per_scan": 1,
3843 "materialized_from_subquery": {
3844 "using_temporary_table": true,
3845 "query_block": {
3846 "table": {
3847 "table_name": "t2",
3848 "access_type": "ALL",
3849 "rows_examined_per_scan": 5,
3850 "rows_produced_per_join": 5,
3851 "filtered": "100.00",
3852 "cost_info": {
3853 "read_cost": "0.25",
3854 "eval_cost": "0.50",
3855 "prefix_cost": "0.75",
3856 "data_read_per_join": "440"
3857 },
3858 "used_columns": [
3859 "a"
3860 ]
3861 }
3862 }
3863 }
3864 }
3865 }
3866 ]
3867 }
3868 }
3869 Warnings:
3870 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where (`<subquery2>`.`a` = `test`.`t1`.`a`)
3871 select * from t1 where t1.a in (select a from t2) ;
3872 a b
3873 set optimizer_switch='materialization=off';
3874 set optimizer_switch='firstmatch=off';
3875 set optimizer_switch='loosescan=off';
3876 explain format = json select * from t1 where t1.a in (select a from t2) ;
3877 EXPLAIN
3878 {
3879 "query_block": {
3880 "select_id": 1,
3881 "cost_info": {
3882 "query_cost": "5.50"
3883 },
3884 "duplicates_removal": {
3885 "using_temporary_table": true,
3886 "nested_loop": [
3887 {
3888 "table": {
3889 "table_name": "t1",
3890 "access_type": "ALL",
3891 "rows_examined_per_scan": 5,
3892 "rows_produced_per_join": 5,
3893 "filtered": "100.00",
3894 "cost_info": {
3895 "read_cost": "0.25",
3896 "eval_cost": "0.50",
3897 "prefix_cost": "0.75",
3898 "data_read_per_join": "440"
3899 },
3900 "used_columns": [
3901 "a",
3902 "b"
3903 ]
3904 }
3905 },
3906 {
3907 "table": {
3908 "table_name": "t2",
3909 "access_type": "ALL",
3910 "rows_examined_per_scan": 5,
3911 "rows_produced_per_join": 5,
3912 "filtered": "20.00",
3913 "using_join_buffer": "hash join",
3914 "cost_info": {
3915 "read_cost": "0.25",
3916 "eval_cost": "0.50",
3917 "prefix_cost": "5.50",
3918 "data_read_per_join": "440"
3919 },
3920 "used_columns": [
3921 "a"
3922 ],
3923 "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
3924 }
3925 }
3926 ]
3927 }
3928 }
3929 }
3930 Warnings:
3931 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`a` = `test`.`t1`.`a`)
3932 select * from t1 where t1.a in (select a from t2) ;
3933 a b
3934 set optimizer_switch='default,derived_merge=off';
3935 explain format = json select count(distinct a) from t1;
3936 EXPLAIN
3937 {
3938 "query_block": {
3939 "select_id": 1,
3940 "cost_info": {
3941 "query_cost": "0.75"
3942 },
3943 "table": {
3944 "table_name": "t1",
3945 "access_type": "ALL",
3946 "rows_examined_per_scan": 5,
3947 "rows_produced_per_join": 5,
3948 "filtered": "100.00",
3949 "cost_info": {
3950 "read_cost": "0.25",
3951 "eval_cost": "0.50",
3952 "prefix_cost": "0.75",
3953 "data_read_per_join": "440"
3954 },
3955 "used_columns": [
3956 "a"
3957 ]
3958 }
3959 }
3960 }
3961 Warnings:
3962 Note 1003 /* select#1 */ select count(distinct `test`.`t1`.`a`) AS `count(distinct a)` from `test`.`t1`
3963 select count(distinct a) from t1;
3964 count(distinct a)
3965 4
3966 explain format = json select b, group_concat(a) from t1;
3967 EXPLAIN
3968 {
3969 "query_block": {
3970 "select_id": 1,
3971 "cost_info": {
3972 "query_cost": "0.75"
3973 },
3974 "table": {
3975 "table_name": "t1",
3976 "access_type": "ALL",
3977 "rows_examined_per_scan": 5,
3978 "rows_produced_per_join": 5,
3979 "filtered": "100.00",
3980 "cost_info": {
3981 "read_cost": "0.25",
3982 "eval_cost": "0.50",
3983 "prefix_cost": "0.75",
3984 "data_read_per_join": "440"
3985 },
3986 "used_columns": [
3987 "a",
3988 "b"
3989 ]
3990 }
3991 }
3992 }
3993 Warnings:
3994 Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b`,group_concat(`test`.`t1`.`a` separator ',') AS `group_concat(a)` from `test`.`t1`
3995 select b, group_concat(a) from t1;
3996 b group_concat(a)
3997 a 9,2,3,3,4
3998 select * from t1;
3999 a b
4000 9 a
4001 2 b
4002 3 c
4003 3 c
4004 4 c
4005 explain format = json select b, count(a), sum(a), avg(a), std(a),
4006 min(a), max(a), bit_and(a), bit_or(a) from t1 group by b;
4007 EXPLAIN
4008 {
4009 "query_block": {
4010 "select_id": 1,
4011 "cost_info": {
4012 "query_cost": "0.75"
4013 },
4014 "grouping_operation": {
4015 "using_temporary_table": true,
4016 "using_filesort": false,
4017 "table": {
4018 "table_name": "t1",
4019 "access_type": "ALL",
4020 "rows_examined_per_scan": 5,
4021 "rows_produced_per_join": 5,
4022 "filtered": "100.00",
4023 "cost_info": {
4024 "read_cost": "0.25",
4025 "eval_cost": "0.50",
4026 "prefix_cost": "0.75",
4027 "data_read_per_join": "440"
4028 },
4029 "used_columns": [
4030 "a",
4031 "b"
4032 ]
4033 }
4034 }
4035 }
4036 }
4037 Warnings:
4038 Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b`,count(`test`.`t1`.`a`) AS `count(a)`,sum(`test`.`t1`.`a`) AS `sum(a)`,avg(`test`.`t1`.`a`) AS `avg(a)`,std(`test`.`t1`.`a`) AS `std(a)`,min(`test`.`t1`.`a`) AS `min(a)`,max(`test`.`t1`.`a`) AS `max(a)`,bit_and(`test`.`t1`.`a`) AS `bit_and(a)`,bit_or(`test`.`t1`.`a`) AS `bit_or(a)` from `test`.`t1` group by `test`.`t1`.`b`
4039 select b, count(a), sum(a), avg(a), std(a), min(a),
4040 max(a), bit_and(a), bit_or(a) from t1 group by b;
4041 b count(a) sum(a) avg(a) std(a) min(a) max(a) bit_and(a) bit_or(a)
4042 a 1 9 9.0000 0 9 9 9 9
4043 b 1 2 2.0000 0 2 2 2 2
4044 c 3 10 3.3333 0.4714045207910316 3 4 0 7
4045 select sql_big_result * from performance_schema.global_variables where variable_name like 'internal_tmp_mem_storage_engine';
4046 VARIABLE_NAME VARIABLE_VALUE
4047 internal_tmp_mem_storage_engine TempTable
4048 select 1 union distinct select 1 union all select 1;
4049 1
4050 1
4051 1
4052 select alias1.`col_int_key` AS field1 from (t8 as alias1) where (5) in
4053 (select 7 union select 1) order by alias1.`col_varchar_key` asc;
4054 field1
4055 select count(table2.col_date_key) as field1 from
4056 (t10 as table1 inner join
4057 ((t10 as table2 right join
4058 t9 as table3 on
4059 ((table3.pk = table2.pk) or
4060 (table3.pk = table2.col_int_key))))
4061 on ((table3.pk < table2.col_int_nokey)))
4062 order by field1 ASC;
4063 field1
4064 2200
4065 select distinct table1.col_varchar_1024_utf8_key as field1
4066 from t11 as table1 left join t11 as table2 on
4067 table1.col_varchar_1024_utf8 = table2.col_varchar_1024_latin1_key
4068 order by field1;
4069 field1
4070 YTLDF
4071 yvuwdllyhymuwyypoiuyeqbnaanbursgddnqhyvxnnpadsjiqdkajofferjfslatroorycttbiuhlubvmoikwwnalqyewgthdmfvsedatazrflwenzeiwdxtheqppbmqfydsawvanehgvfvbsqxzkqzhtjkybvikmpexipoddmoulsnzfchusfgkdazecevrpuakfioamyyqyribcnydfxmmzsxcpkydyczmeajaebsaskneaqwxjbpjptiwgiizoxfygnbjojpifpzldsdhrukdbwpzribxgafunywmmcfpjugozduylotsugqocbadhcnxjqlugncbebwsbobhxgghyyphxfqqnpliazusgjswynfotwdonpbkllfdbuoqceirksegypasuuxnrjumrkaryhkgkbdfgoqbdmrdrmpvwitqzswgossxltimkbtrxitnmigcxgfaqmrnv
4072 select t from t12 group by c;
4073 t
4074 NULL
4075
4076 1
4077 a
4078 hello
4079 HELLO MY
4080 select count(outr.col_varchar_nokey) AS x
4081 from t13 as outr2 left join
4082 (select * from t13) as outr
4083 on (outr2.col_time_key > outr.col_date_key)
4084 where outr.pk in
4085 (select innr.col_int_nokey AS y from
4086 (select * from t14) as innr2 left join
4087 (select * from t14) AS innr
4088 ON (innr2.col_int_nokey = innr.col_int_key)
4089 where innr.col_varchar_nokey > innr.col_varchar_key
4090 or not outr.pk > 9)
4091 or outr.col_varchar_key is null;
4092 x
4093 102
4094 SET @@optimizer_switch=@optimizer_switch_saved;
4095 # restart
4096 select @@big_tables;
4097 @@big_tables
4098 1
4099 create table t0 (m int, n int, key(m)) engine=innodb;
4100 begin;
4101 select count(*) from t0;
4102 count(*)
4103 0
4104 set @@session.sql_mode = ANSI;
4105 set @@session.tmp_table_size = 0;
4106 Warnings:
4107 Warning 1292 Truncated incorrect tmp_table_size value: '0'
4108 set @@session.big_tables = 1;
4109 select @@big_tables;
4110 @@big_tables
4111 1
4112 select event_name, e.min_timer_wait, min(t.min_timer_wait) from
4113 performance_schema.events_waits_summary_global_by_event_name as e JOIN
4114 performance_schema.events_waits_summary_by_thread_by_event_name as t USING
4115 (event_name) group by event_name;
4116 select * from t0;
4117 m n
4118 commit;
4119 drop table t0;
4120 set session big_tables = 1;
4121 select @@big_tables;
4122 @@big_tables
4123 1
4124 drop function func1;
4125 drop table t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14;
4126 #
4127 # BUG#20762059 - innodb_thread_concurrency=1 and queries using intrinsic
4128 # temp tables, causes hang
4129 #
4130 # Scenario 1: Without timezone tables
4131 #
4132 CREATE TABLE t1(a INT);
4133 INSERT INTO t1 VALUES(1);
4134 # On default connection
4135 SET @@session.max_heap_table_size=0;
4136 Warnings:
4137 Warning 1292 Truncated incorrect max_heap_table_size value: '0'
4138 SET @@global.innodb_thread_concurrency=1;
4139 # Executing I_S query which will create and do index read on
4140 # intrinsic temporary tables
4141 # Switch to connection con1
4142 SELECT * FROM t1;
4143 a
4144 1
4145 # Switch to connection default
4146 DROP TABLE t1;
4147 SET @@session.max_heap_table_size = default;
4148 SET @@global.innodb_thread_concurrency = default;
4149 SET @@global.innodb_thread_sleep_delay = default;
4150 #
4151 # Scenario 2: with I_S query and timezone table(uses attachable
4152 # transaction)
4153 #
4154 SET @@session.max_heap_table_size=0;
4155 Warnings:
4156 Warning 1292 Truncated incorrect max_heap_table_size value: '0'
4157 SET @@global.innodb_thread_concurrency=1;
4158 # Executing I_S query which will create and do index read on
4159 # intrinsic temporary tables
4160 select convert_tz(0,'a','a');
4161 convert_tz(0,'a','a')
4162 NULL
4163 SET @@session.max_heap_table_size = default;
4164 SET @@global.innodb_thread_concurrency = default;
4165 SET @@global.innodb_thread_sleep_delay = default;
4166 #
4167 # Scenario 3: with I_S query and help table(uses attachable
4168 # transaction)
4169 #
4170 SET @@global.innodb_thread_concurrency=1;
4171 # Executing I_S query which will create and do index read on
4172 # intrinsic temporary tables
4173 HELP '%function_2';
4174 name is_it_category
4175 SET @@global.innodb_thread_concurrency = default;
4176 SET @@global.innodb_thread_sleep_delay = default;
4177 #
4178 # Bug#21871451: INNODB: FAILING ASSERTION: !(&BUF_POOL->MUTEX)
4179 #
4180 SELECT 'SUCCESS_INNODB_CMPMEM' FROM DUAL WHERE EXISTS
4181 (SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM);
4182 SUCCESS_INNODB_CMPMEM
4183 SUCCESS_INNODB_CMPMEM
4184 SELECT 'SUCCESS_INNODB_CMP' FROM DUAL WHERE EXISTS
4185 (SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM_RESET);
4186 SUCCESS_INNODB_CMP
4187 SUCCESS_INNODB_CMP