MySQL Bugs: #63919: concat() and concat_ws() may return NULL in not-documented circumstance-

Bug #63919	concat() and concat_ws() may return NULL in not-documented circumstance-
Submitted:	4 Jan 2012 13:02	Modified:	6 Jan 2012 15:39
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	any	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
We find that if the result of concat() and concat_ws() exceeds max_allowed_packet then the result is NULL.

It is not documented here:
http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_concat-ws
.. it only tells that if 1st argument is NULL then the result also is.

Here http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_allowed_pac... it is alos not properly documented that setting max_allowed_packet amy affect what concat() and concat_ws() returns

This is a *serious* omission in the docs IMHO. And a BIG SURPRISE! Unexpected NULL results may cause serious issues (miscalculations and crashes) in client programs and scripts.

This old bug report:
http://bugs.mysql.com/bug.php?id=34782
.. complains about same, but it was just closed as 'not a bug' without checking if documentation is proper.

This page http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
.. does however list that group_concat() behaves like this restricted by group_concat_max_len variable (what is again restricted by max_allowed_packet). I could imagine that they use some of the same code internally?

How to repeat:
See above.

Suggested fix:
I posted this for 'docs' category because of the conclusion in bug34782. But I don't necessarily agree with the conclusion here.

If it is really required that concat() and concat_ws() returns NULL in such case it should be documented for each.

But better I think it should just truncate to max_allowed_packet length or not truncate at all. Please reconsider this.

There is a warning BTW "Warning 1301 Result of concat_ws() was larger than max_allowed_packet (1048576) - truncated"

Fixed typo in synopsis

let me add that I actually do not return the result concat_ws() as such.  I have it wrapped in a checksum calculation (simplified) like md5(concat_ws(p1, p2, .. pn)).  

I do not see any reason why I should be denied that calculation no matter what max_allowed_packet setting is. I only return 32 bytes to the client!

Thank you for the bug report. Verified with 5.5.20 on Mac OS X:

macbook-pro:5.5 openxs$ 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 9
Server version: 5.5.20-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show session variables like 'max_allowed%';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

mysql> select concat('a', repeat('b', 1048576)) as a;
+---+
| a |
+---+
| NULL |
+---+
1 row in set, 1 warning (0.07 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1301
Message: Result of concat() was larger than max_allowed_packet (1048576) - truncated
1 row in set (0.00 sec)

So, manual for all versions should include explanations of this case (at least).

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html says, following the inital table:

"
String-valued functions return NULL if the length of the result would be greater than the value of the max_allowed_packet system variable.
"