Bug #19805 | CONCAT_WS(' AND ', NULL, NULL) returns '' instead of NULL | ||
---|---|---|---|
Submitted: | 14 May 2006 18:31 | Modified: | 16 May 2006 5:05 |
Reporter: | Martin Krsek | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.0.22-BK, 5.0.20-nt | OS: | Linux (Linux, Win2k) |
Assigned to: | CPU Architecture: | Any |
[14 May 2006 18:31]
Martin Krsek
[15 May 2006 20:46]
Valeriy Kravchuk
Thank you for a problem report. Verified just as described with 5.0.22-BK on Linux: 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 2 to server version: 5.0.22 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select CONCAT_WS(' AND ', NULL, NULL); +--------------------------------+ | CONCAT_WS(' AND ', NULL, NULL) | +--------------------------------+ | | +--------------------------------+ 1 row in set (0.00 sec) It is a bug according to the manual (http://dev.mysql.com/doc/refman/5.0/en/string-functions.html): "CONCAT() returns NULL if any argument is NULL. mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL' mysql> SELECT CONCAT('My', NULL, 'QL'); -> NULL mysql> SELECT CONCAT(14.3); -> '14.3' - CONCAT_WS(separator,str1,str2,...) CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT()." If it is intended behaviour now for some reason, proper documentation is needed.
[15 May 2006 22:46]
Martin Krsek
Be careful with testing (5.0.20-nt): mysql> select concat_ws(NULL, NULL); +-----------------------+ | concat_ws(NULL, NULL) | +-----------------------+ | | +-----------------------+ but result is NULL: mysql> select ISNULL(concat_ws(NULL, NULL)); +-------------------------------+ | ISNULL(concat_ws(NULL, NULL)) | +-------------------------------+ | 1 | +-------------------------------+ but mysql> select NULL * NULL; +-------------+ | NULL * NULL | +-------------+ | NULL | +-------------+ (in 4.1.11-Debian_4sarge2 it works fine)
[15 May 2006 23:11]
Paul DuBois
The behavior reported is according to the documentation for CONCAT_WS(): http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Which says: If the separator is NULL, the result is NULL. and CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument. The behavior shown for 5.0.20 (not showing NULL when the result is NULL) is due to a bug in the mysql client, which is fixed in 5.0.21: http://dev.mysql.com/doc/refman/5.0/en/news-5-0-21.html (Bug#18265). This is not an issue with CONCAT_WS().
[16 May 2006 5:05]
Valeriy Kravchuk
OK, then it is not a bug. But this behaviour of CONCAT_WS with NULLs is somehow inconsistent with other functions.
[14 Apr 2020 12:04]
Martins Brivnieks
can someone explain this, goes away after server restart +-------------------------+-------------------------+-----------------------------------------------------------------------------+-----------+ | Concat_WS('','aa',Null) | Concat_WS('',Null,'bb') | Concat_WS('',Null,Null) | version() | +-------------------------+-------------------------+-----------------------------------------------------------------------------+-----------+ | aa | bb | 000000000000000000000000000000000000000000000000000000000000000000000000000 | 5.0.67 | +-------------------------+-------------------------+-----------------------------------------------------------------------------+-----------+