Debian Codegraph @

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

raw
1 --echo #
2 --echo # Test that query preparation is able to make plans for executing
3 --echo # a query on a secondary storage engine.
4 --echo #
5 --source include/elide_costs.inc
6
7 --disable_query_log
8 eval INSTALL PLUGIN mock SONAME '$MOCK_PLUGIN';
9 --enable_query_log
10
11 CREATE TABLE t1(id INT PRIMARY KEY, x INT, y INT);
12 INSERT INTO t1 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
13 CREATE INDEX t1_x ON t1(x);
14 --sorted_result
15 SELECT * FROM t1;
16
17 CREATE TABLE t2(id INT PRIMARY KEY, z INT);
18 INSERT INTO t2 VALUES (1, 2), (3, 4), (5, 6);
19
20 CREATE VIEW v1 AS SELECT t1.id, t1.x, t2.z FROM t1, t2 WHERE t1.id = t2.id;
21 SELECT * FROM v1;
22
23 CREATE TABLE t_primary_only(id INT PRIMARY KEY, x INT);
24 INSERT INTO t_primary_only VALUES (1, 1), (2, 2);
25
26 ALTER TABLE t1 SECONDARY_ENGINE MOCK;
27 ALTER TABLE t2 SECONDARY_ENGINE MOCK;
28
29 # t1 has not been loaded into the secondary engine yet.
30 FLUSH STATUS;
31 SELECT * FROM t1;
32 PREPARE ps FROM 'SELECT * FROM t1';
33 EXECUTE ps;
34 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
35 DROP PREPARE ps;
36
37 # Load and unload t1.
38 FLUSH STATUS;
39 ALTER TABLE t1 SECONDARY_LOAD;
40 PREPARE ps FROM 'SELECT * FROM t1';
41 EXECUTE ps;
42 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
43 ALTER TABLE t1 SECONDARY_UNLOAD;
44 EXECUTE ps;
45 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
46 EXECUTE ps;
47 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
48 DROP PREPARE ps;
49
50 ALTER TABLE t1 SECONDARY_LOAD;
51 ALTER TABLE t2 SECONDARY_LOAD;
52
53 # The queries should now use the mock engine, which always returns an
54 # empty result.
55 FLUSH STATUS;
56 SELECT * FROM t1;
57 PREPARE ps FROM 'SELECT * FROM t1';
58 EXECUTE ps;
59 EXECUTE ps;
60 FLUSH TABLES;
61 SELECT * FROM t1;
62 FLUSH TABLES;
63 EXECUTE ps;
64 DROP PREPARE ps;
65 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
66
67 # The row count estimate should be available (via the primary table).
68 ANALYZE TABLE t1;
69 --replace_regex $elide_costs
70 EXPLAIN FORMAT=TREE SELECT * FROM t1;
71
72 # Some queries that should use the secondary engine.
73 FLUSH STATUS;
74 SELECT * FROM t1 JOIN t2 USING (id);
75 SELECT * FROM t1 WHERE t1.x IN (SELECT t2.z FROM t2);
76 SELECT SUM(x) FROM t1 GROUP BY y;
77 SELECT (SELECT MAX(x) FROM t1), (SELECT MIN(z) FROM t2), s FROM
78 (SELECT SUM(z) AS s FROM t2) AS dt;
79 SELECT * FROM v1;
80 SELECT * FROM v1, t2 WHERE v1.id = t2.id;
81 SELECT SUM(x) FROM v1;
82 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
83
84 # A forced index scan can be offloaded, since it's just a hint.
85 FLUSH STATUS;
86 SELECT x FROM t1 FORCE INDEX (t1_x);
87 PREPARE ps FROM 'SELECT x FROM t1 FORCE INDEX (t1_x)';
88 EXECUTE ps;
89 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
90 SET @my_use_secondary_engine = @@use_secondary_engine;
91 SET @@use_secondary_engine = FORCED;
92 EXECUTE ps;
93 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
94 SET @@use_secondary_engine = @my_use_secondary_engine;
95
96 # The query should not be offloaded to the secondary engine if LOCK
97 # TABLES mode is active.
98 FLUSH STATUS;
99 PREPARE ps FROM 'SELECT * FROM t1';
100 LOCK TABLES t1 READ;
101 SELECT * FROM t1;
102 EXECUTE ps;
103 EXECUTE ps;
104 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
105 UNLOCK TABLES;
106 SELECT * FROM t1;
107 EXECUTE ps;
108 EXECUTE ps;
109 DROP PREPARE ps;
110 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
111
112 # The query should not be offloaded to the secondary engine in
113 # multi-statement transaction mode.
114 FLUSH STATUS;
115 PREPARE ps FROM 'SELECT * FROM t1';
116 BEGIN;
117 SELECT * FROM t1;
118 EXECUTE ps;
119 COMMIT;
120 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
121 EXECUTE ps;
122 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
123 FLUSH STATUS;
124 START TRANSACTION;
125 SELECT * FROM t1;
126 EXECUTE ps;
127 COMMIT;
128 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
129 EXECUTE ps;
130 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
131 FLUSH STATUS;
132 SET @@autocommit = 0;
133 SELECT * FROM t1;
134 EXECUTE ps;
135 SET @@autocommit = 1;
136 COMMIT;
137 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
138 SELECT * FROM t1;
139 EXECUTE ps;
140 DROP PREPARE ps;
141 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
142 FLUSH STATUS;
143 BEGIN;
144 PREPARE ps FROM 'SELECT * FROM t1';
145 EXECUTE ps;
146 COMMIT;
147 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
148 EXECUTE ps;
149 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
150 DROP PREPARE ps;
151
152 # Queries that access at least one table that is not available in a
153 # secondary engine, should not be offloaded.
154 SELECT * FROM t1, t_primary_only tp WHERE t1.id = tp.id;
155 PREPARE ps FROM 'SELECT * FROM t1, t_primary_only tp WHERE t1.id = tp.id';
156 EXECUTE ps;
157 EXECUTE ps;
158 DROP PREPARE ps;
159 SELECT * FROM t1, t2, t_primary_only tp WHERE t1.id = t2.id AND t2.id = tp.id;
160
161 # Queries that access schema tables should not be offloaded.
162 FLUSH STATUS;
163 --disable_result_log
164 SELECT * FROM t1, INFORMATION_SCHEMA.COLUMN_PRIVILEGES t2;
165 --enable_result_log
166 SHOW STATUS LIKE 'Secondary_engine_execution_count';
167
168 # It should be possible to have tables with an auto-increment column
169 # in a secondary engine.
170 CREATE TABLE t_with_autoinc(id INT PRIMARY KEY AUTO_INCREMENT, x INT)
171 SECONDARY_ENGINE MOCK;
172 INSERT INTO t_with_autoinc(x) VALUES (4), (5), (6);
173 ALTER TABLE t_with_autoinc SECONDARY_LOAD;
174 SELECT * FROM t_with_autoinc;
175 DROP TABLE t_with_autoinc;
176
177 # Tables that are partitioned in the primary engine can also have
178 # secondary tables.
179 CREATE TABLE t_partitioned(id INT PRIMARY KEY, x INT)
180 SECONDARY_ENGINE MOCK
181 PARTITION BY HASH(id);
182 INSERT INTO t_partitioned VALUES (1, 2), (2, 3), (3, 4), (4, 5);
183 ALTER TABLE t_partitioned SECONDARY_LOAD;
184 SELECT * FROM t_partitioned;
185 DROP TABLE t_partitioned;
186
187 # Multiple queries in a single statement.
188 DELIMITER |;
189 FLUSH STATUS;
190 SELECT * FROM t1;
191 PREPARE ps FROM 'SELECT * FROM t1';
192 EXECUTE ps;
193 SELECT * FROM t1, t_primary_only tp WHERE t1.id = tp.id;
194 EXECUTE ps;
195 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
196 ALTER TABLE t1 SECONDARY_UNLOAD;
197 SELECT * FROM t1;
198 EXECUTE ps;
199 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
200 ALTER TABLE t1 SECONDARY_LOAD;
201 EXECUTE ps;
202 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
203 DROP PREPARE ps|
204 DELIMITER ;|
205
206 # Prepared statement with an argument.
207 FLUSH STATUS;
208 PREPARE ps FROM 'SELECT x FROM t1 WHERE id = ?';
209 SET @a=1;
210 EXECUTE ps USING @a;
211 EXECUTE ps USING @a;
212 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
213 ALTER TABLE t1 SECONDARY_UNLOAD;
214 EXECUTE ps USING @a;
215 EXECUTE ps USING @a;
216 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
217 ALTER TABLE t1 SECONDARY_LOAD;
218 EXECUTE ps USING @a;
219 EXECUTE ps USING @a;
220 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
221 DROP PREPARE ps;
222
223 # Test a query that returns multiple rows, even with the mock engine.
224 FLUSH STATUS;
225 --sorted_result
226 SELECT JSON_EXTRACT(j, '$') FROM
227 (SELECT '[]' AS j FROM DUAL UNION ALL SELECT '{}' FROM DUAL) AS dt
228 LEFT JOIN t1 ON (TRUE);
229 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
230
231 # If an offloaded query fails during execution, after optimization, it
232 # is not automatically reprepared.
233 FLUSH STATUS;
234 --error ER_INVALID_JSON_TEXT_IN_PARAM
235 SELECT JSON_EXTRACT(j, '$') FROM
236 (SELECT '[]' AS j FROM DUAL UNION ALL SELECT 'not-json' FROM DUAL) AS dt
237 LEFT JOIN t1 ON (TRUE);
238 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
239 FLUSH STATUS;
240 PREPARE ps FROM "SELECT JSON_EXTRACT(j, '$') FROM
241 (SELECT '[]' AS j FROM DUAL UNION ALL SELECT 'not-json' FROM DUAL) AS dt
242 LEFT JOIN t1 ON (TRUE)";
243 --error ER_INVALID_JSON_TEXT_IN_PARAM
244 EXECUTE ps;
245 --error ER_INVALID_JSON_TEXT_IN_PARAM
246 EXECUTE ps;
247 DROP PREPARE ps;
248 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
249
250 # Queries inside a procedure should not be offloaded.
251 FLUSH STATUS;
252 CREATE PROCEDURE p1() SELECT COUNT(*) FROM t1;
253 CALL p1();
254 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
255 PREPARE ps1 FROM 'SELECT * FROM t1';
256 EXECUTE ps1;
257 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
258 CREATE PROCEDURE p2() EXECUTE ps1;
259 CREATE PROCEDURE p3() PREPARE ps2 FROM 'SELECT * FROM t1';
260 FLUSH STATUS;
261 CALL p2();
262 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
263 CALL p3();
264 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
265 EXECUTE ps1;
266 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
267 EXECUTE ps2;
268 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
269 DROP PREPARE ps1;
270 DROP PREPARE ps2;
271 DROP PROCEDURE p1;
272 DROP PROCEDURE p2;
273 DROP PROCEDURE p3;
274
275 # Queries inside a trigger should not be offloaded.
276 FLUSH STATUS;
277 CREATE TABLE t_with_trigger(x INT);
278 CREATE TRIGGER tr AFTER INSERT ON t_with_trigger
279 FOR EACH ROW SELECT COUNT(*) FROM t1 INTO @a;
280 INSERT INTO t_with_trigger VALUES (1);
281 SELECT @a;
282 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
283 DROP TABLE t_with_trigger;
284
285 # Non-existent secondary engine.
286 CREATE TABLE t_nonexistent(x INT) SECONDARY_ENGINE NONEXISTENT;
287 INSERT INTO t_nonexistent VALUES (1);
288 FLUSH STATUS;
289 SELECT * FROM t_nonexistent;
290 SELECT * FROM t1 a, t_nonexistent b WHERE a.id = b.x;
291 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
292 DROP TABLE t_nonexistent;
293
294 # Same secondary engine specified with different case and differing
295 # number of trailing blanks.
296 CREATE TABLE t_case1(x INT) SECONDARY_ENGINE 'MOCK';
297 CREATE TABLE t_case2(y INT) SECONDARY_ENGINE 'mock ';
298 CREATE TABLE t_case3(z INT) SECONDARY_ENGINE 'MoCk ';
299 INSERT INTO t_case1 VALUES (1);
300 INSERT INTO t_case2 VALUES (2);
301 INSERT INTO t_case3 VALUES (3);
302 ALTER TABLE t_case1 SECONDARY_LOAD;
303 ALTER TABLE t_case2 SECONDARY_LOAD;
304 ALTER TABLE t_case3 SECONDARY_LOAD;
305 FLUSH STATUS;
306 SELECT * FROM t_case1, t_case2, t_case3;
307 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
308 DROP TABLE t_case1, t_case2, t_case3;
309
310 # Queries that invoke stored routines should not be offloaded.
311 CREATE TABLE tf(x INT) SECONDARY_ENGINE MOCK;
312 INSERT INTO tf VALUES (123);
313 ALTER TABLE tf SECONDARY_LOAD;
314 DELIMITER |;
315 CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
316 BEGIN
317 DECLARE i INT;
318 SELECT x INTO i FROM tf;
319 RETURN i;
320 END|
321 CREATE FUNCTION f2() RETURNS INT DETERMINISTIC
322 BEGIN
323 INSERT INTO tf VALUES (234);
324 RETURN 1;
325 END|
326 DELIMITER ;|
327 FLUSH STATUS;
328 SELECT f1();
329 SELECT f2();
330 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
331 --sorted_result
332 SELECT * FROM tf;
333 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
334 DROP FUNCTION f1;
335 DROP FUNCTION f2;
336 DROP TABLE tf;
337
338 # Drop a table between two executions.
339 CREATE TABLE t_dropped (x INT) SECONDARY_ENGINE MOCK;
340 ALTER TABLE t_dropped SECONDARY_LOAD;
341 PREPARE ps FROM 'SELECT * FROM t_dropped';
342 EXECUTE ps;
343 DROP TABLE t_dropped;
344 --error ER_NO_SUCH_TABLE
345 EXECUTE ps;
346 DROP PREPARE ps;
347
348 # Cleanup.
349 DROP VIEW v1;
350 DROP TABLE t1, t2, t_primary_only;
351
352 --echo #
353 --echo # Rudimentary tests for use_secondary_engine system variable.
354 --echo #
355 SET @my_use_secondary_engine = @@use_secondary_engine;
356 CREATE TABLE t1 (a INT) SECONDARY_ENGINE MOCK;
357 INSERT INTO t1 VALUES (1);
358 ALTER TABLE t1 SECONDARY_LOAD;
359 CREATE TABLE t2 (a INT);
360 INSERT INTO t2 VALUES (1);
361 FLUSH STATUS;
362 --echo # The row shall be returned from the primary storage engine.
363 SET @@use_secondary_engine = OFF;
364 SELECT * FROM t1;
365 INSERT INTO t1 SELECT * FROM t2;
366 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
367 --echo # The mock storage engine does not return any rows.
368 SET @@use_secondary_engine = FORCED;
369 SELECT * FROM t1;
370 -- error ER_SECONDARY_ENGINE
371 INSERT INTO t1 SELECT * FROM t2;
372 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
373 SET @@use_secondary_engine = ON;
374 SELECT * FROM t1;
375 INSERT INTO t1 SELECT * FROM t2;
376 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
377 --echo # An error shall be returned if table is not loaded.
378 ALTER TABLE t1 SECONDARY_UNLOAD;
379 SET @@use_secondary_engine = FORCED;
380 --error ER_SECONDARY_ENGINE,ER_SECONDARY_ENGINE_PLUGIN
381 SELECT * FROM t1;
382 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
383 SET @@use_secondary_engine = ON;
384 SELECT * FROM t1;
385 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
386 DROP TABLE t1;
387 DROP TABLE t2;
388 SET @@use_secondary_engine = @my_use_secondary_engine;
389
390 --echo #
391 --echo # Queries not referencing any base tables shall be executed without
392 --echo # without even if use_secondary_engine is FORCED.
393 --echo #
394 SET @my_use_secondary_engine = @@use_secondary_engine;
395 SET @@use_secondary_engine = FORCED;
396 CREATE VIEW v as SELECT 1;
397 SELECT * FROM v;
398 SELECT 1;
399 SELECT COUNT(*) FROM (SELECT 1) AS dt;
400 DROP VIEW v;
401 SET @@use_secondary_engine = @my_use_secondary_engine;
402
403 --echo #
404 --echo # Attempt to force query on secondary engine even if its referenced
405 --echo # table has no secondary engine defined.
406 --echo #
407 SET @my_use_secondary_engine = @@use_secondary_engine;
408 SET @@use_secondary_engine = FORCED;
409 CREATE TABLE t1 (a INT);
410 INSERT INTO t1 VALUES (1);
411 FLUSH STATUS;
412 --error ER_SECONDARY_ENGINE
413 SELECT * FROM t1;
414 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
415 SET @@use_secondary_engine = ON;
416 SELECT * FROM t1;
417 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
418 DROP TABLE t1;
419 SET @@use_secondary_engine = @my_use_secondary_engine;
420
421 --echo #
422 --echo # Attempt to force query on secondary engine if one of its referenced
423 --echo # table has a secondary engine while another table has none.
424 --echo #
425 SET @my_use_secondary_engine = @@use_secondary_engine;
426 SET @@use_secondary_engine = FORCED;
427 CREATE TABLE t1 (a INT) SECONDARY_ENGINE MOCK;
428 CREATE TABLE t2 (a INT);
429 INSERT INTO t1 VALUES (1);
430 INSERT INTO t2 VALUES (2);
431 ALTER TABLE t1 SECONDARY_LOAD;
432 FLUSH STATUS;
433 SELECT * FROM t1;
434 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
435 --error ER_SECONDARY_ENGINE
436 SELECT * FROM t2;
437 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
438 --error ER_SECONDARY_ENGINE
439 SELECT * FROM t1,t2;
440 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
441 ALTER TABLE t1 SECONDARY_UNLOAD;
442 --error ER_SECONDARY_ENGINE
443 SELECT * FROM t1,t2;
444 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
445 DROP TABLE t1,t2;
446 SET @@use_secondary_engine = @my_use_secondary_engine;
447
448 --echo #
449 --echo # Attempt to force query referencing temporary table on secondary engine.
450 --echo #
451 SET @my_use_secondary_engine = @@use_secondary_engine;
452 SET @@use_secondary_engine = FORCED;
453 CREATE TEMPORARY TABLE t1 (a INT);
454 FLUSH STATUS;
455 --error ER_SECONDARY_ENGINE
456 SELECT * FROM t1;
457 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
458 DROP TABLE t1;
459 SET @@use_secondary_engine = @my_use_secondary_engine;
460
461 --echo #
462 --echo # Change use_secondary_engine across execution of prepared statements.
463 --echo #
464 SET @my_use_secondary_engine = @@use_secondary_engine;
465 SET @@use_secondary_engine = FORCED;
466 CREATE TABLE t1 (a INT) SECONDARY_ENGINE MOCK;
467 FLUSH STATUS;
468 --error ER_SECONDARY_ENGINE_PLUGIN
469 PREPARE ps FROM "SELECT * FROM t1";
470 SET @@use_secondary_engine = OFF;
471 PREPARE ps FROM "SELECT * FROM t1";
472 EXECUTE ps;
473 --echo # Load table into secondary engine.
474 ALTER TABLE t1 SECONDARY_LOAD;
475 EXECUTE ps;
476 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
477 SET @@use_secondary_engine = ON;
478 EXECUTE ps;
479 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
480 SET @@use_secondary_engine = FORCED;
481 EXECUTE ps;
482 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
483 --echo # Unload table from secondary engine.
484 ALTER TABLE t1 SECONDARY_UNLOAD;
485 SET @@use_secondary_engine = ON;
486 EXECUTE ps;
487 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
488 SET @@use_secondary_engine = FORCED;
489 --error ER_SECONDARY_ENGINE
490 EXECUTE ps;
491 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
492 DROP PREPARE ps;
493 DROP TABLE t1;
494 SET @@use_secondary_engine = @my_use_secondary_engine;
495
496 --echo #
497 --echo # Set use_secondary_engine as a hint.
498 --echo #
499 SET @my_use_secondary_engine = @@use_secondary_engine;
500 SET @@use_secondary_engine = OFF;
501 CREATE TABLE t1 (a INT) SECONDARY_ENGINE MOCK;
502 INSERT INTO t1 VALUES (1);
503 FLUSH STATUS;
504 SELECT * FROM t1;
505 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
506 --error ER_SECONDARY_ENGINE,ER_SECONDARY_ENGINE_PLUGIN
507 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM t1;
508 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
509 ALTER TABLE t1 SECONDARY_LOAD;
510 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM t1;
511 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
512 SELECT /*+ SET_VAR(use_secondary_engine = ON) */ * FROM t1;
513 SET @@use_secondary_engine = @my_use_secondary_engine;
514 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
515 SELECT /*+ SET_VAR(use_secondary_engine = OFF) */ * FROM t1;
516 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
517 DROP TABLE t1;
518 SET @@use_secondary_engine = @my_use_secondary_engine;
519
520 --echo #
521 --echo # Bug#28311801: THE GLOBAL VALUE OF SECONDARY_ENGINE_EXECUTION_COUNT
522 --echo # IS ALWAYS ZERO
523 --echo #
524 CREATE TABLE t (x INT) SECONDARY_ENGINE MOCK;
525 ALTER TABLE t SECONDARY_LOAD;
526 let $count_before = query_get_value(
527 SHOW GLOBAL STATUS LIKE 'Secondary_engine_execution_count', Value, 1);
528 SELECT * FROM t;
529 FLUSH STATUS;
530 let $count_after = query_get_value(
531 SHOW GLOBAL STATUS LIKE 'Secondary_engine_execution_count', Value, 1);
532 expr $execution_count = $count_after - $count_before;
533 eval SELECT $execution_count;
534 DROP TABLE t;
535
536 --echo #
537 --echo # Load only a subset of columns into secondary engine.
538 --echo #
539 SET @my_use_secondary_engine = @@use_secondary_engine;
540 CREATE TABLE t1 (
541 pk INT PRIMARY KEY AUTO_INCREMENT,
542 a INT,
543 b INT NOT SECONDARY,
544 c INT,
545 d INT NOT SECONDARY
546 ) SECONDARY_ENGINE MOCK;
547
548 CREATE TABLE t2 (
549 pk INT PRIMARY KEY AUTO_INCREMENT,
550 e INT,
551 f INT NOT SECONDARY,
552 g INT,
553 h INT NOT SECONDARY
554 ) SECONDARY_ENGINE MOCK;
555
556 INSERT INTO t1 (a, b, c, d) VALUES (1,1,1,1), (1,1,1,1), (2,2,2,2);
557 INSERT INTO t2 (e, f, g, h) VALUES (2,2,2,2), (2,2,2,2), (3,3,3,3);
558
559 ALTER TABLE t1 SECONDARY_LOAD;
560 ALTER TABLE t2 SECONDARY_LOAD;
561
562 --echo # Statements which should be sent to primary engine since not all columns
563 --echo # are in secondary engine.
564 CREATE VIEW v1 as SELECT * FROM t1;
565 CREATE VIEW v2 as SELECT t1.a, t2.f FROM t1, t2;
566 SELECT * FROM t1;
567 --error ER_SECONDARY_ENGINE
568 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM t1;
569 SELECT 1 FROM t1 WHERE b=1;
570 SELECT 1 FROM t1 GROUP BY b;
571 SELECT 1 FROM t1 ORDER BY b;
572 --error ER_SECONDARY_ENGINE
573 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ 1 FROM t1 WHERE b=1;
574 --error ER_SECONDARY_ENGINE
575 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ 1 FROM t1 GROUP BY b;
576 --error ER_SECONDARY_ENGINE
577 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ 1 FROM t1 ORDER BY b;
578 SELECT * FROM v1;
579 SELECT SUM(b) FROM v1;
580 SELECT 1 FROM v1 WHERE b=1;
581 SELECT 1 FROM v1 GROUP BY b;
582 SELECT 1 FROM v1 ORDER BY b;
583 --error ER_SECONDARY_ENGINE
584 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM v1;
585 --error ER_SECONDARY_ENGINE
586 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ SUM(b) FROM v1;
587 --error ER_SECONDARY_ENGINE
588 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ 1 FROM v1 WHERE b=1;
589 --error ER_SECONDARY_ENGINE
590 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ 1 FROM v1 GROUP BY b;
591 --error ER_SECONDARY_ENGINE
592 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ 1 FROM v1 ORDER BY b;
593 --sorted_result
594 SELECT t1.a, t2.f FROM t1, t2;
595 --error ER_SECONDARY_ENGINE
596 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ t1.a, t2.f FROM t1, t2;
597 --sorted_result
598 SELECT * FROM v2;
599 --error ER_SECONDARY_ENGINE
600 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM v2;
601
602 --echo # Statements which should be sent to the secondary engine since all
603 --echo # columns are in secondary engine.
604 SET @@use_secondary_engine = FORCED;
605 CREATE VIEW v3 as SELECT a, c FROM t1;
606 CREATE VIEW v4 as SELECT t1.a, t1.c, t2.e, t2.g FROM t1, t2;
607 SELECT a, c FROM t1;
608 SELECT 1 FROM t1 WHERE a=1;
609 SELECT * FROM v3;
610 SELECT t1.a, t1.c, t2.e, t2.g FROM t1, t2;
611 SELECT * FROM v4;
612
613 DROP VIEW v1, v2, v3, v4;
614 DROP TABLE t1, t2;
615 SET @@use_secondary_engine = @my_use_secondary_engine;
616
617 --echo #
618 --echo # NOT SECONDARY on partitioned table
619 --echo #
620 CREATE TABLE t1 (a INT PRIMARY KEY NOT SECONDARY, b INT DEFAULT 10)
621 PARTITION BY RANGE (a) (
622 PARTITION p0 VALUES LESS THAN (1),
623 PARTITION p1 VALUES LESS THAN (2),
624 PARTITION p2 VALUES LESS THAN (3),
625 PARTITION p3 VALUES LESS THAN (4),
626 PARTITION p4 VALUES LESS THAN (5),
627 PARTITION p5 VALUES LESS THAN (6),
628 PARTITION max VALUES LESS THAN MAXVALUE);
629 INSERT INTO t1(a) VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);
630 ALTER TABLE t1 SECONDARY_ENGINE=MOCK;
631 ALTER TABLE t1 SECONDARY_LOAD;
632 ANALYZE TABLE t1;
633 FLUSH STATUS;
634 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
635 --error ER_SECONDARY_ENGINE
636 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ a FROM t1;
637 SELECT a FROM t1;
638 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
639 SELECT b FROM t1;
640 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
641 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ b FROM t1;
642 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
643 DROP TABLE t1;
644
645 --echo #
646 --echo # Use NOT SECONDARY as comment.
647 --echo #
648 CREATE TABLE t1 (a INT /* NOT SECONDARY */, b INT ) SECONDARY_ENGINE MOCK;
649 INSERT INTO t1 VALUES (1,1);
650 ALTER TABLE t1 SECONDARY_LOAD;
651 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ a FROM t1;
652 FLUSH STATUS;
653 SELECT a FROM t1;
654 SELECT * FROM t1;
655 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
656 DROP TABLE t1;
657
658 --echo #
659 --echo # Use NOT SECONDARY with other constraint on column.
660 --echo #
661 CREATE TABLE t1 (a INT, b INT NOT SECONDARY UNIQUE KEY) SECONDARY_ENGINE MOCK;
662 INSERT INTO t1 VALUES (1, 1);
663 ALTER TABLE t1 SECONDARY_LOAD;
664 FLUSH STATUS;
665 --error ER_SECONDARY_ENGINE
666 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM t1;
667 SELECT a FROM t1;
668 --error ER_SECONDARY_ENGINE
669 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ b FROM t1;
670 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
671 DROP TABLE t1;
672
673
674 --echo # Add NON SECONDARY and do not use SECONDARY_ENGINE AT CREATE
675 CREATE TABLE t1 (a INT NOT SECONDARY, b INT);
676 INSERT INTO t1 VALUES (1,1);
677 SELECT a FROM t1;
678 SELECT b FROM t1;
679 SELECT * FROM t1;
680 DROP TABLE t1;
681
682 --echo #
683 --echo # Bug#28738100: SECONDARY ENGINE RETURNS WRONG RESULT WITH IS NULL CLAUSE
684 --echo #
685
686 # Enable special handling of IS NULL for this test case.
687 SET @saved_sql_auto_is_null = @@sql_auto_is_null;
688 SET @@sql_auto_is_null = 1;
689 CREATE TABLE t(a INT PRIMARY KEY AUTO_INCREMENT) SECONDARY_ENGINE MOCK;
690 ALTER TABLE t SECONDARY_LOAD;
691 INSERT INTO t VALUES ();
692 ANALYZE TABLE t;
693 # Verify that a IS NULL is rewritten to a = last_insert_id().
694 # Filter out differences between the old optimizer and the hypergraph optimizer.
695 --replace_regex $elide_costs /.*last_insert_id.*/Found last_insert_id() in the plan./
696 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE a IS NULL;
697 DROP TABLE t;
698 # Restore regular handling of IS NULL.
699 SET @@sql_auto_is_null = @saved_sql_auto_is_null;
700
701 --echo # Bug#29288023: Join condition is substituted with REF access
702
703 CREATE TABLE t1(f1 int, f11 int);
704 CREATE TABLE t2(f2 int, f22 int);
705 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(5,5),(9,9),(7,7);
706 INSERT INTO t2 VALUES(1,1),(3,3),(2,2),(4,4),(8,8),(6,6);
707 ANALYZE TABLE t1, t2;
708 ALTER TABLE t1 SECONDARY_ENGINE=MOCK;
709 ALTER TABLE t1 SECONDARY_LOAD;
710 ALTER TABLE t2 SECONDARY_ENGINE=MOCK;
711 ALTER TABLE t2 SECONDARY_LOAD;
712 ANALYZE TABLE t1, t2;
713 --output $MYSQL_TMP_DIR/explain-bug29288023
714 EXPLAIN FORMAT=tree
715 SELECT *
716 FROM t1 JOIN (SELECT f2, SUM(f22) AS b FROM t2 GROUP BY f2) AS dt
717 ON t1.f1=dt.f2;
718 --let $assert_text=Secondary engine does not support indexes on derived tables
719 --let $assert_file=$MYSQL_TMP_DIR/explain-bug29288023
720 --let $assert_select=<auto_key0>
721 --let $assert_count=0
722 --source include/assert_grep.inc
723 --remove_file $MYSQL_TMP_DIR/explain-bug29288023
724 DROP TABLE t1, t2;
725
726 CREATE TABLE t1(x INT, y INT, z INT, c INT, d INT);
727 INSERT INTO t1 VALUES
728 (1, 5, 2, 2, 3), (2, 2, 5, 2, 3), (3, 5, 5, 2, 3), (4, 2, 5, 2, 3),
729 (2, 3, 4, 2, 3), (7, 6, 5, 2, 3), (8, 9, 5, 2, 3), (9, 6, 5, 2, 3),
730 (10, 7, 5, 2, 3), (11, 6, 5, 2, 3), (12, 7, 5, 2, 3), (13, -1, 5, 2, 3),
731 (14, 7, 5, 2, 3), (15, 8, 5, 2, 3), (16, 4, 5, 2, 3), (17, 7, 5, 2, 3),
732 (18, 7, 5, 2, 3), (19, 17, 5, 2, 3), (20, 17, 5, 2, 3), (21, 5, -1, 2, 3),
733 (22, 5, 5, 2, 3);
734 ALTER TABLE t1 SECONDARY_ENGINE=MOCK;
735 ALTER TABLE t1 SECONDARY_LOAD;
736 ANALYZE TABLE t1;
737 --output $MYSQL_TMP_DIR/explain-bug29288023
738 EXPLAIN FORMAT=TREE
739 SELECT *
740 FROM (SELECT * FROM t1 WHERE x < 7 LIMIT 20) tt1,
741 (SELECT * FROM t1 WHERE x < y LIMIT 20) tt2
742 WHERE tt1.x> 5 and tt1.x=tt2.x;
743 --let $assert_text=Secondary engine does not support indexes on derived tables
744 --let $assert_file=$MYSQL_TMP_DIR/explain-bug29288023
745 --let $assert_select=<auto_key0>
746 --let $assert_count=0
747 --source include/assert_grep.inc
748 --remove_file $MYSQL_TMP_DIR/explain-bug29288023
749
750 DROP TABLE t1;
751
752 --echo #
753 --echo # Bug#29994087: SIG11 AT MATERIALIZEITERATOR::INIT() IN
754 --echo # SQL/COMPOSITE_ITERATORS.CC
755 --echo #
756 CREATE TABLE t (a INT, b INT) SECONDARY_ENGINE MOCK;
757 ALTER TABLE t SECONDARY_LOAD;
758 SELECT 1 FROM t
759 WHERE (1, 2) IN (SELECT 3, 4 UNION SELECT 5, 6) OR b <= 10;
760 DROP TABLE t;
761
762 --echo #
763 --echo # Bug#29615190: SELECTS ON TABLES OFFLOADED TO SECONDARY ENGINE ARE
764 --echo # WRITTEN TWICE TO THE MYSQLD GENERAL LOG
765 --echo #
766
767 CREATE TABLE t(x INT) SECONDARY_ENGINE MOCK;
768 ALTER TABLE t SECONDARY_LOAD;
769
770 SET @saved_general_log = @@global.general_log;
771 SET @saved_log_output = @@global.log_output;
772 SET GLOBAL general_log = 1;
773 SET GLOBAL log_output = 'table';
774
775 TRUNCATE TABLE mysql.general_log;
776 SELECT * FROM t;
777 # Expect the query to be written once to the general log (or twice
778 # under ps protocol - once for preparation and once for execution).
779 --disable_query_log
780 eval SELECT COUNT(*) - $PS_PROTOCOL AS statement_count
781 FROM mysql.general_log
782 WHERE argument = 'SELECT * FROM t';
783 --enable_query_log
784
785 TRUNCATE TABLE mysql.general_log;
786 PREPARE ps FROM 'SELECT * FROM t';
787 SELECT COUNT(*) FROM mysql.general_log WHERE argument = 'SELECT * FROM t';
788 EXECUTE ps;
789 SELECT COUNT(*) FROM mysql.general_log WHERE argument = 'SELECT * FROM t';
790 DROP PREPARE ps;
791
792 TRUNCATE TABLE mysql.general_log;
793 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM t;
794 # Expect the query to be written once to the general log (or twice
795 # under ps protocol - once for preparation and once for execution).
796 --disable_query_log
797 eval SELECT COUNT(*) - $PS_PROTOCOL AS statement_count
798 FROM mysql.general_log
799 WHERE argument LIKE 'SELECT % * FROM t';
800 --enable_query_log
801
802 TRUNCATE TABLE mysql.general_log;
803 PREPARE ps FROM 'SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM t';
804 SELECT COUNT(*) FROM mysql.general_log WHERE argument LIKE 'SELECT % * FROM t';
805 EXECUTE ps;
806 SELECT COUNT(*) FROM mysql.general_log WHERE argument LIKE 'SELECT % * FROM t';
807 DROP PREPARE ps;
808
809 SET GLOBAL general_log = @saved_general_log;
810 SET GLOBAL log_output = @saved_log_output;
811
812 TRUNCATE TABLE mysql.general_log;
813 DROP TABLE t;
814
815 --echo #
816 --echo # Bug#30391501: ASSERTION FAILURE WHEN RUNNING RAPID.AGG_PROJ_ANY
817 --echo # WITH --PS-PROTOCOL
818 --echo #
819 CREATE TABLE t(x INT) SECONDARY_ENGINE MOCK;
820 INSERT INTO t VALUES (1), (2), (3);
821 ALTER TABLE t SECONDARY_LOAD;
822 SELECT (SELECT 9 FROM DUAL) AS y FROM t GROUP BY y;
823 PREPARE ps FROM 'SELECT (SELECT 9 FROM DUAL) AS y FROM t GROUP BY y';
824 EXECUTE ps;
825 DROP PREPARE ps;
826 DROP TABLE t;
827
828 --disable_query_log
829 UNINSTALL PLUGIN mock;
830 --enable_query_log