Why does the expression "HAVING" with "REPLACE" affect the values?

Question

I have a table with columns "tag" and "category". Tags can contain spaces, but I want to filter them using a query string that has a dash instead of spaces (it comes from the URL pool).

I run the following query in mysql:

SELECT GROUP_CONCAT(tag SEPARATOR ',' ) AS tags FROM tags GROUP BY category HAVING REPLACE( tags, ' ', '-' ) like "%a%" 

Result:

 first-tag,second-tag third-tag,fourth-tag fifth-tag 

Can someone explain why returned tags also have dashes? I would expect (and hope) that REPLACE will be used to filter by the HAVING statement, but the original value that will be returned by the request. How can i achieve this?

Here is the script for your reference: http://sqlfiddle.com/#!2/d7573/1

Thanks!

EDITOR (by gordon):

For those who are interested, this problem occurs in this simpler version of the request:

 SELECT GROUP_CONCAT(tag) AS tags FROM tags HAVING REPLACE(tags, ' ', '-') is not null; 

As Rustzhvan notes, this is a reference problem. This does not happen with group_concat() directly in having .

Answer 1

Although this is very interesting, I think you just need to fill out a bugreport for MySQL. They have their own extensions that change the behavior of HAVING .
For example: The SQL standard requires HAVING to refer only to columns in a GROUP BY clause or in columns used in aggregate functions. However, MySQL does support an extension of this behavior and allows HAVING to refer to columns in the SELECT list and to columns in external subqueries.

And since your case is not a real scenario (because you really don't need REPLACE ), I think it's worth mentioning that in the case of a simple "%%" LIKE patern, it does not change the results. Or, if you add OR 1 to the HAVING clause , it will also display the correct results.