Bug #49271 REPLACE(...) inside a stored PROCEDURE
Submitted: 1 Dec 2009 16:36 Modified: 17 Jan 2013 16:19
Reporter: Jannes Faber Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.41, 5.1.44, 5.5.99 OS:Any
Assigned to:
Tags: PROCEDURE, regression, REPLACE
Triage: Triaged: D2 (Serious)

[1 Dec 2009 16:36] Jannes Faber
Description:
The combination of GROUP_CONCAT(REPLACE(txt, ...)) messes up, but ONLY when inside a stored PROCEDURE with txt as its argument.

How to repeat:
# Test string where we'll replace 00000000 with a table name.
SET @bla='(( 00000000 ++ 00000000 ))';

# The query below returns the expected output:
SELECT GROUP_CONCAT(REPLACE(@bla, '00000000', TABLE_NAME))
 FROM information_schema.`TABLES`
 WHERE TABLE_SCHEMA='mysql'
;
# (( columns_priv ++ columns_priv )),(( db ++ db )),(( event ++ event )), ...

# Now create a simple stored procedure that does exactly the same as the above 
# query, except having the string as an argument.
DELIMITER $$

DROP PROCEDURE IF EXISTS `spBla` $$
CREATE DEFINER=`root`@`%` PROCEDURE `spBla`(
sBla VARCHAR(23000)
)
BEGIN
SELECT GROUP_CONCAT(REPLACE(sBla, '00000000', TABLE_NAME))
 FROM information_schema.`TABLES`
 WHERE TABLE_SCHEMA='mysql'
;
END $$

DELIMITER ;

# And call it and notice how it returns the wrong output.
CALL spBla(@bla);
# (( columns_priv ++ columns_priv )),(( columns_priv ++ columns_priv )),(( columns_priv ++ columns_priv )),(( columns_priv ++ columns_priv )),...
# (same table name every time)

The reason I'm doing the above is to create a stored procedure that can dynamically build a complex UNION ALL query, which I can then PREPARE and EXECUTE.
[1 Dec 2009 16:44] Jannes 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] Valerii 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] Jannes Faber
Just tried on Windows version of 5.1.41

Same bug.
[1 Dec 2009 18:14] Jannes 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] Valerii 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] Jannes 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] Miguel Solorzano
Enable the General Query Log to verify how MySQL Browser is querying the server?.
[2 Dec 2009 1:00] Jannes 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] Valerii 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] Jannes 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] Jannes 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] Jannes 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] Valerii Kravchuk
Please, send your my.cnf/my.ini file content.
[10 Dec 2009 8:35] Jannes 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] Jannes 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] Jannes 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] Jannes 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.