Bug #3132 Stored function returns empty when used in union
Submitted: 10 Mar 2004 13:54 Modified: 10 Jun 2004 17:19
Reporter: Ed Patterson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.0 OS:Any (All)
Assigned to: Bugs System CPU Architecture:Any

[10 Mar 2004 13:54] Ed Patterson
Description:
When using a stored function in a union the function returns nothing.

How to repeat:
Create the Hello function as described in the CREATE FUNCTION section of the documentation :

mysql> delimiter //
mysql> CREATE FUNCTION Hello (s CHAR(20)) RETURNS CHAR(50)
    -> RETURN CONCAT('Hello, ',s,'!');
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

Now execute the following query :

mysql> SELECT Hello('Bob') UNION ALL SELECT Hello('Judy');
+--------------+
| Hello('Bob') |
+--------------+
|              |
|              |
+--------------+
2 rows in set (0.00 sec)

I expected the output to be :

+--------------+
| Hello('Bob') |
+--------------+
| Hello, Bob!  |
| Hello, Judy! |
+--------------+
2 rows in set (0.00 sec)
[10 Mar 2004 14:07] Dean Ellis
Verified against 5.0.1-alpha-log.

If you CAST() or RPAD() the initial call the results are as expected.
[10 Mar 2004 14:28] Ed Patterson
I could not get CAST() to work, but RPAD() did return the expected data.  We are working with a function which can return up to 256 characters, so I had to add RTRIM with RPAD(field, 256, ' ') to prevent possible truncation and eliminate the excessive trailing spaces. Is this just a work around or is this the way it is supposed to work.
[10 Mar 2004 14:38] Dean Ellis
My CAST/RPAD comments were purely a workaround, and as additional information for correcting the bug.

The CAST() version:
SELECT CAST(Hello('Bob') AS CHAR(50)) UNION ALL SELECT Hello('Judy');
[10 Mar 2004 14:48] Ed Patterson
Thanks, CAST() works as a great workaround.
[10 Jun 2004 17:19] Per-Erik Martin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html