diff options
4 files changed, 139 insertions, 1 deletions
diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_26247.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_26247.result new file mode 100644 index 00000000000..b405021a6b9 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_26247.result @@ -0,0 +1,55 @@ +# +# MDEV-26247 Spider: Valid LEFT JOIN results in ERROR 1064 +# +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +connection child2_1; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +CREATE TABLE tbl_a ( +a int, +primary key (a) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE tbl_b ( +c int, +d int, +primary key (c, d) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +INSERT INTO tbl_a VALUES (1), (2), (3); +INSERT INTO tbl_b VALUES (1, 11), (2, 22), (3, 33); +connection master_1; +CREATE DATABASE auto_test_local; +USE auto_test_local; +CREATE TABLE tbl_a ( +a int, +primary key (a) +) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='srv "s_2_1", table "tbl_a"'; +CREATE TABLE tbl_b ( +c int, +d int, +primary key (c, d) +) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='srv "s_2_1", table "tbl_b"'; +SELECT d FROM tbl_a LEFT JOIN tbl_b ON a = c WHERE a IN (1); +d +11 +SELECT d FROM tbl_b RIGHT JOIN tbl_a ON c = a WHERE a IN (1); +d +11 +SELECT d FROM tbl_b RIGHT JOIN tbl_a ON c = a WHERE a IN (1,2); +d +11 +22 +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_26247.cnf b/storage/spider/mysql-test/spider/bugfix/t/mdev_26247.cnf new file mode 100644 index 00000000000..05dfd8a0bce --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_26247.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_26247.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_26247.test new file mode 100644 index 00000000000..fb92066530e --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_26247.test @@ -0,0 +1,57 @@ +--echo # +--echo # MDEV-26247 Spider: Valid LEFT JOIN results in ERROR 1064 +--echo # + +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log + +--connection child2_1 +CREATE DATABASE auto_test_remote; +USE auto_test_remote; + +eval CREATE TABLE tbl_a ( + a int, + primary key (a) +) $CHILD2_1_ENGINE $CHILD2_1_CHARSET; + +eval CREATE TABLE tbl_b ( + c int, + d int, + primary key (c, d) +) $CHILD2_1_ENGINE $CHILD2_1_CHARSET; + +INSERT INTO tbl_a VALUES (1), (2), (3); +INSERT INTO tbl_b VALUES (1, 11), (2, 22), (3, 33); + +--connection master_1 +CREATE DATABASE auto_test_local; +USE auto_test_local; + +eval CREATE TABLE tbl_a ( + a int, + primary key (a) +) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='srv "s_2_1", table "tbl_a"'; + +eval CREATE TABLE tbl_b ( + c int, + d int, + primary key (c, d) +) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='srv "s_2_1", table "tbl_b"'; + +SELECT d FROM tbl_a LEFT JOIN tbl_b ON a = c WHERE a IN (1); +SELECT d FROM tbl_b RIGHT JOIN tbl_a ON c = a WHERE a IN (1); +SELECT d FROM tbl_b RIGHT JOIN tbl_a ON c = a WHERE a IN (1,2); + +--connection master_1 +DROP DATABASE IF EXISTS auto_test_local; +--connection child2_1 +DROP DATABASE IF EXISTS auto_test_remote; + +--disable_query_log +--disable_result_log +--source ../t/test_deinit.inc +--enable_query_log +--enable_result_log diff --git a/storage/spider/spd_group_by_handler.cc b/storage/spider/spd_group_by_handler.cc index de041897239..fefe3dba0e0 100644 --- a/storage/spider/spd_group_by_handler.cc +++ b/storage/spider/spd_group_by_handler.cc @@ -1672,8 +1672,31 @@ group_by_handler *spider_create_group_by_handler( } while ((from = from->next_local)); #endif - table_idx = 0; from = query->from; + if ((from && from->next_local && !from->next_local->next_local) && + (from->table->const_table || from->next_local->table->const_table)) + { + /* + Spider's GROUP BY handler does not support this case yet. However, this + wouldn't be a big problem. + + Only two table is involved in the query->from table list and one of them + is const table. Then, Spider only need to scan a single table. + */ + DBUG_RETURN(nullptr); + } + + if (!from->embedding && + (from->outer_join || from->on_expr || from->join_using_fields)) + { + /* + Spider's GROUP BY handler does not support this case yet too. + */ + DBUG_RETURN(nullptr); + } + + table_idx= 0; + from= query->from; while (from && from->table->const_table) { from = from->next_local; |