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).
Attachments
Activity
nayuta-yanagisawa Sure, I'd love to. And if you don't mind, please let me know if there is anything I need to pay attention to before doing it.
And great point on this!
In my understanding, Item is something that returns a value, an expression. So, I think stored procedures aren't represented by Item_func_sp.
DanielYe133 Great! Because of JIRA permission, I cannot assign the issue to you, but you are now in charge of this.
Here is some advice:
- Please read the contribution guide first. https://mariadb.com/kb/en/contributing-code/
- You can use your predecessor's PRs as a guide to creating your own PR.
- In general, the base branch should be the oldest supported major version on which the bug occurs.
- However, for this bug, the server behaves differently based on the major versions (see my comments above). So, I suggest you create different PRs based on 10.2 and 10.3, while the test can be shared. More precisely, you can create a PR including fix and test based on 10.2. Once I merge it to 10.2 and 10.3, you can create another PR, based on 10.3, including only a fix for 10.3 or later.
DanielYe133 If you have any questions, please feel free to ask.
nayuta-yanagisawa Thank you so much for your detailed guidance! I'll inform you once I have any progress on any question.
DanielYe133 OK. BTW, are you interested in fixing the present bug by yourself? If you want me to do it, that's fine too.
> after looking into the code I now realize it is the type of Item_func_sp which represents stored functions.
In my understanding, Item is something that returns a value, an expression. So, I think stored procedures aren't represented by Item_func_sp.