Bug #586 CONCAT_WS() has wrong handling of empty strings
Submitted: 4 Jun 2003 10:40 Modified: 10 Dec 2003 16:54
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0 OS:Any (all)
Assigned to: Jani Tolonen CPU Architecture:Any

[4 Jun 2003 10:40] Peter Zaitsev
CONCAT_WS has wrong handling of empty string, handling them same 
way as NULL, instead of proper concatenation.

This shall be fixed in 4.1 as users in MySQL 4.0 may depends on old behavior.

How to repeat:
mysql> select concat_ws("/","a",NULL,"b");
| concat_ws("/","a",NULL,"b") |
| a/b                         |
1 row in set (0.12 sec)

mysql> select concat_ws("/","a","","b");
| concat_ws("/","a","","b") |
| a/b                       |
1 row in set (0.00 sec)

Last shall return: "a//b"
[5 Jun 2003 5:03] Jani Tolonen

[10 Dec 2003 16:54] Michael Widenius
This was fixed in 4.0.14 as this was a clear bug in how concat_ws() was supposed to work from the start

If you want to skip an empty string and NULL's for an argument you can use:

[19 Apr 2004 22:23] Shayne Paddock
I'm running 4.1.1 and I still see this bug???

[7 Jul 2004 11:13] Mike Price
Was this really a bug? 

I have been using concat_ws for a long time to separate the fields of an address. Having upgraded to v4, all my clients address databases have become very untidy. 

Michael W's solution is not viable for using the function with mutiple arguments. 

Having the old version on concat_ws (perhaps renamed eg concat_wsx??) in future versions would be very useful.
[11 Jan 2007 13:53] torp
It's 2007 now, but I agree with Mike Price.
Maybe the old CONCAT_WS did not work as meant, but it was very handy that way. Now the separator will be added, even after empty results. Ugly!