MySQL Bugs: #112801: replace function not working with empty strings

Bug #112801	replace function not working with empty strings
Submitted:	23 Oct 2023 8:21	Modified:	24 Oct 2023 16:55
Reporter:	Konrad Gorczyca	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	replace() "empty string"

Description:
When i execute following statement
SELECT replace('','','Not working');
I have been getting empty string in case 'Not working'.
When I execute 
Select replace('aaa','aaa','')
I received empty string.
This is in-consequence behavior. 
The documentation has not warning about this issue.

How to repeat:
run 
Select replace('','','aaa');

Suggested fix:
Add empty string to replace function

add tags to better search

Hi Mr. Gorczyca,

Thank you for your bug report.

However, this is not a bug.

Simply, empty string in SQL means no string. Hence, REPLACE is working as expected.

Not a bug.

But this is opposite to natural replace.
Replace is always bi-directional function.

If I do this statement:
select replace(replace('aaa','aaa',''),'','aaa');
I need to return 'aaa' not ''

In SQL Anywhere, SQL Server this works like in any other language (c, c++, php).

Hi,

An empty string does not have anything in it that could be found. 

Next, SQL standard does not stipulate this function at all, so any implementation is free to define it's own behaviour.

Not a bug.

Definition of empty string is array of single character \0.
Still I think Is a bug, 

If is not a bug, You must add this warning to documentation of replace function:
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_replace

Hi,

That is a definition in C Programming Language but not in SQL.

OK, then add notice about replace function

"Warning
This function cannot replace empty string yo other string"

in your documentation:
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_replace