Result of UDF in subquery is truncated

[Arnold Daniels]

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


mysql> select `xql:states` FROM (select xql_agg(xql_element('state', 
NULL, code, description)) as `xql:states` FROM state GROUP BY 
country_code) AS state;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 
xql:states                                                                                
                                                                                          
                                                                          
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| <state code="ab" description="Alberta"/><state code="bc" 
description="British Columbia"/><state code="mb" 
description="Manitoba"/><state code="nb" description="New 
Brunswick"/><state code="nf" description="New Foundland"/><state 
code="nt" description="Nor |
| <state code="ri" description="Rhode Island"/><state code="pr" 
description="Puerto Rico"/><state code="pa" 
description="Pennsylvania"/><state code="pw" description="Palau"/><state 
code="or" description="Oregon"/><state code="ok" 
description="Oklahoma"/><st |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select xql_agg(xql_element('state', NULL, code, description)) as 
`xql:states` FROM state GROUP BY country_code; 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 
xql:states                                                                                
                                                                                          
                                                                                          
                                                                                          
                                                                                          
                                                                                          
                                                                                          
                                                                                          
                                                                                          
                                                                                          
                                                                                          
                        
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| <state code="ab" description="Alberta"/><state code="bc" 
description="British Columbia"/><state code="mb" 
description="Manitoba"/><state code="nb" description="New 
Brunswick"/><state code="nf" description="New Foundland"/><state 
code="nt" description="Northwest Territories"/><state code="ns" 
description="Nova Scotia"/><state code="sk" 
description="Saskatchewan"/><state code="pq" 
description="Quebec"/><state code="pe" description="Prince Edward 
Island"/><state code="on" description="Ontario"/><state code="yt" 
description="Yukon 
Territories"/>                                                                         
                                                                                          
                                                                                          
                                                                                          
                                                                                          
                                        
|
| <state code="ri" description="Rhode Island"/><state code="pr" 
description="Puerto Rico"/><state code="pa" 
description="Pennsylvania"/><state code="pw" description="Palau"/><state 
code="or" description="Oregon"/><state code="ok" 
description="Oklahoma"/><state code="oh" description="Ohio"/><state 
code="mp" description="Northern Mariana Islands"/><state code="nd" 
description="North Dakota"/><state code="va" 
description="Virginia"/><state code="nc" description="North 
Carolina"/><state code="sc" description="South Carolina"/><state 
code="sd" description="South Dakota"/><state code="de" 
description="Delaware"/><state code="wy" description="Wyoming"/><state 
code="wi" description="Wisconsin"/><state code="wv" description="West 
Virginia"/><state code="wa" description="Washington"/><state code="vi" 
description="Virgin Island"/><state code="vt" 
description="Vermont"/><state code="ut" description="Utah"/><state 
code="tx" description="Texas"/><state code="tn" 
description="Tennessee"/><state code="ny" description="New York"/><state 
code="nm" description="New Mexico"/><state code="il" 
description="Illinois"/><state code="id" description="Idaho"/><state 
code="hi" description="Hawaii"/><state code="gu" 
description="Guam"/><state code="ga" description="Georgia"/><state 
code="fl" description="Florida"/><state code="fm" description="Federated 
States of Micronesia"/><state code="dc" description="District of 
Columbia"/><state code="ct" description="Connecticut"/><state code="co" 
description="Colorado"/><state code="ca" 
description="California"/><state code="ar" 
description="Arkansas"/><state code="az" description="Arizona"/><state 
code="as" description="American Samoa"/><state code="ak" 
description="Alaska"/><state code="in" description="Indiana"/><state 
code="ia" description="Iowa"/><state code="ks" 
description="Kansas"/><state code="nj" description="New Jersey"/><state 
code="nh" description="New Hampshire"/><state code="nv" 
description="Nevada"/><state code="ne" description="Nebraska"/><state 
code="mt" description="Montana"/><state code="mo" 
description="Missouri"/><state code="ms" 
description="Mississippi"/><state code="mn" 
description="Minnesota"/><state code="mi" description="Michigan"/><state 
code="ma" description="Massachusetts"/><state code="md" 
description="Maryland"/><state code="mh" description="Marshall 
Islands"/><state code="me" description="Maine"/><state code="la" 
description="Louisiana"/><state code="ky" description="Kentucky"/><state 
code="al" description="Alabama"/> |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select CONCAT(xql_agg(xql_element('state', NULL, code, 
description)), "A") as `xql:states` FROM state GROUP BY country_code;
+------------+
| xql:states |
+------------+
| NULL       |
| NULL       |
+------------+
2 rows in set (0.00 sec)

mysql> select CHAR_LENGTH(xql_agg(xql_element('state', NULL, code, 
description))) as `xql:states` FROM state GROUP BY country_code;
+------------+
| xql:states |
+------------+
|        547 |
|       2506 |
+------------+
2 rows in set (0.00 sec)

mysql> select CHAR_LENGTH(`xql:states`) FROM (select 
CONCAT(xql_agg(xql_element('state', NULL, code, description))) as 
`xql:states` FROM state GROUP BY country_code) AS state;
+---------------------------+
| CHAR_LENGTH(`xql:states`) |
+---------------------------+
|                       255 |
|                       255 |
+---------------------------+
2 rows in set (0.00 sec)