Bug #83427 LOCATE('he','hello',NULL) should return NULL instead of 0
Submitted: 18 Oct 2016 13:22 Modified: 19 Oct 2016 17:12
Reporter: Marko Mäkelä Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.47 OS:Any
Assigned to: CPU Architecture:Any

[18 Oct 2016 13:22] Marko Mäkelä
Description:
The function LOCATE(substr,str,pos) behaves inconsistently with other similar string functions when pos=NULL.

The latest functional changes to this function were by the following bug fixes:
5.0.30: Bug#10963 LEFT/RIGHT/SUBSTR/.. string functions returns wrong result on large length
5.0.47: Bug#27932 Comparing return value of LOCATE() to NULL fails

The functions LEFT(), RIGHT(), MID(), SUBSTR(), SUBSTRING_INDEX() return NULL when any of the arguments is NULL, but LOCATE('x','y',NULL) would return 0 instead of NULL.

How to repeat:
select locate('he','hello',null),locate('he',null,2),locate(null,'hello',2);
select locate(null,'hello',null),locate('he',null,null);
select left('hello',null),right('hello',null),substring('hello',2,null),mid('hello',1,null);

This would return 0 for the first expression, and NULL for the rest.

Suggested fix:
In Item_func_locate::val_int(), set the NULL flag when the third argument evaluates to NULL.
[19 Oct 2016 17:12] Paul DuBois
Posted by developer:
 
Fixed in 8.0.1.

The LOCATE() function returned NULL if the substr or str argument was
NULL, but not if the pos argument was NULL. Now it returns NULL if
any argument is NULL.