Debian Codegraph @

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

raw
1 #
2 # Test that query preparation is able to make plans for executing
3 # a query on a secondary storage engine.
4 #
5 CREATE TABLE t1(id INT PRIMARY KEY, x INT, y INT);
6 INSERT INTO t1 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
7 CREATE INDEX t1_x ON t1(x);
8 SELECT * FROM t1;
9 id x y
10 1 2 3
11 4 5 6
12 7 8 9
13 CREATE TABLE t2(id INT PRIMARY KEY, z INT);
14 INSERT INTO t2 VALUES (1, 2), (3, 4), (5, 6);
15 CREATE VIEW v1 AS SELECT t1.id, t1.x, t2.z FROM t1, t2 WHERE t1.id = t2.id;
16 SELECT * FROM v1;
17 id x z
18 1 2 2
19 CREATE TABLE t_primary_only(id INT PRIMARY KEY, x INT);
20 INSERT INTO t_primary_only VALUES (1, 1), (2, 2);
21 ALTER TABLE t1 SECONDARY_ENGINE MOCK;
22 ALTER TABLE t2 SECONDARY_ENGINE MOCK;
23 FLUSH STATUS;
24 SELECT * FROM t1;
25 id x y
26 1 2 3
27 4 5 6
28 7 8 9
29 PREPARE ps FROM 'SELECT * FROM t1';
30 EXECUTE ps;
31 id x y
32 1 2 3
33 4 5 6
34 7 8 9
35 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
36 Variable_name Value
37 Secondary_engine_execution_count 0
38 DROP PREPARE ps;
39 FLUSH STATUS;
40 ALTER TABLE t1 SECONDARY_LOAD;
41 PREPARE ps FROM 'SELECT * FROM t1';
42 EXECUTE ps;
43 id x y
44 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
45 Variable_name Value
46 Secondary_engine_execution_count 1
47 ALTER TABLE t1 SECONDARY_UNLOAD;
48 EXECUTE ps;
49 id x y
50 1 2 3
51 4 5 6
52 7 8 9
53 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
54 Variable_name Value
55 Secondary_engine_execution_count 1
56 EXECUTE ps;
57 id x y
58 1 2 3
59 4 5 6
60 7 8 9
61 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
62 Variable_name Value
63 Secondary_engine_execution_count 1
64 DROP PREPARE ps;
65 ALTER TABLE t1 SECONDARY_LOAD;
66 ALTER TABLE t2 SECONDARY_LOAD;
67 FLUSH STATUS;
68 SELECT * FROM t1;
69 id x y
70 PREPARE ps FROM 'SELECT * FROM t1';
71 EXECUTE ps;
72 id x y
73 EXECUTE ps;
74 id x y
75 FLUSH TABLES;
76 SELECT * FROM t1;
77 id x y
78 FLUSH TABLES;
79 EXECUTE ps;
80 id x y
81 DROP PREPARE ps;
82 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
83 Variable_name Value
84 Secondary_engine_execution_count 5
85 ANALYZE TABLE t1;
86 Table Op Msg_type Msg_text
87 test.t1 analyze status OK
88 EXPLAIN FORMAT=TREE SELECT * FROM t1;
89 EXPLAIN
90 -> Table scan on t1 in secondary engine MOCK (rows=3)
91
92 Warnings:
93 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
94 FLUSH STATUS;
95 SELECT * FROM t1 JOIN t2 USING (id);
96 id x y z
97 SELECT * FROM t1 WHERE t1.x IN (SELECT t2.z FROM t2);
98 id x y
99 SELECT SUM(x) FROM t1 GROUP BY y;
100 SUM(x)
101 SELECT (SELECT MAX(x) FROM t1), (SELECT MIN(z) FROM t2), s FROM
102 (SELECT SUM(z) AS s FROM t2) AS dt;
103 (SELECT MAX(x) FROM t1) (SELECT MIN(z) FROM t2) s
104 NULL NULL NULL
105 SELECT * FROM v1;
106 id x z
107 SELECT * FROM v1, t2 WHERE v1.id = t2.id;
108 id x z id z
109 SELECT SUM(x) FROM v1;
110 SUM(x)
111 NULL
112 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
113 Variable_name Value
114 Secondary_engine_execution_count 7
115 FLUSH STATUS;
116 SELECT x FROM t1 FORCE INDEX (t1_x);
117 x
118 PREPARE ps FROM 'SELECT x FROM t1 FORCE INDEX (t1_x)';
119 EXECUTE ps;
120 x
121 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
122 Variable_name Value
123 Secondary_engine_execution_count 2
124 SET @my_use_secondary_engine = @@use_secondary_engine;
125 SET @@use_secondary_engine = FORCED;
126 EXECUTE ps;
127 x
128 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
129 Variable_name Value
130 Secondary_engine_execution_count 3
131 SET @@use_secondary_engine = @my_use_secondary_engine;
132 FLUSH STATUS;
133 PREPARE ps FROM 'SELECT * FROM t1';
134 LOCK TABLES t1 READ;
135 SELECT * FROM t1;
136 id x y
137 1 2 3
138 4 5 6
139 7 8 9
140 EXECUTE ps;
141 id x y
142 1 2 3
143 4 5 6
144 7 8 9
145 EXECUTE ps;
146 id x y
147 1 2 3
148 4 5 6
149 7 8 9
150 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
151 Variable_name Value
152 Secondary_engine_execution_count 0
153 UNLOCK TABLES;
154 SELECT * FROM t1;
155 id x y
156 EXECUTE ps;
157 id x y
158 EXECUTE ps;
159 id x y
160 DROP PREPARE ps;
161 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
162 Variable_name Value
163 Secondary_engine_execution_count 3
164 FLUSH STATUS;
165 PREPARE ps FROM 'SELECT * FROM t1';
166 BEGIN;
167 SELECT * FROM t1;
168 id x y
169 1 2 3
170 4 5 6
171 7 8 9
172 EXECUTE ps;
173 id x y
174 1 2 3
175 4 5 6
176 7 8 9
177 COMMIT;
178 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
179 Variable_name Value
180 Secondary_engine_execution_count 0
181 EXECUTE ps;
182 id x y
183 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
184 Variable_name Value
185 Secondary_engine_execution_count 1
186 FLUSH STATUS;
187 START TRANSACTION;
188 SELECT * FROM t1;
189 id x y
190 1 2 3
191 4 5 6
192 7 8 9
193 EXECUTE ps;
194 id x y
195 1 2 3
196 4 5 6
197 7 8 9
198 COMMIT;
199 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
200 Variable_name Value
201 Secondary_engine_execution_count 0
202 EXECUTE ps;
203 id x y
204 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
205 Variable_name Value
206 Secondary_engine_execution_count 1
207 FLUSH STATUS;
208 SET @@autocommit = 0;
209 SELECT * FROM t1;
210 id x y
211 1 2 3
212 4 5 6
213 7 8 9
214 EXECUTE ps;
215 id x y
216 1 2 3
217 4 5 6
218 7 8 9
219 SET @@autocommit = 1;
220 COMMIT;
221 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
222 Variable_name Value
223 Secondary_engine_execution_count 0
224 SELECT * FROM t1;
225 id x y
226 EXECUTE ps;
227 id x y
228 DROP PREPARE ps;
229 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
230 Variable_name Value
231 Secondary_engine_execution_count 2
232 FLUSH STATUS;
233 BEGIN;
234 PREPARE ps FROM 'SELECT * FROM t1';
235 EXECUTE ps;
236 id x y
237 1 2 3
238 4 5 6
239 7 8 9
240 COMMIT;
241 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
242 Variable_name Value
243 Secondary_engine_execution_count 0
244 EXECUTE ps;
245 id x y
246 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
247 Variable_name Value
248 Secondary_engine_execution_count 1
249 DROP PREPARE ps;
250 SELECT * FROM t1, t_primary_only tp WHERE t1.id = tp.id;
251 id x y id x
252 1 2 3 1 1
253 PREPARE ps FROM 'SELECT * FROM t1, t_primary_only tp WHERE t1.id = tp.id';
254 EXECUTE ps;
255 id x y id x
256 1 2 3 1 1
257 EXECUTE ps;
258 id x y id x
259 1 2 3 1 1
260 DROP PREPARE ps;
261 SELECT * FROM t1, t2, t_primary_only tp WHERE t1.id = t2.id AND t2.id = tp.id;
262 id x y id z id x
263 1 2 3 1 2 1 1
264 FLUSH STATUS;
265 SELECT * FROM t1, INFORMATION_SCHEMA.COLUMN_PRIVILEGES t2;
266 SHOW STATUS LIKE 'Secondary_engine_execution_count';
267 Variable_name Value
268 Secondary_engine_execution_count 0
269 CREATE TABLE t_with_autoinc(id INT PRIMARY KEY AUTO_INCREMENT, x INT)
270 SECONDARY_ENGINE MOCK;
271 INSERT INTO t_with_autoinc(x) VALUES (4), (5), (6);
272 ALTER TABLE t_with_autoinc SECONDARY_LOAD;
273 SELECT * FROM t_with_autoinc;
274 id x
275 DROP TABLE t_with_autoinc;
276 CREATE TABLE t_partitioned(id INT PRIMARY KEY, x INT)
277 SECONDARY_ENGINE MOCK
278 PARTITION BY HASH(id);
279 INSERT INTO t_partitioned VALUES (1, 2), (2, 3), (3, 4), (4, 5);
280 ALTER TABLE t_partitioned SECONDARY_LOAD;
281 SELECT * FROM t_partitioned;
282 id x
283 DROP TABLE t_partitioned;
284 FLUSH STATUS;
285 SELECT * FROM t1;
286 PREPARE ps FROM 'SELECT * FROM t1';
287 EXECUTE ps;
288 SELECT * FROM t1, t_primary_only tp WHERE t1.id = tp.id;
289 EXECUTE ps;
290 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
291 ALTER TABLE t1 SECONDARY_UNLOAD;
292 SELECT * FROM t1;
293 EXECUTE ps;
294 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
295 ALTER TABLE t1 SECONDARY_LOAD;
296 EXECUTE ps;
297 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
298 DROP PREPARE ps|
299 id x y
300 id x y
301 id x y id x
302 1 2 3 1 1
303 id x y
304 Variable_name Value
305 Secondary_engine_execution_count 3
306 id x y
307 1 2 3
308 4 5 6
309 7 8 9
310 id x y
311 1 2 3
312 4 5 6
313 7 8 9
314 Variable_name Value
315 Secondary_engine_execution_count 3
316 id x y
317 Variable_name Value
318 Secondary_engine_execution_count 4
319 FLUSH STATUS;
320 PREPARE ps FROM 'SELECT x FROM t1 WHERE id = ?';
321 SET @a=1;
322 EXECUTE ps USING @a;
323 x
324 EXECUTE ps USING @a;
325 x
326 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
327 Variable_name Value
328 Secondary_engine_execution_count 2
329 ALTER TABLE t1 SECONDARY_UNLOAD;
330 EXECUTE ps USING @a;
331 x
332 2
333 EXECUTE ps USING @a;
334 x
335 2
336 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
337 Variable_name Value
338 Secondary_engine_execution_count 2
339 ALTER TABLE t1 SECONDARY_LOAD;
340 EXECUTE ps USING @a;
341 x
342 EXECUTE ps USING @a;
343 x
344 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
345 Variable_name Value
346 Secondary_engine_execution_count 4
347 DROP PREPARE ps;
348 FLUSH STATUS;
349 SELECT JSON_EXTRACT(j, '$') FROM
350 (SELECT '[]' AS j FROM DUAL UNION ALL SELECT '{}' FROM DUAL) AS dt
351 LEFT JOIN t1 ON (TRUE);
352 JSON_EXTRACT(j, '$')
353 []
354 {}
355 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
356 Variable_name Value
357 Secondary_engine_execution_count 1
358 FLUSH STATUS;
359 SELECT JSON_EXTRACT(j, '$') FROM
360 (SELECT '[]' AS j FROM DUAL UNION ALL SELECT 'not-json' FROM DUAL) AS dt
361 LEFT JOIN t1 ON (TRUE);
362 ERROR 22032: Invalid JSON text in argument 1 to function json_extract: "Invalid value." at position 1.
363 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
364 Variable_name Value
365 Secondary_engine_execution_count 0
366 FLUSH STATUS;
367 PREPARE ps FROM "SELECT JSON_EXTRACT(j, '$') FROM
368 (SELECT '[]' AS j FROM DUAL UNION ALL SELECT 'not-json' FROM DUAL) AS dt
369 LEFT JOIN t1 ON (TRUE)";
370 EXECUTE ps;
371 ERROR 22032: Invalid JSON text in argument 1 to function json_extract: "Invalid value." at position 1.
372 EXECUTE ps;
373 ERROR 22032: Invalid JSON text in argument 1 to function json_extract: "Invalid value." at position 1.
374 DROP PREPARE ps;
375 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
376 Variable_name Value
377 Secondary_engine_execution_count 0
378 FLUSH STATUS;
379 CREATE PROCEDURE p1() SELECT COUNT(*) FROM t1;
380 CALL p1();
381 COUNT(*)
382 3
383 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
384 Variable_name Value
385 Secondary_engine_execution_count 0
386 PREPARE ps1 FROM 'SELECT * FROM t1';
387 EXECUTE ps1;
388 id x y
389 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
390 Variable_name Value
391 Secondary_engine_execution_count 1
392 CREATE PROCEDURE p2() EXECUTE ps1;
393 CREATE PROCEDURE p3() PREPARE ps2 FROM 'SELECT * FROM t1';
394 FLUSH STATUS;
395 CALL p2();
396 id x y
397 1 2 3
398 4 5 6
399 7 8 9
400 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
401 Variable_name Value
402 Secondary_engine_execution_count 0
403 CALL p3();
404 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
405 Variable_name Value
406 Secondary_engine_execution_count 0
407 EXECUTE ps1;
408 id x y
409 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
410 Variable_name Value
411 Secondary_engine_execution_count 1
412 EXECUTE ps2;
413 id x y
414 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
415 Variable_name Value
416 Secondary_engine_execution_count 2
417 DROP PREPARE ps1;
418 DROP PREPARE ps2;
419 DROP PROCEDURE p1;
420 DROP PROCEDURE p2;
421 DROP PROCEDURE p3;
422 FLUSH STATUS;
423 CREATE TABLE t_with_trigger(x INT);
424 CREATE TRIGGER tr AFTER INSERT ON t_with_trigger
425 FOR EACH ROW SELECT COUNT(*) FROM t1 INTO @a;
426 INSERT INTO t_with_trigger VALUES (1);
427 SELECT @a;
428 @a
429 3
430 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
431 Variable_name Value
432 Secondary_engine_execution_count 0
433 DROP TABLE t_with_trigger;
434 CREATE TABLE t_nonexistent(x INT) SECONDARY_ENGINE NONEXISTENT;
435 INSERT INTO t_nonexistent VALUES (1);
436 FLUSH STATUS;
437 SELECT * FROM t_nonexistent;
438 x
439 1
440 SELECT * FROM t1 a, t_nonexistent b WHERE a.id = b.x;
441 id x y x
442 1 2 3 1
443 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
444 Variable_name Value
445 Secondary_engine_execution_count 0
446 DROP TABLE t_nonexistent;
447 CREATE TABLE t_case1(x INT) SECONDARY_ENGINE 'MOCK';
448 CREATE TABLE t_case2(y INT) SECONDARY_ENGINE 'mock ';
449 CREATE TABLE t_case3(z INT) SECONDARY_ENGINE 'MoCk ';
450 INSERT INTO t_case1 VALUES (1);
451 INSERT INTO t_case2 VALUES (2);
452 INSERT INTO t_case3 VALUES (3);
453 ALTER TABLE t_case1 SECONDARY_LOAD;
454 ALTER TABLE t_case2 SECONDARY_LOAD;
455 ALTER TABLE t_case3 SECONDARY_LOAD;
456 FLUSH STATUS;
457 SELECT * FROM t_case1, t_case2, t_case3;
458 x y z
459 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
460 Variable_name Value
461 Secondary_engine_execution_count 1
462 DROP TABLE t_case1, t_case2, t_case3;
463 CREATE TABLE tf(x INT) SECONDARY_ENGINE MOCK;
464 INSERT INTO tf VALUES (123);
465 ALTER TABLE tf SECONDARY_LOAD;
466 CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
467 BEGIN
468 DECLARE i INT;
469 SELECT x INTO i FROM tf;
470 RETURN i;
471 END|
472 CREATE FUNCTION f2() RETURNS INT DETERMINISTIC
473 BEGIN
474 INSERT INTO tf VALUES (234);
475 RETURN 1;
476 END|
477 FLUSH STATUS;
478 SELECT f1();
479 f1()
480 123
481 SELECT f2();
482 f2()
483 1
484 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
485 Variable_name Value
486 Secondary_engine_execution_count 0
487 SELECT * FROM tf;
488 x
489 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
490 Variable_name Value
491 Secondary_engine_execution_count 1
492 DROP FUNCTION f1;
493 DROP FUNCTION f2;
494 DROP TABLE tf;
495 CREATE TABLE t_dropped (x INT) SECONDARY_ENGINE MOCK;
496 ALTER TABLE t_dropped SECONDARY_LOAD;
497 PREPARE ps FROM 'SELECT * FROM t_dropped';
498 EXECUTE ps;
499 x
500 DROP TABLE t_dropped;
501 EXECUTE ps;
502 ERROR 42S02: Table 'test.t_dropped' doesn't exist
503 DROP PREPARE ps;
504 DROP VIEW v1;
505 DROP TABLE t1, t2, t_primary_only;
506 #
507 # Rudimentary tests for use_secondary_engine system variable.
508 #
509 SET @my_use_secondary_engine = @@use_secondary_engine;
510 CREATE TABLE t1 (a INT) SECONDARY_ENGINE MOCK;
511 INSERT INTO t1 VALUES (1);
512 ALTER TABLE t1 SECONDARY_LOAD;
513 CREATE TABLE t2 (a INT);
514 INSERT INTO t2 VALUES (1);
515 FLUSH STATUS;
516 # The row shall be returned from the primary storage engine.
517 SET @@use_secondary_engine = OFF;
518 SELECT * FROM t1;
519 a
520 1
521 INSERT INTO t1 SELECT * FROM t2;
522 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
523 Variable_name Value
524 Secondary_engine_execution_count 0
525 # The mock storage engine does not return any rows.
526 SET @@use_secondary_engine = FORCED;
527 SELECT * FROM t1;
528 a
529 INSERT INTO t1 SELECT * FROM t2;
530 ERROR HY000: Secondary engine operation failed. No secondary engine defined for at least one of the query tables.
531 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
532 Variable_name Value
533 Secondary_engine_execution_count 1
534 SET @@use_secondary_engine = ON;
535 SELECT * FROM t1;
536 a
537 INSERT INTO t1 SELECT * FROM t2;
538 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
539 Variable_name Value
540 Secondary_engine_execution_count 2
541 # An error shall be returned if table is not loaded.
542 ALTER TABLE t1 SECONDARY_UNLOAD;
543 SET @@use_secondary_engine = FORCED;
544 SELECT * FROM t1;
545 Got one of the listed errors
546 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
547 Variable_name Value
548 Secondary_engine_execution_count 2
549 SET @@use_secondary_engine = ON;
550 SELECT * FROM t1;
551 a
552 1
553 1
554 1
555 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
556 Variable_name Value
557 Secondary_engine_execution_count 2
558 DROP TABLE t1;
559 DROP TABLE t2;
560 SET @@use_secondary_engine = @my_use_secondary_engine;
561 #
562 # Queries not referencing any base tables shall be executed without
563 # without even if use_secondary_engine is FORCED.
564 #
565 SET @my_use_secondary_engine = @@use_secondary_engine;
566 SET @@use_secondary_engine = FORCED;
567 CREATE VIEW v as SELECT 1;
568 SELECT * FROM v;
569 1
570 1
571 SELECT 1;
572 1
573 1
574 SELECT COUNT(*) FROM (SELECT 1) AS dt;
575 COUNT(*)
576 1
577 DROP VIEW v;
578 SET @@use_secondary_engine = @my_use_secondary_engine;
579 #
580 # Attempt to force query on secondary engine even if its referenced
581 # table has no secondary engine defined.
582 #
583 SET @my_use_secondary_engine = @@use_secondary_engine;
584 SET @@use_secondary_engine = FORCED;
585 CREATE TABLE t1 (a INT);
586 INSERT INTO t1 VALUES (1);
587 FLUSH STATUS;
588 SELECT * FROM t1;
589 ERROR HY000: Secondary engine operation failed. No secondary engine defined for at least one of the query tables.
590 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
591 Variable_name Value
592 Secondary_engine_execution_count 0
593 SET @@use_secondary_engine = ON;
594 SELECT * FROM t1;
595 a
596 1
597 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
598 Variable_name Value
599 Secondary_engine_execution_count 0
600 DROP TABLE t1;
601 SET @@use_secondary_engine = @my_use_secondary_engine;
602 #
603 # Attempt to force query on secondary engine if one of its referenced
604 # table has a secondary engine while another table has none.
605 #
606 SET @my_use_secondary_engine = @@use_secondary_engine;
607 SET @@use_secondary_engine = FORCED;
608 CREATE TABLE t1 (a INT) SECONDARY_ENGINE MOCK;
609 CREATE TABLE t2 (a INT);
610 INSERT INTO t1 VALUES (1);
611 INSERT INTO t2 VALUES (2);
612 ALTER TABLE t1 SECONDARY_LOAD;
613 FLUSH STATUS;
614 SELECT * FROM t1;
615 a
616 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
617 Variable_name Value
618 Secondary_engine_execution_count 1
619 SELECT * FROM t2;
620 ERROR HY000: Secondary engine operation failed. No secondary engine defined for at least one of the query tables.
621 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
622 Variable_name Value
623 Secondary_engine_execution_count 1
624 SELECT * FROM t1,t2;
625 ERROR HY000: Secondary engine operation failed. No secondary engine defined for at least one of the query tables.
626 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
627 Variable_name Value
628 Secondary_engine_execution_count 1
629 ALTER TABLE t1 SECONDARY_UNLOAD;
630 SELECT * FROM t1,t2;
631 ERROR HY000: Secondary engine operation failed. No secondary engine defined for at least one of the query tables.
632 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
633 Variable_name Value
634 Secondary_engine_execution_count 1
635 DROP TABLE t1,t2;
636 SET @@use_secondary_engine = @my_use_secondary_engine;
637 #
638 # Attempt to force query referencing temporary table on secondary engine.
639 #
640 SET @my_use_secondary_engine = @@use_secondary_engine;
641 SET @@use_secondary_engine = FORCED;
642 CREATE TEMPORARY TABLE t1 (a INT);
643 FLUSH STATUS;
644 SELECT * FROM t1;
645 ERROR HY000: Secondary engine operation failed. No secondary engine defined for at least one of the query tables.
646 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
647 Variable_name Value
648 Secondary_engine_execution_count 0
649 DROP TABLE t1;
650 SET @@use_secondary_engine = @my_use_secondary_engine;
651 #
652 # Change use_secondary_engine across execution of prepared statements.
653 #
654 SET @my_use_secondary_engine = @@use_secondary_engine;
655 SET @@use_secondary_engine = FORCED;
656 CREATE TABLE t1 (a INT) SECONDARY_ENGINE MOCK;
657 FLUSH STATUS;
658 PREPARE ps FROM "SELECT * FROM t1";
659 ERROR HY000: Table has not been loaded
660 SET @@use_secondary_engine = OFF;
661 PREPARE ps FROM "SELECT * FROM t1";
662 EXECUTE ps;
663 a
664 # Load table into secondary engine.
665 ALTER TABLE t1 SECONDARY_LOAD;
666 EXECUTE ps;
667 a
668 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
669 Variable_name Value
670 Secondary_engine_execution_count 0
671 SET @@use_secondary_engine = ON;
672 EXECUTE ps;
673 a
674 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
675 Variable_name Value
676 Secondary_engine_execution_count 1
677 SET @@use_secondary_engine = FORCED;
678 EXECUTE ps;
679 a
680 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
681 Variable_name Value
682 Secondary_engine_execution_count 2
683 # Unload table from secondary engine.
684 ALTER TABLE t1 SECONDARY_UNLOAD;
685 SET @@use_secondary_engine = ON;
686 EXECUTE ps;
687 a
688 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
689 Variable_name Value
690 Secondary_engine_execution_count 2
691 SET @@use_secondary_engine = FORCED;
692 EXECUTE ps;
693 ERROR HY000: Secondary engine operation failed. use_secondary_engine is FORCED but query could not be executed in secondary engine.
694 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
695 Variable_name Value
696 Secondary_engine_execution_count 2
697 DROP PREPARE ps;
698 DROP TABLE t1;
699 SET @@use_secondary_engine = @my_use_secondary_engine;
700 #
701 # Set use_secondary_engine as a hint.
702 #
703 SET @my_use_secondary_engine = @@use_secondary_engine;
704 SET @@use_secondary_engine = OFF;
705 CREATE TABLE t1 (a INT) SECONDARY_ENGINE MOCK;
706 INSERT INTO t1 VALUES (1);
707 FLUSH STATUS;
708 SELECT * FROM t1;
709 a
710 1
711 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
712 Variable_name Value
713 Secondary_engine_execution_count 0
714 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM t1;
715 Got one of the listed errors
716 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
717 Variable_name Value
718 Secondary_engine_execution_count 0
719 ALTER TABLE t1 SECONDARY_LOAD;
720 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM t1;
721 a
722 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
723 Variable_name Value
724 Secondary_engine_execution_count 1
725 SELECT /*+ SET_VAR(use_secondary_engine = ON) */ * FROM t1;
726 a
727 SET @@use_secondary_engine = @my_use_secondary_engine;
728 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
729 Variable_name Value
730 Secondary_engine_execution_count 2
731 SELECT /*+ SET_VAR(use_secondary_engine = OFF) */ * FROM t1;
732 a
733 1
734 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
735 Variable_name Value
736 Secondary_engine_execution_count 2
737 DROP TABLE t1;
738 SET @@use_secondary_engine = @my_use_secondary_engine;
739 #
740 # Bug#28311801: THE GLOBAL VALUE OF SECONDARY_ENGINE_EXECUTION_COUNT
741 # IS ALWAYS ZERO
742 #
743 CREATE TABLE t (x INT) SECONDARY_ENGINE MOCK;
744 ALTER TABLE t SECONDARY_LOAD;
745 SELECT * FROM t;
746 x
747 FLUSH STATUS;
748 SELECT 1;
749 1
750 1
751 DROP TABLE t;
752 #
753 # Load only a subset of columns into secondary engine.
754 #
755 SET @my_use_secondary_engine = @@use_secondary_engine;
756 CREATE TABLE t1 (
757 pk INT PRIMARY KEY AUTO_INCREMENT,
758 a INT,
759 b INT NOT SECONDARY,
760 c INT,
761 d INT NOT SECONDARY
762 ) SECONDARY_ENGINE MOCK;
763 CREATE TABLE t2 (
764 pk INT PRIMARY KEY AUTO_INCREMENT,
765 e INT,
766 f INT NOT SECONDARY,
767 g INT,
768 h INT NOT SECONDARY
769 ) SECONDARY_ENGINE MOCK;
770 INSERT INTO t1 (a, b, c, d) VALUES (1,1,1,1), (1,1,1,1), (2,2,2,2);
771 INSERT INTO t2 (e, f, g, h) VALUES (2,2,2,2), (2,2,2,2), (3,3,3,3);
772 ALTER TABLE t1 SECONDARY_LOAD;
773 ALTER TABLE t2 SECONDARY_LOAD;
774 # Statements which should be sent to primary engine since not all columns
775 # are in secondary engine.
776 CREATE VIEW v1 as SELECT * FROM t1;
777 CREATE VIEW v2 as SELECT t1.a, t2.f FROM t1, t2;
778 SELECT * FROM t1;
779 pk a b c d
780 1 1 1 1 1
781 2 1 1 1 1
782 3 2 2 2 2
783 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM t1;
784 ERROR HY000: Secondary engine operation failed. use_secondary_engine is FORCED but query could not be executed in secondary engine.
785 SELECT 1 FROM t1 WHERE b=1;
786 1
787 1
788 1
789 SELECT 1 FROM t1 GROUP BY b;
790 1
791 1
792 1
793 SELECT 1 FROM t1 ORDER BY b;
794 1
795 1
796 1
797 1
798 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ 1 FROM t1 WHERE b=1;
799 ERROR HY000: Secondary engine operation failed. use_secondary_engine is FORCED but query could not be executed in secondary engine.
800 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ 1 FROM t1 GROUP BY b;
801 ERROR HY000: Secondary engine operation failed. use_secondary_engine is FORCED but query could not be executed in secondary engine.
802 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ 1 FROM t1 ORDER BY b;
803 ERROR HY000: Secondary engine operation failed. use_secondary_engine is FORCED but query could not be executed in secondary engine.
804 SELECT * FROM v1;
805 pk a b c d
806 1 1 1 1 1
807 2 1 1 1 1
808 3 2 2 2 2
809 SELECT SUM(b) FROM v1;
810 SUM(b)
811 4
812 SELECT 1 FROM v1 WHERE b=1;
813 1
814 1
815 1
816 SELECT 1 FROM v1 GROUP BY b;
817 1
818 1
819 1
820 SELECT 1 FROM v1 ORDER BY b;
821 1
822 1
823 1
824 1
825 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM v1;
826 ERROR HY000: Secondary engine operation failed. No secondary engine defined for at least one of the query tables.
827 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ SUM(b) FROM v1;
828 ERROR HY000: Secondary engine operation failed. No secondary engine defined for at least one of the query tables.
829 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ 1 FROM v1 WHERE b=1;
830 ERROR HY000: Secondary engine operation failed. No secondary engine defined for at least one of the query tables.
831 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ 1 FROM v1 GROUP BY b;
832 ERROR HY000: Secondary engine operation failed. No secondary engine defined for at least one of the query tables.
833 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ 1 FROM v1 ORDER BY b;
834 ERROR HY000: Secondary engine operation failed. No secondary engine defined for at least one of the query tables.
835 SELECT t1.a, t2.f FROM t1, t2;
836 a f
837 1 2
838 1 2
839 1 2
840 1 2
841 1 3
842 1 3
843 2 2
844 2 2
845 2 3
846 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ t1.a, t2.f FROM t1, t2;
847 ERROR HY000: Secondary engine operation failed. use_secondary_engine is FORCED but query could not be executed in secondary engine.
848 SELECT * FROM v2;
849 a f
850 1 2
851 1 2
852 1 2
853 1 2
854 1 3
855 1 3
856 2 2
857 2 2
858 2 3
859 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM v2;
860 ERROR HY000: Secondary engine operation failed. No secondary engine defined for at least one of the query tables.
861 # Statements which should be sent to the secondary engine since all
862 # columns are in secondary engine.
863 SET @@use_secondary_engine = FORCED;
864 CREATE VIEW v3 as SELECT a, c FROM t1;
865 CREATE VIEW v4 as SELECT t1.a, t1.c, t2.e, t2.g FROM t1, t2;
866 SELECT a, c FROM t1;
867 a c
868 SELECT 1 FROM t1 WHERE a=1;
869 1
870 SELECT * FROM v3;
871 a c
872 SELECT t1.a, t1.c, t2.e, t2.g FROM t1, t2;
873 a c e g
874 SELECT * FROM v4;
875 a c e g
876 DROP VIEW v1, v2, v3, v4;
877 DROP TABLE t1, t2;
878 SET @@use_secondary_engine = @my_use_secondary_engine;
879 #
880 # NOT SECONDARY on partitioned table
881 #
882 CREATE TABLE t1 (a INT PRIMARY KEY NOT SECONDARY, b INT DEFAULT 10)
883 PARTITION BY RANGE (a) (
884 PARTITION p0 VALUES LESS THAN (1),
885 PARTITION p1 VALUES LESS THAN (2),
886 PARTITION p2 VALUES LESS THAN (3),
887 PARTITION p3 VALUES LESS THAN (4),
888 PARTITION p4 VALUES LESS THAN (5),
889 PARTITION p5 VALUES LESS THAN (6),
890 PARTITION max VALUES LESS THAN MAXVALUE);
891 INSERT INTO t1(a) VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);
892 ALTER TABLE t1 SECONDARY_ENGINE=MOCK;
893 ALTER TABLE t1 SECONDARY_LOAD;
894 ANALYZE TABLE t1;
895 Table Op Msg_type Msg_text
896 test.t1 analyze status OK
897 FLUSH STATUS;
898 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
899 Variable_name Value
900 Secondary_engine_execution_count 0
901 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ a FROM t1;
902 ERROR HY000: Secondary engine operation failed. use_secondary_engine is FORCED but query could not be executed in secondary engine.
903 SELECT a FROM t1;
904 a
905 -1
906 0
907 1
908 2
909 3
910 4
911 5
912 6
913 7
914 8
915 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
916 Variable_name Value
917 Secondary_engine_execution_count 0
918 SELECT b FROM t1;
919 b
920 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
921 Variable_name Value
922 Secondary_engine_execution_count 1
923 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ b FROM t1;
924 b
925 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
926 Variable_name Value
927 Secondary_engine_execution_count 2
928 DROP TABLE t1;
929 #
930 # Use NOT SECONDARY as comment.
931 #
932 CREATE TABLE t1 (a INT /* NOT SECONDARY */, b INT ) SECONDARY_ENGINE MOCK;
933 INSERT INTO t1 VALUES (1,1);
934 ALTER TABLE t1 SECONDARY_LOAD;
935 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ a FROM t1;
936 a
937 FLUSH STATUS;
938 SELECT a FROM t1;
939 a
940 SELECT * FROM t1;
941 a b
942 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
943 Variable_name Value
944 Secondary_engine_execution_count 2
945 DROP TABLE t1;
946 #
947 # Use NOT SECONDARY with other constraint on column.
948 #
949 CREATE TABLE t1 (a INT, b INT NOT SECONDARY UNIQUE KEY) SECONDARY_ENGINE MOCK;
950 INSERT INTO t1 VALUES (1, 1);
951 ALTER TABLE t1 SECONDARY_LOAD;
952 FLUSH STATUS;
953 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM t1;
954 ERROR HY000: Secondary engine operation failed. use_secondary_engine is FORCED but query could not be executed in secondary engine.
955 SELECT a FROM t1;
956 a
957 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ b FROM t1;
958 ERROR HY000: Secondary engine operation failed. use_secondary_engine is FORCED but query could not be executed in secondary engine.
959 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
960 Variable_name Value
961 Secondary_engine_execution_count 1
962 DROP TABLE t1;
963 # Add NON SECONDARY and do not use SECONDARY_ENGINE AT CREATE
964 CREATE TABLE t1 (a INT NOT SECONDARY, b INT);
965 INSERT INTO t1 VALUES (1,1);
966 SELECT a FROM t1;
967 a
968 1
969 SELECT b FROM t1;
970 b
971 1
972 SELECT * FROM t1;
973 a b
974 1 1
975 DROP TABLE t1;
976 #
977 # Bug#28738100: SECONDARY ENGINE RETURNS WRONG RESULT WITH IS NULL CLAUSE
978 #
979 SET @saved_sql_auto_is_null = @@sql_auto_is_null;
980 SET @@sql_auto_is_null = 1;
981 CREATE TABLE t(a INT PRIMARY KEY AUTO_INCREMENT) SECONDARY_ENGINE MOCK;
982 ALTER TABLE t SECONDARY_LOAD;
983 INSERT INTO t VALUES ();
984 ANALYZE TABLE t;
985 Table Op Msg_type Msg_text
986 test.t analyze status OK
987 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE a IS NULL;
988 EXPLAIN
989 -> Filter: (t.a = <cache>(last_insert_id())) (rows=1)
990 -> Table scan on t in secondary engine MOCK (rows=1)
991
992 Warnings:
993 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
994 DROP TABLE t;
995 SET @@sql_auto_is_null = @saved_sql_auto_is_null;
996 # Bug#29288023: Join condition is substituted with REF access
997 CREATE TABLE t1(f1 int, f11 int);
998 CREATE TABLE t2(f2 int, f22 int);
999 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(5,5),(9,9),(7,7);
1000 INSERT INTO t2 VALUES(1,1),(3,3),(2,2),(4,4),(8,8),(6,6);
1001 ANALYZE TABLE t1, t2;
1002 Table Op Msg_type Msg_text
1003 test.t1 analyze status OK
1004 test.t2 analyze status OK
1005 ALTER TABLE t1 SECONDARY_ENGINE=MOCK;
1006 ALTER TABLE t1 SECONDARY_LOAD;
1007 ALTER TABLE t2 SECONDARY_ENGINE=MOCK;
1008 ALTER TABLE t2 SECONDARY_LOAD;
1009 ANALYZE TABLE t1, t2;
1010 Table Op Msg_type Msg_text
1011 test.t1 analyze status OK
1012 test.t2 analyze status OK
1013 include/assert_grep.inc [Secondary engine does not support indexes on derived tables]
1014 DROP TABLE t1, t2;
1015 CREATE TABLE t1(x INT, y INT, z INT, c INT, d INT);
1016 INSERT INTO t1 VALUES
1017 (1, 5, 2, 2, 3), (2, 2, 5, 2, 3), (3, 5, 5, 2, 3), (4, 2, 5, 2, 3),
1018 (2, 3, 4, 2, 3), (7, 6, 5, 2, 3), (8, 9, 5, 2, 3), (9, 6, 5, 2, 3),
1019 (10, 7, 5, 2, 3), (11, 6, 5, 2, 3), (12, 7, 5, 2, 3), (13, -1, 5, 2, 3),
1020 (14, 7, 5, 2, 3), (15, 8, 5, 2, 3), (16, 4, 5, 2, 3), (17, 7, 5, 2, 3),
1021 (18, 7, 5, 2, 3), (19, 17, 5, 2, 3), (20, 17, 5, 2, 3), (21, 5, -1, 2, 3),
1022 (22, 5, 5, 2, 3);
1023 ALTER TABLE t1 SECONDARY_ENGINE=MOCK;
1024 ALTER TABLE t1 SECONDARY_LOAD;
1025 ANALYZE TABLE t1;
1026 Table Op Msg_type Msg_text
1027 test.t1 analyze status OK
1028 include/assert_grep.inc [Secondary engine does not support indexes on derived tables]
1029 DROP TABLE t1;
1030 #
1031 # Bug#29994087: SIG11 AT MATERIALIZEITERATOR::INIT() IN
1032 # SQL/COMPOSITE_ITERATORS.CC
1033 #
1034 CREATE TABLE t (a INT, b INT) SECONDARY_ENGINE MOCK;
1035 ALTER TABLE t SECONDARY_LOAD;
1036 SELECT 1 FROM t
1037 WHERE (1, 2) IN (SELECT 3, 4 UNION SELECT 5, 6) OR b <= 10;
1038 1
1039 DROP TABLE t;
1040 #
1041 # Bug#29615190: SELECTS ON TABLES OFFLOADED TO SECONDARY ENGINE ARE
1042 # WRITTEN TWICE TO THE MYSQLD GENERAL LOG
1043 #
1044 CREATE TABLE t(x INT) SECONDARY_ENGINE MOCK;
1045 ALTER TABLE t SECONDARY_LOAD;
1046 SET @saved_general_log = @@global.general_log;
1047 SET @saved_log_output = @@global.log_output;
1048 SET GLOBAL general_log = 1;
1049 SET GLOBAL log_output = 'table';
1050 TRUNCATE TABLE mysql.general_log;
1051 SELECT * FROM t;
1052 x
1053 statement_count
1054 1
1055 TRUNCATE TABLE mysql.general_log;
1056 PREPARE ps FROM 'SELECT * FROM t';
1057 SELECT COUNT(*) FROM mysql.general_log WHERE argument = 'SELECT * FROM t';
1058 COUNT(*)
1059 1
1060 EXECUTE ps;
1061 x
1062 SELECT COUNT(*) FROM mysql.general_log WHERE argument = 'SELECT * FROM t';
1063 COUNT(*)
1064 2
1065 DROP PREPARE ps;
1066 TRUNCATE TABLE mysql.general_log;
1067 SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM t;
1068 x
1069 statement_count
1070 1
1071 TRUNCATE TABLE mysql.general_log;
1072 PREPARE ps FROM 'SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ * FROM t';
1073 SELECT COUNT(*) FROM mysql.general_log WHERE argument LIKE 'SELECT % * FROM t';
1074 COUNT(*)
1075 1
1076 EXECUTE ps;
1077 x
1078 SELECT COUNT(*) FROM mysql.general_log WHERE argument LIKE 'SELECT % * FROM t';
1079 COUNT(*)
1080 2
1081 DROP PREPARE ps;
1082 SET GLOBAL general_log = @saved_general_log;
1083 SET GLOBAL log_output = @saved_log_output;
1084 TRUNCATE TABLE mysql.general_log;
1085 DROP TABLE t;
1086 #
1087 # Bug#30391501: ASSERTION FAILURE WHEN RUNNING RAPID.AGG_PROJ_ANY
1088 # WITH --PS-PROTOCOL
1089 #
1090 CREATE TABLE t(x INT) SECONDARY_ENGINE MOCK;
1091 INSERT INTO t VALUES (1), (2), (3);
1092 ALTER TABLE t SECONDARY_LOAD;
1093 SELECT (SELECT 9 FROM DUAL) AS y FROM t GROUP BY y;
1094 y
1095 PREPARE ps FROM 'SELECT (SELECT 9 FROM DUAL) AS y FROM t GROUP BY y';
1096 EXECUTE ps;
1097 y
1098 DROP PREPARE ps;
1099 DROP TABLE t;