| Bug #49271 | REPLACE(...) inside a stored PROCEDURE | ||
|---|---|---|---|
| Submitted: | 1 Dec 2009 16:36 | Modified: | 17 Jan 2013 16:19 |
| Reporter: | J Faber | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.1.41, 5.1.44, 5.5.99 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | PROCEDURE, regression, REPLACE | ||
[1 Dec 2009 16:36]
J Faber
[1 Dec 2009 16:44]
J Faber
Workaround I'm using now is change REPLACE into fnReplace:
DELIMITER $$
DROP FUNCTION IF EXISTS `fnReplace` $$
CREATE DEFINER=`root`@`%` FUNCTION `fnReplace`(
sTxt VARCHAR(65535)
, sFind VARCHAR(65535)
, sReplace VARCHAR(65535)
) RETURNS varchar(65535) CHARSET latin1
DETERMINISTIC
BEGIN
RETURN REPLACE(sTxt, sFind, sReplace);
END $$
DELIMITER ;
[1 Dec 2009 16:55]
Valeriy Kravchuk
Thank you for the problem report. Please, check with a newer version, 5.1.41, and inform about the results.
[1 Dec 2009 18:08]
J Faber
Just tried on Windows version of 5.1.41 Same bug.
[1 Dec 2009 18:14]
J Faber
Actually, I just tried the same thing but without GROUP_CONCAT and it has the same bug. I hope that narrows it down a little further. So a somewhat simpler "How to reproduce" : SET @bla='(( 00000000 ++ 00000000 ))'; # Correct result: SELECT REPLACE(@bla, '00000000', TABLE_NAME) FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='mysql' ; '(( columns_priv ++ columns_priv ))' '(( db ++ db ))' '(( event ++ event ))' ... # Bad result: DELIMITER $$ DROP PROCEDURE IF EXISTS `spBla` $$ CREATE DEFINER=`root`@`%` PROCEDURE `spBla`( sBla VARCHAR(23000) ) BEGIN SELECT REPLACE(sBla, '00000000', TABLE_NAME) FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='mysql' ; END $$ DELIMITER ; CALL spBla(@bla); '(( columns_priv ++ columns_priv ))' '(( columns_priv ++ columns_priv ))' '(( columns_priv ++ columns_priv ))' ... -- So REPLACE seems to affect the value of the first string argument instead of just returning the new string.
[1 Dec 2009 18:33]
Valeriy Kravchuk
My request to check with 5.1.41 was related to the fact that with current development version, 5.1.42, I've got:
77-52-7-73:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.42-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET @bla='(( 00000000 ++ 00000000 ))';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> # Correct result:
mysql> SELECT REPLACE(@bla, '00000000', TABLE_NAME)
-> FROM information_schema.`TABLES`
-> WHERE TABLE_SCHEMA='mysql'
-> ;
+---------------------------------------+
| REPLACE(@bla, '00000000', TABLE_NAME) |
+---------------------------------------+
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
| (( columns_priv ++ columns_priv )) |
+---------------------------------------+
23 rows in set (0.00 sec)
mysql> explain SELECT REPLACE(@bla, '00000000', TABLE_NAME) FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='mysql';
+----+-------------+--------+------+---------------+--------------+---------+------+------+--------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+--------------+---------+------+------+--------------------------------------------------+
| 1 | SIMPLE | TABLES | ALL | NULL | TABLE_SCHEMA | NULL | NULL | NULL | Using where; Skip_open_table; Scanned 1 database |
+----+-------------+--------+------+---------------+--------------+---------+------+------+--------------------------------------------------+
1 row in set (0.00 sec)
that is, I've got same results (not expected by you) in procedure and outside it...
[1 Dec 2009 23:29]
J Faber
You are right. In my examples above I was using MySQL Browser. When I do this on the mysql command line I get the same as you did. Server version 5.1.41 again. Would you agree this is unexpected behaviour?
[2 Dec 2009 0:30]
MySQL Verification Team
Enable the General Query Log to verify how MySQL Browser is querying the server?.
[2 Dec 2009 1:00]
J Faber
Looks good to me... 091202 1:56:31 3 Connect root@localhost on 3 Query select @@version_comment limit 1 091202 1:56:43 3 Query SET @bla='(( 00000000 ++ 00000000 ))' 3 Query SELECT REPLACE(@bla, '00000000', TABLE_NAME) FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='mysql'
[2 Dec 2009 7:37]
Valeriy Kravchuk
Now, with 5.1.41 on Windows, I see no problem: mysql> SET @bla='(( 00000000 ++ 00000000 ))'; Query OK, 0 rows affected (0.11 sec) mysql> select replace (@bla, '00000000', table_name) as a from information_schem a.tables where table_schema='mysql'; +--------------------------------------------------------------+ | a | +--------------------------------------------------------------+ | (( application_account ++ application_account )) | | (( columns_priv ++ columns_priv )) | | (( db ++ db )) | | (( event ++ event )) | | (( func ++ func )) | | (( general_log ++ general_log )) | | (( help_category ++ help_category )) | | (( help_keyword ++ help_keyword )) | | (( help_relation ++ help_relation )) | | (( help_topic ++ help_topic )) | | (( host ++ host )) | | (( ndb_binlog_index ++ ndb_binlog_index )) | | (( plugin ++ plugin )) | | (( proc ++ proc )) | | (( procs_priv ++ procs_priv )) | | (( servers ++ servers )) | | (( slow_log ++ slow_log )) | | (( tables_priv ++ tables_priv )) | | (( tbit ++ tbit )) | | (( tfk ++ tfk )) | | (( time_zone ++ time_zone )) | | (( time_zone_leap_second ++ time_zone_leap_second )) | | (( time_zone_name ++ time_zone_name )) | | (( time_zone_transition ++ time_zone_transition )) | | (( time_zone_transition_type ++ time_zone_transition_type )) | | (( tpk1 ++ tpk1 )) | | (( user ++ user )) | | (( user_info ++ user_info )) | +--------------------------------------------------------------+ 28 rows in set (0.22 sec) mysql> explain select replace (@bla, '00000000', table_name) as a from informati on_schema.tables where table_schema='mysql'; +----+-------------+--------+------+---------------+--------------+---------+--- ---+------+--------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | re f | rows | Extra | +----+-------------+--------+------+---------------+--------------+---------+--- ---+------+--------------------------------------------------+ | 1 | SIMPLE | tables | ALL | NULL | TABLE_SCHEMA | NULL | NU LL | NULL | Using where; Skip_open_table; Scanned 1 database | +----+-------------+--------+------+---------------+--------------+---------+--- ---+------+--------------------------------------------------+ 1 row in set (0.05 sec) mysql> select @bla; +----------------------------+ | @bla | +----------------------------+ | (( 00000000 ++ 00000000 )) | +----------------------------+ 1 row in set (0.00 sec) mysql> select version(); +------------------+ | version() | +------------------+ | 5.1.41-community | +------------------+ 1 row in set (0.05 sec) Weird.
[2 Dec 2009 9:03]
J Faber
That's weird. I'm still having the same problem with 5.1.41, I tried your version of the query (with "as a") and some other variations, like the one "explain extended" is saying. Another variation I tried was this: mysql> select replace(concat(@bla,'x'),'00000000',`tables`.`TABLE_NAME`) AS `a` from `information_schema`.`tables` where (`tables`.`TABLE_SCHEMA` = 'mysql'); I figured concat(@bla,'x') would result in a temporary string which replace(..) would be able to overwrite without causing problems. But even this still gives the wrong result. Seems like mysql is optimizing away too many function calls or something. But since you don't see this problem, can this be dependent on some setting? Was you mysql a completely clean install? Mine wasn't, I'll try to do that.
[2 Dec 2009 9:23]
J Faber
This is also an interesting one: mysql> select concat(replace(@bla, '00000000', table_name), 'x') as a from infor mation_schema.tables where table_schema='mysql'; +-----------------------------------------------------------+ | a | +-----------------------------------------------------------+ | (( columns_priv ++ columns_priv ))x | | (( columns_priv ++ columns_priv ))xx | | (( columns_priv ++ columns_priv ))xxx | | (( columns_priv ++ columns_priv ))xxxx | | (( columns_priv ++ columns_priv ))xxxxx | | (( columns_priv ++ columns_priv ))xxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxxxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxxxxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxxxxxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxxxxxxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxxxxxxxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxxxxxxxxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxxxxxxxxxxxxxxx | | (( columns_priv ++ columns_priv ))xxxxxxxxxxxxxxxxxxxxxxx | +-----------------------------------------------------------+ 23 rows in set (0.00 sec) But it seems to be triggered by the replace(), this one looks as expected: mysql> select concat(right(@bla, 10), 'x') as a from information_schema.tables w here table_schema='mysql'; +-------------+ | a | +-------------+ | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | | 0000000 ))x | +-------------+ 23 rows in set (0.00 sec)
[2 Dec 2009 9:32]
J Faber
In response to Miguel. I misread your question. This is the general_log when I run the query from MySQL Query Browser: 091202 10:25:22 2 Init DB mysql 2 Query SELECT @@SQL_MODE 2 Query SET @bla='(( 00000000 ++ 00000000 ))' 091202 10:25:38 2 Init DB mysql 2 Query SELECT @@SQL_MODE 2 Query SHOW FULL COLUMNS FROM `information_schema`.`TABLES` 2 Query SHOW CREATE TABLE `information_schema`.`TABLES` 2 Query SELECT REPLACE(@bla, '00000000', TABLE_NAME) as a FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='mysql' Nothing special I think, and running those from the command line still gives the same problem.
[10 Dec 2009 7:45]
Valeriy Kravchuk
Please, send your my.cnf/my.ini file content.
[10 Dec 2009 8:35]
J Faber
mysql ini (I think it was default from setup)
Attachment: my.ini (application/octet-stream, text), 8.86 KiB.
[10 Dec 2009 8:39]
J Faber
show variables output
Attachment: show variables.csv (application/vnd.ms-excel, text), 8.18 KiB.
[11 Jan 2010 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[11 Jan 2010 3:03]
J Faber
Unless I'm mistaken I have provided "the information that was originally requested" and as far as I understand this bug was reproduced independently by: [1 Dec 2009 19:33] Valeriy Kravchuk
[29 Jan 2010 10:46]
Sveta Smirnova
Thank you for the feedback. Verified as described. Test case for the MTR: SET @bla='(( 00000000 ++ 00000000 ))'; select replace(@bla, '00000000', table_name) as a from information_schema.tables where table_schema='mysql'; SET @bla='(( 00000000 ++ 00000000 ))'; select concat(@bla, table_name) as a from information_schema.tables where table_schema='mysql'; delimiter |; create procedure sp1(sBla VARCHAR(23000)) begin select replace(sBla, '00000000', table_name) as a from information_schema.tables where table_schema='mysql'; end | create procedure sp2(sBla VARCHAR(23000)) begin select replace(sBla, '00000000', table_name) as a from information_schema.tables where table_schema='mysql'; end | delimiter ;| SET @bla='(( 00000000 ++ 00000000 ))'; call sp1(@bla); SET @bla='(( 00000000 ++ 00000000 ))'; call sp2(@bla);
[29 Jan 2010 11:12]
J Faber
Thank you for looking into this. Your example code does show this bug, but I don't see any difference between your sp1 and sp2. Maybe you intended to put the concat thing in sp2? Note that concat only fails in combination with replace (when inside a procedure). I would guess both point to the same bug, but concat brings it out in an even more peculiar way, so it could be useful for anyone debugging this. The test queries would then be: SET @bla='(( 00000000 ++ 00000000 ))'; select replace(@bla, '00000000', table_name) as a from information_schema.tables where table_schema='mysql'; SET @bla='(( 00000000 ++ 00000000 ))'; select concat(replace(@bla, '00000000', table_name), 'x') as a from information_schema.tables where table_schema='mysql'; delimiter | create procedure sp1(sBla VARCHAR(23000)) begin select replace(sBla, '00000000', table_name) as a from information_schema.tables where table_schema='mysql'; end | create procedure sp2(sBla VARCHAR(23000)) begin select concat(replace(sBla, '00000000', table_name), 'x') as a from information_schema.tables where table_schema='mysql'; end | delimiter ; SET @bla='(( 00000000 ++ 00000000 ))'; call sp1(@bla); SET @bla='(( 00000000 ++ 00000000 ))'; call sp2(@bla);
[29 Jan 2010 12:39]
Sveta Smirnova
Jannes, thank you for noticing this. Yes, I wanted to place concat in sp2. Just to test how it works without replace.
[17 Jan 2013 16:19]
Paul DuBois
Noted in 5.1.67, 5.5.31, 5.6.11, 5.7.1 changelogs. The REPLACE() function produced incorrect results when a user variable was supplied as an argument and the operation was performed on multiple rows.
