Using replace on a string from select in MySQL with column data leads to same output for different rows

user_11895099 0
Viewed 45

Here is a simplified version of what I'm trying to do, which shows the problem best. My database looks like this:

users table:

user_id first_name
1 Bob
2 Dave
3 Steven

settings table:

name value
format_string Hello {first_name}!

Now I want to retrieve the format_string with inserted user data for every user. If I hardcode the my format_string into my SQL like this, it works:

SELECT first_name,
REPLACE(
    "Hello {first_name}!",
    "{first_name}",
    first_name
)
AS greeting
FROM users

I get this output, which is expected:

first_name greeting
Bob Hello Bob!
Dave Hello Dave!
Steven Hello Steven!

But if I use the format_string from my settings table, like this:

SELECT first_name,
REPLACE(
    (SELECT value FROM settings WHERE name = "format_string"),
    "{first_name}",
    first_name
)
AS greeting
FROM users

I get this output, which is absolutely not expected:

first_name greeting
Bob Hello Bob!
Dave Hello Bob!
Steven Hello Bob!

Does anyone know what the problem there is and how to fix it?

mariadb phpmyadmin mysql sql replace
1 Answers

I can't explain what the problem is. You might have found a bug in the database engine. I don't have access to newer MariaDB (dbfiddle's newer versions seem broken), but while both MariaDB 10.3 and MySQL 5.5 seem to give your output, MySQL 5.6 gives the expected one, so the bug was found and fixed.

Rewriting your query with a join instead of a subquery seems to work in any engine:

SELECT REPLACE(settings.value, "{first_name}", users.first_name) as greeting
FROM
  users,
  settings
WHERE settings.name = 'format_string';
user_2404430
Related