Debian Codegraph @

mysql-test/suite/secondary_engine/inc/transforms.inc

raw
1 --source include/elide_costs.inc
2
3 --disable_query_log
4 eval INSTALL PLUGIN mock SONAME '$MOCK_PLUGIN';
5 --enable_query_log
6
7 CREATE TABLE t1(a DATETIME NOT NULL);
8 INSERT INTO t1 VALUES (TIMESTAMP'2006-06-06 15:55:55');
9 ALTER TABLE t1 SECONDARY_ENGINE MOCK;
10 ALTER TABLE t1 SECONDARY_LOAD;
11 EXPLAIN SELECT max(a) from t1;
12
13 --echo # Check how the two transforms {scalar,table}-subquery-to-derived
14 --echo # behave in PREPARE-<change conditions>-EXECUTE--<change conditions>-EXECUTE
15 --echo
16 --echo # Transform is not active for InnoDB while we the do initial PREPARE
17 PREPARE s FROM "EXPLAIN SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a=TIMESTAMP'2006-06-06 15:55:55')";
18 PREPARE s2 FROM "EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t1 AS t2)";
19 SET secondary_engine_cost_threshold = 0;
20 EXECUTE s;
21 --echo
22 EXECUTE s2;
23 SET optimizer_switch="subquery_to_derived=on";
24 EXECUTE s;
25 --echo
26 EXECUTE s2;
27 SET optimizer_switch="subquery_to_derived=off";
28 EXECUTE s;
29 --echo
30 EXECUTE s2;
31 # The previous EXECUTE used MOCK and transformed the subquery to a derived
32 # table; the next EXECUTE starts with InnoDB, and chooses InnoDB because
33 # the cost is under the threshold; but, as the transformation was specifically
34 # done for MOCK (by the previous EXECUTE), and isn't required for InnoDB
35 # (subquery_to_derived is off), we have to reprepare:
36 SET secondary_engine_cost_threshold = 10000;
37 --error 0, ER_HYPERGRAPH_NOT_SUPPORTED_YET
38 EXECUTE s;
39 --echo
40 --error 0, ER_HYPERGRAPH_NOT_SUPPORTED_YET
41 EXECUTE s2;
42 # The previous EXECUTE used InnoDB and no transformation. The next EXECUTE
43 # starts with InnoDB, and does no transformation, even though one is requested
44 # by changing optimizer_switch. It is questionable behaviour, but not a real
45 # problem.
46 SET optimizer_switch="subquery_to_derived=on";
47 --error 0, ER_HYPERGRAPH_NOT_SUPPORTED_YET
48 EXECUTE s;
49 --echo
50 --error 0, ER_HYPERGRAPH_NOT_SUPPORTED_YET
51 EXECUTE s2;
52 SET optimizer_switch="subquery_to_derived=off";
53 --error 0, ER_HYPERGRAPH_NOT_SUPPORTED_YET
54 EXECUTE s;
55 --echo
56 --error 0, ER_HYPERGRAPH_NOT_SUPPORTED_YET
57 EXECUTE s2;
58 SET secondary_engine_cost_threshold = 0;
59 EXECUTE s;
60 --echo
61 EXECUTE s2;
62 SET optimizer_switch="subquery_to_derived=on";
63 EXECUTE s;
64 --echo
65 EXECUTE s2;
66 SET optimizer_switch="subquery_to_derived=off";
67 EXECUTE s;
68 --echo
69 EXECUTE s2;
70 SET optimizer_switch="subquery_to_derived=on";
71 --echo # Let transform be active for InnoDB while we the initial PREPARE
72 PREPARE s FROM "EXPLAIN SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a=TIMESTAMP'2006-06-06 15:55:55')";
73 EXECUTE s;
74 --echo
75 EXECUTE s2;
76 SET secondary_engine_cost_threshold = 0;
77 EXECUTE s;
78 --echo
79 EXECUTE s2;
80 SET optimizer_switch="subquery_to_derived=on";
81 EXECUTE s;
82 --echo
83 EXECUTE s2;
84 SET optimizer_switch="subquery_to_derived=off";
85 EXECUTE s;
86 --echo
87 EXECUTE s2;
88 SET secondary_engine_cost_threshold = 10000;
89 --error 0, ER_HYPERGRAPH_NOT_SUPPORTED_YET
90 EXECUTE s;
91 --echo
92 --error 0, ER_HYPERGRAPH_NOT_SUPPORTED_YET
93 EXECUTE s2;
94 SET optimizer_switch="subquery_to_derived=on";
95 --error 0, ER_HYPERGRAPH_NOT_SUPPORTED_YET
96 EXECUTE s;
97 --echo
98 --error 0, ER_HYPERGRAPH_NOT_SUPPORTED_YET
99 EXECUTE s2;
100 SET optimizer_switch="subquery_to_derived=off";
101 --error 0, ER_HYPERGRAPH_NOT_SUPPORTED_YET
102 EXECUTE s;
103 --echo
104 --error 0, ER_HYPERGRAPH_NOT_SUPPORTED_YET
105 EXECUTE s2;
106 SET secondary_engine_cost_threshold = 0;
107 EXECUTE s;
108 --echo
109 EXECUTE s2;
110 SET optimizer_switch="subquery_to_derived=on";
111 EXECUTE s;
112 --echo
113 EXECUTE s2;
114 SET optimizer_switch="subquery_to_derived=off";
115 EXECUTE s;
116 --echo
117 EXECUTE s2;
118
119 DROP PREPARE s;
120 DROP PREPARE s2;
121 DROP TABLE t1;
122 SET secondary_engine_cost_threshold = default;
123 SET optimizer_switch="subquery_to_derived=default";
124
125 --echo #
126 --echo # Bug#34445328 (Hypergraph) Optimizer degenerate join
127 --echo # conditions when encountering semi join + outer join
128 --echo #
129
130 CREATE TABLE t (a INT) SECONDARY_ENGINE MOCK;
131 INSERT INTO t VALUES (), (), ();
132 ALTER TABLE t SECONDARY_LOAD;
133 ANALYZE TABLE t;
134
135 # The subquery_to_derived transformation attaches a degenerate join
136 # condition (t3.a=t4.a) to the left outer join. Since HeatWave does
137 # not accept left outer join conditions that reference only the
138 # right/inner side of the join, we create a filter just below the join
139 # for this case when a secondary engine is used (pushing such a
140 # condition down is perfectly valid). Note that currently, due to
141 # bug#34444550, the condition is dropped altogether with the old
142 # optimizer. With the hypergraph optimizer, expect no join conditions
143 # in the outer join, and instead have a filter with the t3.a=t4.a
144 # condition.
145 --replace_regex $elide_costs
146 EXPLAIN FORMAT=TREE
147 SELECT 1
148 FROM t AS t1 LEFT JOIN t AS t2
149 ON 1 IN (
150 SELECT 1 FROM t AS t3 WHERE a <=>
151 (SELECT a FROM t AS t4)
152 );
153
154 DROP TABLE t;
155
156 --echo #
157 --echo # Bug#34534373: Heatwave offload issue - Sees inner tables
158 --echo # of a semijoin when it should not
159 --echo #
160
161 CREATE TABLE t1(f1 INTEGER, f2 INTEGER) SECONDARY_ENGINE MOCK;
162 ALTER TABLE t1 SECONDARY_LOAD;
163 ANALYZE TABLE t1;
164 set optimizer_switch="subquery_to_derived=on";
165 --replace_regex $elide_costs
166 # For the condition that is placed on top of the left join,
167 # hypergraph should not find field replacements from table
168 # "t3" through multiple equalities. This table is not visible
169 # outside of the semijoin.
170 EXPLAIN FORMAT=TREE
171 SELECT 1
172 FROM (t1 JOIN t1 AS t2
173 ON t2.f1 = t1.f2 AND t1.f1 IN (SELECT f1 FROM t1 AS t3))
174 WHERE (SELECT SUM(f2) FROM t1 AS t4) IS NULL
175 AND t1.f1 = t2.f1 OR t2.f2 <= 0;
176 DROP TABLE t1;
177 set optimizer_switch="subquery_to_derived=off";
178
179 --echo #
180 --echo # Bug#34828328: Assertion `right != nullptr' failed in
181 --echo # make_join_hypergraph.cc
182
183 CREATE TABLE t1(f1 INTEGER, f2 INTEGER) SECONDARY_ENGINE MOCK;
184 ALTER TABLE t1 SECONDARY_LOAD;
185 ANALYZE TABLE t1;
186
187 set optimizer_switch="subquery_to_derived=on";
188 # The following query gets offloaded with the old optimizer,
189 # however it does not with hypergraph. The reason is that,
190 # the old optimizer is able to induce LIMIT clause to eliminate
191 # duplicates for the semijoin. Hypergraph however rejects the
192 # plan because of the semijoin condition between inner side of
193 # the semijoin and the outer side of the outer join making
194 # it not possible to create a hash joinable plan.
195 # This could possibly be addressed as part of solution to
196 # Bug#34448532.
197 let $query=
198 SELECT 1
199 FROM t1 JOIN t1 AS t2 ON (t2.f1 = t1.f1)
200 LEFT JOIN t1 AS t3 ON (t2.f2 = t1.f2 AND t2.f2 IN (SELECT f1 FROM t1 AS t4));
201
202 --replace_regex $elide_costs
203 eval EXPLAIN FORMAT=tree $query;
204 eval $query;
205
206 DROP TABLE t1;
207 set optimizer_switch="subquery_to_derived=off";
208
209 --disable_query_log
210 UNINSTALL PLUGIN mock;
211 --enable_query_log