MySQL Bugs: #82425: Problem with stored function which generates BIGINT UNSIGNED used in WHERE claus

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:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.25-1~dotdeb+7.1-log	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
During my development I found some problem related with index on BIGINT's.
I don't even know how to define it but checkout the example.

How to repeat:

# 1. adding function for calculating crc64 values based on some string
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's fields 
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;

# 3. inserting data - calculated crc64 values from some strings using test001 function

INSERT INTO `test001` (`id`, `t1`, `t3`) VALUES
  (1, 0, test001('photos')),
  (2, 0, test001('photos')),
  (3, 0, test001('photos'));
  

# 4. select query works and return records
SELECT *
FROM test001 AS `e`
WHERE e.t3 = test001('photos') 
AND e.t1 = 0;

*************************** 1. row ***************************
id: 1
t1: 0
t3: 15460588431078774149
*************************** 2. row ***************************
id: 2
t1: 0
t3: 15460588431078774149
*************************** 3. row ***************************
id: 3
t1: 0
t3: 15460588431078774149
3 rows 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 (t3) COMMENT '';

# 6. and the same select doesn't work !!!
SELECT *
FROM test001 AS `e`
WHERE e.t3 = test001('photos') 
AND e.t1 = 0;

Empty set (0.00 sec)

# 7. but little simplified works
SELECT *
FROM test001 AS `e`
WHERE e.t3 = test001('photos');

*************************** 1. row ***************************
id: 1
t1: 0
t3: 15460588431078774149
*************************** 2. row ***************************
id: 2
t1: 0
t3: 15460588431078774149
*************************** 3. row ***************************
id: 3
t1: 0
t3: 15460588431078774149
3 rows in set (0.00 sec)

# 8. also when you pass crc64 value it works
SELECT * FROM test001 AS `e` WHERE e.t3 = 15460588431078774149  AND e.t1 = 0\G
*************************** 1. row ***************************
id: 1
t1: 0
t3: 15460588431078774149
*************************** 2. row ***************************
id: 2
t1: 0
t3: 15460588431078774149
*************************** 3. row ***************************
id: 3
t1: 0
t3: 15460588431078774149
3 rows 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.t3 = test001('photos') 
AND e.t1 = 0;

# 11.
SELECT *
FROM test001 AS `e`
WHERE e.t3 = test001('photos');

Suggested fix:
Somehow it doesn't work for strings which generates BIGINT values in higher range then maximum for BIGINT SIGNED (>9223372036854775807) - so for example 'photos' generates crc64 value = 15460588431078774149. But test001 function returns proper BIGINT UNSIGNED values as you can see in simplfied query at #7.

But for 'test' string crc64 value is: 688887797400064883
And for this above example works fine.

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)

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`;

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.

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.

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)

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.

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.

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.

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.

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.

Posted by developer:
 
Noted in 8.0.1 changelog.

Constant folding could produce incorrect results for large unsigned
integers.