Bug #60726 | Functions doing implicit int to string conversion called repeatedly slow down | ||
---|---|---|---|
Submitted: | 1 Apr 2011 21:01 | Modified: | 3 Apr 2011 11:54 |
Reporter: | Karel Belohlavek | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: Charsets | Severity: | S2 (Serious) |
Version: | 5.5.10 | OS: | Linux (Debian Lenny, Arch Linux, adm64 and x86) |
Assigned to: | CPU Architecture: | Any | |
Tags: | character sets, number to string conversion, Stored Functions |
[1 Apr 2011 21:01]
Karel Belohlavek
[1 Apr 2011 21:05]
Karel Belohlavek
Sorry, I trimmed stored function in "how to repeat" section: CREATE FUNCTION format_phone_num(FPN_NUMBER BIGINT(12)) RETURNS CHAR(20) CHARSET cp1250 DETERMINISTIC BEGIN IF( FPN_NUMBER <= 0 ) THEN RETURN NULL; END IF; RETURN CONCAT( '+', NULLIF( CONCAT_WS( ' ', SUBSTR( FPN_NUMBER, 1, 3 ), SUBSTR( FPN_NUMBER, 4, 3 ), SUBSTR( FPN_NUMBER, 7, 3 ), SUBSTR( FPN_NUMBER, 10, 3 ), SUBSTR( FP N_NUMBER, 13 ) ), '' ) ); END
[1 Apr 2011 21:43]
MySQL Verification Team
Even with different test case check if related to: http://bugs.mysql.com/bug.php?id=60687? Thanks.
[1 Apr 2011 22:14]
Karel Belohlavek
Both bugs behave differently: Bug from http://bugs.mysql.com/bug.php?id=60687 does occur if I define procedure specified in testcase while having character_set_connection=latin1. When I do "SET NAMES utf8;" and recreate procedure, times are constant (my database and server character sets are utf8). Bug reported here does occur if character_set_connection is other than "latin1" when defining stored procedure or function, no matter, what database or server character set is. Therefore I would suggest these two could be considered different bugs, though of course cause also might be same (hard to tell).
[2 Apr 2011 7:58]
Valeriy Kravchuk
This can be related to/have the same reason as bug #60025.
[3 Apr 2011 11:54]
Valeriy Kravchuk
Your function uses substr() a lot, and I've checked test case for bug #60025 - there execution of set names latin1; before defining function also allows to workaround the problem and get the same run time for ever call. So, I consider this a duplicate of bug #60025.