summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/federated/federatedx_create_handlers.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/federated/federatedx_create_handlers.result')
-rw-r--r--mysql-test/suite/federated/federatedx_create_handlers.result550
1 files changed, 550 insertions, 0 deletions
diff --git a/mysql-test/suite/federated/federatedx_create_handlers.result b/mysql-test/suite/federated/federatedx_create_handlers.result
new file mode 100644
index 00000000..1a2f8c5d
--- /dev/null
+++ b/mysql-test/suite/federated/federatedx_create_handlers.result
@@ -0,0 +1,550 @@
+connect master,127.0.0.1,root,,test,$MASTER_MYPORT,;
+connect slave,127.0.0.1,root,,test,$SLAVE_MYPORT,;
+connection master;
+CREATE DATABASE federated;
+connection slave;
+CREATE DATABASE federated;
+connection default;
+set global federated_pushdown=1;
+connection slave;
+DROP TABLE IF EXISTS federated.t1;
+Warnings:
+Note 1051 Unknown table 'federated.t1'
+CREATE TABLE federated.t1 (
+id int(20) NOT NULL,
+name varchar(16) NOT NULL default ''
+)
+DEFAULT CHARSET=latin1;
+INSERT INTO federated.t1 VALUES
+(3,'xxx'), (7,'yyy'), (4,'xxx'), (1,'zzz'), (5,'yyy');
+DROP TABLE IF EXISTS federated.t2;
+Warnings:
+Note 1051 Unknown table 'federated.t2'
+CREATE TABLE federated.t2 (
+name varchar(16) NOT NULL default ''
+)
+DEFAULT CHARSET=latin1;
+INSERT INTO federated.t2 VALUES
+('yyy'), ('www'), ('yyy'), ('xxx'), ('www'), ('yyy'), ('www');
+connection master;
+DROP TABLE IF EXISTS federated.t1;
+Warnings:
+Note 1051 Unknown table 'federated.t1'
+CREATE TABLE federated.t1 (
+id int(20) NOT NULL,
+name varchar(16) NOT NULL default ''
+)
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1';
+DROP TABLE IF EXISTS federated.t2;
+Warnings:
+Note 1051 Unknown table 'federated.t2'
+CREATE TABLE federated.t2 (
+name varchar(16) NOT NULL default ''
+)
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t2';
+SELECT * FROM federated.t1;
+id name
+3 xxx
+7 yyy
+4 xxx
+1 zzz
+5 yyy
+SELECT id FROM federated.t1 WHERE id < 5;
+id
+3
+4
+1
+SELECT count(*), name FROM federated.t1 WHERE id < 5 GROUP BY name;
+count(*) name
+2 xxx
+1 zzz
+SELECT * FROM federated.t1, federated.t2
+WHERE federated.t1.name = federated.t2.name;
+id name name
+7 yyy yyy
+5 yyy yyy
+7 yyy yyy
+5 yyy yyy
+3 xxx xxx
+4 xxx xxx
+7 yyy yyy
+5 yyy yyy
+SELECT * FROM federated.t1 LEFT JOIN federated.t2
+ON federated.t1.name = federated.t2.name
+WHERE federated.t1.id > 1;
+id name name
+7 yyy yyy
+5 yyy yyy
+7 yyy yyy
+5 yyy yyy
+3 xxx xxx
+4 xxx xxx
+7 yyy yyy
+5 yyy yyy
+SELECT * FROM federated.t1
+WHERE id IN (SELECT count(*) FROM federated.t2 GROUP BY name);
+id name
+3 xxx
+1 zzz
+EXPLAIN
+SELECT id FROM federated.t1 WHERE id < 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL
+EXPLAIN EXTENDED
+SELECT id FROM federated.t1 WHERE id < 5;
+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 select `federated`.`t1`.`id` AS `id` from `federated`.`t1` where `federated`.`t1`.`id` < 5
+EXPLAIN FORMAT=JSON
+SELECT id FROM federated.t1 WHERE id < 5;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "message": "Pushed select"
+ }
+ }
+}
+ANALYZE
+SELECT id FROM federated.t1 WHERE id < 5;
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
+ANALYZE FORMAT=JSON
+SELECT id FROM federated.t1 WHERE id < 5;
+ANALYZE
+{
+ "query_optimization": {
+ "r_total_time_ms": "REPLACED"
+ },
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "message": "Pushed select"
+ }
+ }
+}
+CREATE TABLE federated.t3 (
+name varchar(16) NOT NULL default ''
+)
+DEFAULT CHARSET=latin1;
+INSERT INTO federated.t3 VALUES
+('yyy'), ('www'), ('yyy'), ('xxx'), ('www'), ('yyy'), ('www');
+SELECT *
+FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
+WHERE federated.t3.name=t.name;
+name id name
+yyy 5 yyy
+yyy 7 yyy
+yyy 5 yyy
+yyy 7 yyy
+xxx 4 xxx
+yyy 5 yyy
+yyy 7 yyy
+EXPLAIN
+SELECT *
+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
+2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL
+EXPLAIN FORMAT=JSON
+SELECT *
+FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
+WHERE federated.t3.name=t.name;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t3",
+ "access_type": "ALL",
+ "rows": 7,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "<derived2>",
+ "access_type": "ref",
+ "possible_keys": ["key0"],
+ "key": "key0",
+ "key_length": "18",
+ "used_key_parts": ["name"],
+ "ref": ["federated.t3.name"],
+ "rows": 2,
+ "filtered": 100,
+ "materialized": {
+ "query_block": {
+ "select_id": 2,
+ "table": {
+ "message": "Pushed derived"
+ }
+ }
+ }
+ }
+ }
+ ]
+ }
+}
+ANALYZE
+SELECT *
+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
+2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+WHERE id IN (SELECT count(*)
+FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+name name
+xxx xxx
+EXPLAIN
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+WHERE id IN (SELECT count(*)
+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
+2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL
+ANALYZE FORMAT=JSON
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+WHERE id IN (SELECT count(*)
+FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+ANALYZE
+{
+ "query_optimization": {
+ "r_total_time_ms": "REPLACED"
+ },
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t3",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 7,
+ "r_rows": 7,
+ "r_table_time_ms": "REPLACED",
+ "r_other_time_ms": "REPLACED",
+ "r_engine_stats": REPLACED,
+ "filtered": 100,
+ "r_filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "<derived2>",
+ "access_type": "ref",
+ "possible_keys": ["key0"],
+ "key": "key0",
+ "key_length": "18",
+ "used_key_parts": ["name"],
+ "ref": ["federated.t3.name"],
+ "r_loops": 7,
+ "rows": 2,
+ "r_rows": 0.142857143,
+ "r_table_time_ms": "REPLACED",
+ "r_other_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100,
+ "materialized": {
+ "query_block": {
+ "select_id": 2,
+ "table": {
+ "message": "Pushed derived"
+ }
+ }
+ }
+ }
+ }
+ ]
+ }
+}
+SELECT t.id, federated.t3.name
+FROM federated.t3,
+( SELECT * FROM federated.t1 WHERE id < 3
+UNION
+SELECT * FROM federated.t1 WHERE id >= 5) t
+WHERE federated.t3.name=t.name;
+id name
+5 yyy
+7 yyy
+5 yyy
+7 yyy
+5 yyy
+7 yyy
+EXPLAIN
+SELECT t.id, federated.t3.name
+FROM federated.t3,
+( SELECT * FROM federated.t1 WHERE id < 3
+UNION
+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
+2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL
+#
+# MDEV-21887: federatedx crashes on SELECT ... INTO query in select_handler code
+#
+CREATE TABLE federated.t4 (
+id int(20) NOT NULL,
+name varchar(16) NOT NULL default ''
+) engine=myisam;
+insert into federated.t4 select * from federated.t1;
+select * from federated.t4;
+id name
+1 zzz
+3 xxx
+4 xxx
+5 yyy
+7 yyy
+select name into @var from federated.t1 where id=3 limit 1 ;
+select @var;
+@var
+xxx
+select name into outfile 'tmp.txt' from federated.t1;
+#
+# MDEV-22993: Crash on EXPLAIN with PUSHED DOWN SELECT and subquery
+#
+explain
+select * from federated.t1
+where name in (select name 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
+explain format=json
+select * from federated.t1
+where name in (select name from federated.t2);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "message": "Pushed select"
+ }
+ }
+}
+#
+# MDEV-22993, testcase #2: EXPLAIN output doesn't make sense when
+# derived table pushdown is used.
+#
+create table t5 (a int) engine=myisam;
+insert into t5 values (1),(2);
+# Must not show lines with id=3
+explain
+select * from t5,
+(select id from federated.t1
+where name in (select name from federated.t2) or name like 'foo%') as TQ;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t5 ALL NULL NULL NULL NULL 2
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
+2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL
+# Must not show elements with select_id=3
+explain format=json
+select * from t5,
+(select id from federated.t1
+where name in (select name from federated.t2) or name like 'foo%') as TQ;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t5",
+ "access_type": "ALL",
+ "rows": 2,
+ "filtered": 100
+ }
+ },
+ {
+ "block-nl-join": {
+ "table": {
+ "table_name": "<derived2>",
+ "access_type": "ALL",
+ "rows": 5,
+ "filtered": 100
+ },
+ "buffer_type": "flat",
+ "buffer_size": "65",
+ "join_type": "BNL",
+ "materialized": {
+ "query_block": {
+ "select_id": 2,
+ "table": {
+ "message": "Pushed derived"
+ }
+ }
+ }
+ }
+ }
+ ]
+ }
+}
+drop table t5;
+DROP TABLE federated.t1, federated.t2, federated.t3, federated.t4;
+connection slave;
+DROP TABLE federated.t1, federated.t2;
+connection default;
+#
+# MDEV-23778: Derived handler used for big derived tables
+#
+connection slave;
+CREATE TABLE federated.t1 (
+a varchar(100) NOT NULL default '123'
+)
+DEFAULT CHARSET=latin1;
+CREATE TABLE federated.t2 LIKE federated.t1;
+BEGIN NOT ATOMIC
+DECLARE i INT DEFAULT 0;
+START TRANSACTION;
+WHILE i < 70000 DO
+INSERT INTO federated.t1 VALUES (i);
+SET i = i + 1;
+END WHILE;
+COMMIT;
+END
+$$
+connection master;
+CREATE TABLE federated.t1 (
+a varchar(100) NOT NULL default '123'
+)
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1';
+CREATE TABLE federated.t2 (
+a varchar(100) NOT NULL default '123'
+)
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t2';
+SELECT COUNT(DISTINCT a) FROM federated.t1;
+COUNT(DISTINCT a)
+70000
+INSERT INTO federated.t2
+SELECT * FROM (SELECT * FROM federated.t1 LIMIT 100) dt;
+SELECT COUNT(DISTINCT a) FROM federated.t2;
+COUNT(DISTINCT a)
+100
+TRUNCATE TABLE federated.t2;
+INSERT INTO federated.t2
+SELECT * FROM (SELECT * FROM federated.t1 LIMIT 70000) dt;
+SELECT COUNT(DISTINCT a) FROM federated.t2;
+COUNT(DISTINCT a)
+70000
+#
+# MDEV-29640 FederatedX does not properly handle pushdown
+# in case of difference in local and remote table names
+#
+connection master;
+# Use tables from the previous test. Make sure pushdown works:
+EXPLAIN SELECT COUNT(DISTINCT a) 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
+SELECT COUNT(DISTINCT a) FROM federated.t2;
+COUNT(DISTINCT a)
+70000
+# Link remote table `federated.t1` with the local table named `t1_local`
+CREATE TABLE federated.t1_local ENGINE="FEDERATED"
+CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1';
+# No pushdown here due to table names mismatch, retrieve data as usual:
+EXPLAIN SELECT COUNT(DISTINCT a) FROM federated.t1_local;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1_local ALL NULL NULL NULL NULL 70000
+SELECT COUNT(DISTINCT a) FROM federated.t1_local;
+COUNT(DISTINCT a)
+70000
+#
+# MDEV-29863 Server crashes in federatedx_txn::acquire after select from
+# the Federated table with partitions and federated_pushdown=1
+# in case of difference in local and remote table names
+#
+connection slave;
+CREATE TABLE federated.t3 (a INT);
+INSERT INTO federated.t3 VALUES (1),(2),(3);
+CREATE TABLE federated.t4 (a INT);
+connection master;
+CREATE SERVER fedlink FOREIGN DATA WRAPPER mysql
+OPTIONS (USER 'root', HOST '127.0.0.1', DATABASE 'federated',
+PORT SLAVE_PORT);
+CREATE TABLE federated.t3 (a INT)
+ENGINE=FEDERATED
+CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t3'
+ PARTITION BY list (a)
+(PARTITION p1 VALUES IN (1) CONNECTION='fedlink/t3',
+PARTITION p2 VALUES IN (2) CONNECTION='fedlink/t4');
+EXPLAIN SELECT * FROM federated.t3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 ALL NULL NULL NULL NULL 3
+SELECT * FROM federated.t3;
+a
+1
+2
+3
+#
+# MDEV-29655: ASAN heap-use-after-free in
+# Pushdown_derived::Pushdown_derived
+#
+connection slave;
+DROP TABLE IF EXISTS federated.t1;
+CREATE TABLE federated.t1 (
+id int(20) NOT NULL,
+name varchar(16) NOT NULL default ''
+)
+DEFAULT CHARSET=latin1;
+INSERT INTO federated.t1 VALUES
+(3,'xxx'), (7,'yyy'), (4,'xxx'), (1,'zzz'), (5,'yyy');
+connection master;
+DROP TABLE IF EXISTS federated.t1;
+CREATE TABLE federated.t1 (
+id int(20) NOT NULL,
+name varchar(16) NOT NULL default ''
+)
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1';
+use federated;
+SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
+WHERE id=2) dt2) dt;
+id name
+connection slave;
+CREATE TABLE federated.t10 (a INT,b INT);
+CREATE TABLE federated.t11 (a INT, b INT);
+INSERT INTO federated.t10 VALUES (1,1),(2,2);
+INSERT INTO federated.t11 VALUES (1,1),(2,2);
+connection master;
+CREATE TABLE federated.t10
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t10';
+CREATE TABLE federated.t11
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t11';
+use federated;
+SELECT * FROM t10 LEFT JOIN
+(t11, (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
+WHERE id=2) dt2) dt
+) ON t10.a=t11.a;
+a b a b id name
+1 1 NULL NULL NULL NULL
+2 2 NULL NULL NULL NULL
+set global federated_pushdown=0;
+connection master;
+DROP TABLE IF EXISTS federated.t1;
+DROP DATABASE IF EXISTS federated;
+connection slave;
+DROP TABLE IF EXISTS federated.t1;
+DROP DATABASE IF EXISTS federated;
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-28 03:33:18 +0000