summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorDaniel Ye <35718816+GreaTdANie1@users.noreply.github.com>2021-09-22 17:55:05 +0800
committerGitHub <noreply@github.com>2021-09-22 18:55:05 +0900
commit9fc1ef932f0b7499724cfcf76bd0f298f135018f (patch)
tree75545537126fdf6a3639cc10fcbe8bff86487611
parent3d30458695ed9c8acf8d33b447e4b18844f2988b (diff)
downloadmariadb-git-9fc1ef932f0b7499724cfcf76bd0f298f135018f.tar.gz
MDEV-26545 Spider does not correctly handle UDF and stored function in where conds
- Handle stored function conditions correctly, with the same logic as with UDFs. - When running queries on Spider SE, by default, we do not push down WHERE conditions containing usage of UDFs/stored functions to remote data nodes, unless the user demands (by setting spider_use_pushdown_udf). - Disable direct update/delete when a udf condition is skipped.
-rw-r--r--sql/sql_delete.cc10
-rw-r--r--sql/sql_update.cc11
-rw-r--r--storage/spider/mysql-test/spider/r/udf_pushdown.result218
-rw-r--r--storage/spider/mysql-test/spider/t/udf_pushdown.inc48
-rw-r--r--storage/spider/mysql-test/spider/t/udf_pushdown.test141
-rw-r--r--storage/spider/spd_db_mysql.cc6
-rw-r--r--storage/spider/spd_param.cc2
7 files changed, 429 insertions, 7 deletions
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 084b5a76b84..f6c05da9445 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -583,14 +583,18 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
if (!table->check_virtual_columns_marked_for_read())
{
DBUG_PRINT("info", ("Trying direct delete"));
- if (select && select->cond &&
- (select->cond->used_tables() == table->map))
+ bool use_direct_delete= !select || !select->cond;
+ if (!use_direct_delete &&
+ (select->cond->used_tables() & ~RAND_TABLE_BIT) == table->map)
{
DBUG_ASSERT(!table->file->pushed_cond);
if (!table->file->cond_push(select->cond))
+ {
+ use_direct_delete= TRUE;
table->file->pushed_cond= select->cond;
+ }
}
- if (!table->file->direct_delete_rows_init())
+ if (use_direct_delete && !table->file->direct_delete_rows_init())
{
/* Direct deleting is supported */
DBUG_PRINT("info", ("Using direct delete"));
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index e808d7f5de0..6065d03402f 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -651,15 +651,20 @@ int mysql_update(THD *thd,
!table->check_virtual_columns_marked_for_write())
{
DBUG_PRINT("info", ("Trying direct update"));
- if (select && select->cond &&
- (select->cond->used_tables() == table->map))
+ bool use_direct_update= !select || !select->cond;
+ if (!use_direct_update &&
+ (select->cond->used_tables() & ~RAND_TABLE_BIT) == table->map)
{
DBUG_ASSERT(!table->file->pushed_cond);
if (!table->file->cond_push(select->cond))
+ {
+ use_direct_update= TRUE;
table->file->pushed_cond= select->cond;
+ }
}
- if (!table->file->info_push(INFO_KIND_UPDATE_FIELDS, &fields) &&
+ if (use_direct_update &&
+ !table->file->info_push(INFO_KIND_UPDATE_FIELDS, &fields) &&
!table->file->info_push(INFO_KIND_UPDATE_VALUES, &values) &&
!table->file->direct_update_rows_init(&fields))
{
diff --git a/storage/spider/mysql-test/spider/r/udf_pushdown.result b/storage/spider/mysql-test/spider/r/udf_pushdown.result
new file mode 100644
index 00000000000..4ca734165e7
--- /dev/null
+++ b/storage/spider/mysql-test/spider/r/udf_pushdown.result
@@ -0,0 +1,218 @@
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+child3_1
+child3_2
+child3_3
+#
+# MDEV-26545 Spider does not correctly handle UDF and stored function in where conds
+#
+
+##### enable general_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";
+TRUNCATE TABLE mysql.general_log;
+
+##### create databases #####
+connection master_1;
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+connection child2_1;
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+
+##### create tables #####
+connection child2_1;
+CHILD_CREATE_TABLE
+connection master_1;
+MASTER_CREATE_TABLE
+CREATE TABLE ta_l (
+id INT NOT NULL,
+a INT,
+PRIMARY KEY(id)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1
+INSERT INTO ta_l VALUES
+(1, 11),
+(2, 22),
+(3, 33),
+(4, 44),
+(5, 55);
+
+##### create functions #####
+connection master_1;
+CREATE FUNCTION `plusone`( param INT ) RETURNS INT
+BEGIN
+RETURN param + 1;
+END //
+connection child2_1;
+CREATE FUNCTION `plusone`( param INT ) RETURNS INT
+BEGIN
+RETURN param + 1;
+END //
+
+########## spider_use_pushdown_udf=0 ##########
+connection master_1;
+SET @@spider_use_pushdown_udf = 0;
+
+##### test SELECTs #####
+connection master_1;
+SELECT * FROM ta_l WHERE id = plusone(1);
+id a
+2 22
+SELECT * FROM ta_l WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32);
+id a
+3 33
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE "%select%" AND argument NOT LIKE "%argument%";
+argument
+select `id`,`a` from `auto_test_remote`.`ta_r`
+select `id`,`a` from `auto_test_remote`.`ta_r`
+
+##### test UPDATEs #####
+connection master_1;
+UPDATE ta_l SET a = plusone(221) WHERE id = plusone(1);
+SELECT * FROM ta_l;
+id a
+1 11
+2 222
+3 33
+4 44
+5 55
+UPDATE ta_l SET a = plusone(332) WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32);
+SELECT * FROM ta_l;
+id a
+1 11
+2 222
+3 333
+4 44
+5 55
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE "%update%" AND argument NOT LIKE "%argument%";
+argument
+select `id`,`a` from `auto_test_remote`.`ta_r` for update
+update `auto_test_remote`.`ta_r` set `a` = 222 where `id` = 2 limit 1
+select `id`,`a` from `auto_test_remote`.`ta_r` for update
+update `auto_test_remote`.`ta_r` set `a` = 333 where `id` = 3 and `a` = 33 limit 1
+
+##### test DELETEs #####
+connection master_1;
+DELETE FROM ta_l WHERE id = plusone(1);
+SELECT * FROM ta_l;
+id a
+1 11
+3 333
+4 44
+5 55
+DELETE FROM ta_l WHERE id IN (plusone(1), plusone(2), plusone(3)) AND a = plusone(43);
+SELECT * FROM ta_l;
+id a
+1 11
+3 333
+5 55
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE (argument LIKE "%delete%" OR argument LIKE "%update%") AND argument NOT LIKE "%argument%";
+argument
+select `id` from `auto_test_remote`.`ta_r` for update
+delete from `auto_test_remote`.`ta_r` where `id` = 2 limit 1
+select `id`,`a` from `auto_test_remote`.`ta_r` for update
+delete from `auto_test_remote`.`ta_r` where `id` = 4 and `a` = 44 limit 1
+
+##### reset records #####
+connection master_1;
+TRUNCATE TABLE ta_l;
+INSERT INTO ta_l VALUES
+(1, 11),
+(2, 22),
+(3, 33),
+(4, 44),
+(5, 55);
+
+########## spider_use_pushdown_udf=1 ##########
+connection master_1;
+SET @@spider_use_pushdown_udf = 1;
+
+##### test SELECTs #####
+connection master_1;
+SELECT * FROM ta_l WHERE id = plusone(1);
+id a
+2 22
+SELECT * FROM ta_l WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32);
+id a
+3 33
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE "%select%" AND argument NOT LIKE "%argument%";
+argument
+select t0.`id` `id`,t0.`a` `a` from `auto_test_remote`.`ta_r` t0 where (t0.`id` = (`plusone`(1)))
+select t0.`id` `id`,t0.`a` `a` from `auto_test_remote`.`ta_r` t0 where ((t0.`id` in( (`plusone`(1)) , (`plusone`(2)))) and (t0.`a` = (`plusone`(32))))
+
+##### test UPDATEs #####
+connection master_1;
+UPDATE ta_l SET a = plusone(221) WHERE id = plusone(1);
+SELECT * FROM ta_l;
+id a
+1 11
+2 222
+3 33
+4 44
+5 55
+UPDATE ta_l SET a = plusone(332) WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32);
+SELECT * FROM ta_l;
+id a
+1 11
+2 222
+3 333
+4 44
+5 55
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE "%update%" AND argument NOT LIKE "%argument%";
+argument
+update `auto_test_remote`.`ta_r` set `a` = (`plusone`(221)) where (`id` = (`plusone`(1)))
+update `auto_test_remote`.`ta_r` set `a` = (`plusone`(332)) where ((`id` in( (`plusone`(1)) , (`plusone`(2)))) and (`a` = (`plusone`(32))))
+
+##### test DELETEs #####
+connection master_1;
+DELETE FROM ta_l WHERE id = plusone(1);
+SELECT * FROM ta_l;
+id a
+1 11
+3 333
+4 44
+5 55
+DELETE FROM ta_l WHERE id IN (plusone(1), plusone(2), plusone(3)) AND a = plusone(43);
+SELECT * FROM ta_l;
+id a
+1 11
+3 333
+5 55
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE (argument LIKE "%delete%" OR argument LIKE "%update%") AND argument NOT LIKE "%argument%";
+argument
+delete from `auto_test_remote`.`ta_r` where (`id` = (`plusone`(1)))
+delete from `auto_test_remote`.`ta_r` where ((`id` in( (`plusone`(1)) , (`plusone`(2)) , (`plusone`(3)))) and (`a` = (`plusone`(43))))
+
+deinit
+connection master_1;
+DROP FUNCTION `plusone`;
+DROP DATABASE IF EXISTS auto_test_local;
+connection child2_1;
+SET @@global.general_log = @general_log_backup;
+SET @@global.log_output = @log_output_backup;
+DROP FUNCTION `plusone`;
+DROP DATABASE IF EXISTS auto_test_remote;
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+child3_1
+child3_2
+child3_3
+
+end of test
diff --git a/storage/spider/mysql-test/spider/t/udf_pushdown.inc b/storage/spider/mysql-test/spider/t/udf_pushdown.inc
new file mode 100644
index 00000000000..160e8af21b2
--- /dev/null
+++ b/storage/spider/mysql-test/spider/t/udf_pushdown.inc
@@ -0,0 +1,48 @@
+--echo
+--echo ##### test SELECTs #####
+--connection master_1
+SELECT * FROM ta_l WHERE id = plusone(1);
+SELECT * FROM ta_l WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32);
+
+if ($USE_CHILD_GROUP2)
+{
+ --connection child2_1
+ SELECT argument FROM mysql.general_log WHERE argument LIKE "%select%" AND argument NOT LIKE "%argument%";
+ --disable_query_log
+ TRUNCATE TABLE mysql.general_log;
+ --enable_query_log
+}
+
+--echo
+--echo ##### test UPDATEs #####
+--connection master_1
+UPDATE ta_l SET a = plusone(221) WHERE id = plusone(1);
+SELECT * FROM ta_l;
+UPDATE ta_l SET a = plusone(332) WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32);
+SELECT * FROM ta_l;
+
+if ($USE_CHILD_GROUP2)
+{
+ --connection child2_1
+ SELECT argument FROM mysql.general_log WHERE argument LIKE "%update%" AND argument NOT LIKE "%argument%";
+ --disable_query_log
+ TRUNCATE TABLE mysql.general_log;
+ --enable_query_log
+}
+
+--echo
+--echo ##### test DELETEs #####
+--connection master_1
+DELETE FROM ta_l WHERE id = plusone(1);
+SELECT * FROM ta_l;
+DELETE FROM ta_l WHERE id IN (plusone(1), plusone(2), plusone(3)) AND a = plusone(43);
+SELECT * FROM ta_l;
+
+if ($USE_CHILD_GROUP2)
+{
+ --connection child2_1
+ SELECT argument FROM mysql.general_log WHERE (argument LIKE "%delete%" OR argument LIKE "%update%") AND argument NOT LIKE "%argument%";
+ --disable_query_log
+ TRUNCATE TABLE mysql.general_log;
+ --enable_query_log
+}
diff --git a/storage/spider/mysql-test/spider/t/udf_pushdown.test b/storage/spider/mysql-test/spider/t/udf_pushdown.test
new file mode 100644
index 00000000000..2eadbbbb40b
--- /dev/null
+++ b/storage/spider/mysql-test/spider/t/udf_pushdown.test
@@ -0,0 +1,141 @@
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source test_init.inc
+--enable_result_log
+--enable_query_log
+
+--echo #
+--echo # MDEV-26545 Spider does not correctly handle UDF and stored function in where conds
+--echo #
+
+let $CHILD_CREATE_TABLE=
+ CREATE TABLE ta_r (
+ id INT NOT NULL,
+ a INT,
+ PRIMARY KEY(id)
+ ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+
+let $MASTER_CREATE_TABLE_OUTPUT=
+ CREATE TABLE ta_l (
+ id INT NOT NULL,
+ a INT,
+ PRIMARY KEY(id)
+ ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1;
+
+let $MASTER_CREATE_TABLE=
+ CREATE TABLE ta_l (
+ id INT NOT NULL,
+ a INT,
+ PRIMARY KEY(id)
+ ) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1;
+
+--echo
+--echo ##### enable general_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";
+TRUNCATE TABLE mysql.general_log;
+
+--echo
+--echo ##### create databases #####
+--connection master_1
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+if ($USE_CHILD_GROUP2)
+{
+ --connection child2_1
+ CREATE DATABASE auto_test_remote;
+ USE auto_test_remote;
+}
+
+--echo
+--echo ##### create tables #####
+if ($USE_CHILD_GROUP2)
+{
+ --connection child2_1
+ --disable_query_log
+ echo CHILD_CREATE_TABLE;
+ eval $CHILD_CREATE_TABLE;
+ --enable_query_log
+}
+
+--connection master_1
+--disable_query_log
+echo MASTER_CREATE_TABLE;
+echo $MASTER_CREATE_TABLE_OUTPUT;
+eval $MASTER_CREATE_TABLE;
+--enable_query_log
+
+INSERT INTO ta_l VALUES
+ (1, 11),
+ (2, 22),
+ (3, 33),
+ (4, 44),
+ (5, 55);
+
+--echo
+--echo ##### create functions #####
+--connection master_1
+DELIMITER //;
+CREATE FUNCTION `plusone`( param INT ) RETURNS INT
+BEGIN
+ RETURN param + 1;
+END //
+DELIMITER ;//
+
+--connection child2_1
+DELIMITER //;
+CREATE FUNCTION `plusone`( param INT ) RETURNS INT
+BEGIN
+ RETURN param + 1;
+END //
+DELIMITER ;//
+
+--echo
+--echo ########## spider_use_pushdown_udf=0 ##########
+--connection master_1
+SET @@spider_use_pushdown_udf = 0;
+--source udf_pushdown.inc
+
+--echo
+--echo ##### reset records #####
+--connection master_1
+TRUNCATE TABLE ta_l;
+INSERT INTO ta_l VALUES
+ (1, 11),
+ (2, 22),
+ (3, 33),
+ (4, 44),
+ (5, 55);
+
+--echo
+--echo ########## spider_use_pushdown_udf=1 ##########
+--connection master_1
+SET @@spider_use_pushdown_udf = 1;
+--source udf_pushdown.inc
+
+--echo
+--echo deinit
+--disable_warnings
+--connection master_1
+DROP FUNCTION `plusone`;
+DROP DATABASE IF EXISTS auto_test_local;
+if ($USE_CHILD_GROUP2)
+{
+ --connection child2_1
+ SET @@global.general_log = @general_log_backup;
+ SET @@global.log_output = @log_output_backup;
+ DROP FUNCTION `plusone`;
+ DROP DATABASE IF EXISTS auto_test_remote;
+}
+--disable_query_log
+--disable_result_log
+--source test_deinit.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
+--echo
+--echo end of test
diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc
index 3cfc1f7eff1..0fd5112380b 100644
--- a/storage/spider/spd_db_mysql.cc
+++ b/storage/spider/spd_db_mysql.cc
@@ -4985,10 +4985,16 @@ int spider_db_mbase_util::open_item_func(
separator_str_length = SPIDER_SQL_AND_LEN;
}
break;
+ case Item_func::FUNC_SP:
case Item_func::UDF_FUNC:
use_pushdown_udf = spider_param_use_pushdown_udf(spider->trx->thd,
spider->share->use_pushdown_udf);
if (!use_pushdown_udf)
+ /*
+ This is the default behavior because the remote nodes may deal with
+ the function in an unexpected way (e.g. not having the same
+ definition). Users can turn it on if they know what they are doing.
+ */
DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM);
if (str)
{
diff --git a/storage/spider/spd_param.cc b/storage/spider/spd_param.cc
index 61fd4339ca5..8e56cae89a8 100644
--- a/storage/spider/spd_param.cc
+++ b/storage/spider/spd_param.cc
@@ -1978,7 +1978,7 @@ static MYSQL_THDVAR_INT(
"Remote server transmission existence when UDF is used at condition and \"engine_condition_pushdown=1\"", /* comment */
NULL, /* check */
NULL, /* update */
- -1, /* def */
+ 0, /* def */
-1, /* min */
1, /* max */
0 /* blk */