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:
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

[9 May 2018 9:24] Den ____
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:

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');

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

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

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
[9 May 2018 10:16] MySQL Verification Team
Thank you for the bug report.
[21 May 2018 16:26] Paul DuBois
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.
[5 Dec 2018 4:59] MySQL Verification Team
Bug #93479  marked as duplicate of this one