Debian Codegraph @

mysql-test/suite/gcol/r/gcol_select_innodb.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 = 'InnoDB';
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 ALL 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 ref c c 5 const 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 ALL 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 t1 index NULL c 5 NULL 5 Using index
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 where
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 # SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <non-indexed gcol>
187 select * from t3 where a between 1 and 2 order by b;
188 a b c
189 2 -2 -2
190 1 -1 -1
191 explain select * from t3 where a between 1 and 2 order by b;
192 id select_type table type possible_keys key key_len ref rows Extra
193 1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort
194 # bug#20022189: WL411:DEBUG ASSERT AT FIELD_LONG::VAL_INT IN SQL/FIELD.CC
195 # SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <non-indexed stored gcol>
196 select * from t3 where a between 1 and 2 order by c;
197 a b c
198 2 -2 -2
199 1 -1 -1
200 explain select * from t3 where a between 1 and 2 order by c;
201 id select_type table type possible_keys key key_len ref rows Extra
202 1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort
203 # bug#20022189: WL411:DEBUG ASSERT AT FIELD_LONG::VAL_INT IN SQL/FIELD.CC
204 CREATE TABLE t4 (
205 `pk` int(11) NOT NULL ,
206 `col_int_nokey` int(11) GENERATED ALWAYS AS (pk + col_int_key) STORED,
207 `col_int_key` int(11) DEFAULT NULL,
208 `col_date_nokey` date DEFAULT NULL,
209 `col_datetime_key` datetime DEFAULT NULL,
210 PRIMARY KEY (`pk`),
211 KEY `col_int_key` (`col_int_key`),
212 KEY `col_datetime_key` (`col_datetime_key`)
213 );
214 INSERT INTO t4 VALUES
215 (1,default,4,'2008-12-05','1900-01-01 00:00:00');
216 SELECT
217 SQL_BIG_RESULT
218 GRANDPARENT1 . `col_int_nokey` AS g1
219 FROM t4 AS GRANDPARENT1 LEFT JOIN t4 AS GRANDPARENT2 ON ( GRANDPARENT2 .
220 `col_datetime_key` <= GRANDPARENT1 . `col_date_nokey` )
221 GROUP BY GRANDPARENT1 . `pk`;
222 g1
223 5
224 DROP TABLE t4;
225 # SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <indexed gcol>
226 select * from t3 where a between 1 and 2 order by c;
227 a b c
228 2 -2 -2
229 1 -1 -1
230 explain select * from t3 where a between 1 and 2 order by c;
231 id select_type table type possible_keys key key_len ref rows Extra
232 1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort
233 # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-gcol>
234 select * from t3 where b between -2 and -1 order by a;
235 a b c
236 1 -1 -1
237 2 -2 -2
238 explain select * from t3 where b between -2 and -1 order by a;
239 id select_type table type possible_keys key key_len ref rows Extra
240 1 SIMPLE t3 index NULL PRIMARY 4 NULL 6 Using where
241 # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-indexed gcol>
242 select * from t3 where b between -2 and -1 order by b;
243 a b c
244 2 -2 -2
245 1 -1 -1
246 explain select * from t3 where b between -2 and -1 order by b;
247 id select_type table type possible_keys key key_len ref rows Extra
248 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort
249 # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-indexed gcol>
250 select * from t3 where c between -2 and -1 order by b;
251 a b c
252 2 -2 -2
253 1 -1 -1
254 explain select * from t3 where c between -2 and -1 order by b;
255 id select_type table type possible_keys key key_len ref rows Extra
256 1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort
257 # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <indexed gcol>
258 select * from t3 where b between -2 and -1 order by c;
259 a b c
260 2 -2 -2
261 1 -1 -1
262 explain select * from t3 where b between -2 and -1 order by c;
263 id select_type table type possible_keys key key_len ref rows Extra
264 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort
265 # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <indexed gcol>
266 select * from t3 where c between -2 and -1 order by c;
267 a b c
268 2 -2 -2
269 1 -1 -1
270 explain select * from t3 where c between -2 and -1 order by c;
271 id select_type table type possible_keys key key_len ref rows Extra
272 1 SIMPLE t3 range c c 5 NULL 2 Using index condition
273 # SELECT sum(<non-indexed gcol>) FROM tbl_name GROUP BY <non-indexed gcol>
274 select sum(b) from t1 group by b;
275 sum(b)
276 NULL
277 -3
278 -2
279 -2
280 explain select sum(b) from t1 group by b;
281 id select_type table type possible_keys key key_len ref rows Extra
282 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort
283 # SELECT sum(<indexed gcol>) FROM tbl_name GROUP BY <indexed gcol>
284 select sum(c) from t1 group by c;
285 sum(c)
286 NULL
287 -3
288 -2
289 -2
290 explain select sum(c) from t1 group by c;
291 id select_type table type possible_keys key key_len ref rows Extra
292 1 SIMPLE t1 index NULL c 5 NULL 5 Using index
293 # SELECT sum(<non-indexed gcol>) FROM tbl_name GROUP BY <indexed gcol>
294 select sum(b) from t1 group by c;
295 sum(b)
296 NULL
297 -3
298 -2
299 -2
300 explain select sum(b) from t1 group by c;
301 id select_type table type possible_keys key key_len ref rows Extra
302 1 SIMPLE t1 index NULL c 5 NULL 5
303 # SELECT sum(<indexed gcol>) FROM tbl_name GROUP BY <non-indexed gcol>
304 select sum(c) from t1 group by b;
305 sum(c)
306 NULL
307 -3
308 -2
309 -2
310 explain select sum(c) from t1 group by b;
311 id select_type table type possible_keys key key_len ref rows Extra
312 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort
313 drop table t1;
314 #
315 # Bug#20241655: WL411:FAILING ASSERTION ASSERTION
316 #
317 CREATE TABLE BB (
318 col_time_key time NOT NULL,
319 col_time_nokey time GENERATED ALWAYS AS (ADDTIME(col_datetime_key, col_time_key)) VIRTUAL,
320 col_datetime_key datetime NOT NULL);
321 INSERT INTO BB VALUES('23:28:02', default, '2005-03-15 22:48:25');
322 Warnings:
323 Note 1265 Data truncated for column 'col_time_nokey' at row 1
324 CREATE TABLE CC (
325 col_time_key time NOT NULL,
326 col_time_nokey time GENERATED ALWAYS AS (ADDTIME(col_datetime_key, col_time_key)) VIRTUAL,
327 col_datetime_key datetime NOT NULL
328 );
329 INSERT INTO CC VALUES('16:22:51', default, '1900-01-01 00:00:00');
330 Warnings:
331 Note 1265 Data truncated for column 'col_time_nokey' at row 1
332 SELECT 1 AS g1 FROM BB AS gp1 LEFT JOIN BB AS gp2 USING ( col_time_nokey);
333 g1
334 1
335 DROP TABLE BB, CC;
336 #
337 # Bug#20328786: WL411:VALGRIND WARNINGS OF CONDITIONAL
338 # JUMP WHILE SELECTING FROM VIEW
339 #
340 CREATE TABLE A (
341 pk INTEGER AUTO_INCREMENT,
342 col_int_nokey INTEGER,
343 col_int_key INTEGER GENERATED ALWAYS AS (2 + 2 + col_int_nokey) STORED,
344 PRIMARY KEY (pk)
345 );
346 CREATE TABLE C (
347 pk INTEGER AUTO_INCREMENT,
348 col_int_nokey INTEGER,
349 col_int_key INTEGER GENERATED ALWAYS AS (2 + 2 + col_int_nokey) STORED,
350 col_varchar_nokey VARCHAR(1),
351 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
352 (CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED,
353 PRIMARY KEY (pk),
354 KEY (col_int_key),
355 KEY (col_varchar_key, col_int_key)
356 );
357 INSERT INTO C (
358 col_int_nokey,
359 col_varchar_nokey
360 ) 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');
361 CREATE OR REPLACE ALGORITHM=MERGE VIEW V1 AS SELECT alias1.
362 col_varchar_key AS field1 , alias1.pk AS field2, alias2.
363 col_int_nokey AS field3 FROM C AS alias1 LEFT JOIN A AS alias2 ON
364 alias1.pk = alias2.col_int_key WHERE alias1.pk > 8 AND alias1
365 .pk < ( 9 + 2 ) AND alias1.col_int_key <> 1 OR alias1.col_int_key
366 > 0 AND alias1.col_int_key <= ( 3 + 2 ) ORDER BY field1, field2, field3
367 LIMIT 100 OFFSET 6;
368 Warnings:
369 Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm)
370 SELECT * FROM V1;
371 field1 field2 field3
372 qq 14 NULL
373 rr 17 NULL
374 ss 9 NULL
375 xx 5 NULL
376 DROP VIEW V1;
377 DROP TABLE A,C;
378 #
379 # Bug#20406510: WL411:VALGRIND WARNINGS WITH
380 # COUNT DISTINCT QUERY ON VIRTUAL GC VARCHAR COLUMN
381 #
382 CREATE TABLE A (
383 pk INTEGER AUTO_INCREMENT,
384 col_time_key TIME NOT NULL,
385 col_datetime_key DATETIME NOT NULL,
386 PRIMARY KEY (pk),
387 KEY (col_time_key),
388 KEY (col_datetime_key)
389 );
390 CREATE TABLE C (
391 pk INTEGER AUTO_INCREMENT,
392 col_int_key INTEGER NOT NULL,
393 col_varchar_key VARCHAR(1) NOT NULL,
394 col_varchar_nokey VARCHAR(2) GENERATED ALWAYS AS
395 (CONCAT(col_varchar_key, col_varchar_key)),
396 PRIMARY KEY (pk),
397 KEY (col_int_key),
398 KEY (col_varchar_key, col_int_key)
399 );
400 INSERT INTO C (col_int_key,col_varchar_key) VALUES (0, 'j'),(8, 'v'),(1, 'c'),(8, 'm'),(9, 'd');
401 SELECT MIN( alias2 . col_int_key ) AS field1,
402 COUNT( DISTINCT alias2 . col_varchar_nokey ) AS field2
403 FROM ( A AS alias1 , C AS alias2 )
404 ORDER BY alias1.col_time_key, alias1.col_datetime_key, alias1.pk ASC;
405 field1 field2
406 NULL 0
407 DROP TABLE A,C;
408 #
409 # Bug#20566325: WL8149: INNODB: FAILING ASSERTION:
410 # COL_NR < TABLE->N_DEF
411 #
412 CREATE TABLE A (
413 pk INTEGER AUTO_INCREMENT,
414 col_varchar_nokey VARCHAR(1) NOT NULL,
415 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
416 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
417 PRIMARY KEY (pk)
418 );
419 INSERT /*! IGNORE */ INTO A (col_varchar_nokey) VALUES ('k');
420 CREATE TABLE CC (
421 pk INTEGER AUTO_INCREMENT,
422 col_datetime_nokey DATETIME /*! NULL */,
423 col_time_nokey TIME /*! NULL */,
424 col_time_key TIME GENERATED ALWAYS AS
425 (ADDTIME(col_datetime_nokey, col_time_nokey)),
426 col_varchar_nokey VARCHAR(1) /*! NULL */,
427 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
428 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
429 PRIMARY KEY (pk));
430 INSERT INTO CC (col_time_nokey,col_datetime_nokey,col_varchar_nokey) VALUES
431 ('13:06:13.033877','1900-01-01 00:00:00', 'p'),
432 (NULL, '2007-05-25 11:58:54.015689', 'g');
433 SELECT
434 table1.col_time_key AS field1,
435 'z' AS field2
436 FROM
437 (CC AS table1 LEFT OUTER JOIN (A AS table2 STRAIGHT_JOIN CC AS table3 ON
438 (table3.col_varchar_key = table2.col_varchar_nokey)) ON
439 (table3.col_varchar_key = table2.col_varchar_nokey))
440 WHERE
441 table2.pk != 6
442 AND table1.col_varchar_key IN ('l', 's' , 'b' )
443 AND table3.col_varchar_key != table1.col_varchar_key
444 ORDER BY table1.col_varchar_key , field1 , field2;
445 field1 field2
446 DROP TABLE A,CC;
447 CREATE TABLE cc (
448 pk int(11) NOT NULL AUTO_INCREMENT,
449 col_int_nokey int(11) NOT NULL,
450 col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) STORED,
451 col_date_nokey date NOT NULL,
452 col_date_key date GENERATED ALWAYS AS (col_date_nokey) STORED,
453 col_datetime_nokey datetime NOT NULL,
454 col_time_nokey time NOT NULL,
455 col_datetime_key datetime GENERATED ALWAYS AS (col_datetime_nokey)STORED,
456 col_time_key time GENERATED ALWAYS AS (col_time_nokey) STORED,
457 col_varchar_nokey varchar(1) NOT NULL,
458 col_varchar_key varchar(1) GENERATED ALWAYS AS (col_varchar_nokey)STORED,
459 PRIMARY KEY (pk),
460 KEY gc_idx1 (col_int_key),
461 KEY gc_idx2 (col_varchar_key),
462 KEY gc_idx3 (col_date_key),
463 KEY gc_idx4 (col_time_key),
464 KEY gc_idx5 (col_datetime_key),
465 KEY gc_idx6 (col_varchar_key,col_int_key),
466 KEY gc_idx7 (col_date_key,col_datetime_key,col_time_key),
467 KEY gc_idx8(col_int_key,col_varchar_key,col_date_key,col_time_key,
468 col_datetime_key)
469 );
470 INSERT INTO cc (
471 col_int_nokey,
472 col_date_nokey,
473 col_time_nokey,
474 col_datetime_nokey,
475 col_varchar_nokey
476 ) VALUES (1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'),
477 (8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'),
478 (9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'),
479 (24, '2000-10-08', '10:14:58.018534', '2006-10-12 04:32:53.031976', 'd'),
480 (6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'),
481 (1, '2008-01-23', '11:14:24.032949', '2004-10-02 20:31:15.022553', 't');
482 SET @save_old_sql_mode= @@sql_mode;
483 SET sql_mode="";
484 SELECT DISTINCT alias1.col_varchar_key AS field1
485 FROM ( cc AS alias1 STRAIGHT_JOIN
486 (( cc AS alias2 STRAIGHT_JOIN cc AS alias3 ON
487 (alias3.col_varchar_key > alias2.col_varchar_key ) ) ) ON
488 (( alias3 .pk >= alias2.col_int_nokey ) AND
489 (alias3 .pk >= alias2.col_int_nokey ) ))
490 WHERE alias1.col_varchar_key <= 'v'
491 GROUP BY field1 HAVING field1 = 91
492 ORDER BY field1, alias1.col_date_key, field1 ASC, field1 DESC,
493 alias1.col_time_key ASC, field1;
494 field1
495 Warnings:
496 Warning 1292 Truncated incorrect DECIMAL value: 'c'
497 Warning 1292 Truncated incorrect DECIMAL value: 't'
498 Warning 1292 Truncated incorrect DECIMAL value: 'm'
499 Warning 1292 Truncated incorrect DECIMAL value: 'd'
500 Warning 1292 Truncated incorrect DECIMAL value: 'd'
501 DROP TABLE cc;
502 SET sql_mode=@save_old_sql_mode;
503 #
504 # Bug#20797941: WL8149:ASSERTION !TABLE ||
505 # (!TABLE->READ_SET || BITMAP_IS_SET(TABLE->READ_SET
506 #
507 CREATE TABLE t(a int, b int as(a+1));
508 INSERT INTO t(a) values(1),(2);
509 SELECT * FROM t ORDER BY b;
510 a b
511 1 2
512 2 3
513 DROP TABLE t;
514 DROP TABLE t2, t3;
515 #
516 # Bug#21317507:GC: STORED COLUMN REJECTED, BUT VIRTUAL IS ACCEPTED
517 #
518 CREATE TABLE t1(a INT);
519 INSERT INTO t1 VALUES(2147483647);
520 ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL;
521 ALTER TABLE t1 DROP COLUMN b;
522 ALTER TABLE t1 ADD COLUMN c SMALLINT AS (a) VIRTUAL;
523 ALTER TABLE t1 DROP COLUMN c;
524 ALTER TABLE t1 ADD COLUMN d SMALLINT AS (a) VIRTUAL;
525 ALTER TABLE t1 DROP COLUMN d;
526 ALTER TABLE t1 ADD COLUMN c INT AS(a) VIRTUAL;
527 ALTER TABLE t1 CHANGE c c SMALLINT AS(a) VIRTUAL;
528 ERROR 22003: Out of range value for column 'c' at row 1
529 ALTER TABLE t1 MODIFY c TINYINT AS(a) VIRTUAL;
530 ERROR 22003: Out of range value for column 'c' at row 1
531 SELECT * FROM t1;
532 a c
533 2147483647 2147483647
534 DROP TABLE t1;
535 CREATE TABLE t1(a INT);
536 INSERT INTO t1 VALUES(2147483647);
537 ALTER TABLE t1 ADD COLUMN h INT AS (a) VIRTUAL;
538 ALTER TABLE t1 CHANGE h i INT AS (a) VIRTUAL, ALGORITHM=COPY;
539 ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE;
540 Got one of the listed errors
541 ALTER TABLE t1 ADD COLUMN e SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE;
542 Got one of the listed errors
543 ALTER TABLE t1 ADD COLUMN f SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=SHARED;
544 ERROR 22003: Out of range value for column 'f' at row 1
545 ALTER TABLE t1 ADD COLUMN g SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=EXCLUSIVE;
546 ERROR 22003: Out of range value for column 'g' at row 1
547 DROP TABLE t1;
548 #
549 # Bug#21980430 GCOLS: CRASHING
550 #
551 CREATE TABLE t (
552 a INT,
553 b BLOB,
554 c BLOB GENERATED ALWAYS AS (a+b) VIRTUAL,
555 UNIQUE KEY i0008 (a)
556 );
557 INSERT INTO t(a,b) VALUES(1,'cccc');
558 EXPLAIN SELECT /*+ bka() */ 1 AS c FROM t AS b RIGHT JOIN t AS c ON b.a > c.c
559 WHERE b.b>c.a;
560 id select_type table type possible_keys key key_len ref rows Extra
561 1 SIMPLE b ALL i0008 NULL NULL NULL 1
562 1 SIMPLE c ALL i0008 NULL NULL NULL 1 Range checked for each record (index map: 0x1)
563 SELECT /*+ bka() */ 1 AS c FROM t AS b RIGHT JOIN t AS c ON b.a > c.c
564 WHERE b.b>c.a;
565 c
566 Warnings:
567 Warning 1292 Truncated incorrect DOUBLE value: 'cccc'
568 DROP TABLE t;
569 set @optimizer_switch_save = @@optimizer_switch;
570 set optimizer_switch='mrr_cost_based=off';
571 set @read_rnd_buffer_size_save= @@read_rnd_buffer_size;
572 set read_rnd_buffer_size=32;
573 CREATE TABLE t0 (
574 i1 INTEGER NOT NULL
575 );
576 INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
577 CREATE TABLE t1 (
578 pk INTEGER NOT NULL,
579 i1 INTEGER NOT NULL,
580 i2 INTEGER NOT NULL,
581 v1 INTEGER GENERATED ALWAYS AS (i2 + 1) VIRTUAL,
582 v2 INTEGER GENERATED ALWAYS AS (i1 / (i1 - i2 + 57)) VIRTUAL,
583 PRIMARY KEY (pk),
584 INDEX idx(i1)
585 );
586 INSERT INTO t1 (pk, i1, i2)
587 SELECT a0.i1 + a1.i1*10 + a2.i1*100,
588 a0.i1 + a1.i1*10,
589 a0.i1 + a1.i1*10
590 FROM t0 AS a0, t0 AS a1, t0 AS a2;
591 EXPLAIN SELECT * FROM t1
592 WHERE i1 > 41 AND i1 <= 43;
593 id select_type table type possible_keys key key_len ref rows Extra
594 1 SIMPLE t1 range idx idx 4 NULL 20 Using index condition
595 SELECT * FROM t1
596 WHERE i1 > 41 AND i1 <= 43;
597 pk i1 i2 v1 v2
598 142 42 42 43 1
599 143 43 43 44 1
600 242 42 42 43 1
601 243 43 43 44 1
602 342 42 42 43 1
603 343 43 43 44 1
604 42 42 42 43 1
605 43 43 43 44 1
606 442 42 42 43 1
607 443 43 43 44 1
608 542 42 42 43 1
609 543 43 43 44 1
610 642 42 42 43 1
611 643 43 43 44 1
612 742 42 42 43 1
613 743 43 43 44 1
614 842 42 42 43 1
615 843 43 43 44 1
616 942 42 42 43 1
617 943 43 43 44 1
618 EXPLAIN SELECT * FROM t1
619 WHERE v1 > 41 AND v1 <= 43;
620 id select_type table type possible_keys key key_len ref rows Extra
621 1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where
622 SELECT * FROM t1
623 WHERE v1 > 41 AND v1 <= 43;
624 pk i1 i2 v1 v2
625 141 41 41 42 1
626 142 42 42 43 1
627 241 41 41 42 1
628 242 42 42 43 1
629 341 41 41 42 1
630 342 42 42 43 1
631 41 41 41 42 1
632 42 42 42 43 1
633 441 41 41 42 1
634 442 42 42 43 1
635 541 41 41 42 1
636 542 42 42 43 1
637 641 41 41 42 1
638 642 42 42 43 1
639 741 41 41 42 1
640 742 42 42 43 1
641 841 41 41 42 1
642 842 42 42 43 1
643 941 41 41 42 1
644 942 42 42 43 1
645 DROP TABLE t0, t1;
646 set optimizer_switch= @optimizer_switch_save;
647 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
648 #
649 # Bug#21872184 CONDITIONAL JUMP AT JOIN_CACHE::WRITE_RECORD_DATA IN
650 # SQL_JOIN_BUFFER.CC
651 #
652 #
653 # Test 1: Dynamic range scan with one covering index
654 #
655 CREATE TABLE t1 (
656 i1 INTEGER NOT NULL,
657 c1 VARCHAR(1) NOT NULL
658 );
659 INSERT INTO t1
660 VALUES (10, 'c'), (10, 'i'), (2, 't'), (4, 'g');
661 CREATE TABLE t2 (
662 i1 INTEGER NOT NULL,
663 c1 VARCHAR(1) NOT NULL
664 );
665 INSERT INTO t2
666 VALUES (2, 'k'), (9, 'k'), (7, 'o'), (5, 'n'), (7, 'e');
667 CREATE TABLE t3 (
668 pk INTEGER NOT NULL,
669 i1 INTEGER,
670 i2_key INTEGER GENERATED ALWAYS AS (i1 + i1) VIRTUAL,
671 PRIMARY KEY (pk)
672 );
673 INSERT INTO t3 (pk, i1)
674 VALUES (1, 1), (2, 48), (3, 228), (4, 3), (5, 5),
675 (6, 39), (7, 6), (8, 8), (9, 3);
676 CREATE TABLE t4 (
677 i1 INTEGER NOT NULL,
678 c1 VARCHAR(1) NOT NULL
679 );
680 INSERT INTO t4
681 VALUES (1, 'j'), (2, 'c'), (0, 'a');
682 ANALYZE TABLE t1, t2, t3, t4;
683 Table Op Msg_type Msg_text
684 test.t1 analyze status Engine-independent statistics collected
685 test.t1 analyze status OK
686 test.t2 analyze status Engine-independent statistics collected
687 test.t2 analyze status OK
688 test.t3 analyze status Engine-independent statistics collected
689 test.t3 analyze status OK
690 test.t4 analyze status Engine-independent statistics collected
691 test.t4 analyze status OK
692 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
693 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
694 WHERE ( t3.pk IN
695 (
696 SELECT /*+ QB_NAME(subq1) */ t4.i1
697 FROM t4
698 WHERE t4.c1 < 'o'
699 )
700 )
701 AND t1.i1 <= t3.i2_key;
702 id select_type table type possible_keys key key_len ref rows Extra
703 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
704 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
705 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where; End temporary
706 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
707 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
708 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
709 WHERE ( t3.pk IN
710 (
711 SELECT /*+ QB_NAME(subq1) */ t4.i1
712 FROM t4
713 WHERE t4.c1 < 'o'
714 )
715 )
716 AND t1.i1 <= t3.i2_key;
717 c1 i1
718 c 2
719 c 5
720 c 7
721 c 7
722 c 9
723 g 2
724 g 5
725 g 7
726 g 7
727 g 9
728 i 2
729 i 5
730 i 7
731 i 7
732 i 9
733 t 2
734 t 2
735 t 5
736 t 5
737 t 7
738 t 7
739 t 7
740 t 7
741 t 9
742 t 9
743 #
744 # Test 2: Two alternative covering indexes for the range scan
745 #
746 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
747 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
748 WHERE ( t3.pk IN
749 (
750 SELECT /*+ QB_NAME(subq1) */ t4.i1
751 FROM t4
752 WHERE t4.c1 < 'o'
753 )
754 )
755 AND t1.i1 <= t3.i2_key;
756 id select_type table type possible_keys key key_len ref rows Extra
757 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
758 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
759 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where; End temporary
760 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
761 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
762 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
763 WHERE ( t3.pk IN
764 (
765 SELECT /*+ QB_NAME(subq1) */ t4.i1
766 FROM t4
767 WHERE t4.c1 < 'o'
768 )
769 )
770 AND t1.i1 <= t3.i2_key;
771 c1 i1
772 c 2
773 c 5
774 c 7
775 c 7
776 c 9
777 g 2
778 g 5
779 g 7
780 g 7
781 g 9
782 i 2
783 i 5
784 i 7
785 i 7
786 i 9
787 t 2
788 t 2
789 t 5
790 t 5
791 t 7
792 t 7
793 t 7
794 t 7
795 t 9
796 t 9
797 #
798 # Test 3: One covering index including the base column for the virtual
799 # column
800 #
801 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
802 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
803 WHERE ( t3.pk IN
804 (
805 SELECT /*+ QB_NAME(subq1) */ t4.i1
806 FROM t4
807 WHERE t4.c1 < 'o'
808 )
809 )
810 AND t1.i1 <= t3.i2_key;
811 id select_type table type possible_keys key key_len ref rows Extra
812 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
813 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
814 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where; End temporary
815 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
816 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
817 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
818 WHERE ( t3.pk IN
819 (
820 SELECT /*+ QB_NAME(subq1) */ t4.i1
821 FROM t4
822 WHERE t4.c1 < 'o'
823 )
824 )
825 AND t1.i1 <= t3.i2_key;
826 c1 i1
827 c 2
828 c 5
829 c 7
830 c 7
831 c 9
832 g 2
833 g 5
834 g 7
835 g 7
836 g 9
837 i 2
838 i 5
839 i 7
840 i 7
841 i 9
842 t 2
843 t 2
844 t 5
845 t 5
846 t 7
847 t 7
848 t 7
849 t 7
850 t 9
851 t 9
852 #
853 # Test 4: One non-covering index
854 #
855 # Add more data to the table so that it will run the dynamic range scan
856 # as both table scan and range scan (the purpose of this is to make the
857 # table scan more expensive).
858 INSERT INTO t3 (pk, i1)
859 VALUES (10,1), (11,1), (12,1), (13,1), (14,1),(15,1), (16,1),(17,1), (18,1),
860 (19,1), (20,1), (21,1), (22,1), (23,1), (24,1),(25,1),(26,1),(27,1),
861 (28,1), (29,1);
862 # Change the query to read an extra column (t3.i1) making the index
863 # non-covering.
864 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1, t3.i1
865 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
866 WHERE ( t3.pk IN
867 (
868 SELECT /*+ QB_NAME(subq1) */ t4.i1
869 FROM t4
870 WHERE t4.c1 < 'o'
871 )
872 )
873 AND t1.i1 <= t3.i2_key;
874 id select_type table type possible_keys key key_len ref rows Extra
875 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
876 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
877 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where; End temporary
878 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
879 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1, t3.i1
880 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
881 WHERE ( t3.pk IN
882 (
883 SELECT /*+ QB_NAME(subq1) */ t4.i1
884 FROM t4
885 WHERE t4.c1 < 'o'
886 )
887 )
888 AND t1.i1 <= t3.i2_key;
889 c1 i1 i1
890 c 2 48
891 c 5 48
892 c 7 48
893 c 7 48
894 c 9 48
895 g 2 48
896 g 5 48
897 g 7 48
898 g 7 48
899 g 9 48
900 i 2 48
901 i 5 48
902 i 7 48
903 i 7 48
904 i 9 48
905 t 2 1
906 t 2 48
907 t 5 1
908 t 5 48
909 t 7 1
910 t 7 1
911 t 7 48
912 t 7 48
913 t 9 1
914 t 9 48
915 #
916 # Test 5: Test where the added primary key to secondary indexes is
917 # used after it has been included in the join buffer
918 #
919 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
920 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
921 WHERE ( t3.pk IN
922 (
923 SELECT /*+ QB_NAME(subq1) */ t4.i1
924 FROM t4
925 WHERE t4.c1 < 'o' and t4.i1 < (t2.i1 + 1)
926 )
927 )
928 AND t1.i1 <= t3.i2_key;
929 id select_type table type possible_keys key key_len ref rows Extra
930 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
931 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
932 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where
933 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join)
934 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
935 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
936 WHERE ( t3.pk IN
937 (
938 SELECT /*+ QB_NAME(subq1) */ t4.i1
939 FROM t4
940 WHERE t4.c1 < 'o' and t4.i1 < (t2.i1 + 1)
941 )
942 )
943 AND t1.i1 <= t3.i2_key;
944 c1 i1
945 c 2
946 c 5
947 c 7
948 c 7
949 c 9
950 g 2
951 g 5
952 g 7
953 g 7
954 g 9
955 i 2
956 i 5
957 i 7
958 i 7
959 i 9
960 t 2
961 t 2
962 t 5
963 t 5
964 t 7
965 t 7
966 t 7
967 t 7
968 t 9
969 t 9
970 DROP TABLE t1, t2, t3, t4;
971 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;
972 SET optimizer_switch='derived_merge=default';
973 DROP VIEW IF EXISTS v1,v2;
974 DROP TABLE IF EXISTS t1,t2,t3;
975 DROP PROCEDURE IF EXISTS p1;
976 DROP FUNCTION IF EXISTS f1;
977 DROP TRIGGER IF EXISTS trg1;
978 DROP TRIGGER IF EXISTS trg2;
979 set sql_warnings = 0;