Bug #27932 Comparing return value of LOCATE() to NULL fails
Submitted: 18 Apr 2007 16:23 Modified: 27 Jun 2007 13:30
Reporter: David Shrewsbury Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.37, 5.1.17, 4.1 OS:Linux
Assigned to: Igor Babaev CPU Architecture:Any

[18 Apr 2007 16:23] David Shrewsbury
Description:
If the LOCATE() function is used with a NULL value, it returns NULL. Comparing the returned value using IS NULL or ISNULL() fails, but using the <=> operator works.

Workaround is to use the <=> operator.

Just for comparison, I tested the results using the CONCAT() function instead of LOCATE() and it worked as expected.

How to repeat:
mysql> SELECT LOCATE('foo', NULL);
+---------------------+
| LOCATE('foo', NULL) |
+---------------------+
|                NULL | 
+---------------------+
)

mysql> SELECT LOCATE('foo', NULL) IS NULL;
+-----------------------------+
| LOCATE('foo', NULL) IS NULL |
+-----------------------------+
|                           0 | 
+-----------------------------+

mysql> SELECT ISNULL(LOCATE('foo', NULL));
+-----------------------------+
| ISNULL(LOCATE('foo', NULL)) |
+-----------------------------+
|                           0 | 
+-----------------------------+

mysql> SELECT LOCATE('foo', NULL) <=> NULL;
+------------------------------+
| LOCATE('foo', NULL) <=> NULL |
+------------------------------+
|                            1 | 
+------------------------------+
)
[19 Apr 2007 6:36] Sveta Smirnova
Thank you for the report.

Verified as described.

mysql -T output is also interesting:

$mysql -T
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 119
Server version: 5.0.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT LOCATE('foo', NULL);
Field   1:  `LOCATE('foo', NULL)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NOT_NULL BINARY NUM 

+---------------------+
| LOCATE('foo', NULL) |
+---------------------+
|                NULL | 
+---------------------+
1 row in set (0.00 sec)
[19 Apr 2007 10:33] Martin Friebe
proposal for a patch and tests

Attachment: locate_null.patch (text/x-patch), 1.15 KiB.

[15 Jun 2007 1:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28832

ChangeSet@1.2522, 2007-06-14 18:55:07-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #27932: the function LOCATE returned NULL if any
  of its arguments was evaluated to NULL, while the predicate
  LOCATE(str,NULL) IS NULL erroneously was evaluated to FALSE.
  
  This happened because the Item_func_locate::fix_length_and_dec
  method by mistake set the value of the maybe_null flag for 
  the function item to 0. In consequence of this the function 
  was considered as the one that could not ever return NULL.
[25 Jun 2007 21:49] Bugs System
Pushed into 5.1.21-beta
[25 Jun 2007 21:51] Bugs System
Pushed into 5.0.46
[27 Jun 2007 13:30] Peter Lavin
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Noted in the changelogs for versions 5.1.21 and 5.0.46.