MySQL Bugs: #1104: UDF returning string containing null character is truncated

Bug #1104	UDF returning string containing null character is truncated
Submitted:	20 Aug 2003 4:18	Modified:	22 Aug 2003 14:18
Reporter:	Ken Allan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S3 (Non-critical)
Version:	4.1.0	OS:	Linux (Linux (mandrake+redhat))
Assigned to:		CPU Architecture:	Any

Description:
A UDF that returns a string which contains one or more null characters in the middle of the result char* (ie: after byte 0 and before byte res_length) will have the string truncated at the occurance of the first null character.

How to repeat:
Create a UDF that contains a function returning a text string of a specified length. Insert a null character in the middle of the string, leaving the res_length set to the complete length of the string. The string returned in the sql call to the function will be terminated at the null byte and not return the remaining portion of the string.

Suggested fix:
Honor the res_length variable, or allow a UDF_ARGS setting that allows inclusion of binary data in return strings.

This is a test UDF that demonstrates the problem. See comment for details of how to use.

Attachment: udf_testnull.cc (application/octet-stream, text), 1004 bytes.

Attached to this bug is a test UDF function that when compiled and loaded into mysql, demonstrates the problem described. The function is designed to take a string as input and place a null character halfway into the output string.

It returns the following when executed in MySQL:
mysql> select testnull("Hi there");
+----------------------+
| testnull("Hi there") |
+----------------------+
| Hi t                 |
+----------------------+
1 row in set (0.00 sec)

It seems that this bug is purely in the output of the mysql client, not in the UDF functions, as can be demonstrated below:

(sorry about this erronous bug report, please close it... :)

mysql> create table testBin (text varchar(255) binary not null primary key);
Query OK, 0 rows affected (0.10 sec)
 
mysql> insert into testBin values ("Hello \0 A"), ("Hello \0 B");
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from testBin;
+-----------+
| text      |
+-----------+
| Hello     |
| Hello     |
+-----------+
2 rows in set (0.06 sec)
 
mysql> select text, count(*) from testBin group by 1;
+-----------+----------+
| text      | count(*) |
+-----------+----------+
| Hello     |        1 |
| Hello     |        1 |
+-----------+----------+
2 rows in set (0.00 sec)
 
mysql> insert into testBin values (testnull("Hello   AAAA")), (testnull("Hello   BBBB"));
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select text, count(*) from testBin group by 1; +--------------+----------+
| text         | count(*) |
+--------------+----------+
| Hello        |        1 |
| Hello        |        1 |
| Hello        |        1 |
| Hello        |        1 |
+--------------+----------+
4 rows in set (0.00 sec)
 
mysql> select hex(text) from testBin;
+--------------------------+
| hex(text)                |
+--------------------------+
| 48656C6C6F20002041       |
| 48656C6C6F20002041414141 |
| 48656C6C6F20002042       |
| 48656C6C6F20002042424242 |
+--------------------------+
4 rows in set (0.04 sec)

Yes, the MySQL client is not really intended for displaying binary data,
so I'm going to close this bug report.