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:

[20 Aug 2003 4:18] Ken Allan
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.
[20 Aug 2003 17:07] Ken Allan
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.

[20 Aug 2003 17:14] Ken Allan
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)
[20 Aug 2003 19:53] Ken Allan
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)
[22 Aug 2003 14:18] Indrek Siitan
Yes, the MySQL client is not really intended for displaying binary data,
so I'm going to close this bug report.