| Bug #21020 | coalesce() inconsistency with empty strings | ||
|---|---|---|---|
| Submitted: | 12 Jul 2006 22:02 | Modified: | 13 Jul 2006 19:55 |
| Reporter: | Ville Skyttä | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.22 | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[13 Jul 2006 19:55]
Valeriy Kravchuk
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. With 5.0.25-BK on Linux I've got:
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.25
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select coalesce('', 'foo');
+---------------------+
| coalesce('', 'foo') |
+---------------------+
| |
+---------------------+
1 row in set (0.00 sec)
mysql> select coalesce('', NULL, 'foo');
+---------------------------+
| coalesce('', NULL, 'foo') |
+---------------------------+
| |
+---------------------------+
1 row in set (0.00 sec)
mysql> select coalesce(NULL, '', 'foo');
+---------------------------+
| coalesce(NULL, '', 'foo') |
+---------------------------+
| |
+---------------------------+
1 row in set (0.00 sec)
mysql> select coalesce('bar', NULL, '', 'foo');
+----------------------------------+
| coalesce('bar', NULL, '', 'foo') |
+----------------------------------+
| bar |
+----------------------------------+
1 row in set (0.00 sec)
That is, consistent results, as documented.
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://www.mysql.com/doc/en/Installing_source_tree.html
Description: coalesce() is documented to return the first non-NULL value in the given list, or NULL if there are no non-NULL values. However, this is not the case when the given values contain empty strings; given such input the return values vary in surprising and apparently incorrect ways. In contrast, ifnull() which can be used for similar purposes (especially when nested) works as documented, and produces consistent results even with cases involving empty strings. How to repeat: mysql> select coalesce('', 'foo'); +---------------------+ | coalesce('', 'foo') | +---------------------+ | | +---------------------+ 1 row in set (0.00 sec) An empty string (not NULL) was returned, this is ok. mysql> select coalesce('', NULL, 'foo'); +---------------------------+ | coalesce('', NULL, 'foo') | +---------------------------+ | NULL | +---------------------------+ 1 row in set (0.00 sec) An empty string should have been returned for consistency with the first example. Or if '' is treated as NULL, 'foo' should have been returned, but in that case, the first example should have returned 'foo' too. mysql> select coalesce(NULL, '', 'foo'); +---------------------------+ | coalesce(NULL, '', 'foo') | +---------------------------+ | NULL | +---------------------------+ 1 row in set (0.00 sec) Same as above.