Debian Codegraph @

mysql-test/suite/opt_trace/include/bugs.inc

raw
1 # Regressiontest for statements that failed with optimizer tracing enabled.
2
3 --source include/have_optimizer_trace.inc
4 # InnoDB page size influences cost numbers
5
6 --source suite/opt_trace/include/elide_trace_costs.inc
7
8 let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB
9 eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;
10
11 SET end_markers_in_json=on;
12 SET optimizer_trace="enabled=on,one_line=off";
13
14 --echo #
15 --echo # BUG#12430646 - SEL_ARG::LEFT AND RIGHT POINTERS INCORRECTLY
16 --echo # USED. CRASHES OPTIMIZER TRACING
17 --echo #
18
19 CREATE TABLE t1 (
20 a INT,
21 b CHAR(2),
22 c INT,
23 d INT,
24 KEY (c),
25 KEY (d,a,b(2)),
26 KEY (b(1))
27 );
28
29 INSERT INTO t1 VALUES (NULL, 'a', 1, 2), (NULL, 'a', 1, 2),
30 (1, 'a', 1, 2), (1, 'a', 1, 2);
31
32 CREATE TABLE t2 (
33 a INT,
34 c INT,
35 e INT,
36 KEY (e)
37 );
38
39 INSERT INTO t2 VALUES (1, 1, NULL), (1, 1, NULL);
40
41 SELECT 1
42 FROM t1, t2
43 WHERE t1.d <> '1' AND t1.b > '1'
44 AND t1.a = t2.a AND t1.c = t2.c;
45
46 DROP TABLE t1, t2;
47
48 --echo #
49 --echo # BUG#12595210 - JSON SYNTAX ERROR ASSERT ON WHERE FIELD NOT IN SUBQUERY
50 --echo #
51
52 # This trace exhibits a non-empty
53 # "evaluating_constant_where_conditions" object which is rare.
54
55 CREATE TABLE t1 (
56 pk INT NOT NULL AUTO_INCREMENT,
57 col_int_key INT DEFAULT NULL,
58 col_varchar_key VARCHAR(1) DEFAULT NULL,
59 PRIMARY KEY (pk)
60 ) ENGINE=InnoDB, CHARSET utf8mb4;
61
62 CREATE TABLE t2 (
63 pk INT NOT NULL AUTO_INCREMENT,
64 col_int_key INT DEFAULT NULL,
65 col_varchar_key VARCHAR(1) DEFAULT NULL,
66 col_varchar_nokey VARCHAR(1) DEFAULT NULL,
67 PRIMARY KEY (pk)
68 ) ENGINE=InnoDB, CHARSET utf8mb4;
69
70 CREATE TABLE t3 (
71 pk INT NOT NULL AUTO_INCREMENT,
72 col_int_key INT DEFAULT NULL,
73 col_varchar_key VARCHAR(1) DEFAULT NULL,
74 col_varchar_nokey VARCHAR(1) DEFAULT NULL,
75 PRIMARY KEY (pk)
76 ) ENGINE=InnoDB, CHARSET utf8mb4;
77
78 CREATE TABLE t4 (
79 pk INT NOT NULL AUTO_INCREMENT,
80 col_int_key INT DEFAULT NULL,
81 col_varchar_key VARCHAR(1) DEFAULT NULL,
82 PRIMARY KEY (pk)
83 ) ENGINE=InnoDB, CHARSET utf8mb4;
84
85 CREATE VIEW view_t4 AS SELECT * FROM t4;
86
87 let $query=
88 SELECT
89 (
90 SELECT SUM(sq1_alias1.pk) AS sq1_field1
91 FROM view_t4 AS sq1_alias1
92 INNER JOIN t1 AS sq1_alias2 ON (sq1_alias2.col_varchar_key =
93 sq1_alias1.col_varchar_key )
94 ) AS field1,
95 alias1.col_varchar_nokey AS field2
96 FROM (t2 AS alias1
97 LEFT JOIN ( t2 AS alias2
98 LEFT OUTER JOIN t2 AS alias3 ON (alias3.col_varchar_nokey =
99 alias2.col_varchar_key )
100 ) ON (alias3.col_varchar_key = alias2.col_varchar_key)
101 )
102 WHERE ( alias2.col_varchar_key IN (
103 SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
104 FROM t3 AS sq2_alias1
105 WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
106 )
107 );
108
109 eval CREATE TABLE where_subselect_19379 $query;
110 eval SELECT * FROM where_subselect_19379 WHERE (field1, field2) NOT IN
111 ($query);
112
113 --skip_if_hypergraph # Does not support the same optimizer trace.
114 select * from information_schema.OPTIMIZER_TRACE;
115
116 drop table t1,t2,t3,t4,where_subselect_19379;
117 drop view view_t4;
118
119 --echo #
120 --echo # BUG#12607524 JSON PARSE ERROR ON SELECT ... FROM ... WHERE .. IN (SUBQUERY)
121 --echo #
122
123 CREATE TABLE t1 (
124 col_int_key int(11) DEFAULT NULL,
125 col_varchar_key varchar(1) DEFAULT NULL,
126 KEY col_int_key (col_int_key),
127 KEY col_varchar_key (col_varchar_key,col_int_key)
128 ) ENGINE=MyISAM;
129 INSERT INTO t1 VALUES (8,'g');
130
131 CREATE TABLE t2 (
132 col_int_key int(11) DEFAULT NULL,
133 col_varchar_key varchar(1) DEFAULT NULL,
134 KEY col_int_key (col_int_key),
135 KEY col_varchar_key (col_varchar_key,col_int_key)
136 ) ENGINE=MyISAM;
137 INSERT INTO t2 VALUES (7,'x');
138
139 CREATE TABLE where_subselect_19033
140 SELECT
141 ( SELECT col_int_key FROM t2 ) as field1
142 FROM t1
143 ;
144
145 SELECT * FROM where_subselect_19033;
146
147 SELECT field1
148 FROM where_subselect_19033
149 WHERE field1 IN
150 ( SELECT
151 ( SELECT col_int_key FROM t2 )
152 FROM t1
153 )
154 ;
155
156 --skip_if_hypergraph # Does not support the same optimizer trace.
157 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
158
159 DROP TABLE where_subselect_19033,t1,t2;
160
161 --echo
162 --echo # BUG#12612201 - SEGFAULT IN
163 --echo # SUBSELECT_UNIQUESUBQUERY_ENGINE::PRINT WITH OPTIMIZER TRACE
164 --echo
165
166 CREATE TABLE t1 (
167 col_int_key int(11) DEFAULT NULL,
168 col_varchar_key varchar(1) DEFAULT NULL,
169 col_varchar_nokey varchar(1) DEFAULT NULL
170 );
171
172 CREATE TABLE t2 (
173 pk int(11) NOT NULL AUTO_INCREMENT,
174 col_int_key int(11) DEFAULT NULL,
175 col_varchar_key varchar(1) DEFAULT NULL,
176 col_varchar_nokey varchar(1) DEFAULT NULL,
177 PRIMARY KEY (pk)
178 );
179
180 INSERT INTO t2 VALUES (1,4,'v','v'),(20,5,'r','r');
181
182 CREATE TABLE t3 (
183 col_int_key int(11) DEFAULT NULL,
184 col_varchar_key varchar(1) DEFAULT NULL,
185 col_varchar_nokey varchar(1) DEFAULT NULL
186 );
187
188 INSERT INTO t3 VALUES (NULL,'j','j'),(8,'c','c');
189
190 let $query=
191 select count( alias2 . col_varchar_key ) as field1
192 from (
193 (select sq1_alias1 . *
194 from ( t3 as sq1_alias1
195 straight_join t1 as sq1_alias2
196 on (sq1_alias2 . col_varchar_key = sq1_alias1 . col_varchar_key)
197 )
198 where sq1_alias1 . col_int_key in (
199 select c_sq1_alias1 . pk as c_sq1_field1
200 from t2 as c_sq1_alias1
201 )
202 ) as alias1
203 left outer join t1 as alias2
204 on (alias2 . col_varchar_key = alias1 . col_varchar_key )
205 )
206 where ( alias2 . col_varchar_key in (
207 select sq2_alias1 . col_varchar_nokey as sq2_field1
208 from t2 as sq2_alias1
209 where sq2_alias1 . col_int_key in (
210 select distinct c_sq2_alias1 . col_int_key as c_sq2_field1
211 from t3 as c_sq2_alias1
212 )
213 ) )
214 or alias1 . col_int_key = 2
215 and alias2 . col_varchar_nokey <= alias1 . col_varchar_nokey
216 order by alias1 . col_varchar_key , field1
217 ;
218
219 --source include/turn_off_only_full_group_by.inc
220
221 eval CREATE TABLE where_updatedelete_20769 $query;
222 eval UPDATE where_updatedelete_20769 SET field1 = ( $query );
223
224 --source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
225
226 DROP TABLE where_updatedelete_20769;
227 DROP TABLE t1,t2,t3;
228
229 --echo
230 --echo # BUG#12710761 - INVALID JSON TRACE ON SUBQUERY IN IN-CLAUSE
231 --echo
232
233 CREATE TABLE t1 (col_int_key int, KEY col_int_key (col_int_key));
234 INSERT INTO t1 VALUES (0),(8),(1),(8);
235
236 CREATE TABLE where_subselect_20070
237 SELECT table2 .col_int_key AS field1,
238 ( SELECT COUNT( col_int_key )
239 FROM t1
240 )
241 FROM t1 AS table1
242 JOIN t1 AS table2
243 ON table2 .col_int_key = table1 .col_int_key;
244
245 --sorted_result
246 SELECT *
247 FROM where_subselect_20070
248 WHERE (field1, ( SELECT COUNT( col_int_key ) FROM t1 )) IN (
249 SELECT table2 .col_int_key AS field1,
250 ( SELECT COUNT( col_int_key )
251 FROM t1
252 )
253 FROM t1 AS table1
254 JOIN t1 AS table2
255 ON table2 .col_int_key = table1 .col_int_key
256 );
257
258 --skip_if_hypergraph # Does not support the same optimizer trace.
259 select * from information_schema.optimizer_trace;
260
261 DROP TABLE where_subselect_20070,t1;
262
263 --echo #
264 --echo # Bug#13430443 - ASSERTION `NEW_TYPE[0] != 'U'' FAILED. WHEN
265 --echo # OPTIMIZER_TRACE IS ENABLED
266 --echo #
267 CREATE TABLE t1
268 (a INT,b INT,c INT, KEY(a),KEY (a,c)) ENGINE=INNODB;
269 SELECT 1 FROM t1 WHERE 1 LIKE
270 (SELECT a FROM t1 WHERE a = 1 ORDER BY c);
271 --skip_if_hypergraph # Does not support the same optimizer trace.
272 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
273 DROP TABLE t1;
274
275 --echo #
276 --echo # Bug #18346750 OPTIMIZER_TRACE & DBUG_PRINT CRASH IN ST_SELECT_LEX::PRINT
277 --echo #
278 if ($support_native_partitioning)
279 {
280 CREATE TABLE t1 (a INT, PRIMARY KEY (a))
281 PARTITION BY KEY (a) PARTITIONS 2;
282 INSERT INTO t1 VALUES (1),(2);
283 --ERROR ER_SUBQUERY_NO_1_ROW
284 SELECT 1 FROM t1 ,t1 w
285 WHERE t1.a <=> (SELECT 22 FROM t1 GROUP BY (SELECT 1 FROM t1));
286 --skip_if_hypergraph # Does not support the same optimizer trace.
287 SELECT TRACE LIKE "%select had some error%" FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
288 DROP TABLE t1;
289 }
290 if (!$support_native_partitioning)
291 {
292 --echo "Skipped since this engine does not support native partitioning.
293 }
294
295 --echo #
296 --echo # Bug#18791851 CRASH IN ST_SELECT_LEX::PRINT WITH OPTIMIZER_TRACE ON SUBQUERY
297 --echo #
298
299 CREATE TABLE t1 (
300 pk INT NOT NULL,
301 col_int_nokey INT,
302 col_int_key INT,
303 col_time_key time,
304 col_varchar_key VARCHAR(1),
305 col_varchar_nokey VARCHAR(1),
306 PRIMARY KEY (pk),
307 KEY col_varchar_key (col_varchar_key,col_int_key)
308 );
309
310 CREATE TABLE t2 (
311 pk INT NOT NULL,
312 col_int_nokey INT,
313 col_int_key INT,
314 col_varchar_key VARCHAR(1),
315 col_varchar_nokey VARCHAR(1),
316 PRIMARY KEY (pk),
317 KEY col_varchar_key (col_varchar_key,col_int_key)
318 );
319
320 CREATE TABLE t3 (
321 pk INT NOT NULL,
322 col_int_nokey INT,
323 col_int_key INT,
324 col_varchar_key VARCHAR(1),
325 col_varchar_nokey VARCHAR(1),
326 PRIMARY KEY (pk),
327 KEY col_varchar_key (col_varchar_key,col_int_key)
328 );
329
330 CREATE TABLE t4 (
331 pk INT NOT NULL,
332 col_int_nokey INT,
333 col_int_key INT,
334 col_time_key time,
335 col_varchar_key VARCHAR(1),
336 col_varchar_nokey VARCHAR(1),
337 PRIMARY KEY (pk),
338 KEY col_varchar_key (col_varchar_key,col_int_key)
339 );
340
341 --source include/turn_off_only_full_group_by.inc
342
343 SELECT alias1.col_int_nokey AS field1,
344 alias2.col_varchar_key AS field2,
345 alias1.col_time_key AS field3,
346 MIN( alias1.col_int_nokey ) AS field4
347 FROM (
348 (
349 SELECT SQ1_alias1.*
350 FROM ( t1 AS SQ1_alias1, t2 AS SQ1_alias2 )
351 ) AS alias1,
352 t4 AS alias2
353 )
354 WHERE
355 EXISTS (
356 SELECT DISTINCT SQ2_alias2.col_varchar_nokey AS SQ2_field1
357 FROM t2 AS SQ2_alias1
358 INNER JOIN (t4 AS SQ2_alias2
359 INNER JOIN t3 AS SQ2_alias3
360 ON SQ2_alias3.pk = SQ2_alias2.pk)
361 ON SQ2_alias3.col_varchar_key = SQ2_alias2.col_varchar_nokey
362 )
363 AND alias1.col_int_key = alias2.pk
364 HAVING alias1.col_int_nokey IN ( SELECT 2 FROM DUAL ) ;
365
366 --source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
367
368 DROP TABLE t1,t2,t3,t4;
369
370 # Inspired by testcase of
371 # Bug #18345786 CRASH AROUND ST_JOIN_TABLE::AND_WITH_CONDITION
372 CREATE TABLE t1(a INT);
373 SET @a:=(SELECT ROW(1, 2)=
374 ROW((SELECT 1 FROM t1 LEFT JOIN t1 t2 ON 1
375 HAVING 3 IN (SELECT 2 FROM DUAL)),
376 1));
377 DROP TABLE t1;
378
379 --echo #
380 --echo # Bug #18945693 CRASH IN PRINT_TABLE_ARRAY AT SQL/SQL_LEX.CC ON 2ND EXEC OF PREPARED STATEMENT
381 --echo #
382
383 CREATE TABLE t1 (
384 pk INT NOT NULL,
385 col_int_nokey INT,
386 col_int_key INT,
387 col_varchar_key VARCHAR(1),
388 col_varchar_nokey VARCHAR(1)
389 );
390
391 INSERT INTO t1 VALUES (13,7,3,'y','y'),(14,0,4,'c','c');
392
393 CREATE TABLE t2 (
394 pk INT NOT NULL,
395 col_int_nokey INT,
396 col_int_key INT,
397 col_varchar_key VARCHAR(1),
398 col_varchar_nokey VARCHAR(1)
399 );
400
401 INSERT INTO t2 VALUES (20,9,8,'e','e');
402
403 CREATE TABLE t3 (
404 pk INT NOT NULL,
405 col_int_nokey INT,
406 col_int_key INT,
407 col_varchar_key VARCHAR(1),
408 col_varchar_nokey VARCHAR(1)
409 );
410
411 INSERT INTO t3 VALUES (1,1,7,'k','k');
412
413 PREPARE prep_stmt FROM "
414 SELECT
415 alias1.col_varchar_nokey AS field1,
416 MAX(alias1.pk) AS field2
417 FROM t3 AS alias1
418 GROUP BY field1
419 HAVING
420 1 > (
421 SELECT MAX(SQ4_alias1.col_varchar_nokey)
422 FROM t1 AS SQ4_alias1
423 RIGHT OUTER JOIN
424 (
425 t2 AS SQ4_alias2
426 JOIN t1 AS SQ4_alias3
427 ON SQ4_alias3.col_int_key = SQ4_alias2.col_int_nokey
428 )
429 ON SQ4_alias3.pk = SQ4_alias2.col_int_nokey
430 )
431 ";
432
433 EXECUTE prep_stmt;
434 EXECUTE prep_stmt;
435
436 DROP TABLE t1,t2,t3;
437
438 --echo #
439 --echo # Bug#19063289 CRASH IN PRINT_TABLE_ARRAY ON 2ND EXECUTION OF PS WITH SUBQUERY AND VIEW
440 --echo #
441
442 CREATE TABLE B (
443 pk INTEGER,
444 col_int_key INTEGER,
445 col_varchar_nokey VARCHAR(1)
446 );
447
448 CREATE TABLE C (
449 col_int_nokey INTEGER,
450 col_int_key INTEGER,
451 col_varchar_key VARCHAR(1),
452 col_varchar_nokey VARCHAR(1)
453 ) ;
454
455 CREATE TABLE CC (
456 pk INTEGER,
457 col_int_key INTEGER
458 ) ENGINE=MYISAM;
459
460 INSERT INTO CC VALUES (1, 0),(2, 7);
461
462 PREPARE stmt FROM "
463 SELECT
464 alias1.col_int_key AS field1 ,
465 COUNT(alias1.pk) AS field2
466 FROM
467 CC AS alias1
468 GROUP BY field1
469 HAVING
470 alias1.col_int_key >
471 (
472 SELECT
473 MAX( SQ2_alias2.col_int_key ) AS SQ2_field1
474 FROM
475 C AS SQ2_alias1 LEFT JOIN
476 ( B AS SQ2_alias2 INNER JOIN C AS SQ2_alias3
477 ON 1)
478 ON 1
479 )
480 OR field2 < 2
481 ";
482 EXECUTE stmt;
483 EXECUTE stmt;
484
485 DROP TABLE B,C,CC;
486
487 --echo #
488 --echo # Bug #23259872: OPTIMIZER CHOOSES TO USE NON PRIMARY
489 --echo # INDEX, EVEN THOUGH COST IS HIGHER
490 --echo #
491
492 CREATE TABLE t1 (
493 a TINYTEXT NOT NULL,
494 b TINYINT(3) UNSIGNED NOT NULL,
495 PRIMARY KEY (a(32),b)
496 ) ENGINE=MyISAM, CHARSET utf8mb4;
497 CREATE TABLE t2 (
498 a TINYTEXT NOT NULL,
499 b TINYINT(3) UNSIGNED NOT NULL,
500 PRIMARY KEY (a(32),b)
501 ) ENGINE=INNODB, CHARSET utf8mb4;
502
503 INSERT INTO t1 VALUES ('a',1),('a',2);
504 INSERT INTO t2 VALUES ('a',1),('a',2),('a',3),('b',1),('c',1),('c',4),('e',2);
505 ANALYZE TABLE t1;
506 ANALYZE TABLE t2;
507
508 # range uses more keyparts than ref, so range is chosen.
509 EXPLAIN SELECT * FROM t1 WHERE a='a' AND b in (1,2);
510 SELECT TRACE into @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
511 --skip_if_hypergraph # Does not support the same optimizer trace.
512 SELECT @trace RLIKE "range_uses_more_keyparts";
513
514 EXPLAIN SELECT * FROM t2 WHERE a='a' AND b in (1,2);
515 SELECT TRACE into @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
516 --skip_if_hypergraph # Does not support the same optimizer trace.
517 SELECT @trace RLIKE "range_uses_more_keyparts";
518
519 DROP TABLE t1, t2;
520
521 --echo #
522 --echo # Bug #23227428: SQL PLAN IS NOT ACCORDING WITH OPTIMIZER_TRACE
523 --echo #
524
525 CREATE TABLE t1(c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
526 c2 CHAR(12)) ENGINE=INNODB;
527 disable_query_log;
528 let $1 = 1000;
529 while ($1)
530 {
531 eval INSERT INTO t1(c2) VALUES ('$1');
532 dec $1;
533 }
534 enable_query_log;
535 ANALYZE TABLE t1;
536
537 EXPLAIN SELECT c1, c2 FROM t1 ORDER BY c1 DESC LIMIT 1;
538 --replace_regex $elide_trace_costs
539 --skip_if_hypergraph # Does not support the same optimizer trace.
540 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
541 DROP TABLE t1;
542
543 --echo # Bug#29016383 - fil_report_invalid_page_access
544
545 CREATE TABLE t1 (
546 pk INTEGER PRIMARY KEY,
547 a INTEGER,
548 b VARCHAR(20)
549 );
550 CREATE TABLE t2 (
551 pk INTEGER PRIMARY KEY,
552 c VARCHAR(20),
553 KEY (c)
554 );
555 INSERT INTO t2 VALUES
556 (1,'koeiwsgpmfyvvuqvtjnc'),
557 (2,'pmfyvvuqvtjncdsvq'),
558 (3,'mfyv'),
559 (4,NULL);
560
561 SET end_markers_in_json=off;
562 SELECT alias1.*
563 FROM t2 AS alias1, t2 AS alias2
564 WHERE alias2.c IN (
565 WITH qn AS (SELECT b FROM t1 WHERE a = alias1.pk)
566 SELECT * FROM qn);
567
568 --echo # Prove that the predicate from the WHERE clause of the CTE
569 --echo # is decorrelated:
570
571 --skip_if_hypergraph # Does not support the same optimizer trace.
572 SELECT json_extract(trace,'$**.decorrelated_predicates')
573 FROM information_schema.optimizer_trace;
574
575 DROP TABLE t1, t2;