| 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: | |
| Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
| Version: | 5.0+ | OS: | Any |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[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.
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.