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.

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 */