MySQL Bugs: #90803: regexp_replace accumulating result

Bug #90803	regexp_replace accumulating result
Submitted:	9 May 2018 9:24	Modified:	21 May 2018 16:26
Reporter:	Den ____	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.11 Community Server	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regexp regexp_replace sql

Description:
Please consider an example:
SELECT regexp_replace(name, "^([[:alnum:]]+)[[:space:]].*$","$1") FROM `table`;

It should return 1st word, and it does, but only for the first row.

Somehow result concatenated to the next row.
If we have table with single column `name` and values: ('1 1 1 1', '2 2 2 2', '3 3 3 3') the query result will be : ('1', '12', '123')
Which is not expected, or if it is, - not represented in the docs.

More to add if we use for replacement anything it will be multiplied for the matched row. 

So if we use previous example and chance replacement to 'bug', result will be: ("bug","bugbug","bugbugbug")

As a version:
https://stackoverflow.com/questions/50247765/mysql-regex-replace-how-to-use-regex-group-in...

How to repeat:
CREATE TABLE `table` (`name` VARCHAR(45) NOT NULL);
insert into `table` (name) values ('1 1 1'),('2 2 2'),('3 3 3');

TEST1:
SELECT regexp_replace(name, "^([[:alnum:]]+)[[:space:]].*$","$1") FROM `table`;

Expected: "1", "2", "3"
Returns:  "1", "12", "123"

TEST1:
SELECT regexp_replace(name, "^([[:alnum:]]+)[[:space:]].*$","c") FROM `table`;

Expected: "c", "c", "c"
Returns:  "c", "cc", "ccc"

Suggested fix:
1) Update the docs to explain current behavior
2) provide a fix to have expected result

Thank you for the bug report.

Posted by developer:
 
Fixed in 8.0.12.

REGEXP_RELACE() results from one result set row could carry forward
to the next row, resulting in accumulation of previous results in the
current row.

Bug #93479  marked as duplicate of this one