EVOLUTION-MANAGER

Edit File: direct_join.result

for master_1 for child2 child2_1 child2_2 child2_3 for child3 child3_1 child3_2 child3_3 drop and create databases connection master_1; DROP DATABASE IF EXISTS auto_test_local; CREATE DATABASE auto_test_local; USE auto_test_local; connection child2_1; SET @old_log_output = @@global.log_output; SET GLOBAL log_output = 'TABLE,FILE'; DROP DATABASE IF EXISTS auto_test_remote; CREATE DATABASE auto_test_remote; USE auto_test_remote; test select 1 connection master_1; SELECT 1; 1 1 connection child2_1; SELECT 1; 1 1 create table and insert connection child2_1; CHILD2_1_DROP_TABLES CHILD2_1_DROP_TABLES6 CHILD2_1_DROP_TABLES5 CHILD2_1_CREATE_TABLES CHILD2_1_CREATE_TABLES6 CHILD2_1_CREATE_TABLES5 TRUNCATE TABLE mysql.general_log; connection master_1; DROP TABLE IF EXISTS tbl_a; CREATE TABLE tbl_a ( a INT DEFAULT 10, b CHAR(1) DEFAULT 'c', c DATETIME DEFAULT '1999-10-10 10:10:10', PRIMARY KEY(a), KEY idx1(b) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1 CREATE TABLE tbl_b ( a INT DEFAULT 10, b CHAR(1) DEFAULT 'c', c DATETIME DEFAULT '1999-10-10 10:10:10' ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT5_2_1 CREATE TABLE tbl_c ( a INT AUTO_INCREMENT, b INT DEFAULT 10, c INT DEFAULT 11, PRIMARY KEY(a), KEY idx1(b), KEY idx2(c) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT4_2_1 insert into tbl_a values (1,'a','2000/01/01'),(2,'b','2000/01/02'),(3,'c','2000/01/03'),(4,'d','2000/01/04'),(5,'e','2000/01/05'); insert into tbl_b values (1,'a','2000/01/01'),(2,'b','2000/01/02'),(3,'c','2000/01/03'),(4,'d','2000/01/04'),(5,'e','2000/01/05'); insert into tbl_c values (1,10,100),(2,20,200),(3,30,300),(4,40,400),(5,50,500); select test connection child2_1; TRUNCATE TABLE mysql.general_log; connection master_1; SELECT a.a, c.b, c.c FROM tbl_a a, tbl_b b, tbl_c c WHERE a.a = b.a and a.a = c.a ORDER BY a.b DESC LIMIT 1,2; a b c 4 40 400 3 30 300 connection child2_1; SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; argument select t0.`b` `b`,t0.`a` `a`,t2.`b` `b`,t2.`c` `c` from `auto_test_remote`.`ta_r` t0,`auto_test_remote`.`ta_r_3` t1,`auto_test_remote`.`ta_r_int` t2 where ((t0.`a` = t1.`a`) and (t2.`a` = t1.`a`)) order by t0.`b` desc limit 1,2 SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') 1 a 2000-01-01 00:00:00 2 b 2000-01-02 00:00:00 3 c 2000-01-03 00:00:00 4 d 2000-01-04 00:00:00 5 e 2000-01-05 00:00:00 Test JOIN on a constant table. Spider should NOT push down the join because the tbl_person table is optimized as a constant table. connection child2_1; CHILD2_1_DROP_CONST_TABLE_JOIN CHILD2_1_DROP_CONST_TABLE2_JOIN CHILD2_1_CREATE_CONST_TABLE_JOIN CHILD2_1_CREATE_CONST_TABLE2_JOIN TRUNCATE TABLE mysql.general_log; connection master_1; DROP TABLE IF EXISTS tbl_person; DROP TABLE IF EXISTS tbl_ncd_cm_person; CREATE TABLE tbl_person ( id VARCHAR(50) NOT NULL, hr_status VARCHAR(50) NULL DEFAULT NULL, region_code VARCHAR(50) NULL DEFAULT NULL, region INT(11) NOT NULL, PRIMARY KEY (id, region) USING BTREE ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_CONST_TABLE_JOIN MASTER_1_ROW_FORMAT_CONST_TABLE_JOIN MASTER_1_PART_CONST_TABLE_JOIN SHOW CREATE TABLE tbl_person Table Create Table tbl_person CREATE TABLE `tbl_person` ( `id` varchar(50) NOT NULL, `hr_status` varchar(50) DEFAULT NULL, `region_code` varchar(50) DEFAULT NULL, `region` int(11) NOT NULL, PRIMARY KEY (`id`,`region`) USING BTREE ) ENGINE=SPIDER DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='table "tbl_ncd_cm_person"' PARTITION BY LIST COLUMNS(`region`) (PARTITION `pt1` VALUES IN (510411) COMMENT = 'tbl "tbl_person", srv "s_2_1"' ENGINE = SPIDER) CREATE TABLE tbl_ncd_cm_person ( id VARCHAR(50) NOT NULL, person_id VARCHAR(50) NULL DEFAULT '', diseaseKind_id VARCHAR(50) NULL DEFAULT NULL, region INT(11) NOT NULL, PRIMARY KEY (id, region) USING BTREE ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_CONST_TABLE2_JOIN MASTER_1_ROW_FORMAT_CONST_TABLE_JOIN MASTER_1_PART_CONST_TABLE2_JOIN SHOW CREATE TABLE tbl_ncd_cm_person Table Create Table tbl_ncd_cm_person CREATE TABLE `tbl_ncd_cm_person` ( `id` varchar(50) NOT NULL, `person_id` varchar(50) DEFAULT '', `diseaseKind_id` varchar(50) DEFAULT NULL, `region` int(11) NOT NULL, PRIMARY KEY (`id`,`region`) USING BTREE ) ENGINE=SPIDER DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC PARTITION BY LIST COLUMNS(`region`) (PARTITION `pt1` VALUES IN (510411) COMMENT = 'tbl "tbl_ncd_cm_person", srv "s_2_1"' ENGINE = SPIDER) INSERT INTO tbl_person VALUES ('24FC3F0A5119432BAE13DD65AABAA39C', '1', '123-51041110620301-321', 510411); INSERT INTO tbl_person VALUES ('123456789012345678901234567890AB', '1', '123-51041110620301-321', 510411); INSERT INTO tbl_ncd_cm_person VALUES ('123456789', '24FC3F0A5119432BAE13DD65AABAA39C', '52A0328740914BCE86ED10A4D2521816', 510411); INSERT INTO tbl_ncd_cm_person VALUES ('123456789AB', '123456789012345678901234567890AB', '52A0328740914BCE86ED10A4D2521816', 510411); DELETE FROM tbl_ncd_cm_person; INSERT INTO tbl_ncd_cm_person VALUES ('123456789', '24FC3F0A5119432BAE13DD65AABAA39C', '52A0328740914BCE86ED10A4D2521816', 510411); INSERT INTO tbl_ncd_cm_person VALUES ('123456789AB', '123456789012345678901234567890AB', '52A0328740914BCE86ED10A4D2521816', 510411); connection child2_1; TRUNCATE TABLE mysql.general_log; connection master_1; SELECT count(0) FROM tbl_person tp INNER JOIN tbl_ncd_cm_person tncp ON tp.id = tncp.person_id WHERE 1 = 1 AND tp.hr_status != "99" AND tp.hr_status != "2" AND tp.region_code LIKE CONCAT(CONCAT('%', '51041110620301', '%')) AND tp.id = '24FC3F0A5119432BAE13DD65AABAA39C' AND tncp.diseaseKind_id = '52A0328740914BCE86ED10A4D2521816' AND tp.region = 510411; count(0) 1 connection child2_1; SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; argument select `id`,`hr_status`,`region_code`,`region` from `auto_test_remote`.`tbl_person` where `id` = '24FC3F0A5119432BAE13DD65AABAA39C' and `region` = 510411 select `person_id`,`diseaseKind_id` from `auto_test_remote`.`tbl_ncd_cm_person` where ((`diseaseKind_id` = '52A0328740914BCE86ED10A4D2521816')) SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' SELECT * FROM tbl_person; id hr_status region_code region 123456789012345678901234567890AB 1 123-51041110620301-321 510411 24FC3F0A5119432BAE13DD65AABAA39C 1 123-51041110620301-321 510411 SELECT * FROM tbl_ncd_cm_person; id person_id diseaseKind_id region 123456789 24FC3F0A5119432BAE13DD65AABAA39C 52A0328740914BCE86ED10A4D2521816 510411 123456789AB 123456789012345678901234567890AB 52A0328740914BCE86ED10A4D2521816 510411 deinit connection master_1; DROP DATABASE IF EXISTS auto_test_local; connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; SET GLOBAL log_output = @old_log_output; for master_1 for child2 child2_1 child2_2 child2_3 for child3 child3_1 child3_2 child3_3 end of test