Debian Codegraph @

mysql-test/suite/federated/federatedx_create_handlers.test

raw
1 --source have_federatedx.inc
2 --source include/federated.inc
3 --source include/no_valgrind_without_big.inc
4 --source include/have_partition.inc
5
6 connection default;
7 ALTER DATABASE federated CHARACTER SET latin1;
8
9 set global federated_pushdown=1;
10
11 connection slave;
12
13 DROP TABLE IF EXISTS federated.t1;
14
15 CREATE TABLE federated.t1 (
16 id int(20) NOT NULL,
17 name varchar(16) NOT NULL default ''
18 )
19 DEFAULT CHARSET=latin1;
20
21 INSERT INTO federated.t1 VALUES
22 (3,'xxx'), (7,'yyy'), (4,'xxx'), (1,'zzz'), (5,'yyy');
23
24 DROP TABLE IF EXISTS federated.t2;
25
26 CREATE TABLE federated.t2 (
27 name varchar(16) NOT NULL default ''
28 )
29 DEFAULT CHARSET=latin1;
30
31 INSERT INTO federated.t2 VALUES
32 ('yyy'), ('www'), ('yyy'), ('xxx'), ('www'), ('yyy'), ('www');
33
34
35 connection master;
36
37 DROP TABLE IF EXISTS federated.t1;
38
39 --replace_result $SLAVE_MYPORT SLAVE_PORT
40 eval
41 CREATE TABLE federated.t1 (
42 id int(20) NOT NULL,
43 name varchar(16) NOT NULL default ''
44 )
45 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
46 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t1';
47
48 DROP TABLE IF EXISTS federated.t2;
49
50 --replace_result $SLAVE_MYPORT SLAVE_PORT
51 eval
52 CREATE TABLE federated.t2 (
53 name varchar(16) NOT NULL default ''
54 )
55 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
56 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t2';
57
58 SELECT * FROM federated.t1;
59
60 SELECT id FROM federated.t1 WHERE id < 5;
61
62 SELECT count(*), name FROM federated.t1 WHERE id < 5 GROUP BY name;
63
64 SELECT * FROM federated.t1, federated.t2
65 WHERE federated.t1.name = federated.t2.name;
66
67 SELECT * FROM federated.t1 LEFT JOIN federated.t2
68 ON federated.t1.name = federated.t2.name
69 WHERE federated.t1.id > 1;
70
71 SELECT * FROM federated.t1
72 WHERE id IN (SELECT count(*) FROM federated.t2 GROUP BY name);
73
74 EXPLAIN
75 SELECT id FROM federated.t1 WHERE id < 5;
76
77 EXPLAIN EXTENDED
78 SELECT id FROM federated.t1 WHERE id < 5;
79
80 --source include/explain-no-costs.inc
81 EXPLAIN FORMAT=JSON
82 SELECT id FROM federated.t1 WHERE id < 5;
83
84 ANALYZE
85 SELECT id FROM federated.t1 WHERE id < 5;
86
87 --source include/analyze-format.inc
88 ANALYZE FORMAT=JSON
89 SELECT id FROM federated.t1 WHERE id < 5;
90
91 CREATE TABLE federated.t3 (
92 name varchar(16) NOT NULL default ''
93 )
94 DEFAULT CHARSET=latin1;
95
96 INSERT INTO federated.t3 VALUES
97 ('yyy'), ('www'), ('yyy'), ('xxx'), ('www'), ('yyy'), ('www');
98
99 SELECT *
100 FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
101 WHERE federated.t3.name=t.name;
102
103 EXPLAIN
104 SELECT *
105 FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
106 WHERE federated.t3.name=t.name;
107
108 --source include/explain-no-costs.inc
109 EXPLAIN FORMAT=JSON
110 SELECT *
111 FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
112 WHERE federated.t3.name=t.name;
113
114 ANALYZE
115 SELECT *
116 FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
117 WHERE federated.t3.name=t.name;
118
119 #Check after fix MDEV-31361
120 --disable_ps2_protocol
121 SELECT *
122 FROM federated.t3, (SELECT t1.name FROM federated.t1
123 WHERE id IN (SELECT count(*)
124 FROM federated.t2 GROUP BY name)) t
125 WHERE federated.t3.name=t.name;
126 --enable_ps2_protocol
127
128 EXPLAIN
129 SELECT *
130 FROM federated.t3, (SELECT t1.name FROM federated.t1
131 WHERE id IN (SELECT count(*)
132 FROM federated.t2 GROUP BY name)) t
133 WHERE federated.t3.name=t.name;
134
135 --source include/analyze-format.inc
136 ANALYZE FORMAT=JSON
137 SELECT *
138 FROM federated.t3, (SELECT t1.name FROM federated.t1
139 WHERE id IN (SELECT count(*)
140 FROM federated.t2 GROUP BY name)) t
141 WHERE federated.t3.name=t.name;
142
143 SELECT t.id, federated.t3.name
144 FROM federated.t3,
145 ( SELECT * FROM federated.t1 WHERE id < 3
146 UNION
147 SELECT * FROM federated.t1 WHERE id >= 5) t
148 WHERE federated.t3.name=t.name;
149
150 EXPLAIN
151 SELECT t.id, federated.t3.name
152 FROM federated.t3,
153 ( SELECT * FROM federated.t1 WHERE id < 3
154 UNION
155 SELECT * FROM federated.t1 WHERE id >= 5) t
156 WHERE federated.t3.name=t.name;
157
158 --echo #
159 --echo # MDEV-21887: federatedx crashes on SELECT ... INTO query in select_handler code
160 --echo #
161
162 CREATE TABLE federated.t4 (
163 id int(20) NOT NULL,
164 name varchar(16) NOT NULL default ''
165 ) engine=myisam;
166 insert into federated.t4 select * from federated.t1;
167
168 --sorted_result
169 select * from federated.t4;
170
171 select name into @var from federated.t1 where id=3 limit 1 ;
172 select @var;
173 select name into outfile 'tmp.txt' from federated.t1;
174
175 let $path=`select concat(@@datadir, 'test/tmp.txt')`;
176 remove_file $path;
177
178 --echo #
179 --echo # MDEV-22993: Crash on EXPLAIN with PUSHED DOWN SELECT and subquery
180 --echo #
181
182 explain
183 select * from federated.t1
184 where name in (select name from federated.t2);
185
186 --source include/explain-no-costs.inc
187 explain format=json
188 select * from federated.t1
189 where name in (select name from federated.t2);
190
191 --echo #
192 --echo # MDEV-22993, testcase #2: EXPLAIN output doesn't make sense when
193 --echo # derived table pushdown is used.
194 --echo #
195
196 create table t5 (a int) engine=myisam;
197 insert into t5 values (1),(2);
198
199 --echo # Must not show lines with id=3
200 explain
201 select * from t5,
202 (select id from federated.t1
203 where name in (select name from federated.t2) or name like 'foo%') as TQ;
204
205 --echo # Must not show elements with select_id=3
206 --source include/explain-no-costs.inc
207 explain format=json
208 select * from t5,
209 (select id from federated.t1
210 where name in (select name from federated.t2) or name like 'foo%') as TQ;
211
212 drop table t5;
213
214 DROP TABLE federated.t1, federated.t2, federated.t3, federated.t4;
215
216 connection slave;
217 DROP TABLE federated.t1, federated.t2;
218
219 connection default;
220
221 --echo #
222 --echo # MDEV-23778: Derived handler used for big derived tables
223 --echo #
224
225 connection slave;
226
227 CREATE TABLE federated.t1 (
228 a varchar(100) NOT NULL default '123'
229 )
230 DEFAULT CHARSET=latin1;
231
232 CREATE TABLE federated.t2 LIKE federated.t1;
233
234 DELIMITER $$;
235 BEGIN NOT ATOMIC
236 DECLARE i INT DEFAULT 0;
237 START TRANSACTION;
238 WHILE i < 70000 DO
239 INSERT INTO federated.t1 VALUES (i);
240 SET i = i + 1;
241 END WHILE;
242 COMMIT;
243 END
244 $$
245
246 DELIMITER ;$$
247
248 connection master;
249
250 --replace_result $SLAVE_MYPORT SLAVE_PORT
251 eval
252 CREATE TABLE federated.t1 (
253 a varchar(100) NOT NULL default '123'
254 )
255 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
256 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t1';
257
258
259 --replace_result $SLAVE_MYPORT SLAVE_PORT
260 eval
261 CREATE TABLE federated.t2 (
262 a varchar(100) NOT NULL default '123'
263 )
264 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
265 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t2';
266
267 SELECT COUNT(DISTINCT a) FROM federated.t1;
268
269 INSERT INTO federated.t2
270 SELECT * FROM (SELECT * FROM federated.t1 LIMIT 100) dt;
271 SELECT COUNT(DISTINCT a) FROM federated.t2;
272
273 TRUNCATE TABLE federated.t2;
274 INSERT INTO federated.t2
275 SELECT * FROM (SELECT * FROM federated.t1 LIMIT 70000) dt;
276 SELECT COUNT(DISTINCT a) FROM federated.t2;
277
278 --echo #
279 --echo # MDEV-29640 FederatedX does not properly handle pushdown
280 --echo # in case of difference in local and remote table names
281 --echo #
282 connection master;
283 --echo # Use tables from the previous test. Make sure pushdown works:
284 EXPLAIN SELECT COUNT(DISTINCT a) FROM federated.t2;
285 SELECT COUNT(DISTINCT a) FROM federated.t2;
286
287 --echo # Link remote table `federated.t1` with the local table named `t1_local`
288 --replace_result $SLAVE_MYPORT SLAVE_PORT
289 eval
290 CREATE TABLE federated.t1_local ENGINE="FEDERATED"
291 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t1';
292
293 --echo # No pushdown here due to table names mismatch, retrieve data as usual:
294 EXPLAIN SELECT COUNT(DISTINCT a) FROM federated.t1_local;
295 SELECT COUNT(DISTINCT a) FROM federated.t1_local;
296
297
298 --echo #
299 --echo # MDEV-29863 Server crashes in federatedx_txn::acquire after select from
300 --echo # the Federated table with partitions and federated_pushdown=1
301 --echo # in case of difference in local and remote table names
302 --echo #
303 connection slave;
304 CREATE TABLE federated.t3 (a INT);
305 INSERT INTO federated.t3 VALUES (1),(2),(3);
306 CREATE TABLE federated.t4 (a INT);
307
308 connection master;
309 --replace_result $SLAVE_MYPORT SLAVE_PORT
310 eval CREATE SERVER fedlink FOREIGN DATA WRAPPER mariadb
311 OPTIONS (USER 'root', HOST '127.0.0.1', DATABASE 'federated',
312 PORT $SLAVE_MYPORT);
313
314 CREATE TABLE federated.t3 (a INT)
315 ENGINE=FEDERATED
316 CONNECTION='mariadb://[email protected]:$SLAVE_MYPORT/federated/t3'
317 PARTITION BY list (a)
318 (PARTITION p1 VALUES IN (1) CONNECTION='fedlink/t3',
319 PARTITION p2 VALUES IN (2) CONNECTION='fedlink/t4');
320
321 EXPLAIN SELECT * FROM federated.t3;
322 SELECT * FROM federated.t3;
323 DROP SERVER fedlink;
324
325 --echo #
326 --echo # MDEV-29655: ASAN heap-use-after-free in
327 --echo # Pushdown_derived::Pushdown_derived
328 --echo #
329
330 connection slave;
331 DROP TABLE IF EXISTS federated.t1;
332
333 CREATE TABLE federated.t1 (
334 id int(20) NOT NULL,
335 name varchar(16) NOT NULL default ''
336 )
337 DEFAULT CHARSET=latin1;
338
339 INSERT INTO federated.t1 VALUES
340 (3,'xxx'), (7,'yyy'), (4,'xxx'), (1,'zzz'), (5,'yyy');
341
342 connection master;
343 DROP TABLE IF EXISTS federated.t1;
344
345 --replace_result $SLAVE_MYPORT SLAVE_PORT
346 eval
347 CREATE TABLE federated.t1 (
348 id int(20) NOT NULL,
349 name varchar(16) NOT NULL default ''
350 )
351 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
352 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t1';
353
354 use federated;
355 SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
356 WHERE id=2) dt2) dt;
357
358 PREPARE stmt FROM "
359 SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
360 WHERE id=3) dt2) dt;
361 ";
362 EXECUTE stmt;
363 EXECUTE stmt;
364 DEALLOCATE PREPARE stmt;
365
366 EXPLAIN
367 SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
368 WHERE id=3) dt2) dt;
369
370 connection slave;
371 CREATE TABLE federated.t10 (a INT,b INT);
372 CREATE TABLE federated.t11 (a INT, b INT);
373 INSERT INTO federated.t10 VALUES (1,1),(2,2);
374 INSERT INTO federated.t11 VALUES (1,1),(2,2);
375
376 connection master;
377 --replace_result $SLAVE_MYPORT SLAVE_PORT
378 eval
379 CREATE TABLE federated.t10
380 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
381 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t10';
382
383 --replace_result $SLAVE_MYPORT SLAVE_PORT
384 eval
385 CREATE TABLE federated.t11
386 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
387 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t11';
388
389 use federated;
390 SELECT * FROM t10 LEFT JOIN
391 (t11, (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
392 WHERE id=2) dt2) dt
393 ) ON t10.a=t11.a;
394
395 --echo #
396 --echo # MDEV-31361: Second execution of PS for query with derived table
397 --echo #
398
399 connection slave;
400 DROP TABLE IF EXISTS federated.t1;
401
402 CREATE TABLE federated.t1 (
403 id int(20) NOT NULL,
404 name varchar(16) NOT NULL default ''
405 )
406 DEFAULT CHARSET=latin1;
407
408 INSERT INTO federated.t1 VALUES
409 (3,'xxx'), (7,'yyy'), (4,'xxx'), (1,'zzz'), (5,'yyy');
410
411 connection master;
412 DROP TABLE IF EXISTS federated.t1;
413
414 --replace_result $SLAVE_MYPORT SLAVE_PORT
415 eval
416 CREATE TABLE federated.t1 (
417 id int(20) NOT NULL,
418 name varchar(16) NOT NULL default ''
419 )
420 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
421 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t1';
422
423 use federated;
424
425 let $q=
426 SELECT * FROM
427 (SELECT * FROM
428 (SELECT * FROM
429 (SELECT * FROM t1 where id>3) dt3
430 WHERE id>3) dt2
431 ) dt;
432
433 eval $q;
434
435 eval PREPARE stmt FROM "$q";
436 EXECUTE stmt;
437 EXECUTE stmt;
438 DEALLOCATE PREPARE stmt;
439
440 --echo #
441 --echo # FederatedX error 10000 on multi-table UPDATE/DELETE
442 --echo #
443
444 connection slave;
445 DROP TABLE IF EXISTS federated.t1, federated.t2;
446
447 CREATE TABLE federated.t1 (a int, b int);
448 INSERT INTO federated.t1 VALUES (1,1), (2,2), (3,3);
449
450 CREATE TABLE federated.t2 (a int, b int);
451 INSERT INTO federated.t2 VALUES (1,1), (2,2), (4,4);
452
453 connection master;
454 DROP TABLE IF EXISTS federated.t1, federated.t2;
455 --replace_result $SLAVE_MYPORT SLAVE_PORT
456 eval
457 CREATE TABLE federated.t1 (a int, b int)
458 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
459 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t1';
460
461 --replace_result $SLAVE_MYPORT SLAVE_PORT
462 eval
463 CREATE TABLE federated.t2 (a int, b int)
464 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
465 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t2';
466
467 use federated;
468
469 --echo # Multi-table UPDATE
470 UPDATE t1, t2 SET t1.a = 2 WHERE t1.a=t2.a;
471
472 --echo # Check the result
473 SELECT * FROM t1;
474
475 --echo # Multi-table DELETE
476 DELETE FROM t1 USING t1 JOIN t2 ON t1.a = t2.a WHERE t2.b > 1;
477
478 SELECT * FROM t1;
479
480 --echo # Another form of multi-table DELETE
481 DELETE FROM a1 USING t1 AS a1;
482
483 SELECT * FROM t1;
484
485 DROP TABLES federated.t1, federated.t2, federated.t3, federated.t10,
486 federated.t11;
487 connection slave;
488 DROP TABLES federated.t1, federated.t2, federated.t3, federated.t10,
489 federated.t11;
490
491
492 --echo # MDEV-25080: Allow pushdown of queries involving UNIONs
493 --echo # in outer select to foreign engines
494 --echo #
495
496 connection slave;
497
498 CREATE TABLE federated.t1 (
499 a varchar(10)
500 )
501 DEFAULT CHARSET=latin1;
502
503 CREATE TABLE federated.t2 (
504 a varchar(16) NOT NULL default ''
505 )
506 DEFAULT CHARSET=latin1;
507
508 INSERT INTO federated.t1 VALUES ('bcd'), ('abc'), ('cde');
509 INSERT INTO federated.t2 VALUES ('cde'), ('efg'), ('abc'), ('bcd'), ('def');
510
511 connection master;
512
513 --replace_result $SLAVE_MYPORT SLAVE_PORT
514 eval
515 CREATE TABLE federated.t1 (
516 a varchar(10)
517 )
518 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
519 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t1';
520
521 --replace_result $SLAVE_MYPORT SLAVE_PORT
522 eval
523 CREATE TABLE federated.t2 (
524 a varchar(16) NOT NULL default ''
525 )
526 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
527 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t2';
528
529 CREATE TABLE t3 (a varchar(30)) ENGINE=MyISAM;
530 CREATE TABLE t4 (a varchar(30)) ENGINE=MyISAM;
531
532 INSERT INTO t3 VALUES ('t3_myisam1'), ('t3_myisam2'), ('t3_myisam3');
533 INSERT INTO t4 VALUES ('t4_myisam1'), ('t4_myisam2'), ('t4_myisam3');
534
535 --echo # Pushdown of the whole UNION
536 --sorted_result
537 SELECT * from federated.t1 UNION SELECT * from federated.t2;
538 EXPLAIN SELECT * from federated.t1 UNION SELECT * from federated.t2;
539
540 --echo # Pushdown of a part of the UNION
541 --sorted_result
542 SELECT * from federated.t1 UNION SELECT * from t3;
543 EXPLAIN SELECT * from federated.t1 UNION SELECT * from t3;
544
545 --sorted_result
546 SELECT * from federated.t1 UNION ALL SELECT * from federated.t2;
547 EXPLAIN SELECT * from federated.t1 UNION ALL SELECT * from federated.t2;
548
549 EXPLAIN FORMAT=JSON SELECT * from federated.t1 UNION ALL
550 SELECT * from federated.t2;
551
552 ANALYZE SELECT * from federated.t1 UNION ALL SELECT * from federated.t2;
553
554 --source include/analyze-format.inc
555 ANALYZE FORMAT=JSON SELECT * from federated.t1 UNION ALL
556 SELECT * from federated.t2;
557
558 --sorted_result
559 SELECT * from federated.t1 EXCEPT SELECT * from federated.t2;
560
561 EXPLAIN EXTENDED SELECT * from federated.t1 EXCEPT
562 SELECT * from federated.t2;
563
564 EXPLAIN FORMAT=JSON SELECT * from federated.t1 EXCEPT
565 SELECT * from federated.t2;
566
567 --sorted_result
568 SELECT * from federated.t1 INTERSECT SELECT * from federated.t2;
569
570 EXPLAIN PARTITIONS SELECT * from federated.t1 INTERSECT
571 SELECT * from federated.t2;
572
573 EXPLAIN FORMAT=JSON SELECT * from federated.t1 INTERSECT
574 SELECT * from federated.t2;
575
576 --echo # More than two SELECTs in a UNIT:
577 --sorted_result
578 SELECT * from federated.t1 INTERSECT
579 SELECT * from federated.t2 UNION ALL
580 SELECT * from federated.t2 EXCEPT
581 SELECT * from federated.t1;
582
583 EXPLAIN
584 SELECT count(*) from federated.t1 INTERSECT
585 SELECT count(*) from federated.t2 UNION ALL
586 SELECT count(*)+20 from federated.t2 EXCEPT
587 SELECT count(*)+5 from federated.t1;
588
589 EXPLAIN FORMAT=JSON
590 SELECT count(*) from federated.t1 INTERSECT
591 SELECT count(*) from federated.t2 UNION ALL
592 SELECT count(*)+20 from federated.t2 EXCEPT
593 SELECT count(*)+5 from federated.t1;
594
595 ANALYZE
596 SELECT count(*) from federated.t1 INTERSECT
597 SELECT count(*) from federated.t2 UNION
598 SELECT count(*)+20 from federated.t2 EXCEPT
599 SELECT count(*)+5 from federated.t1;
600
601 --echo # UNION inside a derived table: the whole derived table must be pushed
602 --sorted_result
603 SELECT * FROM
604 (SELECT * FROM federated.t1 UNION ALL SELECT * FROM federated.t2) q;
605
606 EXPLAIN
607 SELECT * FROM
608 (SELECT a FROM federated.t1 UNION ALL SELECT * FROM federated.t2) q;
609
610 --echo # There is an uncacheable side effect due to fetch into @var,
611 --echo # so the UNION cannot be pushed down as a whole.
612 --echo # But separate SELECTs can be pushed, and the results are combined
613 --echo # at the server side
614
615 --disable_warnings
616 --sorted_result
617 SELECT count(*) FROM federated.t1 UNION
618 SELECT count(*) FROM federated.t1 EXCEPT
619 SELECT count(*)+1 FROM federated.t1
620 INTO @var;
621
622 EXPLAIN SELECT count(*) FROM federated.t1 UNION
623 SELECT count(*) FROM federated.t2 EXCEPT
624 SELECT count(*)+1 FROM federated.t1
625 INTO @var;
626
627 EXPLAIN FORMAT=JSON SELECT count(*) FROM federated.t1 UNION
628 SELECT count(*) FROM federated.t2 EXCEPT
629 SELECT count(*)+2 FROM federated.t2
630 INTO @var;
631 --enable_warnings
632
633 --echo # Prepared statements
634 PREPARE stmt FROM "SELECT * from federated.t1 INTERSECT
635 SELECT * from federated.t2 UNION ALL
636 SELECT * from federated.t2 EXCEPT
637 SELECT * from federated.t1";
638
639 --sorted_result
640 EXECUTE stmt;
641 --sorted_result
642 EXECUTE stmt;
643 --sorted_result
644 EXECUTE stmt;
645
646 PREPARE stmt FROM "EXPLAIN SELECT * from federated.t1 INTERSECT
647 SELECT * from federated.t2 UNION ALL
648 SELECT * from federated.t2 EXCEPT
649 SELECT * from federated.t1";
650
651 --sorted_result
652 EXECUTE stmt;
653 --sorted_result
654 EXECUTE stmt;
655
656 --echo # UNIONs of mixed Federated/MyISAM tables, pushing parts of UNIONs
657 --sorted_result
658 SELECT * FROM federated.t1 UNION SELECT * FROM t3;
659 EXPLAIN SELECT * FROM federated.t1 UNION SELECT * FROM t3;
660
661 --sorted_result
662 SELECT * FROM federated.t1 UNION ALL
663 SELECT * FROM t3 EXCEPT
664 SELECT * FROM federated.t2;
665 EXPLAIN SELECT * FROM federated.t1 UNION ALL
666 SELECT * FROM t3 EXCEPT
667 SELECT * FROM federated.t2;
668
669 --sorted_result
670 SELECT * FROM t3 UNION ALL
671 SELECT * FROM federated.t1 EXCEPT
672 SELECT * FROM t4 INTERSECT
673 SELECT * FROM federated.t2;
674 EXPLAIN SELECT * FROM t3 UNION ALL
675 SELECT * FROM federated.t1 EXCEPT
676 SELECT * FROM t4 INTERSECT
677 SELECT * FROM federated.t2;
678
679 --sorted_result
680 SELECT * FROM federated.t2 UNION ALL
681 SELECT * FROM t3 EXCEPT
682 SELECT * FROM t4 INTERSECT
683 SELECT * FROM federated.t1;
684 EXPLAIN SELECT * FROM federated.t2 UNION ALL
685 SELECT * FROM t3 EXCEPT
686 SELECT * FROM t4 INTERSECT
687 SELECT * FROM federated.t1;
688
689 --echo # Parenthesis must not prevent the whole UNIONs pushdown
690 EXPLAIN (SELECT * FROM federated.t1 UNION
691 SELECT * FROM federated.t2) UNION ALL
692 SELECT * FROM federated.t1;
693
694 --sorted_result
695 (SELECT * FROM federated.t1 UNION
696 SELECT * FROM federated.t2) UNION ALL
697 SELECT * FROM federated.t1;
698
699 EXPLAIN (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2)
700 UNION ALL (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2);
701
702 --sorted_result
703 (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2) UNION ALL
704 (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2);
705
706 --echo # Union of tables containing different INT data types
707 connection slave;
708
709 CREATE TABLE federated.t11 (a smallint(6) NOT NULL);
710 INSERT INTO federated.t11 VALUES (-32678), (-1), (0);
711
712 CREATE TABLE federated.t12 (a int(10) UNSIGNED NOT NULL);
713 INSERT INTO federated.t12 VALUES (0), (1), (32767);
714
715 connection master;
716
717 --replace_result $SLAVE_MYPORT SLAVE_PORT
718 eval
719 CREATE TABLE federated.t11 (a smallint(6) NOT NULL)
720 ENGINE="FEDERATED"
721 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t11';
722
723 --replace_result $SLAVE_MYPORT SLAVE_PORT
724 eval
725 CREATE TABLE federated.t12 (a int(10) UNSIGNED NOT NULL)
726 ENGINE="FEDERATED"
727 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t12';
728
729 --echo # Entire UNION pushdown
730 --sorted_result
731 SELECT a FROM federated.t12 UNION ALL SELECT a FROM federated.t11;
732 EXPLAIN SELECT a FROM federated.t12 UNION ALL SELECT a FROM federated.t11;
733
734 --sorted_result
735 SELECT a FROM federated.t11 UNION SELECT a FROM federated.t12;
736
737 --echo # Partial pushdown of SELECTs composing the UNION
738 --sorted_result
739 SELECT a FROM federated.t12 UNION SELECT a FROM federated.t11 UNION SELECT 123;
740 EXPLAIN
741 SELECT a FROM federated.t12 UNION SELECT a FROM federated.t11
742 UNION SELECT 123;
743
744 --sorted_result
745 SELECT a FROM federated.t12 EXCEPT
746 SELECT 1 UNION ALL
747 SELECT a FROM federated.t11 EXCEPT
748 SELECT 0;
749
750 --echo # Union of tables containing different string data types
751 connection slave;
752 CREATE TABLE federated.t13 (a CHAR(6));
753 INSERT INTO federated.t13 VALUES ('t13abc'), ('t13xx'), ('common');
754
755 CREATE TABLE federated.t14 (a VARCHAR(8));
756 INSERT INTO federated.t14 VALUES ('t14abcde'), ('t14xyzzz'), ('common');
757
758 connection master;
759
760 --replace_result $SLAVE_MYPORT SLAVE_PORT
761 eval
762 CREATE TABLE federated.t13 (a CHAR(6))
763 ENGINE="FEDERATED"
764 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t13';
765
766 --replace_result $SLAVE_MYPORT SLAVE_PORT
767 eval
768 CREATE TABLE federated.t14 (a VARCHAR(8))
769 ENGINE="FEDERATED"
770 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t14';
771
772 --sorted_result
773 SELECT * FROM federated.t13 UNION SELECT * FROM federated.t14;
774 EXPLAIN SELECT * FROM federated.t13 UNION SELECT * FROM federated.t14;
775
776 --sorted_result
777 SELECT * FROM federated.t14 UNION ALL SELECT * FROM federated.t13;
778
779 SELECT * FROM federated.t14 UNION
780 SELECT * FROM federated.t13 UNION
781 SELECT '123456789000';
782
783 EXPLAIN SELECT * FROM federated.t14 UNION
784 SELECT * FROM federated.t13 UNION
785 SELECT '123456789000';
786
787 --sorted_result
788 SELECT * FROM federated.t13 UNION
789 SELECT '123456789000' UNION
790 SELECT * FROM federated.t14;
791
792 --echo # CREATE TABLE .. AS from a pushed UNION
793 CREATE TABLE t5 AS SELECT * FROM federated.t13 UNION
794 SELECT * FROM federated.t14;
795 SHOW CREATE TABLE t5;
796 --sorted_result
797 SELECT * FROM t5;
798
799 CREATE TABLE t6 AS SELECT a FROM federated.t12 EXCEPT
800 SELECT 1 UNION ALL
801 SELECT a FROM federated.t11 EXCEPT
802 SELECT 0;
803 SHOW CREATE TABLE t6;
804 --sorted_result
805 SELECT * FROM t6;
806
807 --echo #
808 --echo # MDEV-30828 ORDER BY clause using an integer (positional argument)
809 --echo #
810 SELECT a FROM federated.t1 UNION SELECT a FROM federated.t2 ORDER BY 1;
811
812 SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY a DESC;
813
814 --echo # Check handling of incorrect ORDER BY clause
815 --error ER_BAD_FIELD_ERROR
816 SELECT a FROM federated.t1 UNION SELECT a FROM federated.t2 ORDER BY 2;
817 --error ER_BAD_FIELD_ERROR
818 PREPARE stmt FROM
819 "SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 2";
820
821 --error ER_BAD_FIELD_ERROR
822 SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 2,1,3;
823 --error ER_TABLENAME_NOT_ALLOWED_HERE
824 SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY t1.a;
825 SELECT * from federated.t1 INTERSECT
826 SELECT * from federated.t2 UNION ALL
827 SELECT * from federated.t2 EXCEPT
828 SELECT * from federated.t1
829 ORDER BY 1;
830 --error ER_BAD_FIELD_ERROR
831 SELECT * from federated.t1 INTERSECT
832 SELECT * from federated.t2 UNION ALL
833 SELECT * from federated.t2 EXCEPT
834 SELECT * from federated.t1
835 ORDER BY 3;
836 --echo # UNION of mixed Federated/MyISAM tables, pushing parts of UNIONs
837 SELECT * FROM federated.t1 UNION SELECT * FROM t3 ORDER BY a;
838 --error ER_BAD_FIELD_ERROR
839 SELECT * FROM federated.t1 UNION SELECT * FROM t3 ORDER BY 2;
840
841
842 --echo #
843 --echo # MDEV-32382 FederatedX error on pushdown of statement having CTE
844 --echo #
845
846 --echo # Single SELECT with CTE
847 let $query= WITH cte AS (SELECT * FROM federated.t1)
848 SELECT * FROM cte;
849 eval $query;
850 eval explain extended $query;
851
852 --echo # Pushdown of a UNION having CTE's
853 let $query= WITH cte AS (SELECT * FROM federated.t1),
854 cte2 AS (SELECT * FROM federated.t2)
855 SELECT * FROM cte
856 UNION
857 SELECT * FROM cte2;
858 --sorted_result
859 eval $query;
860 eval explain extended $query;
861
862 # CREATE TABLE t3 (a int);
863 # INSERT INTO t3 VALUES (101),(102),(103);
864
865 --echo # Partial pushdown is not allowed for unions with CTE's, however a CTE
866 --echo # may be pushed down a derived table
867 let $query= WITH cte AS (SELECT * FROM federated.t1)
868 SELECT * FROM cte
869 UNION ALL
870 SELECT * FROM t3;
871 --sorted_result
872 eval $query;
873 eval explain extended $query;
874
875 let $query= WITH cte AS (SELECT * FROM federated.t1 UNION SELECT * FROM t3)
876 SELECT * FROM cte;
877 --sorted_result
878 eval $query;
879 eval explain extended $query;
880
881 --echo # Two CTE's where one CTE refers to another
882 let $query= WITH cte AS (SELECT * FROM federated.t1),
883 cte2 AS (SELECT * FROM t3
884 WHERE t3.a NOT IN (SELECT * FROM cte))
885 SELECT * FROM cte JOIN cte2;
886 --sorted_result
887 eval $query;
888 eval explain extended $query;
889
890
891 # Cleanup
892 connection master;
893 DROP TABLES federated.t1, federated.t2, t3, t4, t5, t6, federated.t11,
894 federated.t12, federated.t13, federated.t14;
895
896 connection slave;
897 DROP TABLES federated.t1, federated.t2, federated.t11, federated.t12,
898 federated.t13, federated.t14;
899
900 --echo #
901 --echo # MDEV-37484: `Sql_cmd_dml::execute_inner`, `Sql_cmd_update::execute_inner` do not call Select handler for the engine involved
902 --echo #
903
904 connection default;
905 set global federated_pushdown=1;
906
907 connection slave;
908 create table federated.product_tag (product_id bigint);
909 CREATE TABLE federated.product (
910 `product_id` bigint(20) DEFAULT NULL,
911 `price` bigint(20) DEFAULT NULL,
912 `short_description` varchar(7998) DEFAULT NULL,
913 `brand` varchar(254) DEFAULT NULL,
914 `modified_dtm` datetime DEFAULT NULL,
915 `created_dtm` datetime DEFAULT NULL,
916 `modified_by` int(11) DEFAULT NULL
917 ) CHARSET=latin1;
918
919 connection master;
920 --replace_result $SLAVE_MYPORT SLAVE_PORT
921 eval
922 create table federated.product_tag (product_id bigint)
923 ENGINE="FEDERATED"
924 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/product_tag';
925
926 --replace_result $SLAVE_MYPORT SLAVE_PORT
927 eval
928 CREATE TABLE federated.product (
929 `product_id` bigint(20) DEFAULT NULL,
930 `price` bigint(20) DEFAULT NULL,
931 `short_description` varchar(7998) DEFAULT NULL,
932 `brand` varchar(254) DEFAULT NULL,
933 `modified_dtm` datetime DEFAULT NULL,
934 `created_dtm` datetime DEFAULT NULL,
935 `modified_by` int(11) DEFAULT NULL
936 )
937 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
938 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/product';
939
940 insert into federated.product_tag values (1),(2),(3),(4);
941 insert into federated.product values (1,22,'fes','rets','2011-12-12 12:12:12','2011-12-13 1:1:10',343);
942 insert into federated.product values (3,22,'fes','rets','2011-12-12 12:12:12','2011-12-13 1:1:10',343);
943 select * from federated.product order by product_id;
944
945 let $query=
946 update
947 federated.product p join
948 (select product_id from federated.product_tag limit 1000) t using (product_id)
949 set brand=p.product_id, price=555, modified_dtm='2011-12-15 15:15:30',
950 short_description=concat(short_description,modified_by);
951
952 eval explain format=json $query;
953 eval $query;
954
955 select * from federated.product order by product_id;
956
957 drop table federated.product_tag;
958 drop table federated.product;
959
960 connection slave;
961 drop table federated.product_tag;
962 drop table federated.product;
963
964 --echo # Setup
965 create or replace table federated.t1 (id int);
966 create or replace table federated.t2 (id int, op varchar(10), op_count int DEFAULT 0) CHARSET=latin1;
967
968 connection master;
969 --replace_result $SLAVE_MYPORT SLAVE_PORT
970 eval
971 create or replace table federated.t1 (id int)
972 ENGINE="FEDERATED"
973 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t1';
974
975 --replace_result $SLAVE_MYPORT SLAVE_PORT
976 eval
977 create or replace table federated.t2 (id int, op varchar(10), op_count int DEFAULT 0)
978 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
979 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/federated/t2';
980
981 insert into federated.t1 values (1),(2),(3),(4);
982 insert into federated.t2 (id, op) values (1, 'inserted'), (2, 'inserted'), (3, 'inserted'), (4, 'inserted');
983 select * from federated.t2 order by id;
984
985 --echo # Update tests
986 let $query=
987 update federated.t2 inner join federated.t1 on t2.id = t1.id
988 set t2.op = 'updated', t2.op_count = t2.op_count + 1
989 where t2.id in (1, 2);
990
991 eval explain format=json $query;
992 eval $query;
993
994 select * from federated.t2 order by id;
995
996 --echo # Prepared update stmt
997 PREPARE stmt_update FROM
998 '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)';
999 EXECUTE stmt_update;
1000 DEALLOCATE PREPARE stmt_update;
1001
1002 select * from federated.t2 order by id;
1003
1004 --echo # Delete tests
1005 explain format=json
1006 delete federated.t2 from federated.t2 inner join federated.t1 on t2.id = t1.id and t2.op = 'updated';
1007
1008 delete federated.t2 from federated.t2 inner join federated.t1 on t2.id = t1.id and t2.op = 'updated';
1009 select * from federated.t2 order by id;
1010
1011 --echo # Prepared delete stmt
1012 PREPARE stmt_delete FROM
1013 'delete federated.t2 from federated.t2 inner join federated.t1 on t2.id = t1.id where t2.id = 3';
1014 EXECUTE stmt_delete;
1015 DEALLOCATE PREPARE stmt_delete;
1016
1017 select * from federated.t2 order by id;
1018
1019 drop table federated.t1, federated.t2;
1020
1021 connection slave;
1022 drop table federated.t1, federated.t2;
1023
1024 # cleanup
1025 connection default;
1026 set global federated_pushdown=0;
1027 source include/federated_cleanup.inc;
1028
1029 --echo # End of 11.4 tests