From e4cffc92c783b0d0be35fa2e696ed517b381f81f Mon Sep 17 00:00:00 2001 From: Nayuta Yanagisawa Date: Thu, 30 Jun 2022 01:58:57 +0900 Subject: MDEV-27172 Prefix indices on Spider tables may lead to wrong query results Spider converts HA_READ_KEY_EXACT to the equality (=) in the function spider_db_append_key_where_internal() but the conversion is not necessarily correct for tables with prefix indices. We fix the bug by converting HA_READ_KEY_EXACT to 'LIKE "foo%"' when a target key is a prefix key. The fix is partly inspired by FEDERATED. See ha_federated::create_where_from_key() for more details. --- .../mysql-test/spider/bugfix/r/mdev_27172.result | 84 ++++++++++++++++++++ .../mysql-test/spider/bugfix/t/mdev_27172.cnf | 3 + .../mysql-test/spider/bugfix/t/mdev_27172.test | 92 ++++++++++++++++++++++ 3 files changed, 179 insertions(+) create mode 100644 storage/spider/mysql-test/spider/bugfix/r/mdev_27172.result create mode 100644 storage/spider/mysql-test/spider/bugfix/t/mdev_27172.cnf create mode 100644 storage/spider/mysql-test/spider/bugfix/t/mdev_27172.test (limited to 'storage/spider/mysql-test') diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_27172.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_27172.result new file mode 100644 index 00000000000..d4c8c7e8ec2 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_27172.result @@ -0,0 +1,84 @@ +# +# MDEV-27172 Prefix indices on Spider tables may lead to wrong query results +# +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +connection child2_1; +SET @general_log_backup = @@global.general_log; +SET @log_output_backup = @@global.log_output; +SET @@global.general_log = 1; +SET @@global.log_output = "TABLE"; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +CREATE TABLE tbl_a ( +id int NOT NULL, +greeting VARCHAR(255), +KEY(greeting) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE tbl_b ( +id int NOT NULL, +greeting VARCHAR(255), +KEY k (greeting(5)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE tbl_c ( +id int NOT NULL, +greeting TEXT, +KEY k (greeting(5)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +TRUNCATE TABLE mysql.general_log; +connection master_1; +CREATE DATABASE auto_test_local; +USE auto_test_local; +CREATE TABLE tbl_a ( +id int NOT NULL, +greeting VARCHAR(255), +KEY k (greeting) +) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='table "tbl_a", srv "s_2_1"'; +INSERT INTO tbl_a VALUES (1, "Hi!"),(2, "Aloha!"),(3, "Aloha!!!"); +SELECT * FROM tbl_a WHERE greeting = "Aloha!" + AND CASE greeting WHEN "Aloha!" THEN "one" ELSE 'more' END = "one"; +id greeting +2 Aloha! +CREATE TABLE tbl_b ( +id int NOT NULL, +greeting VARCHAR(255), +KEY k (greeting(5)) +) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='table "tbl_b", srv "s_2_1"'; +INSERT INTO tbl_b VALUES (1, "Hi!"),(2, "Aloha!"),(3, "Aloha!!!"); +SELECT * FROM tbl_b WHERE greeting = "Aloha!" + AND CASE greeting WHEN "Aloha!" THEN "one" ELSE 'more' END = "one"; +id greeting +2 Aloha! +CREATE TABLE tbl_c ( +id int NOT NULL, +greeting TEXT, +KEY k (greeting(5)) +) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='table "tbl_c", srv "s_2_1"'; +INSERT INTO tbl_c VALUES (1, "Hi!"),(2, "Aloha!"),(3, "Aloha!!!"); +SELECT * FROM tbl_c WHERE greeting = "Aloha!" + AND CASE greeting WHEN "Aloha!" THEN "one" ELSE 'more' END = "one"; +id greeting +2 Aloha! +connection child2_1; +SELECT argument FROM mysql.general_log WHERE argument LIKE 'select %'; +argument +select `id`,`greeting` from `auto_test_remote`.`tbl_a` where `greeting` = 'Aloha!' and ((`greeting` = 'Aloha!')) +select `id`,`greeting` from `auto_test_remote`.`tbl_b` where `greeting` like 'Aloha%' and ((`greeting` = 'Aloha!')) +select `id`,`greeting` from `auto_test_remote`.`tbl_c` where `greeting` like 'Aloha%' and ((`greeting` = 'Aloha!')) +SELECT argument FROM mysql.general_log WHERE argument LIKE 'select %' +connection child2_1; +SET @@global.general_log = @general_log_backup; +SET @@global.log_output = @log_output_backup; +DROP DATABASE auto_test_remote; +connection master_1; +DROP DATABASE auto_test_local; +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_27172.cnf b/storage/spider/mysql-test/spider/bugfix/t/mdev_27172.cnf new file mode 100644 index 00000000000..05dfd8a0bce --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_27172.cnf @@ -0,0 +1,3 @@ +!include include/default_mysqld.cnf +!include ../my_1_1.cnf +!include ../my_2_1.cnf diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_27172.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_27172.test new file mode 100644 index 00000000000..d544a0b400e --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_27172.test @@ -0,0 +1,92 @@ +--echo # +--echo # MDEV-27172 Prefix indices on Spider tables may lead to wrong query results +--echo # + +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log + +--connection child2_1 +SET @general_log_backup = @@global.general_log; +SET @log_output_backup = @@global.log_output; +SET @@global.general_log = 1; +SET @@global.log_output = "TABLE"; + +CREATE DATABASE auto_test_remote; +USE auto_test_remote; + +eval CREATE TABLE tbl_a ( + id int NOT NULL, + greeting VARCHAR(255), + KEY(greeting) +) $CHILD2_1_ENGINE $CHILD2_1_CHARSET; + +eval CREATE TABLE tbl_b ( + id int NOT NULL, + greeting VARCHAR(255), + KEY k (greeting(5)) +) $CHILD2_1_ENGINE $CHILD2_1_CHARSET; + +eval CREATE TABLE tbl_c ( + id int NOT NULL, + greeting TEXT, + KEY k (greeting(5)) +) $CHILD2_1_ENGINE $CHILD2_1_CHARSET; + +TRUNCATE TABLE mysql.general_log; + +--connection master_1 +CREATE DATABASE auto_test_local; +USE auto_test_local; + +# = (equality) +eval CREATE TABLE tbl_a ( + id int NOT NULL, + greeting VARCHAR(255), + KEY k (greeting) +) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='table "tbl_a", srv "s_2_1"'; + +INSERT INTO tbl_a VALUES (1, "Hi!"),(2, "Aloha!"),(3, "Aloha!!!"); +SELECT * FROM tbl_a WHERE greeting = "Aloha!" + AND CASE greeting WHEN "Aloha!" THEN "one" ELSE 'more' END = "one"; # hack to disable GBH + +# LIKE +eval CREATE TABLE tbl_b ( + id int NOT NULL, + greeting VARCHAR(255), + KEY k (greeting(5)) +) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='table "tbl_b", srv "s_2_1"'; + +INSERT INTO tbl_b VALUES (1, "Hi!"),(2, "Aloha!"),(3, "Aloha!!!"); +SELECT * FROM tbl_b WHERE greeting = "Aloha!" + AND CASE greeting WHEN "Aloha!" THEN "one" ELSE 'more' END = "one"; # hack to disable GBH + +# LIKE +eval CREATE TABLE tbl_c ( + id int NOT NULL, + greeting TEXT, + KEY k (greeting(5)) +) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='table "tbl_c", srv "s_2_1"'; + +INSERT INTO tbl_c VALUES (1, "Hi!"),(2, "Aloha!"),(3, "Aloha!!!"); +SELECT * FROM tbl_c WHERE greeting = "Aloha!" + AND CASE greeting WHEN "Aloha!" THEN "one" ELSE 'more' END = "one"; # hack to disable GBH + +--connection child2_1 +SELECT argument FROM mysql.general_log WHERE argument LIKE 'select %'; + +--connection child2_1 +SET @@global.general_log = @general_log_backup; +SET @@global.log_output = @log_output_backup; +DROP DATABASE auto_test_remote; + +--connection master_1 +DROP DATABASE auto_test_local; + +--disable_query_log +--disable_result_log +--source ../../t/test_deinit.inc +--enable_result_log +--enable_query_log -- cgit v1.2.1