diff options
| -rw-r--r-- | storage/spider/mysql-test/spider/include/direct_join_init.inc | 40 | ||||
| -rw-r--r-- | storage/spider/mysql-test/spider/r/direct_join.result | 92 | ||||
| -rw-r--r-- | storage/spider/mysql-test/spider/t/direct_join.test | 146 | ||||
| -rw-r--r-- | storage/spider/spd_db_include.h | 1 | ||||
| -rw-r--r-- | storage/spider/spd_group_by_handler.cc | 36 |
5 files changed, 315 insertions, 0 deletions
diff --git a/storage/spider/mysql-test/spider/include/direct_join_init.inc b/storage/spider/mysql-test/spider/include/direct_join_init.inc index 7e4947bf078..25c5e7ca39b 100644 --- a/storage/spider/mysql-test/spider/include/direct_join_init.inc +++ b/storage/spider/mysql-test/spider/include/direct_join_init.inc @@ -5,6 +5,46 @@ --enable_result_log --enable_query_log --enable_warnings +let $MASTER_1_COMMENT_CONST_TABLE_JOIN= + COMMENT 'table "tbl_person"'; +let $MASTER_1_COMMENT_CONST_TABLE_JOIN= + COMMENT 'table "tbl_ncd_cm_person"'; +let $MASTER_1_ROW_FORMAT_CONST_TABLE_JOIN= + ROW_FORMAT = Dynamic; +let $MASTER_1_PART_CONST_TABLE_JOIN= + PARTITION BY LIST COLUMNS (region) PARTITIONS 1 + (PARTITION pt1 values in (510411) + COMMENT = 'tbl "tbl_person", srv "s_2_1"' MAX_ROWS = 0 MIN_ROWS = 0); +let $MASTER_1_PART_CONST_TABLE2_JOIN= + PARTITION BY LIST COLUMNS (region) PARTITIONS 1 + (PARTITION pt1 values in (510411) + COMMENT = 'tbl "tbl_ncd_cm_person", srv "s_2_1"' MAX_ROWS = 0 MIN_ROWS = 0); +let $CHILD2_1_ROW_FORMAT_CONST_TABLE_JOIN= + ROW_FORMAT = Dynamic; +let $CHILD2_1_DROP_CONST_TABLE_JOIN= + DROP TABLE IF EXISTS tbl_person; +let $CHILD2_1_CREATE_CONST_TABLE_JOIN= + 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 + ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET $CHILD2_1_ROW_FORMAT_CONST_TABLE_JOIN; +let $CHILD2_1_DROP_CONST_TABLE2_JOIN= + DROP TABLE IF EXISTS tbl_ncd_cm_person; +let $CHILD2_1_CREATE_CONST_TABLE2_JOIN= + 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 + ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET $CHILD2_1_ROW_FORMAT_CONST_TABLE_JOIN; +let $CHILD2_1_SELECT_CONST_TABLE_JOIN= + SELECT * FROM tbl_person; +let $CHILD2_1_SELECT_CONST_TABLE2_JOIN= + SELECT * FROM tbl_ncd_cm_person; let $CHILD2_1_SELECT_ARGUMENT1= SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; --let $OUTPUT_CHILD_GROUP2_BACKUP= $OUTPUT_CHILD_GROUP2 diff --git a/storage/spider/mysql-test/spider/r/direct_join.result b/storage/spider/mysql-test/spider/r/direct_join.result index 5cc44a56910..0a76c3246f9 100644 --- a/storage/spider/mysql-test/spider/r/direct_join.result +++ b/storage/spider/mysql-test/spider/r/direct_join.result @@ -86,6 +86,98 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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; diff --git a/storage/spider/mysql-test/spider/t/direct_join.test b/storage/spider/mysql-test/spider/t/direct_join.test index 680164006c8..3f7c692d2c2 100644 --- a/storage/spider/mysql-test/spider/t/direct_join.test +++ b/storage/spider/mysql-test/spider/t/direct_join.test @@ -178,6 +178,152 @@ if ($USE_CHILD_GROUP2) } --echo +--echo Test JOIN on a constant table. +--echo Spider should NOT push down the join because the tbl_person table +--echo is optimized as a constant table. +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($OUTPUT_CHILD_GROUP2) + { + --disable_query_log + echo CHILD2_1_DROP_CONST_TABLE_JOIN; + echo CHILD2_1_DROP_CONST_TABLE2_JOIN; + echo CHILD2_1_CREATE_CONST_TABLE_JOIN; + echo CHILD2_1_CREATE_CONST_TABLE2_JOIN; + } + --disable_warnings + eval $CHILD2_1_DROP_CONST_TABLE_JOIN; + eval $CHILD2_1_DROP_CONST_TABLE2_JOIN; + --enable_warnings + eval $CHILD2_1_CREATE_CONST_TABLE_JOIN; + eval $CHILD2_1_CREATE_CONST_TABLE2_JOIN; + if ($OUTPUT_CHILD_GROUP2) + { + --enable_query_log + } + if ($USE_GENERAL_LOG) + { + TRUNCATE TABLE mysql.general_log; + } + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} +--connection master_1 +--disable_warnings +DROP TABLE IF EXISTS tbl_person; +DROP TABLE IF EXISTS tbl_ncd_cm_person; +--enable_warnings +--disable_query_log +echo 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; +eval 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; +echo SHOW CREATE TABLE tbl_person; +SHOW CREATE TABLE tbl_person; +echo 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; +eval 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; +echo SHOW CREATE TABLE tbl_ncd_cm_person; +SHOW CREATE TABLE tbl_ncd_cm_person; +--enable_query_log +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); +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($USE_GENERAL_LOG) + { + TRUNCATE TABLE mysql.general_log; + } + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_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; +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($USE_GENERAL_LOG) + { + eval $CHILD2_1_SELECT_ARGUMENT1; + } + eval $CHILD2_1_SELECT_CONST_TABLE_JOIN; + eval $CHILD2_1_SELECT_CONST_TABLE2_JOIN; + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} + +--echo --echo deinit --disable_warnings --connection master_1 diff --git a/storage/spider/spd_db_include.h b/storage/spider/spd_db_include.h index 639bb6dac2d..d19306b1b1a 100644 --- a/storage/spider/spd_db_include.h +++ b/storage/spider/spd_db_include.h @@ -697,6 +697,7 @@ public: SPIDER_TABLE_HOLDER *add_table( ha_spider *spider_arg ); + bool all_query_fields_are_query_table_members(); int create_table_holder( uint table_count_arg ); diff --git a/storage/spider/spd_group_by_handler.cc b/storage/spider/spd_group_by_handler.cc index a5328464bb6..0f8479b6ca6 100644 --- a/storage/spider/spd_group_by_handler.cc +++ b/storage/spider/spd_group_by_handler.cc @@ -911,6 +911,35 @@ SPIDER_TABLE_HOLDER *spider_fields::add_table( DBUG_RETURN(return_table_holder); } +/** + Verify that all fields in the query are members of tables that are in the + query. + + @return TRUE All fields in the query are members of tables + that are in the query. + FALSE At least one field in the query is not a + member of a table that is in the query. +*/ + +bool spider_fields::all_query_fields_are_query_table_members() +{ + SPIDER_FIELD_HOLDER *field_holder; + DBUG_ENTER("spider_fields::all_fields_are_query_table_fields"); + DBUG_PRINT("info", ("spider this=%p", this)); + + set_pos_to_first_field_holder(); + while ((field_holder = get_next_field_holder())) + { + if (!field_holder->spider) + { + DBUG_PRINT("info", ("spider field is not a member of a query table")); + DBUG_RETURN(FALSE); + } + } + + DBUG_RETURN(TRUE); +} + int spider_fields::create_table_holder( uint table_count_arg ) { @@ -1997,6 +2026,13 @@ group_by_handler *spider_create_group_by_handler( } } + if (!fields->all_query_fields_are_query_table_members()) + { + DBUG_PRINT("info", ("spider found a query field that is not a query table member")); + delete fields; + DBUG_RETURN(NULL); + } + fields->check_support_dbton(dbton_bitmap); if (!fields->has_conn_holder()) { |