From d733b3f919e56f88e23efeaf53d7f3cbb35de447 Mon Sep 17 00:00:00 2001 From: Kentoku SHIBA Date: Sun, 23 Aug 2020 12:30:14 +0900 Subject: MDEV-22246 Result rows duplicated by spider engine fix the following type mrr scan (select 0,`id`,`node` from `auto_test_remote`.`tbl_a` where (`id` <> 0) order by `id`)union all(select 1,`id`,`node` from `auto_test_remote`.`tbl_a` where (`id` <> 0) order by `id`) order by `id` --- storage/spider/ha_spider.cc | 89 +++++++++++++++++++++ storage/spider/ha_spider.h | 2 + .../spider/bugfix/include/mdev_22246_deinit.inc | 14 ++++ .../spider/bugfix/include/mdev_22246_init.inc | 48 +++++++++++ .../mysql-test/spider/bugfix/r/mdev_22246.result | 79 +++++++++++++++++++ .../mysql-test/spider/bugfix/t/mdev_22246.cnf | 4 + .../mysql-test/spider/bugfix/t/mdev_22246.test | 92 ++++++++++++++++++++++ storage/spider/spd_db_conn.cc | 6 ++ storage/spider/spd_db_include.h | 1 + 9 files changed, 335 insertions(+) create mode 100644 storage/spider/mysql-test/spider/bugfix/include/mdev_22246_deinit.inc create mode 100644 storage/spider/mysql-test/spider/bugfix/include/mdev_22246_init.inc create mode 100644 storage/spider/mysql-test/spider/bugfix/r/mdev_22246.result create mode 100644 storage/spider/mysql-test/spider/bugfix/t/mdev_22246.cnf create mode 100644 storage/spider/mysql-test/spider/bugfix/t/mdev_22246.test diff --git a/storage/spider/ha_spider.cc b/storage/spider/ha_spider.cc index ce9447a61a9..51ceee310ae 100644 --- a/storage/spider/ha_spider.cc +++ b/storage/spider/ha_spider.cc @@ -1590,6 +1590,7 @@ int ha_spider::reset() multi_range_keys = NULL; } #endif + multi_range_num = 0; ft_handler = NULL; ft_current = NULL; ft_count = 0; @@ -4131,6 +4132,64 @@ int ha_spider::read_range_next() DBUG_RETURN(check_ha_range_eof()); } +void ha_spider::reset_no_where_cond() +{ + uint roop_count; + DBUG_ENTER("ha_spider::reset_no_where_cond"); +#if defined(HS_HAS_SQLCOM) && defined(HAVE_HANDLERSOCKET) + if (sql_kinds & (SPIDER_SQL_KIND_SQL | SPIDER_SQL_KIND_HANDLER)) + { +#endif + for (roop_count = 0; roop_count < share->use_sql_dbton_count; roop_count++) + { + dbton_handler[share->use_sql_dbton_ids[roop_count]]->no_where_cond = + FALSE; + } +#if defined(HS_HAS_SQLCOM) && defined(HAVE_HANDLERSOCKET) + } + if (sql_kinds & SPIDER_SQL_KIND_HS) + { + for (roop_count = 0; roop_count < share->use_hs_dbton_count; roop_count++) + { + dbton_handler[share->use_hs_dbton_ids[roop_count]]->no_where_cond = + FALSE; + } + } +#endif + DBUG_VOID_RETURN; +} + +bool ha_spider::check_no_where_cond() +{ + uint roop_count; + DBUG_ENTER("ha_spider::check_no_where_cond"); +#if defined(HS_HAS_SQLCOM) && defined(HAVE_HANDLERSOCKET) + if (sql_kinds & (SPIDER_SQL_KIND_SQL | SPIDER_SQL_KIND_HANDLER)) + { +#endif + for (roop_count = 0; roop_count < share->use_sql_dbton_count; roop_count++) + { + if (dbton_handler[share->use_sql_dbton_ids[roop_count]]->no_where_cond) + { + DBUG_RETURN(TRUE); + } + } +#if defined(HS_HAS_SQLCOM) && defined(HAVE_HANDLERSOCKET) + } + if (sql_kinds & SPIDER_SQL_KIND_HS) + { + for (roop_count = 0; roop_count < share->use_hs_dbton_count; roop_count++) + { + if (dbton_handler[share->use_hs_dbton_ids[roop_count]]->no_where_cond) + { + DBUG_RETURN(TRUE); + } + } + } +#endif + DBUG_RETURN(FALSE); +} + #ifdef HA_MRR_USE_DEFAULT_IMPL #if defined(MARIADB_BASE_VERSION) && MYSQL_VERSION_ID >= 100000 ha_rows ha_spider::multi_range_read_info_const( @@ -4276,6 +4335,7 @@ int ha_spider::multi_range_read_init( DBUG_PRINT("info",("spider n_ranges=%u", n_ranges)); multi_range_num = n_ranges; mrr_have_range = FALSE; + reset_no_where_cond(); DBUG_RETURN( handler::multi_range_read_init( seq, @@ -4749,6 +4809,10 @@ int ha_spider::read_multi_range_first_internal( result_list.current = result_list.current->prev; } } + if (check_no_where_cond()) + { + DBUG_RETURN(check_error_mode_eof(0)); + } set_where_to_pos_sql(SPIDER_SQL_TYPE_SELECT_SQL); set_where_to_pos_sql(SPIDER_SQL_TYPE_HANDLER); } @@ -5224,6 +5288,15 @@ int ha_spider::read_multi_range_first_internal( DBUG_PRINT("info",("spider range_res8=%d", range_res)); } #endif + if (check_no_where_cond()) + { +#ifdef HA_MRR_USE_DEFAULT_IMPL + range_res = 1; +#else + multi_range_curr = multi_range_end; +#endif + break; + } } #ifdef HA_MRR_USE_DEFAULT_IMPL while (!range_res); @@ -5637,6 +5710,10 @@ int ha_spider::read_multi_range_first_internal( } else DBUG_RETURN(error_num); } + if (check_no_where_cond()) + { + DBUG_RETURN(check_error_mode_eof(0)); + } multi_range_cnt = 0; if ((error_num = reset_sql_sql( SPIDER_SQL_TYPE_SELECT_SQL | SPIDER_SQL_TYPE_HANDLER))) @@ -5858,6 +5935,10 @@ int ha_spider::read_multi_range_next( #ifdef HA_MRR_USE_DEFAULT_IMPL DBUG_PRINT("info",("spider range_res2=%d", range_res)); #endif + if (check_no_where_cond()) + { + DBUG_RETURN(check_error_mode_eof(0)); + } set_where_to_pos_sql(SPIDER_SQL_TYPE_SELECT_SQL); set_where_to_pos_sql(SPIDER_SQL_TYPE_HANDLER); result_list.limit_num = @@ -6279,6 +6360,10 @@ int ha_spider::read_multi_range_next( #endif ) DBUG_RETURN(error_num); + if (check_no_where_cond()) + { + DBUG_RETURN(check_error_mode_eof(0)); + } spider_db_free_one_result_for_start_next(this); spider_first_split_read_param(this); #ifndef WITHOUT_SPIDER_BG_SEARCH @@ -7086,6 +7171,10 @@ int ha_spider::read_multi_range_next( } else DBUG_RETURN(error_num); } + if (check_no_where_cond()) + { + DBUG_RETURN(check_error_mode_eof(0)); + } multi_range_cnt = 0; if ((error_num = reset_sql_sql( SPIDER_SQL_TYPE_SELECT_SQL | SPIDER_SQL_TYPE_HANDLER))) diff --git a/storage/spider/ha_spider.h b/storage/spider/ha_spider.h index 847f7a8e170..5bc58397f28 100644 --- a/storage/spider/ha_spider.h +++ b/storage/spider/ha_spider.h @@ -330,6 +330,8 @@ public: bool sorted ); int read_range_next(); + void reset_no_where_cond(); + bool check_no_where_cond(); #ifdef HA_MRR_USE_DEFAULT_IMPL #if defined(MARIADB_BASE_VERSION) && MYSQL_VERSION_ID >= 100000 ha_rows multi_range_read_info_const( diff --git a/storage/spider/mysql-test/spider/bugfix/include/mdev_22246_deinit.inc b/storage/spider/mysql-test/spider/bugfix/include/mdev_22246_deinit.inc new file mode 100644 index 00000000000..9d255152dd8 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/include/mdev_22246_deinit.inc @@ -0,0 +1,14 @@ +--let $MASTER_1_COMMENT_2_1= $MASTER_1_COMMENT_2_1_BACKUP +--let $CHILD2_1_DROP_TABLES= $CHILD2_1_DROP_TABLES_BACKUP +--let $CHILD2_1_CREATE_TABLES= $CHILD2_1_CREATE_TABLES_BACKUP +--let $CHILD2_1_SELECT_TABLES= $CHILD2_1_SELECT_TABLES_BACKUP +--let $CHILD2_2_DROP_TABLES= $CHILD2_2_DROP_TABLES_BACKUP +--let $CHILD2_2_CREATE_TABLES= $CHILD2_2_CREATE_TABLES_BACKUP +--let $CHILD2_2_SELECT_TABLES= $CHILD2_2_SELECT_TABLES_BACKUP +--disable_warnings +--disable_query_log +--disable_result_log +--source ../t/test_deinit.inc +--enable_result_log +--enable_query_log +--enable_warnings diff --git a/storage/spider/mysql-test/spider/bugfix/include/mdev_22246_init.inc b/storage/spider/mysql-test/spider/bugfix/include/mdev_22246_init.inc new file mode 100644 index 00000000000..48226ba2811 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/include/mdev_22246_init.inc @@ -0,0 +1,48 @@ +--disable_warnings +--disable_query_log +--disable_result_log +--source ../t/test_init.inc +if (!$HAVE_PARTITION) +{ + --source group_by_order_by_limit_deinit.inc + --enable_result_log + --enable_query_log + --enable_warnings + skip Test requires partitioning; +} +--enable_result_log +--enable_query_log +--enable_warnings +--let $MASTER_1_COMMENT_2_1_BACKUP= $MASTER_1_COMMENT_2_1 +let $MASTER_1_COMMENT_2_1= + COMMENT='table "tbl_a"' + PARTITION BY HASH(id) ( + PARTITION pt1 COMMENT='srv "s_2_1"', + PARTITION pt2 COMMENT='srv "s_2_2"' + ); +--let $CHILD2_1_DROP_TABLES_BACKUP= $CHILD2_1_DROP_TABLES +let $CHILD2_1_DROP_TABLES= + DROP TABLE IF EXISTS tbl_a; +--let $CHILD2_1_CREATE_TABLES_BACKUP= $CHILD2_1_CREATE_TABLES +let $CHILD2_1_CREATE_TABLES= + CREATE TABLE tbl_a ( + id bigint NOT NULL, + node text, + PRIMARY KEY (id) + ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET; +--let $CHILD2_1_SELECT_TABLES_BACKUP= $CHILD2_1_SELECT_TABLES +let $CHILD2_1_SELECT_TABLES= + SELECT * FROM tbl_a ORDER BY id; +--let $CHILD2_2_DROP_TABLES_BACKUP= $CHILD2_2_DROP_TABLES +let $CHILD2_2_DROP_TABLES= + DROP TABLE IF EXISTS tbl_a; +--let $CHILD2_2_CREATE_TABLES_BACKUP= $CHILD2_2_CREATE_TABLES +let $CHILD2_2_CREATE_TABLES= + CREATE TABLE tbl_a ( + id bigint NOT NULL, + node text, + PRIMARY KEY (id) + ) $CHILD2_2_ENGINE $CHILD2_2_CHARSET; +--let $CHILD2_2_SELECT_TABLES_BACKUP= $CHILD2_2_SELECT_TABLES +let $CHILD2_2_SELECT_TABLES= + SELECT * FROM tbl_a ORDER BY id; diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_22246.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_22246.result new file mode 100644 index 00000000000..4884f60637e --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_22246.result @@ -0,0 +1,79 @@ +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 + +this test is for MDEV-22246 + +drop and create databases +connection master_1; +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'; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +connection child2_2; +SET @old_log_output = @@global.log_output; +SET GLOBAL log_output = 'TABLE,FILE'; +CREATE DATABASE auto_test_remote2; +USE auto_test_remote2; + +create table and insert +connection child2_1; +CHILD2_1_CREATE_TABLES +TRUNCATE TABLE mysql.general_log; +connection child2_2; +CHILD2_2_CREATE_TABLES +TRUNCATE TABLE mysql.general_log; +connection master_1; +CREATE TABLE tbl_a ( +id bigint NOT NULL, +node text, +PRIMARY KEY (id) +) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1 +INSERT INTO tbl_a (id,node) VALUES (1,'DB-G0'),(2,'DB-G1'); + +select test 1 +connection child2_1; +TRUNCATE TABLE mysql.general_log; +connection child2_2; +TRUNCATE TABLE mysql.general_log; +connection master_1; +SELECT * FROM tbl_a; +id node +2 DB-G1 +1 DB-G0 +SELECT * FROM tbl_a WHERE id != 0; +id node +1 DB-G0 +2 DB-G1 +connection child2_1; +SELECT * FROM tbl_a ORDER BY id; +id node +2 DB-G1 +connection child2_2; +SELECT * FROM tbl_a ORDER BY id; +id node +1 DB-G0 + +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; +connection child2_2; +DROP DATABASE IF EXISTS auto_test_remote2; +SET GLOBAL log_output = @old_log_output; +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 + +end of test diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_22246.cnf b/storage/spider/mysql-test/spider/bugfix/t/mdev_22246.cnf new file mode 100644 index 00000000000..e0ffb99c38e --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_22246.cnf @@ -0,0 +1,4 @@ +!include include/default_mysqld.cnf +!include ../my_1_1.cnf +!include ../my_2_1.cnf +!include ../my_2_2.cnf diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_22246.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_22246.test new file mode 100644 index 00000000000..63b04c14e11 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_22246.test @@ -0,0 +1,92 @@ +--source ../include/mdev_22246_init.inc +--echo +--echo this test is for MDEV-22246 +--echo +--echo drop and create databases +--connection master_1 +--disable_warnings +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'; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; + +--connection child2_2 +SET @old_log_output = @@global.log_output; +SET GLOBAL log_output = 'TABLE,FILE'; +CREATE DATABASE auto_test_remote2; +USE auto_test_remote2; +--enable_warnings + +--echo +--echo create table and insert + +--connection child2_1 +--disable_query_log +echo CHILD2_1_CREATE_TABLES; +eval $CHILD2_1_CREATE_TABLES; +--enable_query_log +TRUNCATE TABLE mysql.general_log; + +--connection child2_2 +--disable_query_log +echo CHILD2_2_CREATE_TABLES; +eval $CHILD2_2_CREATE_TABLES; +--enable_query_log +TRUNCATE TABLE mysql.general_log; + +--connection master_1 +--disable_query_log +echo CREATE TABLE tbl_a ( + id bigint NOT NULL, + node text, + PRIMARY KEY (id) +) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1; +eval CREATE TABLE tbl_a ( + id bigint NOT NULL, + node text, + PRIMARY KEY (id) +) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1; +--enable_query_log +INSERT INTO tbl_a (id,node) VALUES (1,'DB-G0'),(2,'DB-G1'); + +--echo +--echo select test 1 + +--connection child2_1 +TRUNCATE TABLE mysql.general_log; + +--connection child2_2 +TRUNCATE TABLE mysql.general_log; + +--connection master_1 +SELECT * FROM tbl_a; +SELECT * FROM tbl_a WHERE id != 0; + +--connection child2_1 +eval $CHILD2_1_SELECT_TABLES; + +--connection child2_2 +eval $CHILD2_2_SELECT_TABLES; + +--echo +--echo deinit +--disable_warnings +--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; + +--connection child2_2 +DROP DATABASE IF EXISTS auto_test_remote2; +SET GLOBAL log_output = @old_log_output; + +--enable_warnings +--source ../include/mdev_22246_deinit.inc +--echo +--echo end of test diff --git a/storage/spider/spd_db_conn.cc b/storage/spider/spd_db_conn.cc index 2df148b6ea4..e0538259fb2 100644 --- a/storage/spider/spd_db_conn.cc +++ b/storage/spider/spd_db_conn.cc @@ -1742,6 +1742,7 @@ int spider_db_append_key_where_internal( int key_count; uint length; uint store_length; + uint current_pos = str->length(); const uchar *ptr, *another_ptr; const key_range *use_key, *another_key; KEY_PART_INFO *key_part; @@ -2715,6 +2716,11 @@ int spider_db_append_key_where_internal( DBUG_RETURN(error_num); end: + if (spider->multi_range_num && current_pos == str->length()) + { + DBUG_PRINT("info", ("spider no key where condition")); + dbton_hdl->no_where_cond = TRUE; + } /* use condition */ if (dbton_hdl->append_condition_part(NULL, 0, sql_type, FALSE)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); diff --git a/storage/spider/spd_db_include.h b/storage/spider/spd_db_include.h index bff3d95cacb..41e24e06c21 100644 --- a/storage/spider/spd_db_include.h +++ b/storage/spider/spd_db_include.h @@ -1355,6 +1355,7 @@ public: SPIDER_LINK_IDX_CHAIN *link_idx_chain; #endif bool strict_group_by; + bool no_where_cond; spider_db_handler(ha_spider *spider, spider_db_share *db_share) : dbton_id(db_share->dbton_id), spider(spider), db_share(db_share), first_link_idx(-1) {} -- cgit v1.2.1