Debian Codegraph @

mysql-test/suite/gcol/r/gcol_select_myisam.result

raw
1 DROP VIEW IF EXISTS v1,v2;
2 DROP TABLE IF EXISTS t1,t2,t3;
3 DROP PROCEDURE IF EXISTS p1;
4 DROP FUNCTION IF EXISTS f1;
5 DROP TRIGGER IF EXISTS trg1;
6 DROP TRIGGER IF EXISTS trg2;
7 set sql_warnings = 0;
8 SET @@session.default_storage_engine = 'MyISAM';
9 SET optimizer_switch='derived_merge=off';
10 ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
11 create table t1 (a int,
12 b int generated always as (-a) virtual,
13 c int generated always as (-a) stored,
14 index (c));
15 insert into t1 (a) values (2), (1), (1), (3), (NULL);
16 create table t2 like t1;
17 insert into t2 (a) values (1);
18 create table t3 (a int primary key,
19 b int generated always as (-a) virtual,
20 c int generated always as (-a) stored unique);
21 insert into t3 (a) values (2),(1),(3),(5),(4),(7);
22 analyze table t1,t2,t3;
23 Table Op Msg_type Msg_text
24 test.t1 analyze status Engine-independent statistics collected
25 test.t1 analyze status OK
26 test.t2 analyze status Engine-independent statistics collected
27 test.t2 analyze status OK
28 test.t3 analyze status Engine-independent statistics collected
29 test.t3 analyze status OK
30 # select_type=SIMPLE, type=system
31 select * from t2;
32 a b c
33 1 -1 -1
34 explain select * from t2;
35 id select_type table type possible_keys key key_len ref rows Extra
36 1 SIMPLE t2 system NULL NULL NULL NULL 1
37 select * from t2 where c=-1;
38 a b c
39 1 -1 -1
40 explain select * from t2 where c=-1;
41 id select_type table type possible_keys key key_len ref rows Extra
42 1 SIMPLE t2 system c NULL NULL NULL 1
43 # select_type=SIMPLE, type=ALL
44 select * from t1 where b=-1;
45 a b c
46 1 -1 -1
47 1 -1 -1
48 explain select * from t1 where b=-1;
49 id select_type table type possible_keys key key_len ref rows Extra
50 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
51 # select_type=SIMPLE, type=const
52 select * from t3 where a=1;
53 a b c
54 1 -1 -1
55 explain select * from t3 where a=1;
56 id select_type table type possible_keys key key_len ref rows Extra
57 1 SIMPLE t3 const PRIMARY PRIMARY 4 const 1
58 # select_type=SIMPLE, type=range
59 select * from t3 where c>=-1;
60 a b c
61 1 -1 -1
62 explain select * from t3 where c>=-1;
63 id select_type table type possible_keys key key_len ref rows Extra
64 1 SIMPLE t3 range c c 5 NULL 1 Using index condition
65 # select_type=SIMPLE, type=ref
66 select * from t1,t3 where t1.c=t3.c and t3.c=-1;
67 a b c a b c
68 1 -1 -1 1 -1 -1
69 1 -1 -1 1 -1 -1
70 explain select * from t1,t3 where t1.c=t3.c and t3.c=-1;
71 id select_type table type possible_keys key key_len ref rows Extra
72 1 SIMPLE t3 const c c 5 const 1
73 1 SIMPLE t1 ref c c 5 const 2
74 # select_type=PRIMARY, type=index,ALL
75 select * from t1 where b in (select c from t3);
76 a b c
77 1 -1 -1
78 1 -1 -1
79 2 -2 -2
80 3 -3 -3
81 explain select * from t1 where b in (select c from t3);
82 id select_type table type possible_keys key key_len ref rows Extra
83 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where
84 1 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index
85 # select_type=PRIMARY, type=range,ref
86 select * from t1 where c in (select c from t3 where c between -2 and -1);
87 a b c
88 1 -1 -1
89 1 -1 -1
90 2 -2 -2
91 explain select * from t1 where c in (select c from t3 where c between -2 and -1);
92 id select_type table type possible_keys key key_len ref rows Extra
93 1 PRIMARY t3 range c c 5 NULL 2 Using where; Using index
94 1 PRIMARY t1 ref c c 5 test.t3.c 1
95 # select_type=UNION, type=system
96 # select_type=UNION RESULT, type=<union1,2>
97 select * from t1 union select * from t2;
98 a b c
99 1 -1 -1
100 2 -2 -2
101 3 -3 -3
102 NULL NULL NULL
103 explain select * from t1 union select * from t2;
104 id select_type table type possible_keys key key_len ref rows Extra
105 1 PRIMARY t1 ALL NULL NULL NULL NULL 5
106 2 UNION t2 system NULL NULL NULL NULL 1
107 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
108 # select_type=DERIVED, type=system
109 select * from (select a,b,c from t1) as t11;
110 a b c
111 1 -1 -1
112 1 -1 -1
113 2 -2 -2
114 3 -3 -3
115 NULL NULL NULL
116 explain select * from (select a,b,c from t1) as t11;
117 id select_type table type possible_keys key key_len ref rows Extra
118 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5
119 2 DERIVED t1 ALL NULL NULL NULL NULL 5
120 ###
121 ### Using aggregate functions with/without DISTINCT
122 ###
123 # SELECT COUNT(*) FROM tbl_name
124 select count(*) from t1;
125 count(*)
126 5
127 explain select count(*) from t1;
128 id select_type table type possible_keys key key_len ref rows Extra
129 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
130 # SELECT COUNT(DISTINCT <non-gcol>) FROM tbl_name
131 select count(distinct a) from t1;
132 count(distinct a)
133 3
134 explain select count(distinct a) from t1;
135 id select_type table type possible_keys key key_len ref rows Extra
136 1 SIMPLE t1 ALL NULL NULL NULL NULL 5
137 # SELECT COUNT(DISTINCT <non-stored gcol>) FROM tbl_name
138 select count(distinct b) from t1;
139 count(distinct b)
140 3
141 explain select count(distinct b) from t1;
142 id select_type table type possible_keys key key_len ref rows Extra
143 1 SIMPLE t1 ALL NULL NULL NULL NULL 5
144 # SELECT COUNT(DISTINCT <stored gcol>) FROM tbl_name
145 select count(distinct c) from t1;
146 count(distinct c)
147 3
148 explain select count(distinct c) from t1;
149 id select_type table type possible_keys key key_len ref rows Extra
150 1 SIMPLE t1 range NULL c 5 NULL 5 Using index for group-by
151 ###
152 ### filesort & range-based utils
153 ###
154 # SELECT * FROM tbl_name WHERE <gcol expr>
155 select * from t3 where c >= -2;
156 a b c
157 1 -1 -1
158 2 -2 -2
159 explain select * from t3 where c >= -2;
160 id select_type table type possible_keys key key_len ref rows Extra
161 1 SIMPLE t3 range c c 5 NULL 2 Using index condition
162 # SELECT * FROM tbl_name WHERE <non-gcol expr>
163 select * from t3 where a between 1 and 2;
164 a b c
165 1 -1 -1
166 2 -2 -2
167 explain select * from t3 where a between 1 and 2;
168 id select_type table type possible_keys key key_len ref rows Extra
169 1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition
170 # SELECT * FROM tbl_name WHERE <non-indexed gcol expr>
171 select * from t3 where b between -2 and -1;
172 a b c
173 1 -1 -1
174 2 -2 -2
175 explain select * from t3 where b between -2 and -1;
176 id select_type table type possible_keys key key_len ref rows Extra
177 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where
178 # SELECT * FROM tbl_name WHERE <indexed gcol expr>
179 select * from t3 where c between -2 and -1;
180 a b c
181 1 -1 -1
182 2 -2 -2
183 explain select * from t3 where c between -2 and -1;
184 id select_type table type possible_keys key key_len ref rows Extra
185 1 SIMPLE t3 range c c 5 NULL 2 Using index condition
186 # bug#20022189: WL411:DEBUG ASSERT AT FIELD_LONG::VAL_INT IN SQL/FIELD.CC
187 CREATE TABLE t4 (
188 `pk` int(11) NOT NULL ,
189 `col_int_nokey` int(11) GENERATED ALWAYS AS (pk + col_int_key) STORED,
190 `col_int_key` int(11) DEFAULT NULL,
191 `col_date_nokey` date DEFAULT NULL,
192 `col_datetime_key` datetime DEFAULT NULL,
193 PRIMARY KEY (`pk`),
194 KEY `col_int_key` (`col_int_key`),
195 KEY `col_datetime_key` (`col_datetime_key`)
196 );
197 INSERT INTO t4 VALUES
198 (1,default,4,'2008-12-05','1900-01-01 00:00:00');
199 SELECT
200 SQL_BIG_RESULT
201 GRANDPARENT1 . `col_int_nokey` AS g1
202 FROM t4 AS GRANDPARENT1 LEFT JOIN t4 AS GRANDPARENT2 ON ( GRANDPARENT2 .
203 `col_datetime_key` <= GRANDPARENT1 . `col_date_nokey` )
204 GROUP BY GRANDPARENT1 . `pk`;
205 g1
206 5
207 DROP TABLE t4;
208 # SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <indexed gcol>
209 select * from t3 where a between 1 and 2 order by c;
210 a b c
211 2 -2 -2
212 1 -1 -1
213 explain select * from t3 where a between 1 and 2 order by c;
214 id select_type table type possible_keys key key_len ref rows Extra
215 1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using filesort
216 # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-gcol>
217 select * from t3 where b between -2 and -1 order by a;
218 a b c
219 1 -1 -1
220 2 -2 -2
221 explain select * from t3 where b between -2 and -1 order by a;
222 id select_type table type possible_keys key key_len ref rows Extra
223 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort
224 # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-gcol>
225 select * from t3 where c between -2 and -1 order by a;
226 a b c
227 1 -1 -1
228 2 -2 -2
229 explain select * from t3 where c between -2 and -1 order by a;
230 id select_type table type possible_keys key key_len ref rows Extra
231 1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort
232 # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-indexed gcol>
233 select * from t3 where b between -2 and -1 order by b;
234 a b c
235 2 -2 -2
236 1 -1 -1
237 explain select * from t3 where b between -2 and -1 order by b;
238 id select_type table type possible_keys key key_len ref rows Extra
239 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort
240 # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-indexed gcol>
241 select * from t3 where c between -2 and -1 order by b;
242 a b c
243 2 -2 -2
244 1 -1 -1
245 explain select * from t3 where c between -2 and -1 order by b;
246 id select_type table type possible_keys key key_len ref rows Extra
247 1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort
248 # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <indexed gcol>
249 select * from t3 where b between -2 and -1 order by c;
250 a b c
251 2 -2 -2
252 1 -1 -1
253 explain select * from t3 where b between -2 and -1 order by c;
254 id select_type table type possible_keys key key_len ref rows Extra
255 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort
256 # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <indexed gcol>
257 select * from t3 where c between -2 and -1 order by c;
258 a b c
259 2 -2 -2
260 1 -1 -1
261 explain select * from t3 where c between -2 and -1 order by c;
262 id select_type table type possible_keys key key_len ref rows Extra
263 1 SIMPLE t3 range c c 5 NULL 2 Using index condition
264 # SELECT sum(<non-indexed gcol>) FROM tbl_name GROUP BY <non-indexed gcol>
265 select sum(b) from t1 group by b;
266 sum(b)
267 NULL
268 -3
269 -2
270 -2
271 explain select sum(b) from t1 group by b;
272 id select_type table type possible_keys key key_len ref rows Extra
273 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort
274 # SELECT sum(<indexed gcol>) FROM tbl_name GROUP BY <indexed gcol>
275 select sum(c) from t1 group by c;
276 sum(c)
277 NULL
278 -3
279 -2
280 -2
281 explain select sum(c) from t1 group by c;
282 id select_type table type possible_keys key key_len ref rows Extra
283 1 SIMPLE t1 index NULL c 5 NULL 5 Using index
284 # SELECT sum(<non-indexed gcol>) FROM tbl_name GROUP BY <indexed gcol>
285 select sum(b) from t1 group by c;
286 sum(b)
287 NULL
288 -3
289 -2
290 -2
291 explain select sum(b) from t1 group by c;
292 id select_type table type possible_keys key key_len ref rows Extra
293 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort
294 # SELECT sum(<indexed gcol>) FROM tbl_name GROUP BY <non-indexed gcol>
295 select sum(c) from t1 group by b;
296 sum(c)
297 NULL
298 -3
299 -2
300 -2
301 explain select sum(c) from t1 group by b;
302 id select_type table type possible_keys key key_len ref rows Extra
303 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort
304 drop table t1;
305 #
306 # Bug#20241655: WL411:FAILING ASSERTION ASSERTION
307 #
308 CREATE TABLE BB (
309 col_time_key time NOT NULL,
310 col_time_nokey time GENERATED ALWAYS AS (ADDTIME(col_datetime_key, col_time_key)) VIRTUAL,
311 col_datetime_key datetime NOT NULL);
312 INSERT INTO BB VALUES('23:28:02', default, '2005-03-15 22:48:25');
313 Warnings:
314 Note 1265 Data truncated for column 'col_time_nokey' at row 1
315 CREATE TABLE CC (
316 col_time_key time NOT NULL,
317 col_time_nokey time GENERATED ALWAYS AS (ADDTIME(col_datetime_key, col_time_key)) VIRTUAL,
318 col_datetime_key datetime NOT NULL
319 );
320 INSERT INTO CC VALUES('16:22:51', default, '1900-01-01 00:00:00');
321 Warnings:
322 Note 1265 Data truncated for column 'col_time_nokey' at row 1
323 SELECT 1 AS g1 FROM BB AS gp1 LEFT JOIN BB AS gp2 USING ( col_time_nokey);
324 g1
325 1
326 DROP TABLE BB, CC;
327 #
328 # Bug#20328786: WL411:VALGRIND WARNINGS OF CONDITIONAL
329 # JUMP WHILE SELECTING FROM VIEW
330 #
331 CREATE TABLE A (
332 pk INTEGER AUTO_INCREMENT,
333 col_int_nokey INTEGER,
334 col_int_key INTEGER GENERATED ALWAYS AS (2 + 2 + col_int_nokey) STORED,
335 PRIMARY KEY (pk)
336 );
337 CREATE TABLE C (
338 pk INTEGER AUTO_INCREMENT,
339 col_int_nokey INTEGER,
340 col_int_key INTEGER GENERATED ALWAYS AS (2 + 2 + col_int_nokey) STORED,
341 col_varchar_nokey VARCHAR(1),
342 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
343 (CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED,
344 PRIMARY KEY (pk),
345 KEY (col_int_key),
346 KEY (col_varchar_key, col_int_key)
347 );
348 INSERT INTO C (
349 col_int_nokey,
350 col_varchar_nokey
351 ) VALUES (4, 'v'),(62, 'v'),(7, 'c'),(1, NULL),(0, 'x'),(7, 'i'),(7, 'e'),(1, 'p'),(7, 's'),(1, 'j'),(5, 'z'),(2, 'c'),(0, 'a'),(1, 'q'),(8, 'y'),(1, NULL),(1, 'r'),(9, 'v'),(1, NULL),(5, 'r');
352 CREATE OR REPLACE ALGORITHM=MERGE VIEW V1 AS SELECT alias1.
353 col_varchar_key AS field1 , alias1.pk AS field2, alias2.
354 col_int_nokey AS field3 FROM C AS alias1 LEFT JOIN A AS alias2 ON
355 alias1.pk = alias2.col_int_key WHERE alias1.pk > 8 AND alias1
356 .pk < ( 9 + 2 ) AND alias1.col_int_key <> 1 OR alias1.col_int_key
357 > 0 AND alias1.col_int_key <= ( 3 + 2 ) ORDER BY field1, field2, field3
358 LIMIT 100 OFFSET 6;
359 Warnings:
360 Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm)
361 SELECT * FROM V1;
362 field1 field2 field3
363 qq 14 NULL
364 rr 17 NULL
365 ss 9 NULL
366 xx 5 NULL
367 DROP VIEW V1;
368 DROP TABLE A,C;
369 #
370 # Bug#20406510: WL411:VALGRIND WARNINGS WITH
371 # COUNT DISTINCT QUERY ON VIRTUAL GC VARCHAR COLUMN
372 #
373 CREATE TABLE A (
374 pk INTEGER AUTO_INCREMENT,
375 col_time_key TIME NOT NULL,
376 col_datetime_key DATETIME NOT NULL,
377 PRIMARY KEY (pk),
378 KEY (col_time_key),
379 KEY (col_datetime_key)
380 );
381 CREATE TABLE C (
382 pk INTEGER AUTO_INCREMENT,
383 col_int_key INTEGER NOT NULL,
384 col_varchar_key VARCHAR(1) NOT NULL,
385 col_varchar_nokey VARCHAR(2) GENERATED ALWAYS AS
386 (CONCAT(col_varchar_key, col_varchar_key)),
387 PRIMARY KEY (pk),
388 KEY (col_int_key),
389 KEY (col_varchar_key, col_int_key)
390 );
391 INSERT INTO C (col_int_key,col_varchar_key) VALUES (0, 'j'),(8, 'v'),(1, 'c'),(8, 'm'),(9, 'd');
392 SELECT MIN( alias2 . col_int_key ) AS field1,
393 COUNT( DISTINCT alias2 . col_varchar_nokey ) AS field2
394 FROM ( A AS alias1 , C AS alias2 )
395 ORDER BY alias1.col_time_key, alias1.col_datetime_key, alias1.pk ASC;
396 field1 field2
397 NULL 0
398 DROP TABLE A,C;
399 #
400 # Bug#20566325: WL8149: INNODB: FAILING ASSERTION:
401 # COL_NR < TABLE->N_DEF
402 #
403 CREATE TABLE A (
404 pk INTEGER AUTO_INCREMENT,
405 col_varchar_nokey VARCHAR(1) NOT NULL,
406 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
407 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
408 PRIMARY KEY (pk)
409 );
410 INSERT /*! IGNORE */ INTO A (col_varchar_nokey) VALUES ('k');
411 CREATE TABLE CC (
412 pk INTEGER AUTO_INCREMENT,
413 col_datetime_nokey DATETIME /*! NULL */,
414 col_time_nokey TIME /*! NULL */,
415 col_time_key TIME GENERATED ALWAYS AS
416 (ADDTIME(col_datetime_nokey, col_time_nokey)),
417 col_varchar_nokey VARCHAR(1) /*! NULL */,
418 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
419 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
420 PRIMARY KEY (pk));
421 INSERT INTO CC (col_time_nokey,col_datetime_nokey,col_varchar_nokey) VALUES
422 ('13:06:13.033877','1900-01-01 00:00:00', 'p'),
423 (NULL, '2007-05-25 11:58:54.015689', 'g');
424 SELECT
425 table1.col_time_key AS field1,
426 'z' AS field2
427 FROM
428 (CC AS table1 LEFT OUTER JOIN (A AS table2 STRAIGHT_JOIN CC AS table3 ON
429 (table3.col_varchar_key = table2.col_varchar_nokey)) ON
430 (table3.col_varchar_key = table2.col_varchar_nokey))
431 WHERE
432 table2.pk != 6
433 AND table1.col_varchar_key IN ('l', 's' , 'b' )
434 AND table3.col_varchar_key != table1.col_varchar_key
435 ORDER BY table1.col_varchar_key , field1 , field2;
436 field1 field2
437 DROP TABLE A,CC;
438 #
439 # Bug#20573302: WL8149: SEGV IN HA_INNOBASE::
440 # BUILD_TEMPLATE AT INNOBASE/HANDLER/HA_INNODB.CC:665
441 #
442 CREATE TABLE c (
443 pk INTEGER AUTO_INCREMENT,
444 col_int_nokey INTEGER NOT NULL,
445 col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey) VIRTUAL,
446 col_date_nokey DATE NOT NULL,
447 col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL,
448 col_datetime_nokey DATETIME NOT NULL,
449 col_time_nokey TIME NOT NULL,
450 col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)),
451 col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)),
452 col_varchar_nokey VARCHAR(1) NOT NULL,
453 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)),
454 PRIMARY KEY (pk),
455 KEY (col_int_key),
456 KEY (col_varchar_key),
457 KEY (col_date_key),
458 KEY (col_time_key),
459 KEY (col_datetime_key),
460 KEY (col_int_key, col_varchar_key),
461 KEY (col_int_key, col_varchar_key, col_date_key,
462 col_time_key, col_datetime_key));
463 INSERT /*! IGNORE */ INTO c (
464 col_int_nokey,
465 col_date_nokey,
466 col_time_nokey,
467 col_datetime_nokey,
468 col_varchar_nokey
469 ) VALUES
470 (1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'),
471 (8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'),
472 (9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'),
473 (24, '2000-10-08', '10:14:58.018534', '2006-10-12 04:32:53.031976', 'd'),
474 (6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'),
475 (1, '2008-01-23', '11:14:24.032949', '2004-10-02 20:31:15.022553', 't'),
476 (6, '2007-06-18', NULL, '2002-08-20 22:48:00.035785', 'd'),
477 (2, '2002-10-13', '00:00:00', '1900-01-01 00:00:00', 's'),
478 (4, '1900-01-01', '15:57:25.019666', '2005-08-15 00:00:00', 'r'),
479 (8, NULL, '07:05:51.006712', '1900-01-01 00:00:00', 'm'),
480 (4, '2006-03-09', '19:22:21.057406', '2008-05-16 08:09:06.002924', 'b'),
481 (4, '2001-06-05', '03:53:16.001370', '2001-01-20 12:47:23.022022', 'x'),
482 (7, '2006-05-28', '09:16:38.034570', '2008-07-02 00:00:00', 'g'),
483 (4, '2001-04-19', '15:37:26.028315', '1900-01-01 00:00:00', 'p'),
484 (1, '1900-01-01', '00:00:00', '2002-12-08 11:34:58.001571', 'q'),
485 (9, '2004-08-20', '05:03:03.047452', '1900-01-01 00:00:00', 'w'),
486 (4, '2004-10-10', '02:59:24.063764', '1900-01-01 00:00:00', 'd'),
487 (8, '2000-04-02', '00:01:58.064243', '2002-08-25 20:35:06.064634', 'e'),
488 (4, '2006-11-02', '00:00:00', '2001-10-22 11:13:24.048128', 'b'),
489 (8, '2009-01-28', '02:20:16.024931', '2003-03-12 02:00:34.029335', 'y');
490 Warnings:
491 Note 1265 Data truncated for column 'col_time_key' at row 1
492 Note 1265 Data truncated for column 'col_time_key' at row 2
493 Note 1265 Data truncated for column 'col_time_key' at row 3
494 Note 1265 Data truncated for column 'col_time_key' at row 4
495 Note 1265 Data truncated for column 'col_time_key' at row 5
496 Note 1265 Data truncated for column 'col_time_key' at row 6
497 Warning 1048 Column 'col_time_nokey' cannot be null
498 Note 1265 Data truncated for column 'col_time_key' at row 7
499 Note 1265 Data truncated for column 'col_time_key' at row 8
500 Note 1265 Data truncated for column 'col_time_key' at row 9
501 Warning 1048 Column 'col_date_nokey' cannot be null
502 Warning 1292 Incorrect datetime value: '0000-00-00'
503 Note 1265 Data truncated for column 'col_time_key' at row 10
504 Note 1265 Data truncated for column 'col_time_key' at row 11
505 Note 1265 Data truncated for column 'col_time_key' at row 12
506 Note 1265 Data truncated for column 'col_time_key' at row 13
507 Note 1265 Data truncated for column 'col_time_key' at row 14
508 Note 1265 Data truncated for column 'col_time_key' at row 15
509 Note 1265 Data truncated for column 'col_time_key' at row 16
510 Note 1265 Data truncated for column 'col_time_key' at row 17
511 Note 1265 Data truncated for column 'col_time_key' at row 18
512 Note 1265 Data truncated for column 'col_time_key' at row 19
513 Note 1265 Data truncated for column 'col_time_key' at row 20
514 CREATE TABLE cc (
515 pk INTEGER AUTO_INCREMENT,
516 col_int_nokey INTEGER NOT NULL,
517 col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey) VIRTUAL,
518 col_date_nokey DATE NOT NULL,
519 col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL,
520 col_datetime_nokey DATETIME NOT NULL,
521 col_time_nokey TIME NOT NULL,
522 col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)),
523 col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)),
524 col_varchar_nokey VARCHAR(1) NOT NULL,
525 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)),
526 PRIMARY KEY (pk),
527 KEY (col_int_key),
528 KEY (col_varchar_key),
529 KEY (col_date_key),
530 KEY (col_time_key),
531 KEY (col_datetime_key),
532 KEY (col_int_key, col_varchar_key),
533 KEY (col_int_key, col_varchar_key, col_date_key,
534 col_time_key, col_datetime_key));
535 INSERT /*! IGNORE */ INTO cc (
536 col_int_nokey,
537 col_date_nokey,
538 col_time_nokey,
539 col_datetime_nokey,
540 col_varchar_nokey
541 ) VALUES
542 (0, '2003-02-06', '22:02:09.059926', '2003-08-07 14:43:09.011144', 'x'),
543 (0, '2005-04-16', '19:33:15.014160', '2005-12-11 00:00:00', 'n'),
544 (1, '2005-07-23', '22:03:16.058787', '2005-12-26 20:48:07.043628', 'w'),
545 (7, '2001-11-15', '06:31:23.027263', '2008-06-12 06:41:21.012493', 's'),
546 (0, '2006-03-24', '02:19:08.013275', '2007-10-11 18:46:28.030000', 'a'),
547 (4, '2008-07-10', NULL, '2006-04-04 22:22:40.057947', 'd'),
548 (1, '2009-12-07', NULL, '2002-08-10 20:52:58.035137', 'w'),
549 (1, '2008-05-01', '10:28:01.038587', '2008-10-03 11:17:23.005299', 'j'),
550 (1, '2008-06-22', '00:00:00', '2009-01-06 20:11:01.034339', 'm'),
551 (4, '2001-11-11', '15:02:50.048785', '2009-09-19 00:00:00', 'k'),
552 (7, '2000-12-21', '05:29:13.012729', '2007-09-02 12:14:27.029187', 't'),
553 (4, '2007-09-03', '23:45:33.048507', '2003-09-26 00:00:00', 'k'),
554 (2, '2003-02-18', '19:10:53.057455', '2001-11-18 18:10:16.063189', 'e'),
555 (0, '2008-12-01', '01:45:27.037313', '2005-02-15 04:08:17.015554', 'i'),
556 (1, '2008-10-18', '03:56:03.060218', '2009-06-13 23:04:40.013006', 't'),
557 (91, '2004-08-28', '12:43:17.023797', '1900-01-01 00:00:00', 'm'),
558 (6, '2006-10-05', '13:33:46.053634', '2005-03-20 02:48:24.045653', 'z'),
559 (3, '2003-05-16', NULL, '2002-03-16 11:47:27.045297', 'c'),
560 (6, '2008-10-10', NULL, '2000-05-22 00:00:00', 'i'),
561 (8, '2002-01-19', '05:18:40.006865', '2009-02-12 00:00:00', 'v');
562 Warnings:
563 Note 1265 Data truncated for column 'col_time_key' at row 1
564 Note 1265 Data truncated for column 'col_time_key' at row 2
565 Note 1265 Data truncated for column 'col_time_key' at row 3
566 Note 1265 Data truncated for column 'col_time_key' at row 4
567 Note 1265 Data truncated for column 'col_time_key' at row 5
568 Warning 1048 Column 'col_time_nokey' cannot be null
569 Note 1265 Data truncated for column 'col_time_key' at row 6
570 Warning 1048 Column 'col_time_nokey' cannot be null
571 Note 1265 Data truncated for column 'col_time_key' at row 7
572 Note 1265 Data truncated for column 'col_time_key' at row 8
573 Note 1265 Data truncated for column 'col_time_key' at row 9
574 Note 1265 Data truncated for column 'col_time_key' at row 10
575 Note 1265 Data truncated for column 'col_time_key' at row 11
576 Note 1265 Data truncated for column 'col_time_key' at row 12
577 Note 1265 Data truncated for column 'col_time_key' at row 13
578 Note 1265 Data truncated for column 'col_time_key' at row 14
579 Note 1265 Data truncated for column 'col_time_key' at row 15
580 Note 1265 Data truncated for column 'col_time_key' at row 16
581 Note 1265 Data truncated for column 'col_time_key' at row 17
582 Warning 1048 Column 'col_time_nokey' cannot be null
583 Note 1265 Data truncated for column 'col_time_key' at row 18
584 Warning 1048 Column 'col_time_nokey' cannot be null
585 Note 1265 Data truncated for column 'col_time_key' at row 19
586 Note 1265 Data truncated for column 'col_time_key' at row 20
587 EXPLAIN
588 SELECT subquery2_t2.col_int_key AS subquery2_field1
589 FROM (c AS subquery2_t1 RIGHT JOIN
590 (c AS subquery2_t2 LEFT JOIN cc AS subquery2_t3 ON
591 (subquery2_t3.col_int_nokey = subquery2_t2.col_int_key )) ON
592 (subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key))
593 ORDER BY subquery2_field1;
594 id select_type table type possible_keys key key_len ref rows Extra
595 1 SIMPLE subquery2_t2 index NULL col_int_key_2 10 NULL # #
596 1 SIMPLE subquery2_t3 ALL NULL NULL NULL NULL # #
597 1 SIMPLE subquery2_t1 index NULL PRIMARY 4 NULL # #
598 SELECT subquery2_t2.col_int_key AS subquery2_field1
599 FROM (c AS subquery2_t1 RIGHT JOIN
600 (c AS subquery2_t2 LEFT JOIN cc AS subquery2_t3 ON
601 (subquery2_t3.col_int_nokey = subquery2_t2.col_int_key )) ON
602 (subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key))
603 ORDER BY subquery2_field1;
604 subquery2_field1
605 1
606 1
607 1
608 1
609 1
610 1
611 1
612 1
613 1
614 1
615 1
616 1
617 1
618 1
619 1
620 1
621 1
622 1
623 1
624 1
625 1
626 1
627 1
628 1
629 1
630 1
631 1
632 1
633 1
634 1
635 1
636 1
637 1
638 1
639 2
640 4
641 4
642 4
643 4
644 4
645 4
646 4
647 4
648 4
649 4
650 4
651 4
652 4
653 4
654 4
655 4
656 4
657 4
658 4
659 4
660 4
661 4
662 4
663 4
664 4
665 4
666 4
667 4
668 4
669 4
670 4
671 4
672 4
673 4
674 4
675 4
676 4
677 6
678 6
679 6
680 6
681 7
682 7
683 8
684 8
685 8
686 8
687 9
688 9
689 24
690 SELECT subquery2_t2.col_int_key AS subquery2_field1
691 FROM (c AS subquery2_t1 RIGHT JOIN
692 (c AS subquery2_t2 LEFT JOIN cc AS subquery2_t3 ON
693 (subquery2_t3.col_int_nokey = subquery2_t2.col_int_key )) ON
694 (subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key))
695 ORDER BY subquery2_field1;
696 subquery2_field1
697 1
698 1
699 1
700 1
701 1
702 1
703 1
704 1
705 1
706 1
707 1
708 1
709 1
710 1
711 1
712 1
713 1
714 1
715 1
716 1
717 1
718 1
719 1
720 1
721 1
722 1
723 1
724 1
725 1
726 1
727 1
728 1
729 1
730 1
731 2
732 4
733 4
734 4
735 4
736 4
737 4
738 4
739 4
740 4
741 4
742 4
743 4
744 4
745 4
746 4
747 4
748 4
749 4
750 4
751 4
752 4
753 4
754 4
755 4
756 4
757 4
758 4
759 4
760 4
761 4
762 4
763 4
764 4
765 4
766 4
767 4
768 4
769 6
770 6
771 6
772 6
773 7
774 7
775 8
776 8
777 8
778 8
779 9
780 9
781 24
782 DROP TABLE c,cc;
783 #
784 # Bug#2081065: WL8149:RESULT DIFF SEEN FOR SIMPLE
785 # RANGE QUERIES WITH ORDER BY
786 #
787 CREATE TABLE cc (
788 pk INTEGER AUTO_INCREMENT,
789 col_int_nokey INTEGER NOT NULL,
790 col_int_key INTEGER GENERATED ALWAYS AS
791 (col_int_nokey + col_int_nokey) VIRTUAL,
792 PRIMARY KEY (pk),
793 KEY (col_int_key)
794 );
795 INSERT INTO cc (col_int_nokey) VALUES (0),(1),(7),(0),(4),(5);
796 EXPLAIN SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3;
797 id select_type table type possible_keys key key_len ref rows Extra
798 1 SIMPLE cc range col_int_key col_int_key 5 NULL # #
799 SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3;
800 pk
801 5
802 6
803 3
804 EXPLAIN SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3 ORDER BY 1;
805 id select_type table type possible_keys key key_len ref rows Extra
806 1 SIMPLE cc range col_int_key col_int_key 5 NULL # #
807 SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3 ORDER BY 1;
808 pk
809 3
810 5
811 6
812 DROP TABLE cc;
813 #
814 # Bug#20849676 :WL8149:ASSERTION `!TABLE || (!TABLE->READ_SET
815 # || BITMAP_IS_SET(TABLE->READ_SET
816 #
817 CREATE TABLE c (
818 pk INTEGER AUTO_INCREMENT,
819 col_int_nokey INTEGER NOT NULL,
820 col_int_key INTEGER GENERATED ALWAYS AS
821 (col_int_nokey + col_int_nokey) VIRTUAL,
822 col_varchar_nokey VARCHAR(1) NOT NULL,
823 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
824 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
825 PRIMARY KEY (pk),
826 KEY (col_int_key),
827 KEY (col_varchar_key),
828 KEY (col_int_key, col_varchar_key)
829 ) ;
830 INSERT INTO c (col_int_nokey, col_varchar_nokey) VALUES
831 (1, 'c'),(8, 'm'),(9, 'd'),(24, 'd'),(6, 'y'),(1, 't'),(6, 'd'),
832 (2, 'r'),(8, 'm'),(4, 'b'),(4, 'x'),(7, 'g'),(4, 'p'),(1, 'q'),
833 (9, 'w'),(4, 'd'),(8, 'e'),(4, 'b'),(8, 'y');
834 CREATE TABLE a (
835 pk INTEGER AUTO_INCREMENT,
836 col_datetime_nokey DATETIME NOT NULL,
837 col_time_nokey TIME NOT NULL,
838 col_datetime_key DATETIME GENERATED ALWAYS AS
839 (ADDTIME(col_datetime_nokey, col_time_nokey)),
840 col_time_key TIME GENERATED ALWAYS AS
841 (ADDTIME(col_datetime_nokey, col_time_nokey)),
842 col_varchar_nokey VARCHAR(1) NOT NULL,
843 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
844 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
845 PRIMARY KEY (pk),
846 KEY (col_varchar_key),
847 KEY (col_time_key),
848 KEY (col_datetime_key),
849 KEY (col_varchar_key, col_time_key, col_datetime_key)
850 );
851 INSERT INTO a (
852 col_time_nokey,
853 col_datetime_nokey,
854 col_varchar_nokey) VALUES
855 ('04:08:02.046897', '2001-11-04 19:07:55.051133', 'k');
856 Warnings:
857 Note 1265 Data truncated for column 'col_time_key' at row 1
858 ANALYZE TABLE a, c;
859 Table Op Msg_type Msg_text
860 test.a analyze status Engine-independent statistics collected
861 test.a analyze status OK
862 test.c analyze status Engine-independent statistics collected
863 test.c analyze status OK
864 EXPLAIN
865 SELECT
866 table1.pk AS field1 ,
867 table1.col_datetime_key AS field2
868 FROM
869 ( a AS table1 LEFT JOIN ( ( c AS table2 STRAIGHT_JOIN ( SELECT
870 SUBQUERY1_t1.* FROM ( c AS SUBQUERY1_t1 INNER JOIN ( c AS SUBQUERY1_t2
871 STRAIGHT_JOIN c AS SUBQUERY1_t3 ON (SUBQUERY1_t3.col_varchar_key =
872 SUBQUERY1_t2.col_varchar_key ) )
873 ON (SUBQUERY1_t3.pk = SUBQUERY1_t2.col_int_key
874 OR SUBQUERY1_t1.col_int_key <> 1 ) )
875 WHERE SUBQUERY1_t2.pk >= 9 ) AS table3
876 ON (table3.col_int_key = table2.col_int_key ) ) )
877 ON (table3.col_int_nokey = table2.pk ) )
878 GROUP BY field1, field2;
879 id select_type table type possible_keys key key_len ref rows Extra
880 1 PRIMARY table1 system NULL NULL NULL NULL #
881 1 PRIMARY table2 ALL PRIMARY,col_int_key,col_int_key_2 NULL NULL NULL # Using where
882 1 PRIMARY <derived2> ref key0 key0 9 test.table2.pk,test.table2.col_int_key #
883 2 DERIVED SUBQUERY1_t2 ALL PRIMARY,col_int_key,col_varchar_key,col_int_key_2 NULL NULL NULL # Using where
884 2 DERIVED SUBQUERY1_t3 ref PRIMARY,col_varchar_key col_varchar_key 5 test.SUBQUERY1_t2.col_varchar_key #
885 2 DERIVED SUBQUERY1_t1 ALL col_int_key,col_int_key_2 NULL NULL NULL # Using where; Using join buffer (flat, BNL join)
886 SELECT
887 table1.pk AS field1 ,
888 table1.col_datetime_key AS field2
889 FROM
890 ( a AS table1 LEFT JOIN ( ( c AS table2 STRAIGHT_JOIN ( SELECT
891 SUBQUERY1_t1.* FROM ( c AS SUBQUERY1_t1 INNER JOIN ( c AS SUBQUERY1_t2
892 STRAIGHT_JOIN c AS SUBQUERY1_t3 ON (SUBQUERY1_t3.col_varchar_key =
893 SUBQUERY1_t2.col_varchar_key ) )
894 ON (SUBQUERY1_t3.pk = SUBQUERY1_t2.col_int_key
895 OR SUBQUERY1_t1.col_int_key <> 1 ) )
896 WHERE SUBQUERY1_t2.pk >= 9 ) AS table3
897 ON (table3.col_int_key = table2.col_int_key ) ) )
898 ON (table3.col_int_nokey = table2.pk ) )
899 GROUP BY field1, field2;
900 field1 field2
901 1 2001-11-04 23:15:57
902 DROP TABLE IF EXISTS c,a;
903 CREATE TABLE c (
904 col_int_nokey INTEGER NOT NULL,
905 col_int_key INTEGER GENERATED ALWAYS AS
906 (col_int_nokey + col_int_nokey) VIRTUAL,
907 col_varchar_nokey VARCHAR(1) NOT NULL,
908 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
909 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
910 KEY (col_int_key),
911 KEY (col_int_key, col_varchar_key)
912 ) ;
913 INSERT INTO c (
914 col_int_nokey,
915 col_varchar_nokey
916 ) VALUES (1, 'c'),(8, 'm'),(9, 'd'),(24, 'd'),(6, 'y'),(1, 't'),
917 (6, 'd'),(2, 's'),(4, 'r'),(8, 'm'),(4, 'b'),(4, 'x'),(7, 'g'),(4, 'p'),
918 (1, 'q'),(9, 'w'),(4, 'd'),(8, 'e'),(4, 'b'),(8, 'y');
919 CREATE TABLE cc (
920 col_int_nokey INTEGER,
921 col_int_key INTEGER GENERATED ALWAYS AS
922 (col_int_nokey + col_int_nokey) VIRTUAL,
923 col_varchar_nokey VARCHAR(1),
924 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
925 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
926 KEY (col_int_key),
927 KEY (col_varchar_key),
928 KEY (col_int_key, col_varchar_key),
929 KEY (col_int_key, col_int_nokey),
930 KEY (col_varchar_key, col_varchar_nokey)
931 );
932 INSERT INTO cc (
933 col_int_nokey,
934 col_varchar_nokey
935 ) VALUES (8, 'p'),(9, 'g'),(9, 'i'),(4, 'p'),(7, 'h'),(1, 'e'),(8, 'e'),(6, 'u'),
936 (6, 'j'),(6, 'e'),(1, 'z'),(227, 'w'),(NULL, 't'),(9, 'i'),(1, 'i'),(8, 'i'),
937 (5, 'b'),(8,'m'),(7, 'j'),(2, 'v');
938 ANALYZE TABLE c, cc;
939 Table Op Msg_type Msg_text
940 test.c analyze status Engine-independent statistics collected
941 test.c analyze status OK
942 test.cc analyze status Engine-independent statistics collected
943 test.cc analyze status OK
944 EXPLAIN SELECT
945 alias2 . col_varchar_key AS field1
946 FROM ( cc AS alias1 , cc AS alias2 )
947 WHERE
948 ( alias2 . col_int_key , alias1 . col_int_nokey )
949 NOT IN
950 (
951 SELECT
952 DISTINCT SQ1_alias2 . col_int_nokey AS SQ1_field1 ,
953 SQ1_alias1 . col_int_key AS SQ1_field2
954 FROM ( cc AS SQ1_alias1 , c AS SQ1_alias2 )
955 GROUP BY SQ1_field1 , SQ1_field2
956 )
957 GROUP BY field1;
958 id select_type table type possible_keys key key_len ref rows Extra
959 1 PRIMARY alias1 index NULL col_int_key_3 10 NULL # Using index; Using temporary; Using filesort
960 1 PRIMARY alias2 index NULL col_int_key_2 10 NULL # Using where; Using index; Using join buffer (flat, BNL join)
961 2 MATERIALIZED SQ1_alias1 index col_int_key,col_int_key_2,col_int_key_3 col_int_key 5 NULL # Using index
962 2 MATERIALIZED SQ1_alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join)
963 SELECT
964 alias2 . col_varchar_key AS field1
965 FROM ( cc AS alias1 , cc AS alias2 )
966 WHERE
967 ( alias2 . col_int_key , alias1 . col_int_nokey )
968 NOT IN
969 (
970 SELECT
971 DISTINCT SQ1_alias2 . col_int_nokey AS SQ1_field1 ,
972 SQ1_alias1 . col_int_key AS SQ1_field2
973 FROM ( cc AS SQ1_alias1 , c AS SQ1_alias2 )
974 GROUP BY SQ1_field1 , SQ1_field2
975 )
976 GROUP BY field1;
977 field1
978 bb
979 ee
980 gg
981 hh
982 ii
983 jj
984 mm
985 pp
986 uu
987 ww
988 DROP TABLE IF EXISTS c,cc;
989 SET @save_old_sql_mode= @@sql_mode;
990 SET sql_mode="";
991 CREATE TABLE d (
992 col_int int(11) DEFAULT NULL,
993 col_varchar_10_utf8 varchar(10) CHARACTER SET utf8 DEFAULT NULL,
994 pk int(11) NOT NULL AUTO_INCREMENT,
995 col_int_key int(11) GENERATED ALWAYS AS (col_int+col_int) VIRTUAL,
996 col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT(SUBSTRING(col_varchar_10_utf8, -1), 5)) VIRTUAL,
997 PRIMARY KEY (pk),
998 KEY col_int_key (col_int_key),
999 KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
1000 KEY cover_key1 (col_int_key, col_varchar_10_utf8_key)
1001 );
1002 INSERT INTO d (col_int, col_varchar_10_utf8) VALUES ('qhlhtrovam',1),('how',2),('htrovamzqr',3),('rovamzqrdc',4),('well',5),('g',6),('rdcenchyhu',7),('want',8);
1003 SELECT table1.pk AS field1 FROM d AS table1 LEFT JOIN d AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_10_utf8_key WHERE table1.col_int_key IS NULL GROUP BY table1.pk ;
1004 field1
1005 DROP TABLE d;
1006 #
1007 # Bug#21153237: WL8149: QUERIES USING FILESORT
1008 # ON VIRTUAL GC HAVING INDEX GIVES WRONG RESULTS
1009 #
1010 CREATE TABLE j (
1011 col_int int(11),
1012 pk int(11) NOT NULL,
1013 col_varchar_10_utf8 varchar(10) CHARACTER SET utf8 DEFAULT NULL,
1014 col_varchar_255_utf8_key varchar(255) CHARACTER SET utf8 GENERATED ALWAYS AS
1015 (col_varchar_10_utf8) VIRTUAL,
1016 PRIMARY KEY (pk),
1017 KEY cover_key1 (col_int, col_varchar_255_utf8_key));
1018 INSERT INTO j(col_int, pk, col_varchar_10_utf8) VALUES(9, 1, '951910400'),
1019 (-1934295040, 2, '1235025920'),(-584581120, 3, '-1176633344'),(3, 4, '1074462720');
1020 EXPLAIN SELECT col_varchar_255_utf8_key FROM j ORDER BY 1;
1021 id select_type table type possible_keys key key_len ref rows Extra
1022 1 SIMPLE j index NULL cover_key1 773 NULL # Using index; Using filesort
1023 SELECT col_varchar_255_utf8_key FROM j ORDER BY col_varchar_255_utf8_key;
1024 col_varchar_255_utf8_key
1025 -117663334
1026 1074462720
1027 1235025920
1028 951910400
1029 DROP TABLE j;
1030 set sql_mode= @save_old_sql_mode;
1031 CREATE TABLE cc (
1032 pk int(11) NOT NULL AUTO_INCREMENT,
1033 col_int_nokey int(11) NOT NULL,
1034 col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) STORED,
1035 col_date_nokey date NOT NULL,
1036 col_date_key date GENERATED ALWAYS AS (col_date_nokey) STORED,
1037 col_datetime_nokey datetime NOT NULL,
1038 col_time_nokey time NOT NULL,
1039 col_datetime_key datetime GENERATED ALWAYS AS (col_datetime_nokey)STORED,
1040 col_time_key time GENERATED ALWAYS AS (col_time_nokey) STORED,
1041 col_varchar_nokey varchar(1) NOT NULL,
1042 col_varchar_key varchar(1) GENERATED ALWAYS AS (col_varchar_nokey)STORED,
1043 PRIMARY KEY (pk),
1044 KEY gc_idx1 (col_int_key),
1045 KEY gc_idx2 (col_varchar_key),
1046 KEY gc_idx3 (col_date_key),
1047 KEY gc_idx4 (col_time_key),
1048 KEY gc_idx5 (col_datetime_key),
1049 KEY gc_idx6 (col_varchar_key,col_int_key),
1050 KEY gc_idx7 (col_date_key,col_datetime_key,col_time_key),
1051 KEY gc_idx8(col_int_key,col_varchar_key,col_date_key,col_time_key,
1052 col_datetime_key)
1053 );
1054 INSERT INTO cc (
1055 col_int_nokey,
1056 col_date_nokey,
1057 col_time_nokey,
1058 col_datetime_nokey,
1059 col_varchar_nokey
1060 ) VALUES (1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'),
1061 (8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'),
1062 (9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'),
1063 (24, '2000-10-08', '10:14:58.018534', '2006-10-12 04:32:53.031976', 'd'),
1064 (6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'),
1065 (1, '2008-01-23', '11:14:24.032949', '2004-10-02 20:31:15.022553', 't');
1066 SET @save_old_sql_mode= @@sql_mode;
1067 SET sql_mode="";
1068 SELECT DISTINCT alias1.col_varchar_key AS field1
1069 FROM ( cc AS alias1 STRAIGHT_JOIN
1070 (( cc AS alias2 STRAIGHT_JOIN cc AS alias3 ON
1071 (alias3.col_varchar_key > alias2.col_varchar_key ) ) ) ON
1072 (( alias3 .pk >= alias2.col_int_nokey ) AND
1073 (alias3 .pk >= alias2.col_int_nokey ) ))
1074 WHERE alias1.col_varchar_key <= 'v'
1075 GROUP BY field1 HAVING field1 = 91
1076 ORDER BY field1, alias1.col_date_key, field1 ASC, field1 DESC,
1077 alias1.col_time_key ASC, field1;
1078 field1
1079 Warnings:
1080 Warning 1292 Truncated incorrect DECIMAL value: 'c'
1081 Warning 1292 Truncated incorrect DECIMAL value: 't'
1082 Warning 1292 Truncated incorrect DECIMAL value: 'm'
1083 Warning 1292 Truncated incorrect DECIMAL value: 'd'
1084 Warning 1292 Truncated incorrect DECIMAL value: 'd'
1085 DROP TABLE cc;
1086 SET sql_mode=@save_old_sql_mode;
1087 #
1088 # Bug#20797941: WL8149:ASSERTION !TABLE ||
1089 # (!TABLE->READ_SET || BITMAP_IS_SET(TABLE->READ_SET
1090 #
1091 CREATE TABLE t(a int, b int as(a+1));
1092 INSERT INTO t(a) values(1),(2);
1093 SELECT * FROM t ORDER BY b;
1094 a b
1095 1 2
1096 2 3
1097 DROP TABLE t;
1098 #
1099 # Testing a few index-based accesses on the virtual column
1100 #
1101 CREATE TABLE t1 (
1102 id int(11) NOT NULL,
1103 b int(11) GENERATED ALWAYS AS (id+1) VIRTUAL,
1104 UNIQUE KEY (b) );
1105 INSERT INTO t1 (id) VALUES(NULL);
1106 ERROR 23000: Column 'id' cannot be null
1107 INSERT INTO t1 (id) VALUES(2),(3);
1108 EXPLAIN SELECT * FROM t1 FORCE INDEX(b) WHERE b=3;
1109 id select_type table type possible_keys key key_len ref rows Extra
1110 1 SIMPLE t1 const b b 5 const 1
1111 EXPLAIN SELECT * FROM t1 AS t2 STRAIGHT_JOIN t1 FORCE INDEX(b) WHERE t1.b=t2.b;
1112 id select_type table type possible_keys key key_len ref rows Extra
1113 1 SIMPLE t2 ALL b NULL NULL NULL 2 Using where
1114 1 SIMPLE t1 eq_ref b b 5 test.t2.b 1
1115 EXPLAIN SELECT b FROM t1 FORCE INDEX(b);
1116 id select_type table type possible_keys key key_len ref rows Extra
1117 1 SIMPLE t1 index NULL b 5 NULL 2 Using index
1118 INSERT INTO t1 (id) VALUES(4),(5),(6),(7),(8),(9),(10);
1119 EXPLAIN SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5;
1120 id select_type table type possible_keys key key_len ref rows Extra
1121 1 SIMPLE t1 range b b 5 NULL 3 Using where; Using index
1122 EXPLAIN SELECT * FROM t2 AS t1 WHERE b NOT IN (SELECT b FROM t1 FORCE INDEX(b));
1123 id select_type table type possible_keys key key_len ref rows Extra
1124 1 PRIMARY t1 system NULL NULL NULL NULL 1
1125 2 SUBQUERY t1 index_subquery b b 5 func 3 Using index; Full scan on NULL key
1126 DROP TABLE t1;
1127 DROP TABLE t2, t3;
1128 #
1129 # Bug#21317507:GC: STORED COLUMN REJECTED, BUT VIRTUAL IS ACCEPTED
1130 #
1131 CREATE TABLE t1(a INT);
1132 INSERT INTO t1 VALUES(2147483647);
1133 ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL;
1134 ERROR 22003: Out of range value for column 'b' at row 1
1135 ALTER TABLE t1 DROP COLUMN b;
1136 ERROR 42000: Can't DROP COLUMN `b`; check that it exists
1137 ALTER TABLE t1 ADD COLUMN c SMALLINT AS (a) VIRTUAL;
1138 ERROR 22003: Out of range value for column 'c' at row 1
1139 ALTER TABLE t1 DROP COLUMN c;
1140 ERROR 42000: Can't DROP COLUMN `c`; check that it exists
1141 ALTER TABLE t1 ADD COLUMN d SMALLINT AS (a) VIRTUAL;
1142 ERROR 22003: Out of range value for column 'd' at row 1
1143 ALTER TABLE t1 DROP COLUMN d;
1144 ERROR 42000: Can't DROP COLUMN `d`; check that it exists
1145 ALTER TABLE t1 ADD COLUMN c INT AS(a) VIRTUAL;
1146 ALTER TABLE t1 CHANGE c c SMALLINT AS(a) VIRTUAL;
1147 ERROR 22003: Out of range value for column 'c' at row 1
1148 ALTER TABLE t1 MODIFY c TINYINT AS(a) VIRTUAL;
1149 ERROR 22003: Out of range value for column 'c' at row 1
1150 SELECT * FROM t1;
1151 a c
1152 2147483647 2147483647
1153 DROP TABLE t1;
1154 CREATE TABLE t1(a INT);
1155 INSERT INTO t1 VALUES(2147483647);
1156 ALTER TABLE t1 ADD COLUMN h INT AS (a) VIRTUAL;
1157 ALTER TABLE t1 CHANGE h i INT AS (a) VIRTUAL, ALGORITHM=COPY;
1158 ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE;
1159 Got one of the listed errors
1160 ALTER TABLE t1 ADD COLUMN e SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE;
1161 Got one of the listed errors
1162 ALTER TABLE t1 ADD COLUMN f SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=SHARED;
1163 ERROR 22003: Out of range value for column 'f' at row 1
1164 ALTER TABLE t1 ADD COLUMN g SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=EXCLUSIVE;
1165 ERROR 22003: Out of range value for column 'g' at row 1
1166 DROP TABLE t1;
1167 #
1168 # Bug#21980430 GCOLS: CRASHING
1169 #
1170 CREATE TABLE t (
1171 a INT,
1172 b BLOB,
1173 c BLOB GENERATED ALWAYS AS (a+b) VIRTUAL,
1174 UNIQUE KEY i0008 (a)
1175 );
1176 INSERT INTO t(a,b) VALUES(1,'cccc');
1177 EXPLAIN SELECT /*+ bka() */ 1 AS c FROM t AS b RIGHT JOIN t AS c ON b.a > c.c
1178 WHERE b.b>c.a;
1179 id select_type table type possible_keys key key_len ref rows Extra
1180 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
1181 Warnings:
1182 Warning 1292 Truncated incorrect DOUBLE value: 'cccc'
1183 Warning 1292 Truncated incorrect DECIMAL value: 'cccc'
1184 SELECT /*+ bka() */ 1 AS c FROM t AS b RIGHT JOIN t AS c ON b.a > c.c
1185 WHERE b.b>c.a;
1186 c
1187 Warnings:
1188 Warning 1292 Truncated incorrect DOUBLE value: 'cccc'
1189 Warning 1292 Truncated incorrect DECIMAL value: 'cccc'
1190 DROP TABLE t;
1191 set @optimizer_switch_save = @@optimizer_switch;
1192 set optimizer_switch='mrr_cost_based=off';
1193 set @read_rnd_buffer_size_save= @@read_rnd_buffer_size;
1194 set read_rnd_buffer_size=32;
1195 CREATE TABLE t0 (
1196 i1 INTEGER NOT NULL
1197 );
1198 INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1199 CREATE TABLE t1 (
1200 pk INTEGER NOT NULL,
1201 i1 INTEGER NOT NULL,
1202 i2 INTEGER NOT NULL,
1203 v1 INTEGER GENERATED ALWAYS AS (i2 + 1) VIRTUAL,
1204 v2 INTEGER GENERATED ALWAYS AS (i1 / (i1 - i2 + 57)) VIRTUAL,
1205 PRIMARY KEY (pk),
1206 INDEX idx(i1)
1207 );
1208 INSERT INTO t1 (pk, i1, i2)
1209 SELECT a0.i1 + a1.i1*10 + a2.i1*100,
1210 a0.i1 + a1.i1*10,
1211 a0.i1 + a1.i1*10
1212 FROM t0 AS a0, t0 AS a1, t0 AS a2;
1213 EXPLAIN SELECT * FROM t1
1214 WHERE i1 > 41 AND i1 <= 43;
1215 id select_type table type possible_keys key key_len ref rows Extra
1216 1 SIMPLE t1 range idx idx 4 NULL 20 Using index condition
1217 SELECT * FROM t1
1218 WHERE i1 > 41 AND i1 <= 43;
1219 pk i1 i2 v1 v2
1220 142 42 42 43 1
1221 143 43 43 44 1
1222 242 42 42 43 1
1223 243 43 43 44 1
1224 342 42 42 43 1
1225 343 43 43 44 1
1226 42 42 42 43 1
1227 43 43 43 44 1
1228 442 42 42 43 1
1229 443 43 43 44 1
1230 542 42 42 43 1
1231 543 43 43 44 1
1232 642 42 42 43 1
1233 643 43 43 44 1
1234 742 42 42 43 1
1235 743 43 43 44 1
1236 842 42 42 43 1
1237 843 43 43 44 1
1238 942 42 42 43 1
1239 943 43 43 44 1
1240 ALTER TABLE t1 ADD INDEX idx2(v1);
1241 EXPLAIN SELECT * FROM t1
1242 WHERE v1 > 41 AND v1 <= 43;
1243 id select_type table type possible_keys key key_len ref rows Extra
1244 1 SIMPLE t1 range idx2 idx2 5 NULL # Using index condition
1245 SELECT * FROM t1
1246 WHERE v1 > 41 AND v1 <= 43;
1247 pk i1 i2 v1 v2
1248 141 41 41 42 1
1249 142 42 42 43 1
1250 241 41 41 42 1
1251 242 42 42 43 1
1252 341 41 41 42 1
1253 342 42 42 43 1
1254 41 41 41 42 1
1255 42 42 42 43 1
1256 441 41 41 42 1
1257 442 42 42 43 1
1258 541 41 41 42 1
1259 542 42 42 43 1
1260 641 41 41 42 1
1261 642 42 42 43 1
1262 741 41 41 42 1
1263 742 42 42 43 1
1264 841 41 41 42 1
1265 842 42 42 43 1
1266 941 41 41 42 1
1267 942 42 42 43 1
1268 DROP TABLE t0, t1;
1269 set optimizer_switch= @optimizer_switch_save;
1270 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
1271 #
1272 # Bug#21872184 CONDITIONAL JUMP AT JOIN_CACHE::WRITE_RECORD_DATA IN
1273 # SQL_JOIN_BUFFER.CC
1274 #
1275 #
1276 # Test 1: Dynamic range scan with one covering index
1277 #
1278 CREATE TABLE t1 (
1279 i1 INTEGER NOT NULL,
1280 c1 VARCHAR(1) NOT NULL
1281 );
1282 INSERT INTO t1
1283 VALUES (10, 'c'), (10, 'i'), (2, 't'), (4, 'g');
1284 CREATE TABLE t2 (
1285 i1 INTEGER NOT NULL,
1286 c1 VARCHAR(1) NOT NULL
1287 );
1288 INSERT INTO t2
1289 VALUES (2, 'k'), (9, 'k'), (7, 'o'), (5, 'n'), (7, 'e');
1290 CREATE TABLE t3 (
1291 pk INTEGER NOT NULL,
1292 i1 INTEGER,
1293 i2_key INTEGER GENERATED ALWAYS AS (i1 + i1) VIRTUAL,
1294 PRIMARY KEY (pk)
1295 );
1296 # Add a covering index. The reason for this index being covering is that
1297 # secondary indexes in InnoDB include the primary key.
1298 ALTER TABLE t3 ADD INDEX v_idx (i2_key);
1299 INSERT INTO t3 (pk, i1)
1300 VALUES (1, 1), (2, 48), (3, 228), (4, 3), (5, 5),
1301 (6, 39), (7, 6), (8, 8), (9, 3);
1302 CREATE TABLE t4 (
1303 i1 INTEGER NOT NULL,
1304 c1 VARCHAR(1) NOT NULL
1305 );
1306 INSERT INTO t4
1307 VALUES (1, 'j'), (2, 'c'), (0, 'a');
1308 ANALYZE TABLE t1, t2, t3, t4;
1309 Table Op Msg_type Msg_text
1310 test.t1 analyze status Engine-independent statistics collected
1311 test.t1 analyze status OK
1312 test.t2 analyze status Engine-independent statistics collected
1313 test.t2 analyze status OK
1314 test.t3 analyze status Engine-independent statistics collected
1315 test.t3 analyze status OK
1316 test.t4 analyze status Engine-independent statistics collected
1317 test.t4 analyze status OK
1318 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
1319 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
1320 WHERE ( t3.pk IN
1321 (
1322 SELECT /*+ QB_NAME(subq1) */ t4.i1
1323 FROM t4
1324 WHERE t4.c1 < 'o'
1325 )
1326 )
1327 AND t1.i1 <= t3.i2_key;
1328 id select_type table type possible_keys key key_len ref rows Extra
1329 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
1330 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
1331 1 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where; End temporary
1332 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
1333 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
1334 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
1335 WHERE ( t3.pk IN
1336 (
1337 SELECT /*+ QB_NAME(subq1) */ t4.i1
1338 FROM t4
1339 WHERE t4.c1 < 'o'
1340 )
1341 )
1342 AND t1.i1 <= t3.i2_key;
1343 c1 i1
1344 c 2
1345 c 5
1346 c 7
1347 c 7
1348 c 9
1349 g 2
1350 g 5
1351 g 7
1352 g 7
1353 g 9
1354 i 2
1355 i 5
1356 i 7
1357 i 7
1358 i 9
1359 t 2
1360 t 2
1361 t 5
1362 t 5
1363 t 7
1364 t 7
1365 t 7
1366 t 7
1367 t 9
1368 t 9
1369 #
1370 # Test 2: Two alternative covering indexes for the range scan
1371 #
1372 ALTER TABLE t3 ADD INDEX v_idx2 (i2_key, i1);
1373 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
1374 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
1375 WHERE ( t3.pk IN
1376 (
1377 SELECT /*+ QB_NAME(subq1) */ t4.i1
1378 FROM t4
1379 WHERE t4.c1 < 'o'
1380 )
1381 )
1382 AND t1.i1 <= t3.i2_key;
1383 id select_type table type possible_keys key key_len ref rows Extra
1384 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
1385 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
1386 1 PRIMARY t3 eq_ref PRIMARY,v_idx,v_idx2 PRIMARY 4 test.t4.i1 1 Using where; End temporary
1387 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
1388 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
1389 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
1390 WHERE ( t3.pk IN
1391 (
1392 SELECT /*+ QB_NAME(subq1) */ t4.i1
1393 FROM t4
1394 WHERE t4.c1 < 'o'
1395 )
1396 )
1397 AND t1.i1 <= t3.i2_key;
1398 c1 i1
1399 c 2
1400 c 5
1401 c 7
1402 c 7
1403 c 9
1404 g 2
1405 g 5
1406 g 7
1407 g 7
1408 g 9
1409 i 2
1410 i 5
1411 i 7
1412 i 7
1413 i 9
1414 t 2
1415 t 2
1416 t 5
1417 t 5
1418 t 7
1419 t 7
1420 t 7
1421 t 7
1422 t 9
1423 t 9
1424 #
1425 # Test 3: One covering index including the base column for the virtual
1426 # column
1427 #
1428 # Drop the index with only the virtual column
1429 ALTER TABLE t3 DROP INDEX v_idx;
1430 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
1431 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
1432 WHERE ( t3.pk IN
1433 (
1434 SELECT /*+ QB_NAME(subq1) */ t4.i1
1435 FROM t4
1436 WHERE t4.c1 < 'o'
1437 )
1438 )
1439 AND t1.i1 <= t3.i2_key;
1440 id select_type table type possible_keys key key_len ref rows Extra
1441 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
1442 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
1443 1 PRIMARY t3 eq_ref PRIMARY,v_idx2 PRIMARY 4 test.t4.i1 1 Using where; End temporary
1444 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
1445 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
1446 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
1447 WHERE ( t3.pk IN
1448 (
1449 SELECT /*+ QB_NAME(subq1) */ t4.i1
1450 FROM t4
1451 WHERE t4.c1 < 'o'
1452 )
1453 )
1454 AND t1.i1 <= t3.i2_key;
1455 c1 i1
1456 c 2
1457 c 5
1458 c 7
1459 c 7
1460 c 9
1461 g 2
1462 g 5
1463 g 7
1464 g 7
1465 g 9
1466 i 2
1467 i 5
1468 i 7
1469 i 7
1470 i 9
1471 t 2
1472 t 2
1473 t 5
1474 t 5
1475 t 7
1476 t 7
1477 t 7
1478 t 7
1479 t 9
1480 t 9
1481 #
1482 # Test 4: One non-covering index
1483 #
1484 # Drop the index on two columns, add index on just one virtual column
1485 ALTER TABLE t3 DROP INDEX v_idx2;
1486 ALTER TABLE t3 ADD INDEX v_idx (i2_key);
1487 # Add more data to the table so that it will run the dynamic range scan
1488 # as both table scan and range scan (the purpose of this is to make the
1489 # table scan more expensive).
1490 INSERT INTO t3 (pk, i1)
1491 VALUES (10,1), (11,1), (12,1), (13,1), (14,1),(15,1), (16,1),(17,1), (18,1),
1492 (19,1), (20,1), (21,1), (22,1), (23,1), (24,1),(25,1),(26,1),(27,1),
1493 (28,1), (29,1);
1494 # Change the query to read an extra column (t3.i1) making the index
1495 # non-covering.
1496 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1, t3.i1
1497 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
1498 WHERE ( t3.pk IN
1499 (
1500 SELECT /*+ QB_NAME(subq1) */ t4.i1
1501 FROM t4
1502 WHERE t4.c1 < 'o'
1503 )
1504 )
1505 AND t1.i1 <= t3.i2_key;
1506 id select_type table type possible_keys key key_len ref rows Extra
1507 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
1508 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
1509 1 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where; End temporary
1510 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
1511 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1, t3.i1
1512 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
1513 WHERE ( t3.pk IN
1514 (
1515 SELECT /*+ QB_NAME(subq1) */ t4.i1
1516 FROM t4
1517 WHERE t4.c1 < 'o'
1518 )
1519 )
1520 AND t1.i1 <= t3.i2_key;
1521 c1 i1 i1
1522 c 2 48
1523 c 5 48
1524 c 7 48
1525 c 7 48
1526 c 9 48
1527 g 2 48
1528 g 5 48
1529 g 7 48
1530 g 7 48
1531 g 9 48
1532 i 2 48
1533 i 5 48
1534 i 7 48
1535 i 7 48
1536 i 9 48
1537 t 2 1
1538 t 2 48
1539 t 5 1
1540 t 5 48
1541 t 7 1
1542 t 7 1
1543 t 7 48
1544 t 7 48
1545 t 9 1
1546 t 9 48
1547 #
1548 # Test 5: Test where the added primary key to secondary indexes is
1549 # used after it has been included in the join buffer
1550 #
1551 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
1552 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
1553 WHERE ( t3.pk IN
1554 (
1555 SELECT /*+ QB_NAME(subq1) */ t4.i1
1556 FROM t4
1557 WHERE t4.c1 < 'o' and t4.i1 < (t2.i1 + 1)
1558 )
1559 )
1560 AND t1.i1 <= t3.i2_key;
1561 id select_type table type possible_keys key key_len ref rows Extra
1562 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
1563 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
1564 1 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where
1565 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join)
1566 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
1567 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
1568 WHERE ( t3.pk IN
1569 (
1570 SELECT /*+ QB_NAME(subq1) */ t4.i1
1571 FROM t4
1572 WHERE t4.c1 < 'o' and t4.i1 < (t2.i1 + 1)
1573 )
1574 )
1575 AND t1.i1 <= t3.i2_key;
1576 c1 i1
1577 c 2
1578 c 5
1579 c 7
1580 c 7
1581 c 9
1582 g 2
1583 g 5
1584 g 7
1585 g 7
1586 g 9
1587 i 2
1588 i 5
1589 i 7
1590 i 7
1591 i 9
1592 t 2
1593 t 2
1594 t 5
1595 t 5
1596 t 7
1597 t 7
1598 t 7
1599 t 7
1600 t 9
1601 t 9
1602 DROP TABLE t1, t2, t3, t4;
1603 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;
1604 SET optimizer_switch='derived_merge=default';
1605 DROP VIEW IF EXISTS v1,v2;
1606 DROP TABLE IF EXISTS t1,t2,t3;
1607 DROP PROCEDURE IF EXISTS p1;
1608 DROP FUNCTION IF EXISTS f1;
1609 DROP TRIGGER IF EXISTS trg1;
1610 DROP TRIGGER IF EXISTS trg2;
1611 set sql_warnings = 0;