Bug #82425 | Problem with stored function which generates BIGINT UNSIGNED used in WHERE claus | ||
---|---|---|---|
Submitted: | 3 Aug 2016 13:38 | Modified: | 18 Aug 2016 13:55 |
Reporter: | Artur Banul | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.6.25-1~dotdeb+7.1-log | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[3 Aug 2016 13:38]
Artur Banul
[3 Aug 2016 17:47]
Artur Banul
Example can be even simplied # 1. adding function for calculating crc64 values based on some string DROP FUNCTION test001; DELIMITER ;; CREATE DEFINER = 'root'@'localhost' FUNCTION test001( _text TEXT ) RETURNS BIGINT(20) UNSIGNED DETERMINISTIC NO SQL SQL SECURITY DEFINER COMMENT '' BEGIN RETURN CONV(LEFT(MD5(_text),16),16,10); END;; DELIMITER ; # 2. creating table with BIGINT field DROP TABLE `test001`; CREATE TABLE `test001` ( `id` bigint(20) UNSIGNED NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # 3. inserting data - calculated crc64 values from some strings using test001 function INSERT INTO `test001` (`id`) VALUES (test001('photos')); # 4. select query works and return records SELECT * FROM test001 AS `e` WHERE e.id = test001('photos')\G *************************** 1. row *************************** id: 15460588431078774149 1 row in set (0.00 sec) # 5. but when you add an index on t3 colemn something is not right ALTER TABLE test001 ADD INDEX test001_idx1 (id) COMMENT ''; # 6. and the same select doesn't work !!! SELECT * FROM test001 AS `e` WHERE e.id = test001('photos') AND TRUE\G Empty set (0.00 sec) # 7. but little simplified works SELECT * FROM test001 AS `e` WHERE e.id = test001('photos')\G *************************** 1. row *************************** id: 15460588431078774149 1 row in set (0.00 sec) # 9. when you remove added index select queries workds again ALTER TABLE test001 DROP INDEX test001_idx1; # 10. SELECT * FROM test001 AS `e` WHERE e.id = test001('photos') AND TRUE\G *************************** 1. row *************************** id: 15460588431078774149 1 row in set (0.00 sec) # 11. SELECT * FROM test001 AS `e` WHERE e.id = test001('photos')\G *************************** 1. row *************************** id: 15460588431078774149 1 row in set (0.00 sec)
[3 Aug 2016 18:16]
MySQL Verification Team
Hi, Thank you for your bug report. It is a bug, but not that hard since there is a workaround with user variable. Here is a full SQL test case: DROP TABLE IF EXISTS `test001`; DROP FUNCTION IF EXISTS test0001; DROP FUNCTION IF EXISTS test001; DELIMITER $$ CREATE DEFINER = 'root'@'localhost' FUNCTION test0001( _text TEXT ) RETURNS BIGINT(20) UNSIGNED DETERMINISTIC NO SQL SQL SECURITY DEFINER COMMENT '' BEGIN RETURN CONV(LEFT(MD5(_text),16),16,10); END$$ DELIMITER ; CREATE TABLE `test001` ( `id` bigint(20) unsigned NOT NULL DEFAULT '0', `t1` bigint(20) unsigned NOT NULL DEFAULT '0', `t3` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `test001` (`id`, `t1`, `t3`) VALUES (1, 0, test0001('photos')), (2, 0, test0001('photos')), (3, 0, test0001('photos')); # 4. select query works and return records SELECT "It works without index "; SELECT " "; SELECT * FROM test001 AS `e` WHERE e.t3 = test0001('photos') AND e.t1 = 0; ALTER TABLE test001 ADD INDEX test001_idx1 (t3) COMMENT ''; SELECT "It does not work with index"; SELECT " "; SET @rez= test0001('photos'); SELECT *, @rez FROM test001 AS `e` WHERE e.t3 = @rez AND e.t1 = 0; SELECT "But, it works without : AND et1 = 0"; SELECT " "; SELECT * FROM test001 AS `e` WHERE e.t3 = test0001('photos'); DROP FUNCTION IF EXISTS test001; DROP TABLE IF EXISTS `test001`;
[3 Aug 2016 18:24]
Artur Banul
Yes, I'm aware that this example works with user variable but still this is an unexplained bug/behaviour. Unfortunatelly, I our API there lot of queries with such inline function call. It would need lot of changes in many DB classes.
[3 Aug 2016 18:29]
Artur Banul
Little background: Before, we used UDF string2crc64 written in C++. Right now, we have requirement to remove UDF's. There were no problems with inline UDF function calls. So currently simple function replacement will require API rebuilding.
[4 Aug 2016 9:08]
Øystein Grøvlen
Posted by developer: The EXPLAIN output indicates that the optimizer thinks that it is dealing with a const table: mysql> EXPLAIN SELECT * FROM test001 AS `e` WHERE e.t3 = test001('photos') AND e.t1 = 0; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ 1 row in set, 1 warning (0,00 sec) Not sure how that could happen and why that should be dependent on the existence of an index. For the simpler query one gets the following plan: mysql> EXPLAIN SELECT * FROM test001 AS `e` WHERE e.t3 = test001('photos'); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | e | NULL | ALL | test001_idx1 | NULL | NULL | NULL | 3 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0,00 sec) Without index, one gets: mysql> EXPLAIN SELECT * FROM test001 AS `e` WHERE e.t3 = test001('photos') AND e.t1 = 0; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0,00 sec)
[10 Aug 2016 14:42]
Steinar Gunderson
Posted by developer: I made an MTR test case, using FORCE/IGNORE INDEX. Note that this bug only triggers when the function returns a value that would be negative when converted to signed (as you can see from the EXPLAIN). IOW, it is likely to be an issue with signed/unsigned comparisons. This bug is reproducible in 5.7 but not 5.5.
[11 Aug 2016 13:28]
Steinar Gunderson
Posted by developer: I tried a bisect, but even with manual guidance, it looks to be very difficult because there are many worklogs here cross-merging from each other. It looks as if WL #4443 (“Remove scalability problem with many lock's”) is at least partially implicated; there's one commit there where this simply starts segfaulting, but it's hard to figure if that is the true cause or not. WL #6255 is also involved in this merge, but since it's an InnoDB worklog and this also happens with MyISAM, it's not likely to be the culprit.
[11 Aug 2016 14:43]
Steinar Gunderson
Posted by developer: I finally found the offending commit: commit 7491b2b86810a83d06c3467925136329ab6a4fe8 Author: Mattias Jonsson <mattias.jonsson@oracle.com> Date: Mon May 7 23:43:48 2012 +0200 WL#4443 Fix for Item_func_sp::const_item. And one test result fix (same as in trunk) Reverting it fixes the test, but it's unclear what else it might break.
[12 Aug 2016 7:24]
Artur Banul
You wrote: | it is likely to be an issue with signed/unsigned comparisons. Seems so, but check the point 7. # 7. but little simplified works SELECT * FROM test001 AS `e` WHERE e.t3 = test001('photos'); When there is only one comparission in WHERE clause it works. But when you add simple AND 1=1 then it doesn't.
[12 Aug 2016 8:38]
Steinar Gunderson
Posted by developer: Sure, but that's probably just due to some optimization being inhibited. It's relevant for diagnosis, but not a huge smoking gun for anything in particular.
[18 Aug 2016 13:55]
Paul DuBois
Posted by developer: Noted in 8.0.1 changelog. Constant folding could produce incorrect results for large unsigned integers.