Debian Codegraph @

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

raw
1 # Continuation of tests for optimizer trace
2
3 --source include/have_optimizer_trace.inc
4
5 let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB
6 eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;
7
8 set end_markers_in_json=on;
9 set optimizer_trace="enabled=on";
10
11 --echo # check that if a sub-statement should not be traced,
12 --echo # it is not traced even if inside a traced top statement
13 --echo
14 set optimizer_trace_offset=0, optimizer_trace_limit=100;
15 delimiter |;
16 create function f1(arg char(1)) returns int
17 begin
18 declare res int;
19 declare dummy varchar(1);
20 select 1 into res from dual;
21 select TRACE+NULL into dummy from information_schema.OPTIMIZER_TRACE limit 1;
22 select 2 into res from dual;
23 return 3;
24 end|
25 # ps-protocol specific note: as we asked to retain all traces,
26 # we see the one of PREPARE too.
27 select f1("c")|
28 --echo
29 # we should not see the trace of "select TRACE+NULL..."
30 # because tracing is disabled when OPTIMIZER_TRACE table is used.
31 --skip_if_hypergraph # Does not support the same optimizer trace.
32 select * from information_schema.OPTIMIZER_TRACE|
33 delimiter ;|
34 set optimizer_trace_offset=default, optimizer_trace_limit=default;
35 drop function f1;
36
37 --echo # check that if a tracing gets disabled in a routine's body,
38 --echo # substatements are not traced
39 --echo
40 set optimizer_trace_offset=0, optimizer_trace_limit=100;
41 delimiter |;
42 create function f1(arg char(1)) returns int
43 begin
44 declare res int;
45 declare dummy varchar(1);
46 set optimizer_trace="enabled=off";
47 select 1 into res from dual;
48 select TRACE+NULL into dummy from information_schema.OPTIMIZER_TRACE limit 1;
49 select 2 into res from dual;
50 return 3;
51 end|
52 select f1("c")|
53 --echo
54 --skip_if_hypergraph # Does not support the same optimizer trace.
55 select * from information_schema.OPTIMIZER_TRACE|
56 delimiter ;|
57 set optimizer_trace_offset=default, optimizer_trace_limit=default;
58 select @@optimizer_trace;
59 set optimizer_trace="enabled=on";
60 drop function f1;
61
62 --echo
63 --echo # Check that if a sub-statement reads OPTIMIZER_TRACE,
64 --echo # thus reading the unfinished trace of its caller statement,
65 --echo # there is no crash.
66 --echo
67
68 create temporary table optt
69 (id int primary key auto_increment,
70 QUERY varchar(200),
71 TRACE text);
72 create table t1 (a int, key(a));
73 insert into t1 values(2);
74 set optimizer_trace_offset=0, optimizer_trace_limit=100;
75 delimiter |;
76 create function f1(arg char(1)) returns int
77 begin
78 declare res int;
79 insert into optt select NULL, QUERY, TRACE from information_schema.OPTIMIZER_TRACE;
80 return 3;
81 end|
82 select * from t1 where a in (select f1("c") from t1)|
83 --echo
84 delimiter ;|
85 set optimizer_trace="enabled=off";
86 --echo this should find unfinished traces
87 --skip_if_hypergraph # Does not support the same optimizer trace.
88 select count(*) from optt where TRACE NOT LIKE "%] /* steps */\n}";
89 --skip_if_hypergraph # Does not support the same optimizer trace.
90 select count(*)<>0 from optt;
91 --echo this should not
92 --skip_if_hypergraph # Does not support the same optimizer trace.
93 select count(*) from information_schema.OPTIMIZER_TRACE where TRACE NOT LIKE "%] /* steps */\n}";
94 --skip_if_hypergraph # Does not support the same optimizer trace.
95 select count(*)<>0 from information_schema.OPTIMIZER_TRACE;
96
97 set optimizer_trace_offset=default, optimizer_trace_limit=default;
98 drop temporary table optt;
99 drop function f1;
100 drop table t1;
101 set optimizer_trace="enabled=on";
102
103 --echo
104 --echo # check of crash with I_S.VIEWS (TABLE_LIST::alias==NULL)
105 --echo
106 create table t1(a int, b int);
107 create view v1 as select a from t1;
108 select VIEW_DEFINITION from information_schema.VIEWS
109 where TABLE_SCHEMA="test" and TABLE_NAME="v1";
110 drop table t1;
111 drop view v1;
112
113 --echo
114 --echo # check for readable display of BIT values
115 --echo
116 create table t1 (a bit(5), key(a));
117 insert into t1 values(b'00000'),(b'01101');
118 select cast(a as unsigned) from t1 where a > b'01100';
119 # Note that in the trace we get either 0x0c or 12
120 --skip_if_hypergraph # Does not support the same optimizer trace.
121 select TRACE from information_schema.OPTIMIZER_TRACE;
122 drop table t1;
123
124 --echo
125 --echo # check that trace lists all pushed down ON conditions
126 --echo
127 create table t1 (i int not null);
128 insert into t1 values (0), (2),(3),(4);
129 create table t2 (i int not null);
130 insert into t2 values (0),(1), (3),(4);
131 create table t3 (i int not null);
132 insert into t3 values (0),(1),(2), (4);
133 --sorted_result
134 select * from
135 t1 LEFT JOIN
136 ( t2 LEFT JOIN
137 ( t3
138 )
139 ON t3.i = t2.i
140 )
141 ON t2.i = t1.i
142 WHERE t3.i IS NULL
143 ;
144 --skip_if_hypergraph # Does not support the same optimizer trace.
145 select TRACE from information_schema.OPTIMIZER_TRACE;
146 drop table t1,t2,t3;
147
148 --echo
149 --echo # test of tracing a query with an HAVING condition, in
150 --echo # ps-protocol, does not crash
151 --echo
152 # Comes from having.test
153
154 CREATE TABLE t1 (f1 INT, f2 VARCHAR(1)) CHARSET utf8mb4;
155 INSERT INTO t1 VALUES (16,'f');
156 INSERT INTO t1 VALUES (16,'f');
157 CREATE TABLE t2 (f1 INT, f2 VARCHAR(1)) CHARSET utf8mb4;
158 INSERT INTO t2 VALUES (13,'f');
159 INSERT INTO t2 VALUES (20,'f');
160 CREATE TABLE t3 (f1 INT, f2 VARCHAR(1)) CHARSET utf8mb4;
161 INSERT INTO t3 VALUES (7,'f');
162 --source include/turn_off_only_full_group_by.inc
163
164 EXPLAIN SELECT t1.f2 FROM t1
165 STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2
166 HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
167 ORDER BY f2;
168
169 SELECT t1.f2 FROM t1
170 STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2
171 HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
172 ORDER BY f2;
173 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/ /("key_size":) [0-9]+/\1 "XXX"/ /("row_size":) [0-9]+/\1 "XXX"/ /("max_rows_per_buffer":) [0-9]+/\1 "XXX"/
174 --skip_if_hypergraph # Does not support the same optimizer trace.
175 select TRACE from information_schema.OPTIMIZER_TRACE;
176
177 --source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
178 DROP TABLES t1,t2,t3;
179
180 --echo
181 --echo # Test that tracing a query with a materialized FROM-clause
182 --echo # derived table using a GROUP BY, does not crash
183 --echo
184 # Comes from profiling.test
185 create table t1 (a int, b int);
186 insert into t1 values (1,1), (2,null), (3, 4);
187 select max(x) from (select sum(a) as x from t1 group by b) as teeone;
188 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
189 --skip_if_hypergraph # Does not support the same optimizer trace.
190 select TRACE from information_schema.OPTIMIZER_TRACE;
191 drop table t1;
192
193 --echo
194 --echo # To have no crash above, we had to restore the ref_array at
195 --echo # end of JOIN::exec(). This impacts how the query looks like,
196 --echo # but not too much, as seen in the error message below.
197 --echo # Comes from func_gconcat.test.
198 --echo
199 CREATE TABLE t1(f1 int);
200 INSERT INTO t1 values (0),(0);
201 set optimizer_trace="enabled=off";
202 --disable_ps_protocol
203 --error ER_ILLEGAL_VALUE_FOR_TYPE
204 SELECT POLYGON((SELECT 1 FROM (SELECT 1 IN (GROUP_CONCAT(t1.f1)) FROM t1, t1 t GROUP BY t.f1 ) d));
205 --enable_ps_protocol
206 set optimizer_trace="enabled=on";
207 --disable_ps_protocol
208 --error ER_ILLEGAL_VALUE_FOR_TYPE
209 SELECT POLYGON((SELECT 1 FROM (SELECT 1 IN (GROUP_CONCAT(t1.f1)) FROM t1, t1 t GROUP BY t.f1 ) d));
210 --enable_ps_protocol
211 DROP TABLE t1;
212
213 --echo
214 --echo # Check that SQL PREPARE and SQL EXECUTE each produce one trace.
215 --echo
216 set optimizer_trace_offset=0, optimizer_trace_limit=100;
217 prepare stmt from "select 1";
218 --skip_if_hypergraph # Does not support the same optimizer trace.
219 select * from information_schema.OPTIMIZER_TRACE;
220 set optimizer_trace_offset=0, optimizer_trace_limit=100;
221 execute stmt;
222 --skip_if_hypergraph # Does not support the same optimizer trace.
223 select * from information_schema.OPTIMIZER_TRACE;
224 deallocate prepare stmt;
225 set optimizer_trace_offset=default, optimizer_trace_limit=default;
226
227 --echo
228 --echo # Test of SELECTs in IF in stored routine.
229 --echo # Same test for CASE WHEN.
230 --echo
231 create table t1 (a int);
232 delimiter |;
233 create procedure p1()
234 begin
235 if exists(select 1) then
236 insert into t1 values(1);
237 end if;
238 if exists(select 2) then
239 insert into t1 values(2);
240 end if;
241 if (select count(*) from t1) then
242 insert into t1 values(3);
243 end if;
244 set @a=(select count(a) from t1 where a>0);
245 case (select count(a) from t1 where a>1)
246 when 2 then set @b=2;
247 else set @b=3;
248 end case;
249 end|
250 delimiter ;|
251 set optimizer_trace_offset=0, optimizer_trace_limit=100;
252 set @old_max=@@optimizer_trace_max_mem_size;
253 set optimizer_trace_max_mem_size=40000;
254 call p1();
255 # SET @a=(SELECT) is not traced because part of SET
256 # which is a real command and not traced.
257 --skip_if_hypergraph # Does not support the same optimizer trace.
258 select * from information_schema.OPTIMIZER_TRACE;
259 select * from t1;
260 select @a,@b;
261 set optimizer_trace_max_mem_size=@old_max;
262 drop procedure p1;
263 drop table t1;
264
265 --echo
266 --echo # Test of tracing of DO.
267 --echo
268
269 set optimizer_trace_offset=0, optimizer_trace_limit=100;
270 do (select 42);
271 --skip_if_hypergraph # Does not support the same optimizer trace.
272 select * from information_schema.OPTIMIZER_TRACE;
273
274 --echo
275 --echo # Test of tracing of subquery used in parameter of routine call
276 --echo
277 create table t1(a int);
278 insert into t1 values(1),(2);
279 delimiter |;
280 create procedure p1(x int)
281 begin
282 declare b int;
283 set b=(select 2+x from dual);
284 end|
285 delimiter ;|
286 set optimizer_trace_offset=0, optimizer_trace_limit=100;
287 call p1((select a from t1 limit 1));
288 --skip_if_hypergraph # Does not support the same optimizer trace.
289 select * from information_schema.OPTIMIZER_TRACE;
290 drop procedure p1;
291 drop table t1;
292 set optimizer_trace_offset=default, optimizer_trace_limit=default;
293
294 --echo
295 --echo # Test that printing expanded query does not alter query's
296 --echo # results.
297 --echo # Comes from ctype_utf8mb4_heap.test
298 --echo
299 create table t1 (f1 varchar(1) not null) default charset utf8mb4;
300 insert into t1 values (''), ('');
301 select concat(concat(_latin1'->',f1),_latin1'<-') from t1;
302 --skip_if_hypergraph # Does not support the same optimizer trace.
303 select * from information_schema.optimizer_trace;
304 drop table t1;
305
306 --echo
307 --echo # Bug#12546331 - SEGFAULT IN SUBSELECT_INDEXSUBQUERY_ENGINE::PRINT WITH OPTIMIZER TRACE
308 --echo
309
310 CREATE TABLE t1 (
311 col_int_nokey INT,
312 col_int_key INT,
313 col_varchar_key varchar(1),
314 KEY col_int_key (col_int_key),
315 KEY col_varchar_key (col_varchar_key,col_int_key)
316 );
317
318 INSERT INTO t1 VALUES
319 (NULL,8,'x'),
320 (8,7,'d'),
321 (1,1,'r'),
322 (9,7,'f'),
323 (4,9,'y'),
324 (3,NULL,'u'),
325 (2,1,'m'),
326 (NULL,9,NULL),
327 (2,2,'o'),
328 (NULL,9,'w'),
329 (6,2,'m'),
330 (7,4,'q'),
331 (2,0,NULL),
332 (5,4,'d'),
333 (7,8,'g'),
334 (6,NULL,'x'),
335 (6,NULL,'f'),
336 (2,0,'p'),
337 (9,NULL,'j'),
338 (6,8,'c')
339 ;
340
341 CREATE TABLE t2 (
342 col_int_nokey INT,
343 col_int_key INT,
344 col_varchar_key varchar(1),
345 KEY col_int_key (col_int_key),
346 KEY col_varchar_key (col_varchar_key,col_int_key)
347 );
348
349 INSERT INTO t2 VALUES
350 (2,4,'v'),
351 (150,62,'v'),
352 (NULL,7,'c'),
353 (2,1,NULL),
354 (5,0,'x'),
355 (3,7,'i'),
356 (1,7,'e'),
357 (4,1,'p'),
358 (NULL,7,'s'),
359 (2,1,'j'),
360 (6,5,'z'),
361 (6,2,'c'),
362 (8,0,'a'),
363 (2,1,'q'),
364 (6,8,'y'),
365 (8,1,NULL),
366 (3,1,'r'),
367 (3,9,'v'),
368 (9,1,NULL),
369 (6,5,'r')
370 ;
371
372 SELECT col_int_nokey
373 FROM (
374 SELECT *
375 FROM t2
376 WHERE col_varchar_key > 'a'
377 OR ( 7 , 5 ) NOT IN (
378 SELECT col_int_nokey , col_int_key
379 FROM t1 )
380 ) AS alias1;
381
382 DROP TABLE t1;
383 DROP TABLE t2;
384
385 --echo
386 --echo BUG#12552262 - INVALID JSON WITH TWO CALLS TO TEST_QUICK_SELECT
387 --echo
388
389 CREATE TABLE t1 (
390 col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 DEFAULT NULL,
391 col_int_key INT,
392 KEY col_int_key (col_int_key)
393 );
394
395 CREATE TABLE t2 (
396 col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 DEFAULT NULL,
397 col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
398 col_int_key INT,
399 KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
400 KEY col_int_key (col_int_key)
401 );
402
403 INSERT INTO t2 VALUES ('qykbaqfyhz','l',NULL);
404
405 CREATE TABLE t3 (
406 col_int_key INT,
407 col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
408 col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 DEFAULT NULL,
409 KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
410 KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key)
411 );
412
413 INSERT INTO t3 VALUES (0,'s','it');
414 INSERT INTO t3 VALUES (9,'IQTHK','JCAQM');
415
416 SELECT table2.col_int_key
417 FROM t3 AS table1
418 LEFT JOIN t1 AS table2 ON table1.col_int_key < table2.col_int_key
419 LEFT JOIN t2 AS table3 ON table2.col_varchar_10_latin1_key >=
420 table3.col_varchar_10_utf8_key
421 ;
422
423 --skip_if_hypergraph # Does not support the same optimizer trace.
424 select * from information_schema.optimizer_trace;
425
426 DROP TABLE t1,t2,t3;
427
428 --echo
429 --echo Tests of tracing of the "eq_ref optimization" of plan search
430 --echo
431
432 # test for trace point "chosen:true","pruned_by_cost:true" and
433 # "added_to_eq_ref_extension:true" (from main.subquery_sj_none_jcl7)
434
435 create table t0 (a int);
436 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
437 create table t1 (a int, b int, key(a));
438 create table t2 (a int, b int, key(a));
439 create table t3 (a int, b int, key(a));
440 insert into t1 select a,a from t0;
441 insert into t2 select a,a from t0;
442 insert into t3 select a,a from t0;
443
444 set @old_opt_switch=@@optimizer_switch;
445 # The SET below must not be output, because only servers supporting
446 # semijoin will execute it (would make varying output).
447 if (`select locate('semijoin', @@optimizer_switch) > 0`)
448 {
449 --disable_query_log
450 set optimizer_switch="semijoin=off,materialization=off";
451 --enable_query_log
452 }
453 explain select *
454 from t0 where a in
455 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
456
457 --skip_if_hypergraph # Does not support the same optimizer trace.
458 select * from information_schema.optimizer_trace;
459 set optimizer_switch=@old_opt_switch;
460 drop table t0,t1,t2,t3;
461
462 # test for trace point "added_to_eq_ref_extension:false" (from main.derived)
463
464 CREATE TABLE t1 (
465 OBJECTID int(11) NOT NULL default '0',
466 SORTORDER int(11) NOT NULL auto_increment,
467 KEY t1_SortIndex (SORTORDER),
468 KEY t1_IdIndex (OBJECTID)
469 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
470 CREATE TABLE t2 (
471 ID int(11) default NULL,
472 PARID int(11) default NULL,
473 UNIQUE KEY t2_ID_IDX (ID),
474 KEY t2_PARID_IDX (PARID)
475 ) engine=MyISAM DEFAULT CHARSET=latin1;
476 INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
477 CREATE TABLE t3 (
478 ID int(11) default NULL,
479 DATA decimal(10,2) default NULL,
480 UNIQUE KEY t3_ID_IDX (ID)
481 ) engine=MyISAM DEFAULT CHARSET=latin1;
482 INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
483
484 select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp;
485 --skip_if_hypergraph # Does not support the same optimizer trace.
486 select * from information_schema.optimizer_trace;
487 drop table t1,t2,t3;
488
489 # test of multiple nested trace points "added_to_eq_ref_extension:true"
490 # (eq_ref optimization finding a sequence of eq_ref-joined tables) (from
491 # main.type_blob)
492
493 CREATE TABLE t1 (
494 t1_id bigint(21) NOT NULL auto_increment,
495 _field_72 varchar(128) DEFAULT '' NOT NULL,
496 _field_95 varchar(32),
497 _field_115 tinyint(4) DEFAULT '0' NOT NULL,
498 _field_122 tinyint(4) DEFAULT '0' NOT NULL,
499 _field_126 tinyint(4),
500 _field_134 tinyint(4),
501 PRIMARY KEY (t1_id),
502 UNIQUE _field_72 (_field_72),
503 KEY _field_115 (_field_115),
504 KEY _field_122 (_field_122)
505 );
506 INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL);
507 INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL);
508 INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL);
509
510 CREATE TABLE t2 (
511 seq_0_id bigint(21) DEFAULT '0' NOT NULL,
512 seq_1_id bigint(21) DEFAULT '0' NOT NULL,
513 PRIMARY KEY (seq_0_id,seq_1_id)
514 );
515 INSERT INTO t2 VALUES (1,1);
516 INSERT INTO t2 VALUES (2,1);
517 INSERT INTO t2 VALUES (2,2);
518 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
519 CREATE TABLE t3 (
520 t3_id bigint(21) NOT NULL auto_increment,
521 _field_131 varchar(128),
522 _field_133 tinyint(4) DEFAULT '0' NOT NULL,
523 _field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
524 _field_137 tinyint(4),
525 _field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
526 _field_140 blob,
527 _field_142 tinyint(4) DEFAULT '0' NOT NULL,
528 _field_145 tinyint(4) DEFAULT '0' NOT NULL,
529 _field_148 tinyint(4) DEFAULT '0' NOT NULL,
530 PRIMARY KEY (t3_id),
531 KEY _field_133 (_field_133),
532 KEY _field_135 (_field_135),
533 KEY _field_139 (_field_139),
534 KEY _field_142 (_field_142),
535 KEY _field_145 (_field_145),
536 KEY _field_148 (_field_148)
537 );
538 INSERT INTO t3 VALUES (1,'test job 1',0,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0);
539 INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0);
540 SET sql_mode = default;
541 CREATE TABLE t4 (
542 seq_0_id bigint(21) DEFAULT '0' NOT NULL,
543 seq_1_id bigint(21) DEFAULT '0' NOT NULL,
544 PRIMARY KEY (seq_0_id,seq_1_id)
545 );
546 INSERT INTO t4 VALUES (1,1);
547 INSERT INTO t4 VALUES (2,1);
548
549 CREATE TABLE t5 (
550 t5_id bigint(21) NOT NULL auto_increment,
551 _field_149 tinyint(4),
552 _field_156 varchar(128) DEFAULT '' NOT NULL,
553 _field_157 varchar(128) DEFAULT '' NOT NULL,
554 _field_158 varchar(128) DEFAULT '' NOT NULL,
555 _field_159 varchar(128) DEFAULT '' NOT NULL,
556 _field_160 varchar(128) DEFAULT '' NOT NULL,
557 _field_161 varchar(128) DEFAULT '' NOT NULL,
558 PRIMARY KEY (t5_id),
559 KEY _field_156 (_field_156),
560 KEY _field_157 (_field_157),
561 KEY _field_158 (_field_158),
562 KEY _field_159 (_field_159),
563 KEY _field_160 (_field_160),
564 KEY _field_161 (_field_161)
565 );
566 INSERT INTO t5 VALUES (1,0,'tomato','','','','','');
567 INSERT INTO t5 VALUES (2,0,'cilantro','','','','','');
568
569 CREATE TABLE t6 (
570 seq_0_id bigint(21) DEFAULT '0' NOT NULL,
571 seq_1_id bigint(21) DEFAULT '0' NOT NULL,
572 PRIMARY KEY (seq_0_id,seq_1_id)
573 );
574 INSERT INTO t6 VALUES (1,1);
575 INSERT INTO t6 VALUES (1,2);
576 INSERT INTO t6 VALUES (2,2);
577
578 CREATE TABLE t7 (
579 t7_id bigint(21) NOT NULL auto_increment,
580 _field_143 tinyint(4),
581 _field_165 varchar(32),
582 _field_166 smallint(6) DEFAULT '0' NOT NULL,
583 PRIMARY KEY (t7_id),
584 KEY _field_166 (_field_166)
585 );
586 INSERT INTO t7 VALUES (1,0,'High',1);
587 INSERT INTO t7 VALUES (2,0,'Medium',2);
588 INSERT INTO t7 VALUES (3,0,'Low',3);
589 --source include/turn_off_only_full_group_by.inc
590
591 # Hypergraph optimizer has different warnings.
592 --disable_warnings
593 select
594 replace(t3._field_140, "\r","^M"),
595 t3_id,
596 min(t3._field_131),
597 min(t3._field_135),
598 min(t3._field_139),
599 min(t3._field_137),
600 min(link_alias_142._field_165),
601 min(link_alias_133._field_72),
602 min(t3._field_145),
603 min(link_alias_148._field_156),
604 replace(min(t3._field_140), "\r","^M"),
605 t3.t3_id
606 from
607 t3 left join t4 on t4.seq_0_id = t3.t3_id
608 left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id
609 left join t6 on t6.seq_0_id = t3.t3_id
610 left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id
611 left join t2 on t2.seq_0_id = t3.t3_id
612 left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id
613 where
614 t3.t3_id in (1)
615 group by
616 t3.t3_id
617 order by
618 link_alias_142._field_166,
619 _field_139,
620 link_alias_133._field_72,
621 _field_135,
622 link_alias_148._field_156
623 ;
624 --enable_warnings
625 --skip_if_hypergraph # Does not support the same optimizer trace.
626 select * from information_schema.optimizer_trace;
627
628 --source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
629 drop table t1,t2,t3,t4,t5,t6,t7;
630
631 --echo #
632 --echo # Tracing of ORDER BY & GROUP BY simplification.
633 --echo #
634
635 # this is originally the testcase for
636 # Bug#12699645 SELECT SUM() + STRAIGHT_JOIN QUERY MISSES ROWS
637 CREATE TABLE t1 (
638 pk INT, col_int_key INT,
639 col_varchar_key VARCHAR(1), col_varchar_nokey VARCHAR(1)
640 );
641 INSERT INTO t1 VALUES
642 (10,7,'v','v'),(11,0,'s','s'),(12,9,'l','l'),(13,3,'y','y'),(14,4,'c','c'),
643 (15,2,'i','i'),(16,5,'h','h'),(17,3,'q','q'),(18,1,'a','a'),(19,3,'v','v'),
644 (20,6,'u','u'),(21,7,'s','s'),(22,5,'y','y'),(23,1,'z','z'),(24,204,'h','h'),
645 (25,224,'p','p'),(26,9,'e','e'),(27,5,'i','i'),(28,0,'y','y'),(29,3,'w','w');
646
647 CREATE TABLE t2 (
648 pk INT, col_int_key INT,
649 col_varchar_key VARCHAR(1), col_varchar_nokey VARCHAR(1),
650 PRIMARY KEY (pk)
651 );
652 INSERT INTO t2 VALUES
653 (1,4,'b','b'),(2,8,'y','y'),(3,0,'p','p'),(4,0,'f','f'),(5,0,'p','p'),
654 (6,7,'d','d'),(7,7,'f','f'),(8,5,'j','j'),(9,3,'e','e'),(10,188,'u','u'),
655 (11,4,'v','v'),(12,9,'u','u'),(13,6,'i','i'),(14,1,'x','x'),(15,5,'l','l'),
656 (16,6,'q','q'),(17,2,'n','n'),(18,4,'r','r'),(19,231,'c','c'),(20,4,'h','h'),
657 (21,3,'k','k'),(22,3,'t','t'),(23,7,'t','t'),(24,6,'k','k'),(25,7,'g','g'),
658 (26,9,'z','z'),(27,4,'n','n'),(28,4,'j','j'),(29,2,'l','l'),(30,1,'d','d'),
659 (31,2,'t','t'),(32,194,'y','y'),(33,2,'i','i'),(34,3,'j','j'),(35,8,'r','r'),
660 (36,4,'b','b'),(37,9,'o','o'),(38,4,'k','k'),(39,5,'a','a'),(40,5,'f','f'),
661 (41,9,'t','t'),(42,3,'c','c'),(43,8,'c','c'),(44,0,'r','r'),(45,98,'k','k'),
662 (46,3,'l','l'),(47,1,'o','o'),(48,0,'t','t'),(49,189,'v','v'),(50,8,'x','x'),
663 (51,3,'j','j'),(52,3,'x','x'),(53,9,'k','k'),(54,6,'o','o'),(55,8,'z','z'),
664 (56,3,'n','n'),(57,9,'c','c'),(58,5,'d','d'),(59,9,'s','s'),(60,2,'j','j'),
665 (61,2,'w','w'),(62,5,'f','f'),(63,8,'p','p'),(64,6,'o','o'),(65,9,'f','f'),
666 (66,0,'x','x'),(67,3,'q','q'),(68,6,'g','g'),(69,5,'x','x'),(70,8,'p','p'),
667 (71,2,'q','q'),(72,120,'q','q'),(73,25,'v','v'),(74,1,'g','g'),(75,3,'l','l'),
668 (76,1,'w','w'),(77,3,'h','h'),(78,153,'c','c'),(79,5,'o','o'),(80,9,'o','o'),
669 (81,1,'v','v'),(82,8,'y','y'),(83,7,'d','d'),(84,6,'p','p'),(85,2,'z','z'),
670 (86,4,'t','t'),(87,7,'b','b'),(88,3,'y','y'),(89,8,'k','k'),(90,4,'c','c'),
671 (91,6,'z','z'),(92,1,'t','t'),(93,7,'o','o'),(94,1,'u','u'),(95,0,'t','t'),
672 (96,2,'k','k'),(97,7,'u','u'),(98,2,'b','b'),(99,1,'m','m'),(100,5,'o','o');
673
674 # We see the functional dependency implied by ON
675
676 let $query=
677 SELECT SUM(alias2.col_varchar_nokey) , alias2.pk AS field2 FROM t1 AS alias1
678 STRAIGHT_JOIN t2 AS alias2 ON alias2.pk = alias1.col_int_key WHERE alias1.pk
679 GROUP BY field2 ORDER BY alias1.col_int_key,alias2.pk ;
680 # Hypergraph optimizer has different warnings.
681 --disable_warnings
682 eval $query;
683 --enable_warnings
684 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/ /("key_size":) [0-9]+/\1 "XXX"/ /("row_size":) [0-9]+/\1 "XXX"/ /("max_rows_per_buffer":) [0-9]+/\1 "XXX"/
685 --skip_if_hypergraph # Does not support the same optimizer trace.
686 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
687
688 DROP TABLE t1,t2;
689
690 --echo #
691 --echo # Trace of "condition on constant tables"
692 --echo #
693 create table t1(a int) engine=myisam;
694 insert into t1 values(26);
695 create table t2(b int primary key, c int) engine=myisam;
696 insert into t2 values(1,100),(2,200),(3,300);
697 select * from t1,t2 where t1.a+t2.c=cos(10) and t2.b=2;
698 --skip_if_hypergraph # Does not support the same optimizer trace.
699 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
700 drop table t1,t2;
701
702 --echo #
703 --echo # Trace of non-default db
704 --echo #
705 create table t1(a int);
706 insert into t1 values(1),(2),(3);
707 create database mysqltest2;
708 create table mysqltest2.t2(a int);
709 insert into mysqltest2.t2 values(1),(2);
710 analyze table t1, mysqltest2.t2;
711 --sorted_result
712 select * from t1,mysqltest2.t2;
713 --skip_if_hypergraph # Does not support the same optimizer trace.
714 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
715 drop table t1;
716 drop database mysqltest2;