MySQL Bugs: #29202: Result of UDF in subquery is truncated

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:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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

Hi,

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,
Arnold

How to repeat:
see http://lists.mysql.com/internals/34725

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.

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".

Contains the code for the xql_agg function

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

Might be related to bug #30503

Thank you for the feedback.

Verified as described.

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!

UDF for Windows and Fedora

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

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?

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.