Debian Codegraph @

mysql-test/suite/federated/federatedx_create_handlers.result

raw
1 connect master,127.0.0.1,root,,test,$MASTER_MYPORT,;
2 connect slave,127.0.0.1,root,,test,$SLAVE_MYPORT,;
3 connection master;
4 CREATE DATABASE federated;
5 connection slave;
6 CREATE DATABASE federated;
7 connection default;
8 ALTER DATABASE federated CHARACTER SET latin1;
9 set global federated_pushdown=1;
10 connection slave;
11 DROP TABLE IF EXISTS federated.t1;
12 Warnings:
13 Note 1051 Unknown table 'federated.t1'
14 CREATE TABLE federated.t1 (
15 id int(20) NOT NULL,
16 name varchar(16) NOT NULL default ''
17 )
18 DEFAULT CHARSET=latin1;
19 INSERT INTO federated.t1 VALUES
20 (3,'xxx'), (7,'yyy'), (4,'xxx'), (1,'zzz'), (5,'yyy');
21 DROP TABLE IF EXISTS federated.t2;
22 Warnings:
23 Note 1051 Unknown table 'federated.t2'
24 CREATE TABLE federated.t2 (
25 name varchar(16) NOT NULL default ''
26 )
27 DEFAULT CHARSET=latin1;
28 INSERT INTO federated.t2 VALUES
29 ('yyy'), ('www'), ('yyy'), ('xxx'), ('www'), ('yyy'), ('www');
30 connection master;
31 DROP TABLE IF EXISTS federated.t1;
32 Warnings:
33 Note 1051 Unknown table 'federated.t1'
34 CREATE TABLE federated.t1 (
35 id int(20) NOT NULL,
36 name varchar(16) NOT NULL default ''
37 )
38 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
39 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t1';
40 DROP TABLE IF EXISTS federated.t2;
41 Warnings:
42 Note 1051 Unknown table 'federated.t2'
43 CREATE TABLE federated.t2 (
44 name varchar(16) NOT NULL default ''
45 )
46 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
47 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t2';
48 SELECT * FROM federated.t1;
49 id name
50 3 xxx
51 7 yyy
52 4 xxx
53 1 zzz
54 5 yyy
55 SELECT id FROM federated.t1 WHERE id < 5;
56 id
57 3
58 4
59 1
60 SELECT count(*), name FROM federated.t1 WHERE id < 5 GROUP BY name;
61 count(*) name
62 2 xxx
63 1 zzz
64 SELECT * FROM federated.t1, federated.t2
65 WHERE federated.t1.name = federated.t2.name;
66 id name name
67 7 yyy yyy
68 5 yyy yyy
69 7 yyy yyy
70 5 yyy yyy
71 3 xxx xxx
72 4 xxx xxx
73 7 yyy yyy
74 5 yyy yyy
75 SELECT * FROM federated.t1 LEFT JOIN federated.t2
76 ON federated.t1.name = federated.t2.name
77 WHERE federated.t1.id > 1;
78 id name name
79 7 yyy yyy
80 5 yyy yyy
81 7 yyy yyy
82 5 yyy yyy
83 3 xxx xxx
84 4 xxx xxx
85 7 yyy yyy
86 5 yyy yyy
87 SELECT * FROM federated.t1
88 WHERE id IN (SELECT count(*) FROM federated.t2 GROUP BY name);
89 id name
90 3 xxx
91 1 zzz
92 EXPLAIN
93 SELECT id FROM federated.t1 WHERE id < 5;
94 id select_type table type possible_keys key key_len ref rows Extra
95 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
96 EXPLAIN EXTENDED
97 SELECT id FROM federated.t1 WHERE id < 5;
98 id select_type table type possible_keys key key_len ref rows filtered Extra
99 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL NULL
100 Warnings:
101 Note 1003 select `federated`.`t1`.`id` AS `id` from `federated`.`t1` where `federated`.`t1`.`id` < 5
102 EXPLAIN FORMAT=JSON
103 SELECT id FROM federated.t1 WHERE id < 5;
104 EXPLAIN
105 {
106 "query_block": {
107 "select_id": 1,
108 "table": {
109 "message": "Pushed select"
110 }
111 }
112 }
113 ANALYZE
114 SELECT id FROM federated.t1 WHERE id < 5;
115 id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
116 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
117 ANALYZE FORMAT=JSON
118 SELECT id FROM federated.t1 WHERE id < 5;
119 ANALYZE
120 {
121 "query_optimization": {
122 "r_total_time_ms": "REPLACED"
123 },
124 "query_block": {
125 "select_id": 1,
126 "table": {
127 "message": "Pushed select"
128 }
129 }
130 }
131 CREATE TABLE federated.t3 (
132 name varchar(16) NOT NULL default ''
133 )
134 DEFAULT CHARSET=latin1;
135 INSERT INTO federated.t3 VALUES
136 ('yyy'), ('www'), ('yyy'), ('xxx'), ('www'), ('yyy'), ('www');
137 SELECT *
138 FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
139 WHERE federated.t3.name=t.name;
140 name id name
141 yyy 5 yyy
142 yyy 7 yyy
143 yyy 5 yyy
144 yyy 7 yyy
145 xxx 4 xxx
146 yyy 5 yyy
147 yyy 7 yyy
148 EXPLAIN
149 SELECT *
150 FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
151 WHERE federated.t3.name=t.name;
152 id select_type table type possible_keys key key_len ref rows Extra
153 1 PRIMARY t3 ALL NULL NULL NULL NULL 7
154 1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 1
155 2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL
156 EXPLAIN FORMAT=JSON
157 SELECT *
158 FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
159 WHERE federated.t3.name=t.name;
160 EXPLAIN
161 {
162 "query_block": {
163 "select_id": 1,
164 "cost": "COST_REPLACED",
165 "nested_loop": [
166 {
167 "table": {
168 "table_name": "t3",
169 "access_type": "ALL",
170 "loops": 1,
171 "rows": 7,
172 "cost": "COST_REPLACED",
173 "filtered": 100
174 }
175 },
176 {
177 "table": {
178 "table_name": "<derived2>",
179 "access_type": "ref",
180 "possible_keys": ["key0"],
181 "key": "key0",
182 "key_length": "18",
183 "used_key_parts": ["name"],
184 "ref": ["federated.t3.name"],
185 "loops": 7,
186 "rows": 1,
187 "cost": "COST_REPLACED",
188 "filtered": 100,
189 "materialized": {
190 "query_block": {
191 "select_id": 2,
192 "table": {
193 "message": "Pushed derived"
194 }
195 }
196 }
197 }
198 }
199 ]
200 }
201 }
202 ANALYZE
203 SELECT *
204 FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
205 WHERE federated.t3.name=t.name;
206 id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
207 1 PRIMARY t3 ALL NULL NULL NULL NULL 7 7.00 100.00 100.00
208 1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 1 1.00 100.00 100.00
209 2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
210 SELECT *
211 FROM federated.t3, (SELECT t1.name FROM federated.t1
212 WHERE id IN (SELECT count(*)
213 FROM federated.t2 GROUP BY name)) t
214 WHERE federated.t3.name=t.name;
215 name name
216 xxx xxx
217 EXPLAIN
218 SELECT *
219 FROM federated.t3, (SELECT t1.name FROM federated.t1
220 WHERE id IN (SELECT count(*)
221 FROM federated.t2 GROUP BY name)) t
222 WHERE federated.t3.name=t.name;
223 id select_type table type possible_keys key key_len ref rows Extra
224 1 PRIMARY t3 ALL NULL NULL NULL NULL 7
225 1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 1
226 2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL
227 ANALYZE FORMAT=JSON
228 SELECT *
229 FROM federated.t3, (SELECT t1.name FROM federated.t1
230 WHERE id IN (SELECT count(*)
231 FROM federated.t2 GROUP BY name)) t
232 WHERE federated.t3.name=t.name;
233 ANALYZE
234 {
235 "query_optimization": {
236 "r_total_time_ms": "REPLACED"
237 },
238 "query_block": {
239 "select_id": 1,
240 "cost": "REPLACED",
241 "r_loops": 1,
242 "r_total_time_ms": "REPLACED",
243 "nested_loop": [
244 {
245 "table": {
246 "table_name": "t3",
247 "access_type": "ALL",
248 "loops": 1,
249 "r_loops": 1,
250 "rows": 7,
251 "r_rows": 7,
252 "cost": "REPLACED",
253 "r_table_time_ms": "REPLACED",
254 "r_other_time_ms": "REPLACED",
255 "r_engine_stats": REPLACED,
256 "filtered": 100,
257 "r_total_filtered": 100,
258 "r_filtered": 100
259 }
260 },
261 {
262 "table": {
263 "table_name": "<derived2>",
264 "access_type": "ref",
265 "possible_keys": ["key0"],
266 "key": "key0",
267 "key_length": "18",
268 "used_key_parts": ["name"],
269 "ref": ["federated.t3.name"],
270 "loops": 7,
271 "r_loops": 7,
272 "rows": 1,
273 "r_rows": 0.142857143,
274 "cost": "REPLACED",
275 "r_table_time_ms": "REPLACED",
276 "r_other_time_ms": "REPLACED",
277 "filtered": 100,
278 "r_total_filtered": 100,
279 "r_filtered": 100,
280 "materialized": {
281 "query_block": {
282 "select_id": 2,
283 "table": {
284 "message": "Pushed derived"
285 }
286 }
287 }
288 }
289 }
290 ]
291 }
292 }
293 SELECT t.id, federated.t3.name
294 FROM federated.t3,
295 ( SELECT * FROM federated.t1 WHERE id < 3
296 UNION
297 SELECT * FROM federated.t1 WHERE id >= 5) t
298 WHERE federated.t3.name=t.name;
299 id name
300 5 yyy
301 7 yyy
302 5 yyy
303 7 yyy
304 5 yyy
305 7 yyy
306 EXPLAIN
307 SELECT t.id, federated.t3.name
308 FROM federated.t3,
309 ( SELECT * FROM federated.t1 WHERE id < 3
310 UNION
311 SELECT * FROM federated.t1 WHERE id >= 5) t
312 WHERE federated.t3.name=t.name;
313 id select_type table type possible_keys key key_len ref rows Extra
314 1 PRIMARY t3 ALL NULL NULL NULL NULL 7
315 1 PRIMARY <derived2> ref key1,distinct_key key1 18 federated.t3.name 1
316 2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL
317 #
318 # MDEV-21887: federatedx crashes on SELECT ... INTO query in select_handler code
319 #
320 CREATE TABLE federated.t4 (
321 id int(20) NOT NULL,
322 name varchar(16) NOT NULL default ''
323 ) engine=myisam;
324 insert into federated.t4 select * from federated.t1;
325 select * from federated.t4;
326 id name
327 1 zzz
328 3 xxx
329 4 xxx
330 5 yyy
331 7 yyy
332 select name into @var from federated.t1 where id=3 limit 1 ;
333 select @var;
334 @var
335 xxx
336 select name into outfile 'tmp.txt' from federated.t1;
337 #
338 # MDEV-22993: Crash on EXPLAIN with PUSHED DOWN SELECT and subquery
339 #
340 explain
341 select * from federated.t1
342 where name in (select name from federated.t2);
343 id select_type table type possible_keys key key_len ref rows Extra
344 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
345 explain format=json
346 select * from federated.t1
347 where name in (select name from federated.t2);
348 EXPLAIN
349 {
350 "query_block": {
351 "select_id": 1,
352 "table": {
353 "message": "Pushed select"
354 }
355 }
356 }
357 #
358 # MDEV-22993, testcase #2: EXPLAIN output doesn't make sense when
359 # derived table pushdown is used.
360 #
361 create table t5 (a int) engine=myisam;
362 insert into t5 values (1),(2);
363 # Must not show lines with id=3
364 explain
365 select * from t5,
366 (select id from federated.t1
367 where name in (select name from federated.t2) or name like 'foo%') as TQ;
368 id select_type table type possible_keys key key_len ref rows Extra
369 1 PRIMARY t5 ALL NULL NULL NULL NULL 2
370 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
371 2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL
372 # Must not show elements with select_id=3
373 explain format=json
374 select * from t5,
375 (select id from federated.t1
376 where name in (select name from federated.t2) or name like 'foo%') as TQ;
377 EXPLAIN
378 {
379 "query_block": {
380 "select_id": 1,
381 "cost": "COST_REPLACED",
382 "nested_loop": [
383 {
384 "table": {
385 "table_name": "t5",
386 "access_type": "ALL",
387 "loops": 1,
388 "rows": 2,
389 "cost": "COST_REPLACED",
390 "filtered": 100
391 }
392 },
393 {
394 "block-nl-join": {
395 "table": {
396 "table_name": "<derived2>",
397 "access_type": "ALL",
398 "loops": 2,
399 "rows": 5,
400 "cost": "COST_REPLACED",
401 "filtered": 100
402 },
403 "buffer_type": "flat",
404 "buffer_size": "65",
405 "join_type": "BNL",
406 "materialized": {
407 "query_block": {
408 "select_id": 2,
409 "table": {
410 "message": "Pushed derived"
411 }
412 }
413 }
414 }
415 }
416 ]
417 }
418 }
419 drop table t5;
420 DROP TABLE federated.t1, federated.t2, federated.t3, federated.t4;
421 connection slave;
422 DROP TABLE federated.t1, federated.t2;
423 connection default;
424 #
425 # MDEV-23778: Derived handler used for big derived tables
426 #
427 connection slave;
428 CREATE TABLE federated.t1 (
429 a varchar(100) NOT NULL default '123'
430 )
431 DEFAULT CHARSET=latin1;
432 CREATE TABLE federated.t2 LIKE federated.t1;
433 BEGIN NOT ATOMIC
434 DECLARE i INT DEFAULT 0;
435 START TRANSACTION;
436 WHILE i < 70000 DO
437 INSERT INTO federated.t1 VALUES (i);
438 SET i = i + 1;
439 END WHILE;
440 COMMIT;
441 END
442 $$
443 connection master;
444 CREATE TABLE federated.t1 (
445 a varchar(100) NOT NULL default '123'
446 )
447 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
448 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t1';
449 CREATE TABLE federated.t2 (
450 a varchar(100) NOT NULL default '123'
451 )
452 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
453 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t2';
454 SELECT COUNT(DISTINCT a) FROM federated.t1;
455 COUNT(DISTINCT a)
456 70000
457 INSERT INTO federated.t2
458 SELECT * FROM (SELECT * FROM federated.t1 LIMIT 100) dt;
459 SELECT COUNT(DISTINCT a) FROM federated.t2;
460 COUNT(DISTINCT a)
461 100
462 TRUNCATE TABLE federated.t2;
463 INSERT INTO federated.t2
464 SELECT * FROM (SELECT * FROM federated.t1 LIMIT 70000) dt;
465 SELECT COUNT(DISTINCT a) FROM federated.t2;
466 COUNT(DISTINCT a)
467 70000
468 #
469 # MDEV-29640 FederatedX does not properly handle pushdown
470 # in case of difference in local and remote table names
471 #
472 connection master;
473 # Use tables from the previous test. Make sure pushdown works:
474 EXPLAIN SELECT COUNT(DISTINCT a) FROM federated.t2;
475 id select_type table type possible_keys key key_len ref rows Extra
476 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
477 SELECT COUNT(DISTINCT a) FROM federated.t2;
478 COUNT(DISTINCT a)
479 70000
480 # Link remote table `federated.t1` with the local table named `t1_local`
481 CREATE TABLE federated.t1_local ENGINE="FEDERATED"
482 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t1';
483 # No pushdown here due to table names mismatch, retrieve data as usual:
484 EXPLAIN SELECT COUNT(DISTINCT a) FROM federated.t1_local;
485 id select_type table type possible_keys key key_len ref rows Extra
486 1 SIMPLE t1_local ALL NULL NULL NULL NULL 70000
487 SELECT COUNT(DISTINCT a) FROM federated.t1_local;
488 COUNT(DISTINCT a)
489 70000
490 #
491 # MDEV-29863 Server crashes in federatedx_txn::acquire after select from
492 # the Federated table with partitions and federated_pushdown=1
493 # in case of difference in local and remote table names
494 #
495 connection slave;
496 CREATE TABLE federated.t3 (a INT);
497 INSERT INTO federated.t3 VALUES (1),(2),(3);
498 CREATE TABLE federated.t4 (a INT);
499 connection master;
500 CREATE SERVER fedlink FOREIGN DATA WRAPPER mariadb
501 OPTIONS (USER 'root', HOST '127.0.0.1', DATABASE 'federated',
502 PORT SLAVE_PORT);
503 CREATE TABLE federated.t3 (a INT)
504 ENGINE=FEDERATED
505 CONNECTION='mariadb://[email protected]:$SLAVE_MYPORT/federated/t3'
506 PARTITION BY list (a)
507 (PARTITION p1 VALUES IN (1) CONNECTION='fedlink/t3',
508 PARTITION p2 VALUES IN (2) CONNECTION='fedlink/t4');
509 EXPLAIN SELECT * FROM federated.t3;
510 id select_type table type possible_keys key key_len ref rows Extra
511 1 SIMPLE t3 ALL NULL NULL NULL NULL 3
512 SELECT * FROM federated.t3;
513 a
514 1
515 2
516 3
517 DROP SERVER fedlink;
518 #
519 # MDEV-29655: ASAN heap-use-after-free in
520 # Pushdown_derived::Pushdown_derived
521 #
522 connection slave;
523 DROP TABLE IF EXISTS federated.t1;
524 CREATE TABLE federated.t1 (
525 id int(20) NOT NULL,
526 name varchar(16) NOT NULL default ''
527 )
528 DEFAULT CHARSET=latin1;
529 INSERT INTO federated.t1 VALUES
530 (3,'xxx'), (7,'yyy'), (4,'xxx'), (1,'zzz'), (5,'yyy');
531 connection master;
532 DROP TABLE IF EXISTS federated.t1;
533 CREATE TABLE federated.t1 (
534 id int(20) NOT NULL,
535 name varchar(16) NOT NULL default ''
536 )
537 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
538 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t1';
539 use federated;
540 SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
541 WHERE id=2) dt2) dt;
542 id name
543 PREPARE stmt FROM "
544 SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
545 WHERE id=3) dt2) dt;
546 ";
547 EXECUTE stmt;
548 id name
549 3 xxx
550 EXECUTE stmt;
551 id name
552 3 xxx
553 DEALLOCATE PREPARE stmt;
554 EXPLAIN
555 SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
556 WHERE id=3) dt2) dt;
557 id select_type table type possible_keys key key_len ref rows Extra
558 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
559 connection slave;
560 CREATE TABLE federated.t10 (a INT,b INT);
561 CREATE TABLE federated.t11 (a INT, b INT);
562 INSERT INTO federated.t10 VALUES (1,1),(2,2);
563 INSERT INTO federated.t11 VALUES (1,1),(2,2);
564 connection master;
565 CREATE TABLE federated.t10
566 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
567 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t10';
568 CREATE TABLE federated.t11
569 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
570 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t11';
571 use federated;
572 SELECT * FROM t10 LEFT JOIN
573 (t11, (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
574 WHERE id=2) dt2) dt
575 ) ON t10.a=t11.a;
576 a b a b id name
577 1 1 NULL NULL NULL NULL
578 2 2 NULL NULL NULL NULL
579 #
580 # MDEV-31361: Second execution of PS for query with derived table
581 #
582 connection slave;
583 DROP TABLE IF EXISTS federated.t1;
584 CREATE TABLE federated.t1 (
585 id int(20) NOT NULL,
586 name varchar(16) NOT NULL default ''
587 )
588 DEFAULT CHARSET=latin1;
589 INSERT INTO federated.t1 VALUES
590 (3,'xxx'), (7,'yyy'), (4,'xxx'), (1,'zzz'), (5,'yyy');
591 connection master;
592 DROP TABLE IF EXISTS federated.t1;
593 CREATE TABLE federated.t1 (
594 id int(20) NOT NULL,
595 name varchar(16) NOT NULL default ''
596 )
597 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
598 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t1';
599 use federated;
600 SELECT * FROM
601 (SELECT * FROM
602 (SELECT * FROM
603 (SELECT * FROM t1 where id>3) dt3
604 WHERE id>3) dt2
605 ) dt;
606 id name
607 7 yyy
608 4 xxx
609 5 yyy
610 PREPARE stmt FROM "SELECT * FROM
611 (SELECT * FROM
612 (SELECT * FROM
613 (SELECT * FROM t1 where id>3) dt3
614 WHERE id>3) dt2
615 ) dt";
616 EXECUTE stmt;
617 id name
618 7 yyy
619 4 xxx
620 5 yyy
621 EXECUTE stmt;
622 id name
623 7 yyy
624 4 xxx
625 5 yyy
626 DEALLOCATE PREPARE stmt;
627 #
628 # FederatedX error 10000 on multi-table UPDATE/DELETE
629 #
630 connection slave;
631 DROP TABLE IF EXISTS federated.t1, federated.t2;
632 CREATE TABLE federated.t1 (a int, b int);
633 INSERT INTO federated.t1 VALUES (1,1), (2,2), (3,3);
634 CREATE TABLE federated.t2 (a int, b int);
635 INSERT INTO federated.t2 VALUES (1,1), (2,2), (4,4);
636 connection master;
637 DROP TABLE IF EXISTS federated.t1, federated.t2;
638 CREATE TABLE federated.t1 (a int, b int)
639 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
640 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t1';
641 CREATE TABLE federated.t2 (a int, b int)
642 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
643 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t2';
644 use federated;
645 # Multi-table UPDATE
646 UPDATE t1, t2 SET t1.a = 2 WHERE t1.a=t2.a;
647 # Check the result
648 SELECT * FROM t1;
649 a b
650 2 1
651 2 2
652 3 3
653 # Multi-table DELETE
654 DELETE FROM t1 USING t1 JOIN t2 ON t1.a = t2.a WHERE t2.b > 1;
655 SELECT * FROM t1;
656 a b
657 3 3
658 # Another form of multi-table DELETE
659 DELETE FROM a1 USING t1 AS a1;
660 SELECT * FROM t1;
661 a b
662 DROP TABLES federated.t1, federated.t2, federated.t3, federated.t10,
663 federated.t11;
664 connection slave;
665 DROP TABLES federated.t1, federated.t2, federated.t3, federated.t10,
666 federated.t11;
667 # MDEV-25080: Allow pushdown of queries involving UNIONs
668 # in outer select to foreign engines
669 #
670 connection slave;
671 CREATE TABLE federated.t1 (
672 a varchar(10)
673 )
674 DEFAULT CHARSET=latin1;
675 CREATE TABLE federated.t2 (
676 a varchar(16) NOT NULL default ''
677 )
678 DEFAULT CHARSET=latin1;
679 INSERT INTO federated.t1 VALUES ('bcd'), ('abc'), ('cde');
680 INSERT INTO federated.t2 VALUES ('cde'), ('efg'), ('abc'), ('bcd'), ('def');
681 connection master;
682 CREATE TABLE federated.t1 (
683 a varchar(10)
684 )
685 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
686 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t1';
687 CREATE TABLE federated.t2 (
688 a varchar(16) NOT NULL default ''
689 )
690 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
691 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t2';
692 CREATE TABLE t3 (a varchar(30)) ENGINE=MyISAM;
693 CREATE TABLE t4 (a varchar(30)) ENGINE=MyISAM;
694 INSERT INTO t3 VALUES ('t3_myisam1'), ('t3_myisam2'), ('t3_myisam3');
695 INSERT INTO t4 VALUES ('t4_myisam1'), ('t4_myisam2'), ('t4_myisam3');
696 # Pushdown of the whole UNION
697 SELECT * from federated.t1 UNION SELECT * from federated.t2;
698 a
699 abc
700 bcd
701 cde
702 def
703 efg
704 EXPLAIN SELECT * from federated.t1 UNION SELECT * from federated.t2;
705 id select_type table type possible_keys key key_len ref rows Extra
706 NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL
707 # Pushdown of a part of the UNION
708 SELECT * from federated.t1 UNION SELECT * from t3;
709 a
710 abc
711 bcd
712 cde
713 t3_myisam1
714 t3_myisam2
715 t3_myisam3
716 EXPLAIN SELECT * from federated.t1 UNION SELECT * from t3;
717 id select_type table type possible_keys key key_len ref rows Extra
718 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
719 2 UNION t3 ALL NULL NULL NULL NULL 3
720 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
721 SELECT * from federated.t1 UNION ALL SELECT * from federated.t2;
722 a
723 abc
724 abc
725 bcd
726 bcd
727 cde
728 cde
729 def
730 efg
731 EXPLAIN SELECT * from federated.t1 UNION ALL SELECT * from federated.t2;
732 id select_type table type possible_keys key key_len ref rows Extra
733 NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL
734 EXPLAIN FORMAT=JSON SELECT * from federated.t1 UNION ALL
735 SELECT * from federated.t2;
736 EXPLAIN
737 {
738 "query_block": {
739 "union_result": {
740 "message": "PUSHED UNION"
741 }
742 }
743 }
744 ANALYZE SELECT * from federated.t1 UNION ALL SELECT * from federated.t2;
745 id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
746 NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
747 ANALYZE FORMAT=JSON SELECT * from federated.t1 UNION ALL
748 SELECT * from federated.t2;
749 ANALYZE
750 {
751 "query_optimization": {
752 "r_total_time_ms": "REPLACED"
753 },
754 "query_block": {
755 "union_result": {
756 "message": "PUSHED UNION"
757 }
758 }
759 }
760 SELECT * from federated.t1 EXCEPT SELECT * from federated.t2;
761 a
762 EXPLAIN EXTENDED SELECT * from federated.t1 EXCEPT
763 SELECT * from federated.t2;
764 id select_type table type possible_keys key key_len ref rows filtered Extra
765 NULL PUSHED EXCEPT NULL NULL NULL NULL NULL NULL NULL NULL NULL
766 Warnings:
767 Note 1003 /* select#1 */ select `federated`.`t1`.`a` AS `a` from `federated`.`t1` except /* select#2 */ select `federated`.`t2`.`a` AS `a` from `federated`.`t2`
768 EXPLAIN FORMAT=JSON SELECT * from federated.t1 EXCEPT
769 SELECT * from federated.t2;
770 EXPLAIN
771 {
772 "query_block": {
773 "union_result": {
774 "message": "PUSHED EXCEPT"
775 }
776 }
777 }
778 SELECT * from federated.t1 INTERSECT SELECT * from federated.t2;
779 a
780 abc
781 bcd
782 cde
783 EXPLAIN PARTITIONS SELECT * from federated.t1 INTERSECT
784 SELECT * from federated.t2;
785 id select_type table partitions type possible_keys key key_len ref rows Extra
786 NULL PUSHED INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL NULL
787 EXPLAIN FORMAT=JSON SELECT * from federated.t1 INTERSECT
788 SELECT * from federated.t2;
789 EXPLAIN
790 {
791 "query_block": {
792 "union_result": {
793 "message": "PUSHED INTERSECT"
794 }
795 }
796 }
797 # More than two SELECTs in a UNIT:
798 SELECT * from federated.t1 INTERSECT
799 SELECT * from federated.t2 UNION ALL
800 SELECT * from federated.t2 EXCEPT
801 SELECT * from federated.t1;
802 a
803 def
804 efg
805 EXPLAIN
806 SELECT count(*) from federated.t1 INTERSECT
807 SELECT count(*) from federated.t2 UNION ALL
808 SELECT count(*)+20 from federated.t2 EXCEPT
809 SELECT count(*)+5 from federated.t1;
810 id select_type table type possible_keys key key_len ref rows Extra
811 NULL PUSHED UNIT NULL NULL NULL NULL NULL NULL NULL NULL
812 EXPLAIN FORMAT=JSON
813 SELECT count(*) from federated.t1 INTERSECT
814 SELECT count(*) from federated.t2 UNION ALL
815 SELECT count(*)+20 from federated.t2 EXCEPT
816 SELECT count(*)+5 from federated.t1;
817 EXPLAIN
818 {
819 "query_block": {
820 "union_result": {
821 "message": "PUSHED UNIT"
822 }
823 }
824 }
825 ANALYZE
826 SELECT count(*) from federated.t1 INTERSECT
827 SELECT count(*) from federated.t2 UNION
828 SELECT count(*)+20 from federated.t2 EXCEPT
829 SELECT count(*)+5 from federated.t1;
830 id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
831 NULL PUSHED UNIT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
832 # UNION inside a derived table: the whole derived table must be pushed
833 SELECT * FROM
834 (SELECT * FROM federated.t1 UNION ALL SELECT * FROM federated.t2) q;
835 a
836 abc
837 abc
838 bcd
839 bcd
840 cde
841 cde
842 def
843 efg
844 EXPLAIN
845 SELECT * FROM
846 (SELECT a FROM federated.t1 UNION ALL SELECT * FROM federated.t2) q;
847 id select_type table type possible_keys key key_len ref rows Extra
848 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
849 # There is an uncacheable side effect due to fetch into @var,
850 # so the UNION cannot be pushed down as a whole.
851 # But separate SELECTs can be pushed, and the results are combined
852 # at the server side
853 SELECT count(*) FROM federated.t1 UNION
854 SELECT count(*) FROM federated.t1 EXCEPT
855 SELECT count(*)+1 FROM federated.t1
856 INTO @var;
857 EXPLAIN SELECT count(*) FROM federated.t1 UNION
858 SELECT count(*) FROM federated.t2 EXCEPT
859 SELECT count(*)+1 FROM federated.t1
860 INTO @var;
861 id select_type table type possible_keys key key_len ref rows Extra
862 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
863 2 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
864 3 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
865 NULL UNIT RESULT <unit1,2,3> ALL NULL NULL NULL NULL NULL
866 EXPLAIN FORMAT=JSON SELECT count(*) FROM federated.t1 UNION
867 SELECT count(*) FROM federated.t2 EXCEPT
868 SELECT count(*)+2 FROM federated.t2
869 INTO @var;
870 EXPLAIN
871 {
872 "query_block": {
873 "union_result": {
874 "table_name": "<unit1,2,3>",
875 "access_type": "ALL",
876 "query_specifications": [
877 {
878 "query_block": {
879 "select_id": 1,
880 "table": {
881 "message": "Pushed select"
882 }
883 }
884 },
885 {
886 "query_block": {
887 "select_id": 2,
888 "operation": "UNION",
889 "table": {
890 "message": "Pushed select"
891 }
892 }
893 },
894 {
895 "query_block": {
896 "select_id": 3,
897 "operation": "EXCEPT",
898 "table": {
899 "message": "Pushed select"
900 }
901 }
902 }
903 ]
904 }
905 }
906 }
907 # Prepared statements
908 PREPARE stmt FROM "SELECT * from federated.t1 INTERSECT
909 SELECT * from federated.t2 UNION ALL
910 SELECT * from federated.t2 EXCEPT
911 SELECT * from federated.t1";
912 EXECUTE stmt;
913 a
914 def
915 efg
916 EXECUTE stmt;
917 a
918 def
919 efg
920 EXECUTE stmt;
921 a
922 def
923 efg
924 PREPARE stmt FROM "EXPLAIN SELECT * from federated.t1 INTERSECT
925 SELECT * from federated.t2 UNION ALL
926 SELECT * from federated.t2 EXCEPT
927 SELECT * from federated.t1";
928 EXECUTE stmt;
929 id select_type table type possible_keys key key_len ref rows Extra
930 NULL PUSHED UNIT NULL NULL NULL NULL NULL NULL NULL NULL
931 EXECUTE stmt;
932 id select_type table type possible_keys key key_len ref rows Extra
933 NULL PUSHED UNIT NULL NULL NULL NULL NULL NULL NULL NULL
934 # UNIONs of mixed Federated/MyISAM tables, pushing parts of UNIONs
935 SELECT * FROM federated.t1 UNION SELECT * FROM t3;
936 a
937 abc
938 bcd
939 cde
940 t3_myisam1
941 t3_myisam2
942 t3_myisam3
943 EXPLAIN SELECT * FROM federated.t1 UNION SELECT * FROM t3;
944 id select_type table type possible_keys key key_len ref rows Extra
945 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
946 2 UNION t3 ALL NULL NULL NULL NULL 3
947 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
948 SELECT * FROM federated.t1 UNION ALL
949 SELECT * FROM t3 EXCEPT
950 SELECT * FROM federated.t2;
951 a
952 t3_myisam1
953 t3_myisam2
954 t3_myisam3
955 EXPLAIN SELECT * FROM federated.t1 UNION ALL
956 SELECT * FROM t3 EXCEPT
957 SELECT * FROM federated.t2;
958 id select_type table type possible_keys key key_len ref rows Extra
959 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
960 2 UNION t3 ALL NULL NULL NULL NULL 3
961 3 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
962 NULL UNIT RESULT <unit1,2,3> ALL NULL NULL NULL NULL NULL
963 SELECT * FROM t3 UNION ALL
964 SELECT * FROM federated.t1 EXCEPT
965 SELECT * FROM t4 INTERSECT
966 SELECT * FROM federated.t2;
967 a
968 abc
969 bcd
970 cde
971 t3_myisam1
972 t3_myisam2
973 t3_myisam3
974 EXPLAIN SELECT * FROM t3 UNION ALL
975 SELECT * FROM federated.t1 EXCEPT
976 SELECT * FROM t4 INTERSECT
977 SELECT * FROM federated.t2;
978 id select_type table type possible_keys key key_len ref rows Extra
979 1 PRIMARY t3 ALL NULL NULL NULL NULL 3
980 2 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
981 5 EXCEPT <derived3> ALL NULL NULL NULL NULL 3
982 3 DERIVED t4 ALL NULL NULL NULL NULL 3
983 4 INTERSECT t2 ALL NULL NULL NULL NULL 5
984 NULL INTERSECT RESULT <intersect3,4> ALL NULL NULL NULL NULL NULL
985 NULL UNIT RESULT <unit1,2,5> ALL NULL NULL NULL NULL NULL
986 SELECT * FROM federated.t2 UNION ALL
987 SELECT * FROM t3 EXCEPT
988 SELECT * FROM t4 INTERSECT
989 SELECT * FROM federated.t1;
990 a
991 abc
992 bcd
993 cde
994 def
995 efg
996 t3_myisam1
997 t3_myisam2
998 t3_myisam3
999 EXPLAIN SELECT * FROM federated.t2 UNION ALL
1000 SELECT * FROM t3 EXCEPT
1001 SELECT * FROM t4 INTERSECT
1002 SELECT * FROM federated.t1;
1003 id select_type table type possible_keys key key_len ref rows Extra
1004 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
1005 2 UNION t3 ALL NULL NULL NULL NULL 3
1006 5 EXCEPT <derived3> ALL NULL NULL NULL NULL 3
1007 3 DERIVED t4 ALL NULL NULL NULL NULL 3
1008 4 INTERSECT t1 ALL NULL NULL NULL NULL 3
1009 NULL INTERSECT RESULT <intersect3,4> ALL NULL NULL NULL NULL NULL
1010 NULL UNIT RESULT <unit1,2,5> ALL NULL NULL NULL NULL NULL
1011 # Parenthesis must not prevent the whole UNIONs pushdown
1012 EXPLAIN (SELECT * FROM federated.t1 UNION
1013 SELECT * FROM federated.t2) UNION ALL
1014 SELECT * FROM federated.t1;
1015 id select_type table type possible_keys key key_len ref rows Extra
1016 NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL
1017 (SELECT * FROM federated.t1 UNION
1018 SELECT * FROM federated.t2) UNION ALL
1019 SELECT * FROM federated.t1;
1020 a
1021 abc
1022 abc
1023 bcd
1024 bcd
1025 cde
1026 cde
1027 def
1028 efg
1029 EXPLAIN (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2)
1030 UNION ALL (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2);
1031 id select_type table type possible_keys key key_len ref rows Extra
1032 NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL
1033 (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2) UNION ALL
1034 (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2);
1035 a
1036 abc
1037 abc
1038 bcd
1039 bcd
1040 cde
1041 cde
1042 def
1043 def
1044 efg
1045 efg
1046 # Union of tables containing different INT data types
1047 connection slave;
1048 CREATE TABLE federated.t11 (a smallint(6) NOT NULL);
1049 INSERT INTO federated.t11 VALUES (-32678), (-1), (0);
1050 CREATE TABLE federated.t12 (a int(10) UNSIGNED NOT NULL);
1051 INSERT INTO federated.t12 VALUES (0), (1), (32767);
1052 connection master;
1053 CREATE TABLE federated.t11 (a smallint(6) NOT NULL)
1054 ENGINE="FEDERATED"
1055 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t11';
1056 CREATE TABLE federated.t12 (a int(10) UNSIGNED NOT NULL)
1057 ENGINE="FEDERATED"
1058 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t12';
1059 # Entire UNION pushdown
1060 SELECT a FROM federated.t12 UNION ALL SELECT a FROM federated.t11;
1061 a
1062 -1
1063 -32678
1064 0
1065 0
1066 1
1067 32767
1068 EXPLAIN SELECT a FROM federated.t12 UNION ALL SELECT a FROM federated.t11;
1069 id select_type table type possible_keys key key_len ref rows Extra
1070 NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL
1071 SELECT a FROM federated.t11 UNION SELECT a FROM federated.t12;
1072 a
1073 -1
1074 -32678
1075 0
1076 1
1077 32767
1078 # Partial pushdown of SELECTs composing the UNION
1079 SELECT a FROM federated.t12 UNION SELECT a FROM federated.t11 UNION SELECT 123;
1080 a
1081 -1
1082 -32678
1083 0
1084 1
1085 123
1086 32767
1087 EXPLAIN
1088 SELECT a FROM federated.t12 UNION SELECT a FROM federated.t11
1089 UNION SELECT 123;
1090 id select_type table type possible_keys key key_len ref rows Extra
1091 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
1092 2 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
1093 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
1094 NULL UNION RESULT <union1,2,3> ALL NULL NULL NULL NULL NULL
1095 SELECT a FROM federated.t12 EXCEPT
1096 SELECT 1 UNION ALL
1097 SELECT a FROM federated.t11 EXCEPT
1098 SELECT 0;
1099 a
1100 -1
1101 -32678
1102 32767
1103 # Union of tables containing different string data types
1104 connection slave;
1105 CREATE TABLE federated.t13 (a CHAR(6));
1106 INSERT INTO federated.t13 VALUES ('t13abc'), ('t13xx'), ('common');
1107 CREATE TABLE federated.t14 (a VARCHAR(8));
1108 INSERT INTO federated.t14 VALUES ('t14abcde'), ('t14xyzzz'), ('common');
1109 connection master;
1110 CREATE TABLE federated.t13 (a CHAR(6))
1111 ENGINE="FEDERATED"
1112 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t13';
1113 CREATE TABLE federated.t14 (a VARCHAR(8))
1114 ENGINE="FEDERATED"
1115 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t14';
1116 SELECT * FROM federated.t13 UNION SELECT * FROM federated.t14;
1117 a
1118 common
1119 t13abc
1120 t13xx
1121 t14abcde
1122 t14xyzzz
1123 EXPLAIN SELECT * FROM federated.t13 UNION SELECT * FROM federated.t14;
1124 id select_type table type possible_keys key key_len ref rows Extra
1125 NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL
1126 SELECT * FROM federated.t14 UNION ALL SELECT * FROM federated.t13;
1127 a
1128 common
1129 common
1130 t13abc
1131 t13xx
1132 t14abcde
1133 t14xyzzz
1134 SELECT * FROM federated.t14 UNION
1135 SELECT * FROM federated.t13 UNION
1136 SELECT '123456789000';
1137 a
1138 t14abcde
1139 t14xyzzz
1140 common
1141 t13abc
1142 t13xx
1143 123456789000
1144 EXPLAIN SELECT * FROM federated.t14 UNION
1145 SELECT * FROM federated.t13 UNION
1146 SELECT '123456789000';
1147 id select_type table type possible_keys key key_len ref rows Extra
1148 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
1149 2 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
1150 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
1151 NULL UNION RESULT <union1,2,3> ALL NULL NULL NULL NULL NULL
1152 SELECT * FROM federated.t13 UNION
1153 SELECT '123456789000' UNION
1154 SELECT * FROM federated.t14;
1155 a
1156 123456789000
1157 common
1158 t13abc
1159 t13xx
1160 t14abcde
1161 t14xyzzz
1162 # CREATE TABLE .. AS from a pushed UNION
1163 CREATE TABLE t5 AS SELECT * FROM federated.t13 UNION
1164 SELECT * FROM federated.t14;
1165 SHOW CREATE TABLE t5;
1166 Table Create Table
1167 t5 CREATE TABLE `t5` (
1168 `a` varchar(8) DEFAULT NULL
1169 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
1170 SELECT * FROM t5;
1171 a
1172 common
1173 t13abc
1174 t13xx
1175 t14abcde
1176 t14xyzzz
1177 CREATE TABLE t6 AS SELECT a FROM federated.t12 EXCEPT
1178 SELECT 1 UNION ALL
1179 SELECT a FROM federated.t11 EXCEPT
1180 SELECT 0;
1181 SHOW CREATE TABLE t6;
1182 Table Create Table
1183 t6 CREATE TABLE `t6` (
1184 `a` decimal(10,0) NOT NULL DEFAULT 0
1185 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
1186 SELECT * FROM t6;
1187 a
1188 -1
1189 -32678
1190 32767
1191 #
1192 # MDEV-30828 ORDER BY clause using an integer (positional argument)
1193 #
1194 SELECT a FROM federated.t1 UNION SELECT a FROM federated.t2 ORDER BY 1;
1195 a
1196 abc
1197 bcd
1198 cde
1199 def
1200 efg
1201 SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY a DESC;
1202 a
1203 efg
1204 def
1205 cde
1206 cde
1207 bcd
1208 bcd
1209 abc
1210 abc
1211 # Check handling of incorrect ORDER BY clause
1212 SELECT a FROM federated.t1 UNION SELECT a FROM federated.t2 ORDER BY 2;
1213 ERROR 42S22: Unknown column '2' in 'ORDER BY'
1214 PREPARE stmt FROM
1215 "SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 2";
1216 ERROR 42S22: Unknown column '2' in 'ORDER BY'
1217 SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 2,1,3;
1218 ERROR 42S22: Unknown column '2' in 'ORDER BY'
1219 SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY t1.a;
1220 ERROR 42000: Table 't1' from one of the SELECTs cannot be used in ORDER BY
1221 SELECT * from federated.t1 INTERSECT
1222 SELECT * from federated.t2 UNION ALL
1223 SELECT * from federated.t2 EXCEPT
1224 SELECT * from federated.t1
1225 ORDER BY 1;
1226 a
1227 def
1228 efg
1229 SELECT * from federated.t1 INTERSECT
1230 SELECT * from federated.t2 UNION ALL
1231 SELECT * from federated.t2 EXCEPT
1232 SELECT * from federated.t1
1233 ORDER BY 3;
1234 ERROR 42S22: Unknown column '3' in 'ORDER BY'
1235 # UNION of mixed Federated/MyISAM tables, pushing parts of UNIONs
1236 SELECT * FROM federated.t1 UNION SELECT * FROM t3 ORDER BY a;
1237 a
1238 abc
1239 bcd
1240 cde
1241 t3_myisam1
1242 t3_myisam2
1243 t3_myisam3
1244 SELECT * FROM federated.t1 UNION SELECT * FROM t3 ORDER BY 2;
1245 ERROR 42S22: Unknown column '2' in 'ORDER BY'
1246 #
1247 # MDEV-32382 FederatedX error on pushdown of statement having CTE
1248 #
1249 # Single SELECT with CTE
1250 WITH cte AS (SELECT * FROM federated.t1)
1251 SELECT * FROM cte;
1252 a
1253 bcd
1254 abc
1255 cde
1256 explain extended WITH cte AS (SELECT * FROM federated.t1)
1257 SELECT * FROM cte;
1258 id select_type table type possible_keys key key_len ref rows filtered Extra
1259 1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL NULL
1260 Warnings:
1261 Note 1003 with cte as (/* select#2 */ select `federated`.`t1`.`a` AS `a` from `federated`.`t1`)/* select#1 */ select `cte`.`a` AS `a` from `cte`
1262 # Pushdown of a UNION having CTE's
1263 WITH cte AS (SELECT * FROM federated.t1),
1264 cte2 AS (SELECT * FROM federated.t2)
1265 SELECT * FROM cte
1266 UNION
1267 SELECT * FROM cte2;
1268 a
1269 abc
1270 bcd
1271 cde
1272 def
1273 efg
1274 explain extended WITH cte AS (SELECT * FROM federated.t1),
1275 cte2 AS (SELECT * FROM federated.t2)
1276 SELECT * FROM cte
1277 UNION
1278 SELECT * FROM cte2;
1279 id select_type table type possible_keys key key_len ref rows filtered Extra
1280 NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL
1281 Warnings:
1282 Note 1003 with cte as (/* select#2 */ select `federated`.`t1`.`a` AS `a` from `federated`.`t1`), cte2 as (/* select#3 */ select `federated`.`t2`.`a` AS `a` from `federated`.`t2`)/* select#1 */ select `cte`.`a` AS `a` from `cte` union /* select#4 */ select `cte2`.`a` AS `a` from `cte2`
1283 # Partial pushdown is not allowed for unions with CTE's, however a CTE
1284 # may be pushed down a derived table
1285 WITH cte AS (SELECT * FROM federated.t1)
1286 SELECT * FROM cte
1287 UNION ALL
1288 SELECT * FROM t3;
1289 a
1290 abc
1291 bcd
1292 cde
1293 t3_myisam1
1294 t3_myisam2
1295 t3_myisam3
1296 explain extended WITH cte AS (SELECT * FROM federated.t1)
1297 SELECT * FROM cte
1298 UNION ALL
1299 SELECT * FROM t3;
1300 id select_type table type possible_keys key key_len ref rows filtered Extra
1301 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00
1302 2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL
1303 3 UNION t3 ALL NULL NULL NULL NULL 3 100.00
1304 Warnings:
1305 Note 1003 with cte as (/* select#2 */ select `federated`.`t1`.`a` AS `a` from `federated`.`t1`)/* select#1 */ select `cte`.`a` AS `a` from `cte` union all /* select#3 */ select `federated`.`t3`.`a` AS `a` from `federated`.`t3`
1306 WITH cte AS (SELECT * FROM federated.t1 UNION SELECT * FROM t3)
1307 SELECT * FROM cte;
1308 a
1309 abc
1310 bcd
1311 cde
1312 t3_myisam1
1313 t3_myisam2
1314 t3_myisam3
1315 explain extended WITH cte AS (SELECT * FROM federated.t1 UNION SELECT * FROM t3)
1316 SELECT * FROM cte;
1317 id select_type table type possible_keys key key_len ref rows filtered Extra
1318 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00
1319 2 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00
1320 3 UNION t3 ALL NULL NULL NULL NULL 3 100.00
1321 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
1322 Warnings:
1323 Note 1003 with cte as (/* select#2 */ select `federated`.`t1`.`a` AS `a` from `federated`.`t1` union /* select#3 */ select `federated`.`t3`.`a` AS `a` from `federated`.`t3`)/* select#1 */ select `cte`.`a` AS `a` from `cte`
1324 # Two CTE's where one CTE refers to another
1325 WITH cte AS (SELECT * FROM federated.t1),
1326 cte2 AS (SELECT * FROM t3
1327 WHERE t3.a NOT IN (SELECT * FROM cte))
1328 SELECT * FROM cte JOIN cte2;
1329 a a
1330 abc t3_myisam1
1331 abc t3_myisam2
1332 abc t3_myisam3
1333 bcd t3_myisam1
1334 bcd t3_myisam2
1335 bcd t3_myisam3
1336 cde t3_myisam1
1337 cde t3_myisam2
1338 cde t3_myisam3
1339 explain extended WITH cte AS (SELECT * FROM federated.t1),
1340 cte2 AS (SELECT * FROM t3
1341 WHERE t3.a NOT IN (SELECT * FROM cte))
1342 SELECT * FROM cte JOIN cte2;
1343 id select_type table type possible_keys key key_len ref rows filtered Extra
1344 1 PRIMARY <derived5> ALL NULL NULL NULL NULL 3 100.00
1345 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
1346 5 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL
1347 4 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 3 100.00
1348 2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL
1349 Warnings:
1350 Note 1003 with cte as (/* select#2 */ select `federated`.`t1`.`a` AS `a` from `federated`.`t1`), cte2 as (/* select#3 */ select `federated`.`t3`.`a` AS `a` from `federated`.`t3` where !<expr_cache><`federated`.`t3`.`a`>(<in_optimizer>(`federated`.`t3`.`a`,`federated`.`t3`.`a` in ( <materialize> (/* select#4 */ select `cte`.`a` from `cte` ), <primary_index_lookup>(`federated`.`t3`.`a` in <temporary table> on distinct_key where `federated`.`t3`.`a` = `<subquery4>`.`a`)))))/* select#1 */ select `cte`.`a` AS `a`,`federated`.`t3`.`a` AS `a` from `cte` join `federated`.`t3` where !<expr_cache><`federated`.`t3`.`a`>(<in_optimizer>(`federated`.`t3`.`a`,`federated`.`t3`.`a` in ( <materialize> (/* select#4 */ select `cte`.`a` from `cte` ), <primary_index_lookup>(`federated`.`t3`.`a` in <temporary table> on distinct_key where `federated`.`t3`.`a` = `<subquery4>`.`a`))))
1351 connection master;
1352 DROP TABLES federated.t1, federated.t2, t3, t4, t5, t6, federated.t11,
1353 federated.t12, federated.t13, federated.t14;
1354 connection slave;
1355 DROP TABLES federated.t1, federated.t2, federated.t11, federated.t12,
1356 federated.t13, federated.t14;
1357 #
1358 # MDEV-37484: `Sql_cmd_dml::execute_inner`, `Sql_cmd_update::execute_inner` do not call Select handler for the engine involved
1359 #
1360 connection default;
1361 set global federated_pushdown=1;
1362 connection slave;
1363 create table federated.product_tag (product_id bigint);
1364 CREATE TABLE federated.product (
1365 `product_id` bigint(20) DEFAULT NULL,
1366 `price` bigint(20) DEFAULT NULL,
1367 `short_description` varchar(7998) DEFAULT NULL,
1368 `brand` varchar(254) DEFAULT NULL,
1369 `modified_dtm` datetime DEFAULT NULL,
1370 `created_dtm` datetime DEFAULT NULL,
1371 `modified_by` int(11) DEFAULT NULL
1372 ) CHARSET=latin1;
1373 connection master;
1374 create table federated.product_tag (product_id bigint)
1375 ENGINE="FEDERATED"
1376 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/product_tag';
1377 CREATE TABLE federated.product (
1378 `product_id` bigint(20) DEFAULT NULL,
1379 `price` bigint(20) DEFAULT NULL,
1380 `short_description` varchar(7998) DEFAULT NULL,
1381 `brand` varchar(254) DEFAULT NULL,
1382 `modified_dtm` datetime DEFAULT NULL,
1383 `created_dtm` datetime DEFAULT NULL,
1384 `modified_by` int(11) DEFAULT NULL
1385 )
1386 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
1387 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/product';
1388 insert into federated.product_tag values (1),(2),(3),(4);
1389 insert into federated.product values (1,22,'fes','rets','2011-12-12 12:12:12','2011-12-13 1:1:10',343);
1390 insert into federated.product values (3,22,'fes','rets','2011-12-12 12:12:12','2011-12-13 1:1:10',343);
1391 select * from federated.product order by product_id;
1392 product_id price short_description brand modified_dtm created_dtm modified_by
1393 1 22 fes rets 2011-12-12 12:12:12 2011-12-13 01:01:10 343
1394 3 22 fes rets 2011-12-12 12:12:12 2011-12-13 01:01:10 343
1395 explain format=json update
1396 federated.product p join
1397 (select product_id from federated.product_tag limit 1000) t using (product_id)
1398 set brand=p.product_id, price=555, modified_dtm='2011-12-15 15:15:30',
1399 short_description=concat(short_description,modified_by);
1400 EXPLAIN
1401 {
1402 "query_block": {
1403 "select_id": 1,
1404 "cost": 1000.010933,
1405 "nested_loop": [
1406 {
1407 "table": {
1408 "table_name": "p",
1409 "access_type": "ALL",
1410 "loops": 1,
1411 "rows": 2,
1412 "cost": 1000.010388,
1413 "filtered": 100,
1414 "attached_condition": "p.product_id is not null"
1415 }
1416 },
1417 {
1418 "table": {
1419 "table_name": "<derived2>",
1420 "access_type": "ref",
1421 "possible_keys": ["key0"],
1422 "key": "key0",
1423 "key_length": "9",
1424 "used_key_parts": ["product_id"],
1425 "ref": ["federated.p.product_id"],
1426 "loops": 2,
1427 "rows": 1,
1428 "cost": 0.000545388,
1429 "filtered": 100,
1430 "materialized": {
1431 "query_block": {
1432 "select_id": 2,
1433 "table": {
1434 "message": "Pushed derived"
1435 }
1436 }
1437 }
1438 }
1439 }
1440 ]
1441 }
1442 }
1443 update
1444 federated.product p join
1445 (select product_id from federated.product_tag limit 1000) t using (product_id)
1446 set brand=p.product_id, price=555, modified_dtm='2011-12-15 15:15:30',
1447 short_description=concat(short_description,modified_by);
1448 select * from federated.product order by product_id;
1449 product_id price short_description brand modified_dtm created_dtm modified_by
1450 1 555 fes343 1 2011-12-15 15:15:30 2011-12-13 01:01:10 343
1451 3 555 fes343 3 2011-12-15 15:15:30 2011-12-13 01:01:10 343
1452 drop table federated.product_tag;
1453 drop table federated.product;
1454 connection slave;
1455 drop table federated.product_tag;
1456 drop table federated.product;
1457 # Setup
1458 create or replace table federated.t1 (id int);
1459 create or replace table federated.t2 (id int, op varchar(10), op_count int DEFAULT 0) CHARSET=latin1;
1460 connection master;
1461 create or replace table federated.t1 (id int)
1462 ENGINE="FEDERATED"
1463 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t1';
1464 create or replace table federated.t2 (id int, op varchar(10), op_count int DEFAULT 0)
1465 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
1466 CONNECTION='mysql://[email protected]:SLAVE_PORT/federated/t2';
1467 insert into federated.t1 values (1),(2),(3),(4);
1468 insert into federated.t2 (id, op) values (1, 'inserted'), (2, 'inserted'), (3, 'inserted'), (4, 'inserted');
1469 select * from federated.t2 order by id;
1470 id op op_count
1471 1 inserted 0
1472 2 inserted 0
1473 3 inserted 0
1474 4 inserted 0
1475 # Update tests
1476 explain format=json update federated.t2 inner join federated.t1 on t2.id = t1.id
1477 set t2.op = 'updated', t2.op_count = t2.op_count + 1
1478 where t2.id in (1, 2);
1479 EXPLAIN
1480 {
1481 "query_block": {
1482 "select_id": 1,
1483 "cost": 2000.022975,
1484 "nested_loop": [
1485 {
1486 "table": {
1487 "table_name": "t2",
1488 "access_type": "ALL",
1489 "loops": 1,
1490 "rows": 4,
1491 "cost": 1000.010655,
1492 "filtered": 100,
1493 "attached_condition": "t2.`id` in (1,2)"
1494 }
1495 },
1496 {
1497 "table": {
1498 "table_name": "t1",
1499 "access_type": "ALL",
1500 "loops": 4,
1501 "rows": 4,
1502 "cost": 1000.012319,
1503 "filtered": 100,
1504 "attached_condition": "t1.`id` = t2.`id`"
1505 }
1506 }
1507 ]
1508 }
1509 }
1510 update federated.t2 inner join federated.t1 on t2.id = t1.id
1511 set t2.op = 'updated', t2.op_count = t2.op_count + 1
1512 where t2.id in (1, 2);
1513 select * from federated.t2 order by id;
1514 id op op_count
1515 1 updated 1
1516 2 updated 1
1517 3 inserted 0
1518 4 inserted 0
1519 # Prepared update stmt
1520 PREPARE stmt_update FROM
1521 'update federated.t2 inner join federated.t1 on t2.id = t1.id set t2.op_count = t2.op_count + 1, t2.op="updated" where t2.id in (1, 2)';
1522 EXECUTE stmt_update;
1523 DEALLOCATE PREPARE stmt_update;
1524 select * from federated.t2 order by id;
1525 id op op_count
1526 1 updated 2
1527 2 updated 2
1528 3 inserted 0
1529 4 inserted 0
1530 # Delete tests
1531 explain format=json
1532 delete federated.t2 from federated.t2 inner join federated.t1 on t2.id = t1.id and t2.op = 'updated';
1533 EXPLAIN
1534 {
1535 "query_block": {
1536 "select_id": 1,
1537 "cost": 2000.023025,
1538 "nested_loop": [
1539 {
1540 "table": {
1541 "table_name": "t2",
1542 "access_type": "ALL",
1543 "loops": 1,
1544 "rows": 4,
1545 "cost": 1000.010705,
1546 "filtered": 100,
1547 "attached_condition": "t2.op = 'updated'"
1548 }
1549 },
1550 {
1551 "table": {
1552 "table_name": "t1",
1553 "access_type": "ALL",
1554 "loops": 4,
1555 "rows": 4,
1556 "cost": 1000.012319,
1557 "filtered": 100,
1558 "attached_condition": "t1.`id` = t2.`id`"
1559 }
1560 }
1561 ]
1562 }
1563 }
1564 delete federated.t2 from federated.t2 inner join federated.t1 on t2.id = t1.id and t2.op = 'updated';
1565 select * from federated.t2 order by id;
1566 id op op_count
1567 3 inserted 0
1568 4 inserted 0
1569 # Prepared delete stmt
1570 PREPARE stmt_delete FROM
1571 'delete federated.t2 from federated.t2 inner join federated.t1 on t2.id = t1.id where t2.id = 3';
1572 EXECUTE stmt_delete;
1573 DEALLOCATE PREPARE stmt_delete;
1574 select * from federated.t2 order by id;
1575 id op op_count
1576 4 inserted 0
1577 drop table federated.t1, federated.t2;
1578 connection slave;
1579 drop table federated.t1, federated.t2;
1580 connection default;
1581 set global federated_pushdown=0;
1582 connection master;
1583 DROP TABLE IF EXISTS federated.t1;
1584 DROP DATABASE IF EXISTS federated;
1585 connection slave;
1586 DROP TABLE IF EXISTS federated.t1;
1587 DROP DATABASE IF EXISTS federated;
1588 # End of 11.4 tests