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:
None 
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
Description:
CONCAT_WS(' AND ', NULL, NULL) returns empty string instead of NULL,
so 
SET @where_cond = CONCAT_WS(' AND ', @where1, @where2);
SET @sql = CONCAT_WS(' WHERE ', 'SELECT * FROM table', @where_cond);
SELECT @sql;
results in
SELECT * FROM table WHERE
if both @where1 and @where2 are NULL
(before 4.0.14 it must work fine, because '' and NULL wasn't distinguished in CONCAT_WS)

How to repeat:
simply write CONCAT_WS(' AND ', NULL, NULL);

Suggested fix:
shuld return NULL istead of ''
[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    |
+-------------------------+-------------------------+-----------------------------------------------------------------------------+-----------+