Bug #29202 Result of UDF in subquery is truncated
Submitted: 19 Jun 2007 9:11 Modified: 6 Sep 2007 12:19
Reporter: Sergei Golubchik Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: User-defined functions ( UDF ) Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any

[19 Jun 2007 9:11] Sergei Golubchik
From: Arnold Daniels
Subject: Result of UDF in subquery is truncated
Date: Wed, 13 Jun 2007 01:31:34 +0200
List-Archive: http://lists.mysql.com/internals/34725


I have a problem with using the result of an UDF from a subquery. The
data is truncated on 255 chars. The data is probably seen as normal
string (varchar), while is should be seen as blob.

Also (might be unrelated), using the output of an UDF as input for
string of cast functions makes the input appear as NULL. Using a
function like char_size works fine though.

I didn't notice this problem in earlier versions of MySQL, but it might
simply have to do with my test setup.

Can anyone explain what is happening and how this can be solved. I
really need to have this solved to make the lib work. Please do no send
comments like "you don't need subqueries", because it might not be
important in this example but it is needed in order to create an XML tree.

Thanks for any reply,

How to repeat:
see http://lists.mysql.com/internals/34725
[19 Jun 2007 10:18] Sveta Smirnova
Thank you for the report.

Please provide C code for UDF functions you use in the subquery and indicate accurate version of MySQL server you use.
[19 Jul 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[13 Aug 2007 14:19] Roland Bouman
Contains the code for the xql_agg function

Attachment: lib_mysqludf_xql-0.9.4.tar.gz (application/x-gzip, text), 15.80 KiB.

[28 Aug 2007 17:49] Arnold Daniels
Might be related to bug #30503
[6 Sep 2007 12:19] Sveta Smirnova
Thank you for the feedback.

Verified as described.
[5 Dec 2007 21:26] Bri Gipson
We experienced the same issue and think we've nailed down the scenario that causes it - somewhat.

1. Involves table joins. If you only have one table in the query, the UDF appears to be fine in its output.

2. Involves ORDER BY. If you don't order your data the output appears to be fine.

3. Output is truncated to the length of the input on that UDF.

Here's a sample query set-up (code will be attached in a follow-up):
CREATE TABLE happy ( my_id BINARY(16) );

CREATE TABLE sad ( my_id BINARY(16) );

INSERT INTO happy( my_id ) VALUES ( 0x68617070792068617070792068617070 );

INSERT INTO sad( my_id ) VALUES ( 0x68617070792068617070792068617070 );

SELECT fn_bin_to_hex( happy.my_id ) FROM happy ORDER BY happy.my_id; -- Looks Good!

SELECT fn_bin_to_hex( happy.my_id ) FROM happy, sad WHERE happy.my_id = sad.my_id; -- Looks Good, Too!

SELECT fn_bin_to_hex( happy.my_id ) FROM happy, sad WHERE happy.my_id = sad.my_id ORDER BY happy.my_id; -- Error! Only 16 bytes (the number of bytes for the input) are output!
[5 Dec 2007 22:08] Bri Gipson
UDF for Windows and Fedora

Attachment: udf_bin_to_hex.zip (application/x-zip-compressed, text), 344.38 KiB.

[2 Jul 2014 8:35] Владимир Глушенков
I have similar bug on 5.6.16, 5.6.19 and 5.7.4m14. Here my question on SO: http://stackoverflow.com/questions/24493515/mysql-cuts-off-string-from-subquery-which-uses....
Whether there is any best decision instead of rewriting subqueries as joins?
[2 Jan 2019 14:14] Steffen Schulz
This is still a thing 11 Years later with 5.7.24.
A workaround would be to CAST fields that are contained in the subquery to BINARY.