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
| Field | Original Value | New Value |
|---|---|---|
| Labels | bug |
| Labels | bug | bug need_feedback |
| Description |
h2. What's the problem
User Defined Functions and Stored Procedures 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. h2. How to reproduce Define UDFs: {code} 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) {code} Create a test table: {code} 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 (crc32(`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) {code} h3. 1. Test SELECT SELECTs get not results when using the UDFs in where conditions: {code} 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) {code} Check the general log on one of the remote nodes, and it can be seen Spider simply passed the parameters as cmp values here: {code} 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')) {code} h3. 2. Test UPDATEs {code} -- 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)) {code} h3. 3. Test DELETEs *The entire table is deleted!* {code} -- 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` {code} Meanwhile, using UDFs in other query parts give us expected results (e.g. as INSERT values, in a GROUP BY HAVING clause). |
h2. What's the problem
User Defined Functions and Stored Procedures 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. h2. How to reproduce Define UDFs: {code} 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) {code} Create a test table: {code} 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) {code} h3. 1. Test SELECT SELECTs get not results when using the UDFs in where conditions: {code} 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) {code} Check the general log on one of the remote nodes, and it can be seen Spider simply passed the parameters as cmp values here: {code} 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')) {code} h3. 2. Test UPDATEs {code} -- 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)) {code} h3. 3. Test DELETEs *The entire table is deleted!* {code} -- 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` {code} Meanwhile, using UDFs in other query parts give us expected results (e.g. as INSERT values, in a GROUP BY HAVING clause). |
| Assignee | Nayuta Yanagisawa [ JIRAUSER47117 ] |
| Labels | bug need_feedback | bug |
| Issue Type | Task [ 3 ] | Bug [ 1 ] |
| Affects Version/s | 10.6 [ 24028 ] |
| Fix Version/s | 10.6 [ 24028 ] |
| Affects Version/s | 10.3 [ 22126 ] | |
| Affects Version/s | 10.4 [ 22408 ] | |
| Affects Version/s | 10.5 [ 23123 ] |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] |
| Affects Version/s | 10.2 [ 14601 ] |
| Fix Version/s | 10.2 [ 14601 ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Comment | [ An easy fix would be not to perform direct UPDATE or DELETE when the WHERE condition includes UDFs. ] |
| Description |
h2. What's the problem
User Defined Functions and Stored Procedures 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. h2. How to reproduce Define UDFs: {code} 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) {code} Create a test table: {code} 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) {code} h3. 1. Test SELECT SELECTs get not results when using the UDFs in where conditions: {code} 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) {code} Check the general log on one of the remote nodes, and it can be seen Spider simply passed the parameters as cmp values here: {code} 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')) {code} h3. 2. Test UPDATEs {code} -- 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)) {code} h3. 3. Test DELETEs *The entire table is deleted!* {code} -- 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` {code} Meanwhile, using UDFs in other query parts give us expected results (e.g. as INSERT values, in a GROUP BY HAVING clause). |
h2. 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. h2. How to reproduce Define UDFs: {code} 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) {code} Create a test table: {code} 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) {code} h3. 1. Test SELECT SELECTs get not results when using the UDFs in where conditions: {code} 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) {code} Check the general log on one of the remote nodes, and it can be seen Spider simply passed the parameters as cmp values here: {code} 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')) {code} h3. 2. Test UPDATEs {code} -- 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)) {code} h3. 3. Test DELETEs *The entire table is deleted!* {code} -- 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` {code} Meanwhile, using UDFs in other query parts give us expected results (e.g. as INSERT values, in a GROUP BY HAVING clause). |
| Summary | Spider does not correctly handle UDF in where conds | Spider does not correctly handle UDF and stored function in where conds |
| Fix Version/s | 10.2.41 [ 26032 ] | |
| Fix Version/s | 10.3.32 [ 26029 ] | |
| Fix Version/s | 10.4.22 [ 26031 ] | |
| Fix Version/s | 10.5.13 [ 26026 ] | |
| Fix Version/s | 10.6.5 [ 26034 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Description |
h2. 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. h2. How to reproduce Define UDFs: {code} 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) {code} Create a test table: {code} 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) {code} h3. 1. Test SELECT SELECTs get not results when using the UDFs in where conditions: {code} 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) {code} Check the general log on one of the remote nodes, and it can be seen Spider simply passed the parameters as cmp values here: {code} 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')) {code} h3. 2. Test UPDATEs {code} -- 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)) {code} h3. 3. Test DELETEs *The entire table is deleted!* {code} -- 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` {code} Meanwhile, using UDFs in other query parts give us expected results (e.g. as INSERT values, in a GROUP BY HAVING clause). |
NOTE: The fix of this bug includes a change of a default setting.
{{spider_use_pushdown_udf}} default changed from {{-1}} to {{0}} h2. 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. h2. How to reproduce Define UDFs: {code} 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) {code} Create a test table: {code} 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) {code} h3. 1. Test SELECT SELECTs get not results when using the UDFs in where conditions: {code} 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) {code} Check the general log on one of the remote nodes, and it can be seen Spider simply passed the parameters as cmp values here: {code} 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')) {code} h3. 2. Test UPDATEs {code} -- 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)) {code} h3. 3. Test DELETEs *The entire table is deleted!* {code} -- 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` {code} Meanwhile, using UDFs in other query parts give us expected results (e.g. as INSERT values, in a GROUP BY HAVING clause). |
| Workflow | MariaDB v3 [ 124831 ] | MariaDB v4 [ 159662 ] |