Bug #48766 SHOW CREATE FUNCTION returns extra data in return clause
Submitted: 13 Nov 2009 18:34 Modified: 15 Mar 2010 15:36
Reporter: Chuck Bell Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.0+ OS:Any
Assigned to: Alexander Barkov CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[13 Nov 2009 18:34] Chuck Bell
Description:
If you create a function with a return clause that contains enumerated values as strings, the SHOW CREATE FUNCTION returns extra data in the output (see below).

Note: The SHOW CREATE FUNCTION is used by MySQL Backup to backup functions and procedures. A good test for the solution is to run a backup then restore and check the DTD_IDENTIFIER column of the INFORMATION_SCHEMA.ROUTINES table.

Note: A sample test file will be attached to this bug report.

How to repeat:
mysql> CREATE DATABASE db1 CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql> USE db1;
Database changed
mysql> CREATE FUNCTION f4 ()
    -> RETURNS ENUM( 'w' ) CHARACTER SET ucs2
    -> RETURN 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE FUNCTION f4 \G
*************************** 1. row ***************************
            Function: f4
            sql_mode: 
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `f4`() RETURNS
enum('\0w') CHARSET ucs2
RETURN 0
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

mysql> 

Notice the extra \0.
[13 Nov 2009 18:36] Chuck Bell
Sample test with backup

Attachment: bug48766.test (application/test, text), 1.97 KiB.

[13 Nov 2009 18:40] Valeriy Kravchuk
Verified just as described with 5.0.88 and newer versions on Mac OS X:

mysql> CREATE FUNCTION f4 ()
    -> RETURNS ENUM( 'w' ) CHARACTER SET ucs2
    -> RETURN 0;
Query OK, 0 rows affected (0.02 sec)

mysql> show create function f4\G
*************************** 1. row ***************************
       Function: f4
       sql_mode: 
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `f4`() RETURNS enum('\0w') CHARSET ucs2
RETURN 0
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.88-debug | 
+--------------+
1 row in set (0.00 sec)
[13 Nov 2009 19:06] Peter Laursen
Also SET type is affected:

CREATE FUNCTION f6()
RETURNS SET( 'w' ) CHARACTER SET ucs2
RETURN 0;

CREATE DEFINER=`root`@`localhost` FUNCTION `f6`() RETURNS set('\0w')
-- CREATE DEFINER=`root`@`localhost` FUNCTION `f6`() RETURNS set('\0w') ... etc

But only UCS2 charset is affected. UTF8 and LATIN1 are not. I think category should be changed to 'charsets'.  Looks like it is just one more UCS2 issue with MySQL ..
[1 Dec 2009 8:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92223

3232 Alexander Barkov	2009-12-01
      Bug#48766 SHOW CREATE FUNCTION returns extra data in return clause
      
      Problem: SHOW CREATE FUNCTION and SELECT DTD_IDENTIFIER FROM I_S.ROUTINES
      returned wrong values in case of ENUM return data type and UCS2 character set.
      
      Fix: the string to collect returned data type was incorrectly set to
      "binary" character set, therefore UCS2 values where returned with 
      extra '\0' characters.
      Setting string character set to utf8 fixes the problem.
[1 Dec 2009 8:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92224

3232 Alexander Barkov	2009-12-01
      Bug#48766 SHOW CREATE FUNCTION returns extra data in return clause
      
      Problem: SHOW CREATE FUNCTION and SELECT DTD_IDENTIFIER FROM I_S.ROUTINES
      returned wrong values in case of ENUM return data type and UCS2 character set.
      
      Fix: the string to collect returned data type was incorrectly set to
      "binary" character set, therefore UCS2 values where returned with 
      extra '\0' characters.
      Setting string character set to utf8 fixes the problem.
[1 Dec 2009 12:58] Georgi Kodinov
creation_ctx->get_client_cs() needs to be used for the RETURNS buffer in sp_find_routine() instead of system_charset_info (to match what's expected from it in db_load_routine().
[2 Dec 2009 5:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92375
[2 Dec 2009 11:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92430
[2 Dec 2009 11:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92432
[2 Dec 2009 11:38] Alexander Barkov
Pushed into mysql-5.1-bugteam (5.1.42)
Pushed into mysql-pe (6.0.14)
[19 Dec 2009 8:28] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216180721-eoa754i79j4ssd3m) (merge vers: 6.0.14-alpha) (pib:15)
[19 Dec 2009 8:36] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216180221-a5ps59gajad3pip9) (pib:15)
[8 Jan 2010 17:32] Paul Dubois
Noted in 6.0.14 changelog.

If a stored function contained a RETURN statement with an ENUM value
in the ucs2 character set, SHOW CREATE FUNCTION and SELECT
DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES returned incorrect
values. 

Setting report to NDI pending push to 5.1.x, Celosia.
[12 Mar 2010 14:12] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:28] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:43] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[12 Mar 2010 16:50] Paul Dubois
Fixed in earlier 5.5.x.
Setting report to Need Merge pending push to 5.1.x.
[15 Mar 2010 15:36] Paul Dubois
Noted in 5.1.46, 5.5.3 changelogs.