| 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: | |
| 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 |
[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.
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 | +------------------------------+ )