Debian Codegraph @

mysql-test/suite/secondary_engine/t/plan_cost_debug.test

raw
1 --source include/have_debug.inc
2 # Offloading planning to a secondary engine is not yet supported by hypergraph.
3 --source include/not_hypergraph.inc
4
5 --disable_query_log
6 eval INSTALL PLUGIN MOCK SONAME '$MOCK_PLUGIN';
7 --enable_query_log
8
9 --echo #
10 --echo # Bug#29023387: GET PLAN COST FROM SECONDARY ENGINE
11 --echo #
12 CREATE TABLE t1(x INT, y INT) SECONDARY_ENGINE MOCK;
13 CREATE TABLE t2(x INT, y INT) SECONDARY_ENGINE MOCK;
14 CREATE TABLE t3(x INT, y INT) SECONDARY_ENGINE MOCK;
15 INSERT INTO t1 VALUES (1, 2), (3, 4);
16 INSERT INTO t1 SELECT * FROM t1;
17 INSERT INTO t1 SELECT * FROM t1;
18 INSERT INTO t2 VALUES (1, 2), (3, 4);
19 INSERT INTO t2 SELECT * FROM t2;
20 INSERT INTO t3 VALUES (1, 2), (3, 4);
21 ALTER TABLE t1 SECONDARY_LOAD;
22 ALTER TABLE t2 SECONDARY_LOAD;
23 ALTER TABLE t3 SECONDARY_LOAD;
24 ANALYZE TABLE t1, t2, t3;
25 SET @@optimizer_trace='enabled=on';
26 EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.x = t2.y LEFT JOIN t3 ON t2.x = t3.y;
27 SELECT TRACE->'$**.secondary_engine_cost' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
28 SET @@optimizer_trace='enabled=off';
29 # Set a debug flag so that the join order is chosen based on how early
30 # a table with the alias X is.
31 SET DEBUG = '+d,secondary_engine_mock_change_join_order';
32 # The note in the warning from EXPLAIN shows the alias X in different case
33 # on different platforms, so disable warnings to hide the differences.
34 --disable_warnings
35 EXPLAIN SELECT * FROM t1 AS X JOIN t2 ON X.x = t2.y LEFT JOIN t3 ON t2.x = t3.y;
36 EXPLAIN SELECT * FROM t1 JOIN t2 AS X ON t1.x = X.y LEFT JOIN t3 ON X.x = t3.y;
37 EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.x = t2.y LEFT JOIN t3 AS X ON t2.x = X.y;
38 --enable_warnings
39 SET DEBUG = '-d,secondary_engine_mock_change_join_order';
40 SET DEBUG = '+d,secondary_engine_mock_compare_cost_error';
41 # The injected error causes fallback to the primary engine.
42 EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.x = t2.y LEFT JOIN t3 ON t2.x = t3.y;
43 SET DEBUG = '-d,secondary_engine_mock_compare_cost_error';
44
45 let $query =
46 SELECT * FROM t1 a, t2 b, t1 c, t2 d
47 WHERE a.x = b.x AND b.x = c.y AND a.x >= d.y;
48 let $count_plans =
49 SELECT JSON_LENGTH(
50 TRACE->'\$**.considered_execution_plans**.secondary_engine_cost')
51 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
52 SET optimizer_trace = 'enabled=on';
53 SET use_secondary_engine = 'FORCED';
54 # Expect multiple plans to be evaluated.
55 --eval $query
56 --eval $count_plans
57 # Expect a single plan to be evaluated when this debug flag is set.
58 SET DEBUG = '+d,secondary_engine_mock_choose_first_plan';
59 --eval $query
60 --eval $count_plans
61 SET DEBUG = '-d,secondary_engine_mock_choose_first_plan';
62 SET use_secondary_engine = DEFAULT;
63 SET optimizer_trace = 'enabled=off';
64
65 DROP TABLE t1, t2, t3;
66
67 --disable_query_log
68 UNINSTALL PLUGIN mock;
69 --enable_query_log