Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
Description
NOTE: The fix of this bug includes a change of a default setting.
spider_use_pushdown_udf default changed from -1 to 0
What's the problem
User Defined Functions and Stored Procedures Functions defined on Spider do not take effect when using them in queries' where conditions, because Spider does not handle them correctly. This can result in Spider getting wrong query results, or even deleting the whole table.
How to reproduce
Define UDFs:
MariaDB [mytest]> show create function plusone\G
|
*************************** 1. row ***************************
|
Function: plusone
|
sql_mode:
|
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `plusone`(num INT) RETURNS int(11)
|
BEGIN
|
|
|
RETURN num + 1;
|
|
|
END
|
character_set_client: latin1
|
collation_connection: latin1_swedish_ci
|
Database Collation: utf8mb4_general_ci
|
1 row in set (0.00 sec)
|
|
|
MariaDB [mytest]> show create function addcom\G
|
*************************** 1. row ***************************
|
Function: addcom
|
sql_mode:
|
Create Function: CREATE DEFINER=`mysql`@`127.0.0.1` FUNCTION `addcom`( domain_name TEXT ) RETURNS text CHARSET utf8mb4
|
BEGIN
|
|
|
RETURN CONCAT(domain_name, ".com");
|
|
|
END
|
character_set_client: latin1
|
collation_connection: latin1_swedish_ci
|
Database Collation: utf8mb4_general_ci
|
1 row in set (0.00 sec)
|
|
|
-- function tests
|
MariaDB [mytest]> select plusone(99);
|
+-------------+
|
| plusone(99) |
|
+-------------+
|
| 100 |
|
+-------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [mytest]> select addcom("tencent");
|
+-------------------+
|
| addcom("tencent") |
|
+-------------------+
|
| tencent.com |
|
+-------------------+
|
1 row in set (0.00 sec)
|
Create a test table:
MariaDB [mytest]> show create table test_table\G
|
*************************** 1. row ***************************
|
Table: test_table
|
Create Table: CREATE TABLE `test_table` (
|
`c1` int(11) NOT NULL,
|
`c2` text DEFAULT NULL,
|
PRIMARY KEY (`c1`)
|
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb4
|
PARTITION BY LIST (`c1` MOD 2)
|
(PARTITION `pt0` VALUES IN (0) COMMENT = 'database "mytest_0", table "test_table", server "SPT0"' ENGINE = SPIDER,
|
PARTITION `pt1` VALUES IN (1) COMMENT = 'database "mytest_1", table "test_table", server "SPT1"' ENGINE = SPIDER)
|
1 row in set (0.00 sec)
|
|
|
MariaDB [mytest]> select * from test_table;
|
+-----+-------------+
|
| c1 | c2 |
|
+-----+-------------+
|
| 100 | tencent.com |
|
| 101 | aa.com |
|
| 102 | google.com |
|
+-----+-------------+
|
3 rows in set (0.00 sec)
|
1. Test SELECT
SELECTs get not results when using the UDFs in where conditions:
MariaDB [mytest]> select * from test_table where c1=plusone(99);
|
Empty set (0.00 sec)
|
|
|
MariaDB [mytest]> select * from test_table where c2=addcom("tencent");
|
Empty set (0.00 sec)
|
Check the general log on one of the remote nodes, and it can be seen Spider simply passed the parameters as cmp values here:
2021-09-01T11:35:49.243441Z 4902 Query select `c1`,`c2` from `mytest_0`.`test_table` where (`c1` = (99))
|
2021-09-01T11:35:51.515475Z 4902 Query select `c1`,`c2` from `mytest_0`.`test_table` where (`c2` = ('tencent'))
|
2. Test UPDATEs
-- Spider
|
MariaDB [mytest]> update test_table set c2="qq.com" where c1=plusone(99);
|
Query OK, 0 rows affected (0.00 sec)
|
Rows matched: 0 Changed: 0 Warnings: 0
|
|
|
MariaDB [mytest]> select * from test_table;
|
+-----+-------------+
|
| c1 | c2 |
|
+-----+-------------+
|
| 100 | tencent.com |
|
| 101 | aa.com |
|
| 102 | google.com |
|
+-----+-------------+
|
3 rows in set (0.00 sec)
|
|
|
-- General log on remote nodes
|
2021-09-01T11:42:54.410058Z 48269 Query update `mytest_1`.`test_table` set `c2` = 'qq.com' where (`c1` = (99))
|
3. Test DELETEs
The entire table is deleted!
-- Spider
|
MariaDB [mytest]> delete from test_table where c1=plusone(99);
|
Query OK, 3 rows affected (0.02 sec)
|
|
|
MariaDB [mytest]> select * from test_table;
|
Empty set (0.00 sec)
|
|
|
-- General log on remote nodes
|
2021-09-01T11:44:11.491758Z 48269 Query delete from `mytest_1`.`test_table`
|
Meanwhile, using UDFs in other query parts give us expected results (e.g. as INSERT values, in a GROUP BY HAVING clause).