Debian Codegraph @

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

raw
1 # Basic test for optimizer trace
2
3 --source include/have_optimizer_trace.inc
4 --source include/not_hypergraph.inc # Optimizer trace is different.
5
6 # This test gives different results when ps-protocol is enabled
7 # because in ps-protocol, we read OPTIMIZER_TRACE after
8 # 'EXECUTE stmt'; for subqueries the IN->EXISTS transformation is done
9 # at PREPARE time, and not re-done at EXECUTE time, so in normal
10 # protocol the trace talks about IN->EXISTS, but in ps-protocol it
11 # doesn't (as it's the EXECUTE trace and IN->EXISTS was done once for
12 # all at PREPARE; we see IN->EXISTS if we look at the trace of
13 # PREPARE).
14 # That's why we have two result files.
15 # This test is skipped in view-protocol because this changes queries
16 # submitted to the optimizer (from 'SELECT x FROM ... WHERE ... etc'
17 # to 'SELECT x FROM mysqltest_tmp_v') so changes trace.
18 # It's also skipped in sp-protocol for the same reason (SELECT becomes
19 # CALL).
20
21 # When adding tests here, try to have some of them use SELECT and
22 # others use EXPLAIN SELECT, to verify that both ways give sufficient
23 # tracing.
24
25 # should be empty
26 select * from information_schema.OPTIMIZER_TRACE;
27
28 let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB
29 eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;
30 set @@session.optimizer_trace="enabled=on";
31
32 select * from information_schema.OPTIMIZER_TRACE;
33
34 CREATE TABLE t5 (c int);
35 INSERT INTO t5 VALUES (NULL);
36
37 CREATE TABLE t6 (d int , KEY (d));
38 INSERT INTO t6 VALUES (NULL),(NULL);
39
40 SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 ;
41
42 # some float values might not be identical on all platforms, we will see.
43 select * from information_schema.OPTIMIZER_TRACE;
44 # check frequency of spaces
45 select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
46 from information_schema.OPTIMIZER_TRACE;
47
48 # now all on one line
49 set optimizer_trace="one_line=on";
50 SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 ;
51 select * from information_schema.OPTIMIZER_TRACE;
52 # check frequency of spaces
53 select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
54 from information_schema.OPTIMIZER_TRACE;
55
56 # From now on, use end markers, easier to read
57 set end_markers_in_json=on;
58 set optimizer_trace="one_line=off";
59
60 # trace should be produced by EXPLAIN too
61 EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
62 select * from information_schema.OPTIMIZER_TRACE;
63
64 SELECT /* should be last */ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
65 select * from information_schema.OPTIMIZER_TRACE;
66
67 set @@session.optimizer_trace="enabled=off";
68 SELECT /* bug if you see this*/ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
69 # should not see last statement but previous
70 select QUERY from information_schema.OPTIMIZER_TRACE;
71
72 set @@session.optimizer_trace="enabled=on";
73
74 # union in subquery
75 SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
76 select * from information_schema.OPTIMIZER_TRACE;
77
78 # union in subquery in WHERE clause
79 SELECT * FROM t5 WHERE 5 IN (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null));
80 select * from information_schema.OPTIMIZER_TRACE;
81
82 # Test max_mem_size: re-run same query with lower max_mem_size:
83 # save non-truncated trace but don't output it again
84 --disable_warnings
85 select (@query:=QUERY)+NULL, (@trace:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
86 --enable_warnings
87 --skip_if_hypergraph # Different optimier trace.
88 select length(@trace);
89 # The concatenation of query and trace above has length:
90 # - >@max_mem_size in normal mode
91 # - <@max_mem_size in ps-protocol mode (because IN->EXISTS is done at PREPARE
92 # and we trace only EXECUTE)
93 # - So in normal mode, the lines below verify truncation,
94 # whereas in ps-protocol mode they verify non-truncation.
95 set @max_mem_size=13307;
96 set optimizer_trace_max_mem_size=@max_mem_size;
97 --skip_if_hypergraph # Different optimier trace.
98 select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
99 SELECT * FROM t5 WHERE 5 IN (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null));
100 select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE;
101 --disable_warnings
102 select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
103 --enable_warnings
104 --skip_if_hypergraph # Different optimier trace.
105 select length(@trace2),
106 (length(@trace2) + @missing_bytes) = length(@trace),
107 @query2 = @query;
108 # If truncated, trace length should be around the maximum, possibly a
109 # bit higher as we stop tracing after passing the maximum.
110 select length(@query2) + length(@trace2)
111 between (@@optimizer_trace_max_mem_size-200) and (@@optimizer_trace_max_mem_size+200);
112 # if truncated, trace should be a prefix of non-truncated trace
113 select instr(@trace, @trace2) = 1;
114
115 # Test that if the query is longer than max size, trace is not produced
116 set optimizer_trace_max_mem_size=1;
117 select 1;
118 select * from information_schema.OPTIMIZER_TRACE;
119 # Test that with max size == 0, even query is not produced
120 set optimizer_trace_max_mem_size=0;
121 select 1;
122 select * from information_schema.OPTIMIZER_TRACE;
123 eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;
124
125 # semijoin
126
127 explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null);
128 select * from information_schema.OPTIMIZER_TRACE;
129
130 # subquery materialization
131
132 set @old_opt_switch=@@optimizer_switch;
133 # The SET below must not be output, because only servers supporting
134 # semijoin will execute it (would make varying output).
135 if (`select locate('semijoin', @@optimizer_switch) > 0`)
136 {
137 --disable_query_log
138 set optimizer_switch="semijoin=off,subquery_materialization_cost_based=off";
139 --enable_query_log
140 }
141 explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null);
142 select * from information_schema.OPTIMIZER_TRACE;
143 set optimizer_switch=@old_opt_switch;
144
145 # 5.1-style subquery transformations
146 CREATE TABLE t1 (s1 CHAR(5),
147 s2 CHAR(5)) CHARSET utf8mb4;
148 INSERT INTO t1 VALUES ('z','?'),('y','!');
149 # > ANY -> > MIN...
150 explain select * from t1 where s1 > any (select s2 from t1);
151 select * from information_schema.OPTIMIZER_TRACE;
152 explain select * from t1 where s1 > any (select max(s2) from t1);
153 select * from information_schema.OPTIMIZER_TRACE;
154 # IN -> EXISTS
155 if (`select locate('semijoin', @@optimizer_switch) > 0`)
156 {
157 --disable_query_log
158 set optimizer_switch="semijoin=off,materialization=off";
159 --enable_query_log
160 }
161 explain select * from t1 where s1 in (select s2 from t1);
162 select * from information_schema.OPTIMIZER_TRACE;
163 explain select * from t1 where (s1,s2) in (select s2,s1 from t1);
164 select * from information_schema.OPTIMIZER_TRACE;
165 set optimizer_switch=@old_opt_switch;
166 drop table t1;
167
168 # explanation of plan choice
169 create table t1(a int);
170 create table t2(a int);
171 insert into t1 values(1),(2),(3);
172 insert into t2 values(1),(2);
173 # print many plans:
174 set @@session.optimizer_prune_level=0;
175 explain select * from t1,t2;
176 select * from information_schema.OPTIMIZER_TRACE;
177 # don't print plans:
178 select @@optimizer_trace_features;
179 set @@optimizer_trace_features="greedy_search=off";
180 explain select * from t1,t2;
181 select * from information_schema.OPTIMIZER_TRACE;
182 set @@optimizer_trace_features=default;
183 set @@session.optimizer_prune_level=default;
184 drop table t1, t2;
185
186 # test late decision to abandon subquery materialization due to BLOBs
187 # (from subselect_mat.test)
188 # force materialization to be always considered
189 if (`select locate('semijoin', @@optimizer_switch) > 0`)
190 {
191 --disable_query_log
192 set @@optimizer_switch='semijoin=off';
193 --enable_query_log
194 }
195 set @prefix_len = 6;
196 # BLOB == 16 (small blobs that could be stored in HEAP tables)
197 set @blob_len = 16;
198 set @suffix_len = @blob_len - @prefix_len;
199 create table t1_16 (a1 blob(16), a2 blob(16));
200 create table t2_16 (b1 blob(16), b2 blob(16));
201 create table t3_16 (c1 blob(16), c2 blob(16));
202 insert into t1_16 values
203 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
204 insert into t1_16 values
205 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
206 insert into t1_16 values
207 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
208 insert into t2_16 values
209 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
210 insert into t2_16 values
211 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
212 insert into t2_16 values
213 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
214 insert into t3_16 values
215 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
216 insert into t3_16 values
217 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
218 insert into t3_16 values
219 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
220 insert into t3_16 values
221 (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
222 # single value transformer
223 explain select left(a1,7), left(a2,7)
224 from t1_16
225 where a1 in (select b1 from t2_16 where b1 > '0');
226 select * from information_schema.OPTIMIZER_TRACE;
227 drop table t1_16,t2_16,t3_16;
228 set @@optimizer_switch=@old_opt_switch;
229
230 # test of outer-join preventing semijoin
231 # from subselect.test
232
233 CREATE table t1 ( c1 integer );
234 INSERT INTO t1 VALUES ( 1 );
235 INSERT INTO t1 VALUES ( 2 );
236 INSERT INTO t1 VALUES ( 3 );
237
238 CREATE TABLE t2 ( c2 integer );
239 INSERT INTO t2 VALUES ( 1 );
240 INSERT INTO t2 VALUES ( 4 );
241 INSERT INTO t2 VALUES ( 5 );
242
243 SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2
244 WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );
245 select * from information_schema.OPTIMIZER_TRACE;
246
247 # union in top-query
248
249 SELECT * FROM t1 WHERE c1=5 UNION SELECT * FROM t2 WHERE c2=5;
250 select * from information_schema.OPTIMIZER_TRACE;
251
252 # two subqueries in top query
253
254 if (`select locate('semijoin', @@optimizer_switch) > 0`)
255 {
256 --disable_query_log
257 set optimizer_switch="semijoin=off";
258 --enable_query_log
259 }
260
261 explain
262 select * from t1
263 where concat(c1,'x') IN
264 (select left(c2,8) from t2)
265 and
266 concat(c1,'y') IN
267 (select left(c2,9) from t2);
268
269 select * from information_schema.OPTIMIZER_TRACE;
270
271 set optimizer_switch=@old_opt_switch;
272
273 DROP TABLE t1,t2;
274
275 # two constant tables
276
277 create table t1 (a int);
278 insert into t1 values(1);
279 create table t2 (a int);
280 insert into t2 values(1);
281 select * from t1,t2;
282 select * from information_schema.OPTIMIZER_TRACE;
283
284 # semijoin pullout
285
286 create table t3 (a int, b int);
287 create table t4 (a int primary key);
288 insert into t4 values(1),(2);
289 # we do it with prepared statements; by reading the expanded query we
290 # can see that
291 # - subquery->semijoin transformation is done at first EXECUTE
292 # - same for semijoin table pullout
293 # - both are reused by the second EXECUTE (where expanded_query is a
294 # pure join)
295 prepare stmt from 'select * from t3 where (a,a,b) in (select * from t1,t2,t4)';
296 select trace from information_schema.OPTIMIZER_TRACE;
297 execute stmt;
298 select trace from information_schema.OPTIMIZER_TRACE;
299 execute stmt;
300 select trace from information_schema.OPTIMIZER_TRACE;
301 DROP TABLE t1,t2,t3,t4;
302
303 # Trig_cond printing
304
305 create table t1(a int);
306 insert into t1 values(1),(1);
307 create table t2(a int);
308 insert into t2 values(1),(1);
309 # guarded pushed down conds for WHERE and ON in LEFT JOIN
310 select * from t1 left join t2 on t2.a=500 where t2.a is NULL;
311 select * from information_schema.OPTIMIZER_TRACE;
312 drop table t1,t2;
313 # guarded added conds for IN->EXISTS:
314 create table t1(a int, b int);
315 insert into t1 values(1,NULL),(NULL,2);
316 create table t2(c int, d int);
317 insert into t2 values(1,1),(2,2);
318 select * from t1 where (t1.a,t1.b) not in (select c,d from t2 where c>0);
319 select * from information_schema.OPTIMIZER_TRACE;
320
321 # HAVING
322 --sorted_result
323 select t1.a,avg(t2.c) as moyenne from t1, t2 where t2.c>-1
324 group by t1.a having moyenne<>0;
325 --replace_regex /("sort_buffer_size":) [0-9]+/\1 "NNN"/
326 select trace from information_schema.OPTIMIZER_TRACE;
327 # impossible HAVING
328 select t1.a,avg(t2.c) as moyenne from t1, t2 where t2.c>-1
329 group by t1.a having 4=5;
330 select trace from information_schema.OPTIMIZER_TRACE;
331 drop table t1,t2;
332
333 # HAVING
334
335
336 # test non-SELECT statements
337
338 update t6 set d=5 where d is NULL;
339 select * from information_schema.OPTIMIZER_TRACE;
340
341 delete from t6 where d=5;
342 select * from information_schema.OPTIMIZER_TRACE;
343
344 insert into t6 values(6),(7),(8);
345 select * from information_schema.OPTIMIZER_TRACE;
346
347 insert into t6 select * from t6 where d>7;
348 select * from information_schema.OPTIMIZER_TRACE;
349
350 update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-4 where d>7000;
351 select * from information_schema.OPTIMIZER_TRACE;
352
353 delete t6 from t5, t6 where d>7000;
354 select * from information_schema.OPTIMIZER_TRACE;
355
356 # Test optimizer_trace_offset/limit for consecutive
357 # non-related statements (as opposed to "for sub-statements"
358 # which is tested further below).
359 set optimizer_trace_offset=2,optimizer_trace_limit=2;
360 # should be empty
361 select * from information_schema.OPTIMIZER_TRACE;
362 select 1;
363 select 2;
364 select 3;
365 select 4;
366 select 5;
367 # In normal mode, each SELECT is a standalone statement and generates
368 # one trace: so 3rd and 4th trace means 3rd and 4th SELECT.
369 # In ps-protocol mode, each SELECT is two statements: PREPARE and
370 # EXECUTE; each of the two creates one trace; so 3rd and 4th trace
371 # means 2nd SELECT (preparation) and 2nd SELECT again (execution).
372 select * from information_schema.OPTIMIZER_TRACE;
373 set optimizer_trace_offset=-2,optimizer_trace_limit=2;
374 # should be empty
375 select * from information_schema.OPTIMIZER_TRACE;
376 select 1;
377 select 2;
378 select 3;
379 select 4;
380 select 5;
381 select * from information_schema.OPTIMIZER_TRACE;
382 set optimizer_trace_offset=default,optimizer_trace_limit=default;
383 # should be empty
384 select * from information_schema.OPTIMIZER_TRACE;
385
386 # For stored functions, stored procedures, triggers, with
387 # offset=-1,limit=1, what we see in the trace is the trace of the last
388 # statement (i.e. last call to mysql_execute_command()). Thus:
389 # - for a call to a stored procedure, we will see only the last
390 # statement of this procedure
391 # - for a call to a trigger, we will see only the last statement of
392 # this trigger, and not the caller.
393 # etc.
394 # If the user wants to see more, 'offset' should be adjusted.
395
396 # Stored functions
397
398 create table t1 (
399 id char(16) not null default '',
400 data int not null
401 ) charset utf8mb4;
402 create table t2 (
403 s char(16),
404 i int,
405 d double
406 ) charset utf8mb4;
407 delimiter |;
408 insert into t1 values ("a", 1), ("b", 2) |
409 insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0), ("d", 4, 4.0) |
410 create function f1() returns int
411 begin
412 declare ret int;
413 insert into t1 values("z",0);
414 delete from t1 where id="z";
415 select sum(data) into ret from t1;
416 return ret;
417 end|
418 select f1()|
419 # Here we will see the trace of RETURN.
420 select * from information_schema.OPTIMIZER_TRACE|
421 select s, f1() from t2 order by s desc|
422 select * from information_schema.OPTIMIZER_TRACE|
423 select * from t6 where d in (select f1() from t2 where s="c")|
424 select * from information_schema.OPTIMIZER_TRACE|
425 # Want to see the top and invoked sub-statements; this means 11 traces:
426 # 1 top statement + two executions of f1() (there is one
427 # execution inside the range optimizer and one "normal" execution);
428 # in the stored function we have traces: DECLARE (1 trace), 3 DMLs
429 # and RETURN (1 trace). 1+2*(1+3+1)=11.
430 # In ps-protocol mode, we have those 11, plus one for PREPARE of the
431 # top SELECT.
432 # We ask for a larger number (60) and will check how many we got.
433 # Note that when semijoin features are disabled, the subquery is
434 # treated differently: it is executed, so the order and amount of
435 # executions of f1() becomes different, which changes the order of
436 # substatements in the trace.
437 set @old_opt_switch=@@optimizer_switch;
438 # Test was created when sj-mat-scan could not handle this query...
439 set optimizer_switch="materialization=off";
440 set optimizer_trace_offset=-60, optimizer_trace_limit=60|
441 select * from t6 where d in (select f1() from t2 where s="c")|
442 select * from information_schema.OPTIMIZER_TRACE|
443 select count(*) from information_schema.OPTIMIZER_TRACE|
444 # Want to see the DELETE (invoked sub-statement):
445 set optimizer_trace_offset=3, optimizer_trace_limit=1|
446 select * from t6 where d in (select f1() from t2 where s="c")|
447 # In normal mode, we have traces for the top SELECT, then the
448 # function's INSERT then the function's DELETE, so DELETE is the
449 # third trace, which we see.
450 # In ps-protocol mode, we also have trace of PREPARE for the top
451 # SELECT, so we see one trace before the DELETE: the INSERT.
452 select * from information_schema.OPTIMIZER_TRACE|
453 set @@optimizer_switch=@old_opt_switch;
454
455 # Stored procedures
456 create procedure p1(arg char(1) charset utf8mb4)
457 begin
458 declare res int;
459 select d into res from t6 where d in (select f1() from t2 where s=arg);
460 select d+1 into res from t6 where d=res+1;
461 end|
462 set @old_opt_switch=@@optimizer_switch;
463 # Test was created when sj-mat-scan could not handle this query...
464 set optimizer_switch="materialization=off";
465 set optimizer_trace_offset=0, optimizer_trace_limit=100;
466 call p1("c")|
467 # Regex to replace 'NAME_CONST(var_name,value)' with 'var_name'
468 # in the opt trace when the variables used are declared within
469 # procedures or passed as arguments and log-bin is enabled.
470 --replace_regex /[ ]NAME_CONST[(][\'](.*)[\'][,][^)]*[)]/\1/
471 select * from information_schema.OPTIMIZER_TRACE|
472 set @@optimizer_switch=@old_opt_switch;
473
474 # Triggers
475 create trigger trg1 before insert on t2 for each row
476 begin
477 set new.s=f1();
478 end|
479 set optimizer_trace_offset=0, optimizer_trace_limit=100|
480 insert into t2 select d,100,200 from t6 where d is not null|
481 select * from information_schema.OPTIMIZER_TRACE|
482 select * from t2|
483 delimiter ;|
484
485 # PREPARE/EXECUTE/EXECUTE
486 prepare stmt from 'select count(*) from t1 where t1.data=?';
487 set @param="c";
488 set optimizer_trace_offset=0, optimizer_trace_limit=100;
489 execute stmt using @param;
490 select count(*) from information_schema.OPTIMIZER_TRACE;
491 select TRACE into @trace from information_schema.OPTIMIZER_TRACE;
492 select @trace;
493 # second EXECUTE should give same trace
494 set optimizer_trace_offset=0, optimizer_trace_limit=100;
495 execute stmt using @param;
496 select count(*) from information_schema.OPTIMIZER_TRACE;
497 select TRACE into @trace2 from information_schema.OPTIMIZER_TRACE;
498 select @trace=@trace2;
499
500 # enable/disable tracing in middle of procedure
501 drop procedure p1;
502 create temporary table optt like information_schema.OPTIMIZER_TRACE;
503 delimiter |;
504 create procedure p1(arg char(1) charset utf8mb4)
505 begin
506 declare res int;
507 set optimizer_trace="enabled=off";
508 # want to see all of SELECT below
509 set optimizer_trace_offset=0, optimizer_trace_limit=100;
510 set optimizer_trace="enabled=on";
511 select d into res from t6 where d in (select f1() from t2 where s=arg);
512 set optimizer_trace="enabled=off"; # and not more
513 insert into optt select * from information_schema.OPTIMIZER_TRACE;
514 set optimizer_trace_offset=default, optimizer_trace_limit=default;
515 select d+1 into res from t6 where d=res+1;
516 end|
517 call p1("c")|
518 # Regex to replace 'NAME_CONST(var_name,value)' with 'var_name'
519 # in the opt trace when the variables used are declared within
520 # procedures or passed as arguments and log-bin is enabled.
521 --replace_regex /[ ]NAME_CONST[(][\'](.*)[\'][,][^)]*[)]/\1/
522 select * from optt|
523 select @@optimizer_trace|
524 delimiter ;|
525
526 set optimizer_trace="enabled=on";
527 drop temporary table optt;
528 drop function f1;
529 drop procedure p1;
530 drop trigger trg1;
531
532 # Views
533 # merge-able
534 create view v1 as select * from t1 where id < "c";
535 explain select * from v1 where id="b";
536 select * from information_schema.OPTIMIZER_TRACE;
537 # insert into view
538 insert into v1 values("z", 100);
539 select * from information_schema.OPTIMIZER_TRACE;
540 delete from v1 where data=100;
541 select * from information_schema.OPTIMIZER_TRACE;
542 drop view v1;
543 # not merge-able
544 create view v1 as select * from t1 where id < "c" limit 2;
545 explain select * from v1 where id="b";
546 select * from information_schema.OPTIMIZER_TRACE;
547 drop view v1;
548
549 # test that DUMPFILE does no escaping of \n
550 # otherwise a JSON parser could not read
551 set end_markers_in_json=off;
552 select 1 union select 2;
553 --let $file=$MYSQLTEST_VARDIR/tmp/optimizer_trace.txt
554 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
555 --eval select TRACE into dumpfile '$file' from information_schema.OPTIMIZER_TRACE;
556 # it has been manually checked that this file's content is JSON-compliant
557 --cat_file $file
558 --remove_file $file
559
560 # Test for crashing bug
561 --error ER_WRONG_VALUE_FOR_VAR
562 set optimizer_switch='default,index_merge=on,index_merge=off,default';
563 select @@optimizer_switch=@old_opt_switch;
564
565 # Test for long query (1070 chars)
566 --disable_query_log
567 --disable_result_log
568 select "abc1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111def" as col;
569 --enable_result_log
570 --enable_query_log
571 select * from information_schema.OPTIMIZER_TRACE;
572
573 drop table t1,t2;
574 DROP TABLE t5,t6;
575
576
577 --echo # Trace for transformation scalar subquery to derived table
578 CREATE TABLE t1(a INT);
579 CREATE TABLE t2(a INT);
580 INSERT INTO t1 VALUES (1),(2),(3),(4);
581 INSERT INTO t2 VALUES (1),(2);
582 ANALYZE TABLE t1, t2;
583
584 SET optimizer_switch='subquery_to_derived=on';
585 SELECT a + (SELECT SUM(a) + (SELECT COUNT(a) FROM t1) FROM t1) AS cnt FROM t2;
586 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
587 SET optimizer_switch='subquery_to_derived=default';
588
589 DROP TABLE t1, t2;
590
591 SET optimizer_trace=default;