3

This looks like strange behaviour, and I get the same on the MySQL command line as via db<>fiddle:

create table t(foo varchar(255));
insert into t (foo) values
('the quick brown fox'),
('jumped over the lazy dog');
select * from t;
| foo                      |
| :----------------------- |
| the quick brown fox      |
| jumped over the lazy dog |
select regexp_replace(foo, '[^a-z]+', '-') from t;
| regexp_replace(foo, '[^a-z]+', '-')         |
| :------------------------------------------ |
| the-quick-brown-fox                         |
| the-quick-brown-foxjumped-over-the-lazy-dog |

db<>fiddle here

I'd expect to see:

select REGEXP_REPLACE(foo, '[^a-z]+', '-') from t;
| REGEXP_REPLACE(foo, '[^a-z]+', '-') |
| :---------------------------------- |
| the-quick-brown-fox                 |
| jumped-over-the-lazy-dog            |

Am I doing something wrong or is this a MySQL bug?

1
  • select @bar := REGEXP_REPLACE(foo, '[^a-z]+', '-') from t; gives wrong output on db<>fiddle too...
    – Akina
    Jul 13, 2018 at 11:55

1 Answer 1

5

It's a known bug, 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.

2
  • Thanks — it looks like 8.0.12 isn't released yet: dev.mysql.com/downloads. When it is I'll update dbfiddle and confirm it's fixed! Jul 13, 2018 at 17:20
  • Or MariaDB 10.0 ?
    – Rick James
    Jul 13, 2018 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.