Debian Codegraph @

mysql-test/suite/secondary_engine/r/plan_cost_debug.result

raw
1 #
2 # Bug#29023387: GET PLAN COST FROM SECONDARY ENGINE
3 #
4 CREATE TABLE t1(x INT, y INT) SECONDARY_ENGINE MOCK;
5 CREATE TABLE t2(x INT, y INT) SECONDARY_ENGINE MOCK;
6 CREATE TABLE t3(x INT, y INT) SECONDARY_ENGINE MOCK;
7 INSERT INTO t1 VALUES (1, 2), (3, 4);
8 INSERT INTO t1 SELECT * FROM t1;
9 INSERT INTO t1 SELECT * FROM t1;
10 INSERT INTO t2 VALUES (1, 2), (3, 4);
11 INSERT INTO t2 SELECT * FROM t2;
12 INSERT INTO t3 VALUES (1, 2), (3, 4);
13 ALTER TABLE t1 SECONDARY_LOAD;
14 ALTER TABLE t2 SECONDARY_LOAD;
15 ALTER TABLE t3 SECONDARY_LOAD;
16 ANALYZE TABLE t1, t2, t3;
17 Table Op Msg_type Msg_text
18 test.t1 analyze status OK
19 test.t2 analyze status OK
20 test.t3 analyze status OK
21 SET @@optimizer_trace='enabled=on';
22 EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.x = t2.y LEFT JOIN t3 ON t2.x = t3.y;
23 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
24 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 4 100.00 Using secondary engine MOCK
25 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where; Using join buffer (hash join); Using secondary engine MOCK
26 1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (hash join); Using secondary engine MOCK
27 Warnings:
28 Note 1003 /* select#1 */ select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y`,`test`.`t2`.`x` AS `x`,`test`.`t2`.`y` AS `y`,`test`.`t3`.`x` AS `x`,`test`.`t3`.`y` AS `y` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`y` = `test`.`t2`.`x`)) where (`test`.`t1`.`x` = `test`.`t2`.`y`)
29 SELECT TRACE->'$**.secondary_engine_cost' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
30 TRACE->'$**.secondary_engine_cost'
31 [9.10036, 5.90021]
32 SET @@optimizer_trace='enabled=off';
33 SET DEBUG = '+d,secondary_engine_mock_change_join_order';
34 EXPLAIN SELECT * FROM t1 AS X JOIN t2 ON X.x = t2.y LEFT JOIN t3 ON t2.x = t3.y;
35 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
36 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 4 100.00 Using secondary engine MOCK
37 1 SIMPLE X NULL ALL NULL NULL NULL NULL 8 12.50 Using where; Using join buffer (hash join); Using secondary engine MOCK
38 1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (hash join); Using secondary engine MOCK
39 EXPLAIN SELECT * FROM t1 JOIN t2 AS X ON t1.x = X.y LEFT JOIN t3 ON X.x = t3.y;
40 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
41 1 SIMPLE X NULL ALL NULL NULL NULL NULL 4 100.00 Using secondary engine MOCK
42 1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (hash join); Using secondary engine MOCK
43 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where; Using join buffer (hash join); Using secondary engine MOCK
44 EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.x = t2.y LEFT JOIN t3 AS X ON t2.x = X.y;
45 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
46 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 4 100.00 Using secondary engine MOCK
47 1 SIMPLE X NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (hash join); Using secondary engine MOCK
48 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where; Using join buffer (hash join); Using secondary engine MOCK
49 SET DEBUG = '-d,secondary_engine_mock_change_join_order';
50 SET DEBUG = '+d,secondary_engine_mock_compare_cost_error';
51 EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.x = t2.y LEFT JOIN t3 ON t2.x = t3.y;
52 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
53 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 4 100.00 NULL
54 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where; Using join buffer (hash join)
55 1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (hash join)
56 Warnings:
57 Note 1003 /* select#1 */ select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y`,`test`.`t2`.`x` AS `x`,`test`.`t2`.`y` AS `y`,`test`.`t3`.`x` AS `x`,`test`.`t3`.`y` AS `y` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`y` = `test`.`t2`.`x`)) where (`test`.`t1`.`x` = `test`.`t2`.`y`)
58 SET DEBUG = '-d,secondary_engine_mock_compare_cost_error';
59 SET optimizer_trace = 'enabled=on';
60 SET use_secondary_engine = 'FORCED';
61 SELECT * FROM t1 a, t2 b, t1 c, t2 d
62 WHERE a.x = b.x AND b.x = c.y AND a.x >= d.y;
63 x y x y x y x y
64 SELECT JSON_LENGTH(
65 TRACE->'$**.considered_execution_plans**.secondary_engine_cost')
66 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
67 JSON_LENGTH(
68 TRACE->'$**.considered_execution_plans**.secondary_engine_cost')
69 3
70 SET DEBUG = '+d,secondary_engine_mock_choose_first_plan';
71 SELECT * FROM t1 a, t2 b, t1 c, t2 d
72 WHERE a.x = b.x AND b.x = c.y AND a.x >= d.y;
73 x y x y x y x y
74 SELECT JSON_LENGTH(
75 TRACE->'$**.considered_execution_plans**.secondary_engine_cost')
76 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
77 JSON_LENGTH(
78 TRACE->'$**.considered_execution_plans**.secondary_engine_cost')
79 1
80 SET DEBUG = '-d,secondary_engine_mock_choose_first_plan';
81 SET use_secondary_engine = DEFAULT;
82 SET optimizer_trace = 'enabled=off';
83 DROP TABLE t1, t2, t3;