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