diff options
Diffstat (limited to 'mysql-test/suite/federated')
| -rw-r--r-- | mysql-test/suite/federated/federated_bug_25714.result | 8 | ||||
| -rw-r--r-- | mysql-test/suite/federated/federated_bug_25714.test | 21 | ||||
| -rw-r--r-- | mysql-test/suite/federated/federated_server.result | 10 | ||||
| -rw-r--r-- | mysql-test/suite/federated/federated_server.test | 4 | ||||
| -rw-r--r-- | mysql-test/suite/federated/federatedx.result | 16 | ||||
| -rw-r--r-- | mysql-test/suite/federated/federatedx.test | 22 | ||||
| -rw-r--r-- | mysql-test/suite/federated/federatedx_create_handlers.result | 726 | ||||
| -rw-r--r-- | mysql-test/suite/federated/federatedx_create_handlers.test | 421 | ||||
| -rw-r--r-- | mysql-test/suite/federated/my.cnf | 3 |
9 files changed, 1204 insertions, 27 deletions
diff --git a/mysql-test/suite/federated/federated_bug_25714.result b/mysql-test/suite/federated/federated_bug_25714.result index 9ac15672..8d695a86 100644 --- a/mysql-test/suite/federated/federated_bug_25714.result +++ b/mysql-test/suite/federated/federated_bug_25714.result @@ -7,10 +7,13 @@ CREATE DATABASE federated; connection master; SET @OLD_MASTER_CONCURRENT_INSERT= @@GLOBAL.CONCURRENT_INSERT; SET @@GLOBAL.CONCURRENT_INSERT= 0; +create user fed@127.0.0.1 identified by 'def'; +grant all privileges on federated.* to fed@127.0.0.1; connection slave; SET @OLD_SLAVE_CONCURRENT_INSERT= @@GLOBAL.CONCURRENT_INSERT; SET @@GLOBAL.CONCURRENT_INSERT= 0; -DROP TABLE IF EXISTS federated.bug_13118_table; +create user fed@127.0.0.1 identified by 'def'; +grant all privileges on federated.* to fed@127.0.0.1; CREATE TABLE federated.t1 ( `id` int auto_increment primary key, `value` int @@ -19,7 +22,6 @@ INSERT INTO federated.t1 SET value=1; INSERT INTO federated.t1 SET value=2; INSERT INTO federated.t1 SET value=2; connection master; -DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 ( `id` int auto_increment primary key, `value` int @@ -51,9 +53,11 @@ id value 7 54 8 55 DROP TABLE federated.t1; +drop user fed@127.0.0.1; SET @@GLOBAL.CONCURRENT_INSERT= @OLD_MASTER_CONCURRENT_INSERT; connection slave; DROP TABLE federated.t1; +drop user fed@127.0.0.1; SET @@GLOBAL.CONCURRENT_INSERT= @OLD_SLAVE_CONCURRENT_INSERT; connection master; DROP TABLE IF EXISTS federated.t1; diff --git a/mysql-test/suite/federated/federated_bug_25714.test b/mysql-test/suite/federated/federated_bug_25714.test index bb5dcfe5..c979e55a 100644 --- a/mysql-test/suite/federated/federated_bug_25714.test +++ b/mysql-test/suite/federated/federated_bug_25714.test @@ -13,12 +13,20 @@ connection master; SET @OLD_MASTER_CONCURRENT_INSERT= @@GLOBAL.CONCURRENT_INSERT; SET @@GLOBAL.CONCURRENT_INSERT= 0; +# +# use a user with the password, so that the test client $MYSQL_BUG25714 +# would be able to connect when ssl and certificate validation are +# enabled by default (MDEV-31857, MDEV-31855) +# +create user fed@127.0.0.1 identified by 'def'; +grant all privileges on federated.* to fed@127.0.0.1; + connection slave; SET @OLD_SLAVE_CONCURRENT_INSERT= @@GLOBAL.CONCURRENT_INSERT; SET @@GLOBAL.CONCURRENT_INSERT= 0; ---disable_warnings -DROP TABLE IF EXISTS federated.bug_13118_table; ---enable_warnings + +create user fed@127.0.0.1 identified by 'def'; +grant all privileges on federated.* to fed@127.0.0.1; CREATE TABLE federated.t1 ( `id` int auto_increment primary key, @@ -29,9 +37,6 @@ INSERT INTO federated.t1 SET value=2; INSERT INTO federated.t1 SET value=2; connection master; ---disable_warnings -DROP TABLE IF EXISTS federated.t1; ---enable_warnings --replace_result $SLAVE_MYPORT SLAVE_PORT eval CREATE TABLE federated.t1 ( @@ -52,11 +57,11 @@ SELECT LAST_INSERT_ID(); SELECT * from federated.t1; DROP TABLE federated.t1; +drop user fed@127.0.0.1; SET @@GLOBAL.CONCURRENT_INSERT= @OLD_MASTER_CONCURRENT_INSERT; connection slave; DROP TABLE federated.t1; +drop user fed@127.0.0.1; SET @@GLOBAL.CONCURRENT_INSERT= @OLD_SLAVE_CONCURRENT_INSERT; - - source suite/federated/include/federated_cleanup.inc; diff --git a/mysql-test/suite/federated/federated_server.result b/mysql-test/suite/federated/federated_server.result index d25676e7..1207a4bb 100644 --- a/mysql-test/suite/federated/federated_server.result +++ b/mysql-test/suite/federated/federated_server.result @@ -197,7 +197,7 @@ OWNER 'root'); create user guest_select@localhost; grant select on federated.* to guest_select@localhost; create user guest_super@localhost; -grant select,SUPER,RELOAD on *.* to guest_super@localhost; +grant select,FEDERATED ADMIN,RELOAD on *.* to guest_super@localhost; create user guest_usage@localhost; grant usage on *.* to guest_usage@localhost; CREATE TABLE federated.t1 ( @@ -212,7 +212,7 @@ connect conn_usage,127.0.0.1,guest_usage,,,$MASTER_MYPORT; connect conn_super,127.0.0.1,guest_super,,,$MASTER_MYPORT; connection conn_select; alter server s1 options (database 'db_bogus'); -ERROR 42000: Access denied; you need (at least one of) the SUPER, FEDERATED ADMIN privilege(s) for this operation +ERROR 42000: Access denied; you need (at least one of) the FEDERATED ADMIN privilege(s) for this operation connection master; flush tables; select * from federated.t1; @@ -220,7 +220,7 @@ id name 1 this is legitimate connection conn_usage; alter server s1 options (database 'db_bogus'); -ERROR 42000: Access denied; you need (at least one of) the SUPER, FEDERATED ADMIN privilege(s) for this operation +ERROR 42000: Access denied; you need (at least one of) the FEDERATED ADMIN privilege(s) for this operation connection master; flush tables; select * from federated.t1; @@ -234,7 +234,7 @@ select * from federated.t1; Got one of the listed errors connection conn_select; drop server if exists 's1'; -ERROR 42000: Access denied; you need (at least one of) the SUPER, FEDERATED ADMIN privilege(s) for this operation +ERROR 42000: Access denied; you need (at least one of) the FEDERATED ADMIN privilege(s) for this operation create server 's1' foreign data wrapper 'mysql' options (HOST '127.0.0.1', DATABASE 'db_legitimate', @@ -243,7 +243,7 @@ PASSWORD 'foo', PORT SLAVE_PORT, SOCKET '', OWNER 'root'); -ERROR 42000: Access denied; you need (at least one of) the SUPER, FEDERATED ADMIN privilege(s) for this operation +ERROR 42000: Access denied; you need (at least one of) the FEDERATED ADMIN privilege(s) for this operation connection conn_super; drop server 's1'; create server 's1' foreign data wrapper 'mysql' options diff --git a/mysql-test/suite/federated/federated_server.test b/mysql-test/suite/federated/federated_server.test index 3d491b1d..10f383d4 100644 --- a/mysql-test/suite/federated/federated_server.test +++ b/mysql-test/suite/federated/federated_server.test @@ -162,7 +162,7 @@ drop database second_db; # # Bug#25671 - CREATE/DROP/ALTER SERVER should require privileges # -# Changes to SERVER declarations should require SUPER privilege. +# Changes to SERVER declarations should require FEDERATED ADMIN privilege. # Based upon test case by Giuseppe Maxia create database db_legitimate; @@ -202,7 +202,7 @@ create user guest_select@localhost; grant select on federated.* to guest_select@localhost; create user guest_super@localhost; -grant select,SUPER,RELOAD on *.* to guest_super@localhost; +grant select,FEDERATED ADMIN,RELOAD on *.* to guest_super@localhost; create user guest_usage@localhost; grant usage on *.* to guest_usage@localhost; diff --git a/mysql-test/suite/federated/federatedx.result b/mysql-test/suite/federated/federatedx.result index 3054060b..e75fb253 100644 --- a/mysql-test/suite/federated/federatedx.result +++ b/mysql-test/suite/federated/federatedx.result @@ -2358,6 +2358,22 @@ DROP TABLE t2_fed, t1, t2; set @@optimizer_switch=@save_optimizer_switch; DROP SERVER s; # End of 10.5 tests +# +# MDEV-30569: Assertion ...ha_table_flags() failed in Duplicate_weedout_picker::check_qep +# +create server s foreign data wrapper mysql options +(host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT); +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); +CREATE TABLE t1_fed ENGINE=FEDERATED CONNECTION='s/t1'; +CREATE VIEW v AS SELECT * FROM t1_fed; +SELECT * FROM v WHERE a IN ( SELECT b FROM t2); +a +DROP VIEW v; +DROP TABLE t1_fed, t1, t2; +DROP SERVER s; connection master; DROP TABLE IF EXISTS federated.t1; DROP DATABASE IF EXISTS federated; diff --git a/mysql-test/suite/federated/federatedx.test b/mysql-test/suite/federated/federatedx.test index 7e5a335b..579316be 100644 --- a/mysql-test/suite/federated/federatedx.test +++ b/mysql-test/suite/federated/federatedx.test @@ -2090,4 +2090,26 @@ DROP SERVER s; --echo # End of 10.5 tests +--echo # +--echo # MDEV-30569: Assertion ...ha_table_flags() failed in Duplicate_weedout_picker::check_qep +--echo # + +evalp create server s foreign data wrapper mysql options + (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT); + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); + +CREATE TABLE t1_fed ENGINE=FEDERATED CONNECTION='s/t1'; +CREATE VIEW v AS SELECT * FROM t1_fed; + +SELECT * FROM v WHERE a IN ( SELECT b FROM t2); + +DROP VIEW v; +DROP TABLE t1_fed, t1, t2; +DROP SERVER s; + source include/federated_cleanup.inc; diff --git a/mysql-test/suite/federated/federatedx_create_handlers.result b/mysql-test/suite/federated/federatedx_create_handlers.result index 7288c520..698dfab9 100644 --- a/mysql-test/suite/federated/federatedx_create_handlers.result +++ b/mysql-test/suite/federated/federatedx_create_handlers.result @@ -150,7 +150,7 @@ FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t WHERE federated.t3.name=t.name; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 7 -1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 2 +1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 1 2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL EXPLAIN FORMAT=JSON SELECT * @@ -160,12 +160,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", + "loops": 1, "rows": 7, + "cost": "COST_REPLACED", "filtered": 100 } }, @@ -178,7 +181,9 @@ EXPLAIN "key_length": "18", "used_key_parts": ["name"], "ref": ["federated.t3.name"], - "rows": 2, + "loops": 7, + "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "query_block": { @@ -199,7 +204,7 @@ FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t WHERE federated.t3.name=t.name; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 7 7.00 100.00 100.00 -1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 2 1.00 100.00 100.00 +1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 1 1.00 100.00 100.00 2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL SELECT * FROM federated.t3, (SELECT t1.name FROM federated.t1 @@ -216,7 +221,7 @@ FROM federated.t2 GROUP BY name)) t WHERE federated.t3.name=t.name; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 7 -1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 2 +1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 1 2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL ANALYZE FORMAT=JSON SELECT * @@ -231,6 +236,7 @@ ANALYZE }, "query_block": { "select_id": 1, + "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ @@ -238,9 +244,11 @@ ANALYZE "table": { "table_name": "t3", "access_type": "ALL", + "loops": 1, "r_loops": 1, "rows": 7, "r_rows": 7, + "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, @@ -257,9 +265,11 @@ ANALYZE "key_length": "18", "used_key_parts": ["name"], "ref": ["federated.t3.name"], + "loops": 7, "r_loops": 7, - "rows": 2, + "rows": 1, "r_rows": 0.142857143, + "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, @@ -299,7 +309,7 @@ SELECT * FROM federated.t1 WHERE id >= 5) t WHERE federated.t3.name=t.name; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 7 -1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 2 +1 PRIMARY <derived2> ref key1,distinct_key key1 18 federated.t3.name 1 2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL # # MDEV-21887: federatedx crashes on SELECT ... INTO query in select_handler code @@ -365,12 +375,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t5", "access_type": "ALL", + "loops": 1, "rows": 2, + "cost": "COST_REPLACED", "filtered": 100 } }, @@ -379,7 +392,9 @@ EXPLAIN "table": { "table_name": "<derived2>", "access_type": "ALL", + "loops": 2, "rows": 5, + "cost": "COST_REPLACED", "filtered": 100 }, "buffer_type": "flat", @@ -536,8 +551,7 @@ EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3 WHERE id=3) dt2) dt; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived4> ALL NULL NULL NULL NULL 5 Using where -4 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL +1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL connection slave; CREATE TABLE federated.t10 (a INT,b INT); CREATE TABLE federated.t11 (a INT, b INT); @@ -606,6 +620,702 @@ id name 4 xxx 5 yyy DEALLOCATE PREPARE stmt; +DROP TABLES federated.t1, federated.t2, federated.t3, federated.t10, +federated.t11; +connection slave; +DROP TABLES federated.t1, federated.t2, federated.t3, federated.t10, +federated.t11; +# MDEV-25080: Allow pushdown of queries involving UNIONs +# in outer select to foreign engines +# +connection slave; +CREATE TABLE federated.t1 ( +a varchar(10) +) +DEFAULT CHARSET=latin1; +CREATE TABLE federated.t2 ( +a varchar(16) NOT NULL default '' +) +DEFAULT CHARSET=latin1; +INSERT INTO federated.t1 VALUES ('bcd'), ('abc'), ('cde'); +INSERT INTO federated.t2 VALUES ('cde'), ('efg'), ('abc'), ('bcd'), ('def'); +connection master; +CREATE TABLE federated.t1 ( +a varchar(10) +) +ENGINE="FEDERATED" DEFAULT CHARSET=latin1 +CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1'; +CREATE TABLE federated.t2 ( +a varchar(16) NOT NULL default '' +) +ENGINE="FEDERATED" DEFAULT CHARSET=latin1 +CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t2'; +CREATE TABLE t3 (a varchar(30)) ENGINE=MyISAM; +CREATE TABLE t4 (a varchar(30)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('t3_myisam1'), ('t3_myisam2'), ('t3_myisam3'); +INSERT INTO t4 VALUES ('t4_myisam1'), ('t4_myisam2'), ('t4_myisam3'); +# Pushdown of the whole UNION +SELECT * from federated.t1 UNION SELECT * from federated.t2; +a +abc +bcd +cde +def +efg +EXPLAIN SELECT * from federated.t1 UNION SELECT * from federated.t2; +id select_type table type possible_keys key key_len ref rows Extra +NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL +# Pushdown of a part of the UNION +SELECT * from federated.t1 UNION SELECT * from t3; +a +abc +bcd +cde +t3_myisam1 +t3_myisam2 +t3_myisam3 +EXPLAIN SELECT * from federated.t1 UNION SELECT * from t3; +id select_type table type possible_keys key key_len ref rows Extra +1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +2 UNION t3 ALL NULL NULL NULL NULL 3 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +SELECT * from federated.t1 UNION ALL SELECT * from federated.t2; +a +abc +abc +bcd +bcd +cde +cde +def +efg +EXPLAIN SELECT * from federated.t1 UNION ALL SELECT * from federated.t2; +id select_type table type possible_keys key key_len ref rows Extra +NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL +EXPLAIN FORMAT=JSON SELECT * from federated.t1 UNION ALL +SELECT * from federated.t2; +EXPLAIN +{ + "query_block": { + "union_result": { + "message": "PUSHED UNION" + } + } +} +ANALYZE SELECT * from federated.t1 UNION ALL SELECT * from federated.t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL +ANALYZE FORMAT=JSON SELECT * from federated.t1 UNION ALL +SELECT * from federated.t2; +ANALYZE +{ + "query_optimization": { + "r_total_time_ms": "REPLACED" + }, + "query_block": { + "union_result": { + "message": "PUSHED UNION" + } + } +} +SELECT * from federated.t1 EXCEPT SELECT * from federated.t2; +a +EXPLAIN EXTENDED SELECT * from federated.t1 EXCEPT +SELECT * from federated.t2; +id select_type table type possible_keys key key_len ref rows filtered Extra +NULL PUSHED EXCEPT NULL NULL NULL NULL NULL NULL NULL NULL NULL +Warnings: +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` +EXPLAIN FORMAT=JSON SELECT * from federated.t1 EXCEPT +SELECT * from federated.t2; +EXPLAIN +{ + "query_block": { + "union_result": { + "message": "PUSHED EXCEPT" + } + } +} +SELECT * from federated.t1 INTERSECT SELECT * from federated.t2; +a +abc +bcd +cde +EXPLAIN PARTITIONS SELECT * from federated.t1 INTERSECT +SELECT * from federated.t2; +id select_type table partitions type possible_keys key key_len ref rows Extra +NULL PUSHED INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL NULL +EXPLAIN FORMAT=JSON SELECT * from federated.t1 INTERSECT +SELECT * from federated.t2; +EXPLAIN +{ + "query_block": { + "union_result": { + "message": "PUSHED INTERSECT" + } + } +} +# More than two SELECTs in a UNIT: +SELECT * from federated.t1 INTERSECT +SELECT * from federated.t2 UNION ALL +SELECT * from federated.t2 EXCEPT +SELECT * from federated.t1; +a +def +efg +EXPLAIN +SELECT count(*) from federated.t1 INTERSECT +SELECT count(*) from federated.t2 UNION ALL +SELECT count(*)+20 from federated.t2 EXCEPT +SELECT count(*)+5 from federated.t1; +id select_type table type possible_keys key key_len ref rows Extra +NULL PUSHED UNIT NULL NULL NULL NULL NULL NULL NULL NULL +EXPLAIN FORMAT=JSON +SELECT count(*) from federated.t1 INTERSECT +SELECT count(*) from federated.t2 UNION ALL +SELECT count(*)+20 from federated.t2 EXCEPT +SELECT count(*)+5 from federated.t1; +EXPLAIN +{ + "query_block": { + "union_result": { + "message": "PUSHED UNIT" + } + } +} +ANALYZE +SELECT count(*) from federated.t1 INTERSECT +SELECT count(*) from federated.t2 UNION +SELECT count(*)+20 from federated.t2 EXCEPT +SELECT count(*)+5 from federated.t1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +NULL PUSHED UNIT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL +# UNION inside a derived table: the whole derived table must be pushed +SELECT * FROM +(SELECT * FROM federated.t1 UNION ALL SELECT * FROM federated.t2) q; +a +abc +abc +bcd +bcd +cde +cde +def +efg +EXPLAIN +SELECT * FROM +(SELECT a FROM federated.t1 UNION ALL SELECT * FROM federated.t2) q; +id select_type table type possible_keys key key_len ref rows Extra +1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +# There is an uncacheable side effect due to fetch into @var, +# so the UNION cannot be pushed down as a whole. +# But separate SELECTs can be pushed, and the results are combined +# at the server side +SELECT count(*) FROM federated.t1 UNION +SELECT count(*) FROM federated.t1 EXCEPT +SELECT count(*)+1 FROM federated.t1 +INTO @var; +EXPLAIN SELECT count(*) FROM federated.t1 UNION +SELECT count(*) FROM federated.t2 EXCEPT +SELECT count(*)+1 FROM federated.t1 +INTO @var; +id select_type table type possible_keys key key_len ref rows Extra +1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +2 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +3 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +NULL UNIT RESULT <unit1,2,3> ALL NULL NULL NULL NULL NULL +EXPLAIN FORMAT=JSON SELECT count(*) FROM federated.t1 UNION +SELECT count(*) FROM federated.t2 EXCEPT +SELECT count(*)+2 FROM federated.t2 +INTO @var; +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<unit1,2,3>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "Pushed select" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "Pushed select" + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "EXCEPT", + "table": { + "message": "Pushed select" + } + } + } + ] + } + } +} +# Prepared statements +PREPARE stmt FROM "SELECT * from federated.t1 INTERSECT + SELECT * from federated.t2 UNION ALL + SELECT * from federated.t2 EXCEPT + SELECT * from federated.t1"; +EXECUTE stmt; +a +def +efg +EXECUTE stmt; +a +def +efg +EXECUTE stmt; +a +def +efg +PREPARE stmt FROM "EXPLAIN SELECT * from federated.t1 INTERSECT + SELECT * from federated.t2 UNION ALL + SELECT * from federated.t2 EXCEPT + SELECT * from federated.t1"; +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +NULL PUSHED UNIT NULL NULL NULL NULL NULL NULL NULL NULL +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +NULL PUSHED UNIT NULL NULL NULL NULL NULL NULL NULL NULL +# UNIONs of mixed Federated/MyISAM tables, pushing parts of UNIONs +SELECT * FROM federated.t1 UNION SELECT * FROM t3; +a +abc +bcd +cde +t3_myisam1 +t3_myisam2 +t3_myisam3 +EXPLAIN SELECT * FROM federated.t1 UNION SELECT * FROM t3; +id select_type table type possible_keys key key_len ref rows Extra +1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +2 UNION t3 ALL NULL NULL NULL NULL 3 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +SELECT * FROM federated.t1 UNION ALL +SELECT * FROM t3 EXCEPT +SELECT * FROM federated.t2; +a +t3_myisam1 +t3_myisam2 +t3_myisam3 +EXPLAIN SELECT * FROM federated.t1 UNION ALL +SELECT * FROM t3 EXCEPT +SELECT * FROM federated.t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +2 UNION t3 ALL NULL NULL NULL NULL 3 +3 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +NULL UNIT RESULT <unit1,2,3> ALL NULL NULL NULL NULL NULL +SELECT * FROM t3 UNION ALL +SELECT * FROM federated.t1 EXCEPT +SELECT * FROM t4 INTERSECT +SELECT * FROM federated.t2; +a +abc +bcd +cde +t3_myisam1 +t3_myisam2 +t3_myisam3 +EXPLAIN SELECT * FROM t3 UNION ALL +SELECT * FROM federated.t1 EXCEPT +SELECT * FROM t4 INTERSECT +SELECT * FROM federated.t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 +2 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +5 EXCEPT <derived3> ALL NULL NULL NULL NULL 3 +3 DERIVED t4 ALL NULL NULL NULL NULL 3 +4 INTERSECT t2 ALL NULL NULL NULL NULL 5 +NULL INTERSECT RESULT <intersect3,4> ALL NULL NULL NULL NULL NULL +NULL UNIT RESULT <unit1,2,5> ALL NULL NULL NULL NULL NULL +SELECT * FROM federated.t2 UNION ALL +SELECT * FROM t3 EXCEPT +SELECT * FROM t4 INTERSECT +SELECT * FROM federated.t1; +a +abc +bcd +cde +def +efg +t3_myisam1 +t3_myisam2 +t3_myisam3 +EXPLAIN SELECT * FROM federated.t2 UNION ALL +SELECT * FROM t3 EXCEPT +SELECT * FROM t4 INTERSECT +SELECT * FROM federated.t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +2 UNION t3 ALL NULL NULL NULL NULL 3 +5 EXCEPT <derived3> ALL NULL NULL NULL NULL 3 +3 DERIVED t4 ALL NULL NULL NULL NULL 3 +4 INTERSECT t1 ALL NULL NULL NULL NULL 3 +NULL INTERSECT RESULT <intersect3,4> ALL NULL NULL NULL NULL NULL +NULL UNIT RESULT <unit1,2,5> ALL NULL NULL NULL NULL NULL +# Parenthesis must not prevent the whole UNIONs pushdown +EXPLAIN (SELECT * FROM federated.t1 UNION +SELECT * FROM federated.t2) UNION ALL +SELECT * FROM federated.t1; +id select_type table type possible_keys key key_len ref rows Extra +NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL +(SELECT * FROM federated.t1 UNION +SELECT * FROM federated.t2) UNION ALL +SELECT * FROM federated.t1; +a +abc +abc +bcd +bcd +cde +cde +def +efg +EXPLAIN (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2) +UNION ALL (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2); +id select_type table type possible_keys key key_len ref rows Extra +NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL +(SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2) UNION ALL +(SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2); +a +abc +abc +bcd +bcd +cde +cde +def +def +efg +efg +# Union of tables containing different INT data types +connection slave; +CREATE TABLE federated.t11 (a smallint(6) NOT NULL); +INSERT INTO federated.t11 VALUES (-32678), (-1), (0); +CREATE TABLE federated.t12 (a int(10) UNSIGNED NOT NULL); +INSERT INTO federated.t12 VALUES (0), (1), (32767); +connection master; +CREATE TABLE federated.t11 (a smallint(6) NOT NULL) +ENGINE="FEDERATED" +CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t11'; +CREATE TABLE federated.t12 (a int(10) UNSIGNED NOT NULL) +ENGINE="FEDERATED" +CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t12'; +# Entire UNION pushdown +SELECT a FROM federated.t12 UNION ALL SELECT a FROM federated.t11; +a +-1 +-32678 +0 +0 +1 +32767 +EXPLAIN SELECT a FROM federated.t12 UNION ALL SELECT a FROM federated.t11; +id select_type table type possible_keys key key_len ref rows Extra +NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL +SELECT a FROM federated.t11 UNION SELECT a FROM federated.t12; +a +-1 +-32678 +0 +1 +32767 +# Partial pushdown of SELECTs composing the UNION +SELECT a FROM federated.t12 UNION SELECT a FROM federated.t11 UNION SELECT 123; +a +-1 +-32678 +0 +1 +123 +32767 +EXPLAIN +SELECT a FROM federated.t12 UNION SELECT a FROM federated.t11 +UNION SELECT 123; +id select_type table type possible_keys key key_len ref rows Extra +1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +2 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2,3> ALL NULL NULL NULL NULL NULL +SELECT a FROM federated.t12 EXCEPT +SELECT 1 UNION ALL +SELECT a FROM federated.t11 EXCEPT +SELECT 0; +a +-1 +-32678 +32767 +# Union of tables containing different string data types +connection slave; +CREATE TABLE federated.t13 (a CHAR(6)); +INSERT INTO federated.t13 VALUES ('t13abc'), ('t13xx'), ('common'); +CREATE TABLE federated.t14 (a VARCHAR(8)); +INSERT INTO federated.t14 VALUES ('t14abcde'), ('t14xyzzz'), ('common'); +connection master; +CREATE TABLE federated.t13 (a CHAR(6)) +ENGINE="FEDERATED" +CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t13'; +CREATE TABLE federated.t14 (a VARCHAR(8)) +ENGINE="FEDERATED" +CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t14'; +SELECT * FROM federated.t13 UNION SELECT * FROM federated.t14; +a +common +t13abc +t13xx +t14abcde +t14xyzzz +EXPLAIN SELECT * FROM federated.t13 UNION SELECT * FROM federated.t14; +id select_type table type possible_keys key key_len ref rows Extra +NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL +SELECT * FROM federated.t14 UNION ALL SELECT * FROM federated.t13; +a +common +common +t13abc +t13xx +t14abcde +t14xyzzz +SELECT * FROM federated.t14 UNION +SELECT * FROM federated.t13 UNION +SELECT '123456789000'; +a +t14abcde +t14xyzzz +common +t13abc +t13xx +123456789000 +EXPLAIN SELECT * FROM federated.t14 UNION +SELECT * FROM federated.t13 UNION +SELECT '123456789000'; +id select_type table type possible_keys key key_len ref rows Extra +1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +2 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2,3> ALL NULL NULL NULL NULL NULL +SELECT * FROM federated.t13 UNION +SELECT '123456789000' UNION +SELECT * FROM federated.t14; +a +123456789000 +common +t13abc +t13xx +t14abcde +t14xyzzz +# CREATE TABLE .. AS from a pushed UNION +CREATE TABLE t5 AS SELECT * FROM federated.t13 UNION +SELECT * FROM federated.t14; +SHOW CREATE TABLE t5; +Table Create Table +t5 CREATE TABLE `t5` ( + `a` varchar(8) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT * FROM t5; +a +common +t13abc +t13xx +t14abcde +t14xyzzz +CREATE TABLE t6 AS SELECT a FROM federated.t12 EXCEPT +SELECT 1 UNION ALL +SELECT a FROM federated.t11 EXCEPT +SELECT 0; +SHOW CREATE TABLE t6; +Table Create Table +t6 CREATE TABLE `t6` ( + `a` decimal(10,0) NOT NULL DEFAULT 0 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT * FROM t6; +a +-1 +-32678 +32767 +# +# MDEV-30828 ORDER BY clause using an integer (positional argument) +# +SELECT a FROM federated.t1 UNION SELECT a FROM federated.t2 ORDER BY 1; +a +abc +bcd +cde +def +efg +SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY a DESC; +a +efg +def +cde +cde +bcd +bcd +abc +abc +# Check handling of incorrect ORDER BY clause +SELECT a FROM federated.t1 UNION SELECT a FROM federated.t2 ORDER BY 2; +ERROR 42S22: Unknown column '2' in 'order clause' +PREPARE stmt FROM +"SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 2"; +ERROR 42S22: Unknown column '2' in 'order clause' +SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 2,1,3; +ERROR 42S22: Unknown column '2' in 'order clause' +SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY t1.a; +ERROR 42000: Table 't1' from one of the SELECTs cannot be used in ORDER clause +SELECT * from federated.t1 INTERSECT +SELECT * from federated.t2 UNION ALL +SELECT * from federated.t2 EXCEPT +SELECT * from federated.t1 +ORDER BY 1; +a +def +efg +SELECT * from federated.t1 INTERSECT +SELECT * from federated.t2 UNION ALL +SELECT * from federated.t2 EXCEPT +SELECT * from federated.t1 +ORDER BY 3; +ERROR 42S22: Unknown column '3' in 'order clause' +# UNION of mixed Federated/MyISAM tables, pushing parts of UNIONs +SELECT * FROM federated.t1 UNION SELECT * FROM t3 ORDER BY a; +a +abc +bcd +cde +t3_myisam1 +t3_myisam2 +t3_myisam3 +SELECT * FROM federated.t1 UNION SELECT * FROM t3 ORDER BY 2; +ERROR 42S22: Unknown column '2' in 'order clause' +# +# MDEV-32382 FederatedX error on pushdown of statement having CTE +# +# Single SELECT with CTE +WITH cte AS (SELECT * FROM federated.t1) +SELECT * FROM cte; +a +bcd +abc +cde +explain extended WITH cte AS (SELECT * FROM federated.t1) +SELECT * FROM cte; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL NULL +Warnings: +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` +# Pushdown of a UNION having CTE's +WITH cte AS (SELECT * FROM federated.t1), +cte2 AS (SELECT * FROM federated.t2) +SELECT * FROM cte +UNION +SELECT * FROM cte2; +a +abc +bcd +cde +def +efg +explain extended WITH cte AS (SELECT * FROM federated.t1), +cte2 AS (SELECT * FROM federated.t2) +SELECT * FROM cte +UNION +SELECT * FROM cte2; +id select_type table type possible_keys key key_len ref rows filtered Extra +NULL PUSHED UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL +Warnings: +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` +# Partial pushdown is not allowed for unions with CTE's, however a CTE +# may be pushed down a derived table +WITH cte AS (SELECT * FROM federated.t1) +SELECT * FROM cte +UNION ALL +SELECT * FROM t3; +a +abc +bcd +cde +t3_myisam1 +t3_myisam2 +t3_myisam3 +explain extended WITH cte AS (SELECT * FROM federated.t1) +SELECT * FROM cte +UNION ALL +SELECT * FROM t3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 +2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL +3 UNION t3 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +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` +WITH cte AS (SELECT * FROM federated.t1 UNION SELECT * FROM t3) +SELECT * FROM cte; +a +abc +bcd +cde +t3_myisam1 +t3_myisam2 +t3_myisam3 +explain extended WITH cte AS (SELECT * FROM federated.t1 UNION SELECT * FROM t3) +SELECT * FROM cte; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 +3 UNION t3 ALL NULL NULL NULL NULL 3 100.00 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +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` +# Two CTE's where one CTE refers to another +WITH cte AS (SELECT * FROM federated.t1), +cte2 AS (SELECT * FROM t3 +WHERE t3.a NOT IN (SELECT * FROM cte)) +SELECT * FROM cte JOIN cte2; +a a +abc t3_myisam1 +abc t3_myisam2 +abc t3_myisam3 +bcd t3_myisam1 +bcd t3_myisam2 +bcd t3_myisam3 +cde t3_myisam1 +cde t3_myisam2 +cde t3_myisam3 +explain extended WITH cte AS (SELECT * FROM federated.t1), +cte2 AS (SELECT * FROM t3 +WHERE t3.a NOT IN (SELECT * FROM cte)) +SELECT * FROM cte JOIN cte2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +5 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL +4 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 3 100.00 +2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL +Warnings: +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`)))) +connection master; +DROP TABLES federated.t1, federated.t2, t3, t4, t5, t6, federated.t11, +federated.t12, federated.t13, federated.t14; +connection slave; +DROP TABLES federated.t1, federated.t2, federated.t11, federated.t12, +federated.t13, federated.t14; +connection default; set global federated_pushdown=0; connection master; DROP TABLE IF EXISTS federated.t1; diff --git a/mysql-test/suite/federated/federatedx_create_handlers.test b/mysql-test/suite/federated/federatedx_create_handlers.test index c5ac36de..736d42e9 100644 --- a/mysql-test/suite/federated/federatedx_create_handlers.test +++ b/mysql-test/suite/federated/federatedx_create_handlers.test @@ -76,6 +76,7 @@ SELECT id FROM federated.t1 WHERE id < 5; EXPLAIN EXTENDED SELECT id FROM federated.t1 WHERE id < 5; +--source include/explain-no-costs.inc EXPLAIN FORMAT=JSON SELECT id FROM federated.t1 WHERE id < 5; @@ -103,6 +104,7 @@ SELECT * FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t WHERE federated.t3.name=t.name; +--source include/explain-no-costs.inc EXPLAIN FORMAT=JSON SELECT * FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t @@ -182,6 +184,7 @@ explain select * from federated.t1 where name in (select name from federated.t2); +--source include/explain-no-costs.inc explain format=json select * from federated.t1 where name in (select name from federated.t2); @@ -201,6 +204,7 @@ select * from t5, where name in (select name from federated.t2) or name like 'foo%') as TQ; --echo # Must not show elements with select_id=3 +--source include/explain-no-costs.inc explain format=json select * from t5, (select id from federated.t1 @@ -434,6 +438,423 @@ EXECUTE stmt; DEALLOCATE PREPARE stmt; +DROP TABLES federated.t1, federated.t2, federated.t3, federated.t10, + federated.t11; +connection slave; +DROP TABLES federated.t1, federated.t2, federated.t3, federated.t10, + federated.t11; + + +--echo # MDEV-25080: Allow pushdown of queries involving UNIONs +--echo # in outer select to foreign engines +--echo # + +connection slave; + +CREATE TABLE federated.t1 ( + a varchar(10) +) +DEFAULT CHARSET=latin1; + +CREATE TABLE federated.t2 ( + a varchar(16) NOT NULL default '' +) +DEFAULT CHARSET=latin1; + +INSERT INTO federated.t1 VALUES ('bcd'), ('abc'), ('cde'); +INSERT INTO federated.t2 VALUES ('cde'), ('efg'), ('abc'), ('bcd'), ('def'); + +connection master; + +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval +CREATE TABLE federated.t1 ( + a varchar(10) +) +ENGINE="FEDERATED" DEFAULT CHARSET=latin1 +CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; + +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval +CREATE TABLE federated.t2 ( + a varchar(16) NOT NULL default '' +) +ENGINE="FEDERATED" DEFAULT CHARSET=latin1 +CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t2'; + +CREATE TABLE t3 (a varchar(30)) ENGINE=MyISAM; +CREATE TABLE t4 (a varchar(30)) ENGINE=MyISAM; + +INSERT INTO t3 VALUES ('t3_myisam1'), ('t3_myisam2'), ('t3_myisam3'); +INSERT INTO t4 VALUES ('t4_myisam1'), ('t4_myisam2'), ('t4_myisam3'); + +--echo # Pushdown of the whole UNION +--sorted_result +SELECT * from federated.t1 UNION SELECT * from federated.t2; +EXPLAIN SELECT * from federated.t1 UNION SELECT * from federated.t2; + +--echo # Pushdown of a part of the UNION +--sorted_result +SELECT * from federated.t1 UNION SELECT * from t3; +EXPLAIN SELECT * from federated.t1 UNION SELECT * from t3; + +--sorted_result +SELECT * from federated.t1 UNION ALL SELECT * from federated.t2; +EXPLAIN SELECT * from federated.t1 UNION ALL SELECT * from federated.t2; + +EXPLAIN FORMAT=JSON SELECT * from federated.t1 UNION ALL + SELECT * from federated.t2; + +ANALYZE SELECT * from federated.t1 UNION ALL SELECT * from federated.t2; + +--source include/analyze-format.inc +ANALYZE FORMAT=JSON SELECT * from federated.t1 UNION ALL + SELECT * from federated.t2; + +--sorted_result +SELECT * from federated.t1 EXCEPT SELECT * from federated.t2; + +EXPLAIN EXTENDED SELECT * from federated.t1 EXCEPT + SELECT * from federated.t2; + +EXPLAIN FORMAT=JSON SELECT * from federated.t1 EXCEPT + SELECT * from federated.t2; + +--sorted_result +SELECT * from federated.t1 INTERSECT SELECT * from federated.t2; + +EXPLAIN PARTITIONS SELECT * from federated.t1 INTERSECT + SELECT * from federated.t2; + +EXPLAIN FORMAT=JSON SELECT * from federated.t1 INTERSECT + SELECT * from federated.t2; + +--echo # More than two SELECTs in a UNIT: +--sorted_result +SELECT * from federated.t1 INTERSECT + SELECT * from federated.t2 UNION ALL + SELECT * from federated.t2 EXCEPT + SELECT * from federated.t1; + +EXPLAIN + SELECT count(*) from federated.t1 INTERSECT + SELECT count(*) from federated.t2 UNION ALL + SELECT count(*)+20 from federated.t2 EXCEPT + SELECT count(*)+5 from federated.t1; + +EXPLAIN FORMAT=JSON + SELECT count(*) from federated.t1 INTERSECT + SELECT count(*) from federated.t2 UNION ALL + SELECT count(*)+20 from federated.t2 EXCEPT + SELECT count(*)+5 from federated.t1; + +ANALYZE + SELECT count(*) from federated.t1 INTERSECT + SELECT count(*) from federated.t2 UNION + SELECT count(*)+20 from federated.t2 EXCEPT + SELECT count(*)+5 from federated.t1; + +--echo # UNION inside a derived table: the whole derived table must be pushed +--sorted_result +SELECT * FROM + (SELECT * FROM federated.t1 UNION ALL SELECT * FROM federated.t2) q; + +EXPLAIN + SELECT * FROM + (SELECT a FROM federated.t1 UNION ALL SELECT * FROM federated.t2) q; + +--echo # There is an uncacheable side effect due to fetch into @var, +--echo # so the UNION cannot be pushed down as a whole. +--echo # But separate SELECTs can be pushed, and the results are combined +--echo # at the server side + +--disable_warnings +--sorted_result +SELECT count(*) FROM federated.t1 UNION + SELECT count(*) FROM federated.t1 EXCEPT + SELECT count(*)+1 FROM federated.t1 + INTO @var; + +EXPLAIN SELECT count(*) FROM federated.t1 UNION + SELECT count(*) FROM federated.t2 EXCEPT + SELECT count(*)+1 FROM federated.t1 + INTO @var; + +EXPLAIN FORMAT=JSON SELECT count(*) FROM federated.t1 UNION + SELECT count(*) FROM federated.t2 EXCEPT + SELECT count(*)+2 FROM federated.t2 + INTO @var; +--enable_warnings + +--echo # Prepared statements +PREPARE stmt FROM "SELECT * from federated.t1 INTERSECT + SELECT * from federated.t2 UNION ALL + SELECT * from federated.t2 EXCEPT + SELECT * from federated.t1"; + +--sorted_result +EXECUTE stmt; +--sorted_result +EXECUTE stmt; +--sorted_result +EXECUTE stmt; + +PREPARE stmt FROM "EXPLAIN SELECT * from federated.t1 INTERSECT + SELECT * from federated.t2 UNION ALL + SELECT * from federated.t2 EXCEPT + SELECT * from federated.t1"; + +--sorted_result +EXECUTE stmt; +--sorted_result +EXECUTE stmt; + +--echo # UNIONs of mixed Federated/MyISAM tables, pushing parts of UNIONs +--sorted_result +SELECT * FROM federated.t1 UNION SELECT * FROM t3; +EXPLAIN SELECT * FROM federated.t1 UNION SELECT * FROM t3; + +--sorted_result +SELECT * FROM federated.t1 UNION ALL + SELECT * FROM t3 EXCEPT + SELECT * FROM federated.t2; +EXPLAIN SELECT * FROM federated.t1 UNION ALL + SELECT * FROM t3 EXCEPT + SELECT * FROM federated.t2; + +--sorted_result +SELECT * FROM t3 UNION ALL + SELECT * FROM federated.t1 EXCEPT + SELECT * FROM t4 INTERSECT + SELECT * FROM federated.t2; +EXPLAIN SELECT * FROM t3 UNION ALL + SELECT * FROM federated.t1 EXCEPT + SELECT * FROM t4 INTERSECT + SELECT * FROM federated.t2; + +--sorted_result +SELECT * FROM federated.t2 UNION ALL + SELECT * FROM t3 EXCEPT + SELECT * FROM t4 INTERSECT + SELECT * FROM federated.t1; +EXPLAIN SELECT * FROM federated.t2 UNION ALL + SELECT * FROM t3 EXCEPT + SELECT * FROM t4 INTERSECT + SELECT * FROM federated.t1; + +--echo # Parenthesis must not prevent the whole UNIONs pushdown +EXPLAIN (SELECT * FROM federated.t1 UNION + SELECT * FROM federated.t2) UNION ALL + SELECT * FROM federated.t1; + +--sorted_result +(SELECT * FROM federated.t1 UNION + SELECT * FROM federated.t2) UNION ALL + SELECT * FROM federated.t1; + +EXPLAIN (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2) + UNION ALL (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2); + +--sorted_result +(SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2) UNION ALL + (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2); + +--echo # Union of tables containing different INT data types +connection slave; + +CREATE TABLE federated.t11 (a smallint(6) NOT NULL); +INSERT INTO federated.t11 VALUES (-32678), (-1), (0); + +CREATE TABLE federated.t12 (a int(10) UNSIGNED NOT NULL); +INSERT INTO federated.t12 VALUES (0), (1), (32767); + +connection master; + +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval +CREATE TABLE federated.t11 (a smallint(6) NOT NULL) +ENGINE="FEDERATED" +CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t11'; + +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval +CREATE TABLE federated.t12 (a int(10) UNSIGNED NOT NULL) +ENGINE="FEDERATED" +CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t12'; + +--echo # Entire UNION pushdown +--sorted_result +SELECT a FROM federated.t12 UNION ALL SELECT a FROM federated.t11; +EXPLAIN SELECT a FROM federated.t12 UNION ALL SELECT a FROM federated.t11; + +--sorted_result +SELECT a FROM federated.t11 UNION SELECT a FROM federated.t12; + +--echo # Partial pushdown of SELECTs composing the UNION +--sorted_result +SELECT a FROM federated.t12 UNION SELECT a FROM federated.t11 UNION SELECT 123; +EXPLAIN + SELECT a FROM federated.t12 UNION SELECT a FROM federated.t11 + UNION SELECT 123; + +--sorted_result +SELECT a FROM federated.t12 EXCEPT + SELECT 1 UNION ALL + SELECT a FROM federated.t11 EXCEPT + SELECT 0; + +--echo # Union of tables containing different string data types +connection slave; +CREATE TABLE federated.t13 (a CHAR(6)); +INSERT INTO federated.t13 VALUES ('t13abc'), ('t13xx'), ('common'); + +CREATE TABLE federated.t14 (a VARCHAR(8)); +INSERT INTO federated.t14 VALUES ('t14abcde'), ('t14xyzzz'), ('common'); + +connection master; + +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval +CREATE TABLE federated.t13 (a CHAR(6)) +ENGINE="FEDERATED" +CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t13'; + +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval +CREATE TABLE federated.t14 (a VARCHAR(8)) +ENGINE="FEDERATED" +CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t14'; + +--sorted_result +SELECT * FROM federated.t13 UNION SELECT * FROM federated.t14; +EXPLAIN SELECT * FROM federated.t13 UNION SELECT * FROM federated.t14; + +--sorted_result +SELECT * FROM federated.t14 UNION ALL SELECT * FROM federated.t13; + +SELECT * FROM federated.t14 UNION + SELECT * FROM federated.t13 UNION + SELECT '123456789000'; + +EXPLAIN SELECT * FROM federated.t14 UNION + SELECT * FROM federated.t13 UNION + SELECT '123456789000'; + +--sorted_result +SELECT * FROM federated.t13 UNION + SELECT '123456789000' UNION + SELECT * FROM federated.t14; + +--echo # CREATE TABLE .. AS from a pushed UNION +CREATE TABLE t5 AS SELECT * FROM federated.t13 UNION + SELECT * FROM federated.t14; +SHOW CREATE TABLE t5; +--sorted_result +SELECT * FROM t5; + +CREATE TABLE t6 AS SELECT a FROM federated.t12 EXCEPT + SELECT 1 UNION ALL + SELECT a FROM federated.t11 EXCEPT + SELECT 0; +SHOW CREATE TABLE t6; +--sorted_result +SELECT * FROM t6; + +--echo # +--echo # MDEV-30828 ORDER BY clause using an integer (positional argument) +--echo # +SELECT a FROM federated.t1 UNION SELECT a FROM federated.t2 ORDER BY 1; + +SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY a DESC; + +--echo # Check handling of incorrect ORDER BY clause +--error ER_BAD_FIELD_ERROR +SELECT a FROM federated.t1 UNION SELECT a FROM federated.t2 ORDER BY 2; +--error ER_BAD_FIELD_ERROR +PREPARE stmt FROM + "SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 2"; + +--error ER_BAD_FIELD_ERROR +SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 2,1,3; +--error ER_TABLENAME_NOT_ALLOWED_HERE +SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY t1.a; +SELECT * from federated.t1 INTERSECT + SELECT * from federated.t2 UNION ALL + SELECT * from federated.t2 EXCEPT + SELECT * from federated.t1 + ORDER BY 1; +--error ER_BAD_FIELD_ERROR +SELECT * from federated.t1 INTERSECT + SELECT * from federated.t2 UNION ALL + SELECT * from federated.t2 EXCEPT + SELECT * from federated.t1 + ORDER BY 3; +--echo # UNION of mixed Federated/MyISAM tables, pushing parts of UNIONs +SELECT * FROM federated.t1 UNION SELECT * FROM t3 ORDER BY a; +--error ER_BAD_FIELD_ERROR +SELECT * FROM federated.t1 UNION SELECT * FROM t3 ORDER BY 2; + + +--echo # +--echo # MDEV-32382 FederatedX error on pushdown of statement having CTE +--echo # + +--echo # Single SELECT with CTE +let $query= WITH cte AS (SELECT * FROM federated.t1) + SELECT * FROM cte; +eval $query; +eval explain extended $query; + +--echo # Pushdown of a UNION having CTE's +let $query= WITH cte AS (SELECT * FROM federated.t1), + cte2 AS (SELECT * FROM federated.t2) + SELECT * FROM cte + UNION + SELECT * FROM cte2; +--sorted_result +eval $query; +eval explain extended $query; + +# CREATE TABLE t3 (a int); +# INSERT INTO t3 VALUES (101),(102),(103); + +--echo # Partial pushdown is not allowed for unions with CTE's, however a CTE +--echo # may be pushed down a derived table +let $query= WITH cte AS (SELECT * FROM federated.t1) + SELECT * FROM cte + UNION ALL + SELECT * FROM t3; +--sorted_result +eval $query; +eval explain extended $query; + +let $query= WITH cte AS (SELECT * FROM federated.t1 UNION SELECT * FROM t3) + SELECT * FROM cte; +--sorted_result +eval $query; +eval explain extended $query; + +--echo # Two CTE's where one CTE refers to another +let $query= WITH cte AS (SELECT * FROM federated.t1), + cte2 AS (SELECT * FROM t3 + WHERE t3.a NOT IN (SELECT * FROM cte)) + SELECT * FROM cte JOIN cte2; +--sorted_result +eval $query; +eval explain extended $query; + + +# Cleanup +connection master; +DROP TABLES federated.t1, federated.t2, t3, t4, t5, t6, federated.t11, + federated.t12, federated.t13, federated.t14; + +connection slave; +DROP TABLES federated.t1, federated.t2, federated.t11, federated.t12, + federated.t13, federated.t14; + +connection default; + set global federated_pushdown=0; source include/federated_cleanup.inc; diff --git a/mysql-test/suite/federated/my.cnf b/mysql-test/suite/federated/my.cnf index b97f8266..21fd1b39 100644 --- a/mysql-test/suite/federated/my.cnf +++ b/mysql-test/suite/federated/my.cnf @@ -1,6 +1,5 @@ # Use default setting for mysqld processes -!include include/default_mysqld.cnf -!include include/default_client.cnf +!include include/default_my.cnf [mysqld.1] federated |