Debian Codegraph @

mysql-test/suite/opt_trace/t/join_order.test

raw
1 --source include/have_optimizer_trace.inc
2 --source include/have_hypergraph.inc # join_order is shown only in hypergraph
3 --source include/no_cursor_protocol.inc
4 --source include/no_sp_protocol.inc
5
6 --echo #
7 --echo # Bug#34299473: Hypergraph : More join order information in the optimizer
8 --echo # trace
9 --echo #
10
11 SET optimizer_trace="enabled=on,one_line=off";
12
13 create table json_tab(je json);
14 let $insert_stmt = insert into json_tab
15 select json_extract(trace,"$.steps[*].join_optimization.steps[*].join_optimizer[*]") je
16 from information_schema.optimizer_trace;
17 let $select_stmt = select jt.t from
18 json_tab, json_table(json_tab.je, "$[*]" COLUMNS(t text path "$")) as jt
19 where jt.t like '%current access paths%join_order%' order by t;
20
21
22 # Testcase 1: More than two tables.
23
24 create table t1(id int);
25 insert into t1 values (1), (2), (3);
26 analyze table t1;
27 select * from t1, t1 t2, t1 t3 where t1.id = t2.id and t2.id = t3.id;
28
29 eval $insert_stmt;
30 eval $select_stmt;
31
32 drop table t1;
33 truncate table json_tab;
34
35
36 # Testcase 2: Table scan under a Materialize/Filter plan.
37
38 create table t1 (a int primary key, b int);
39 insert into t1 values (1,1);
40 analyze table t1;
41
42 with x1 as (select max(a) as m1 from t1 group by b)
43 select * from t1 y1 left join t1 y2 on y1.a=-y2.a
44 where y1.b+y2.b = (select max(m1) from x1);
45
46 eval $insert_stmt;
47 eval $select_stmt;
48
49 drop table t1;
50 drop table json_tab;