CODEWITHSUNDEEP
mysqlsql
Sasha
Sasha

Reputation: 1072

Why does "HAVING" statement with "REPLACE" affects the values?

I have a table with "tag" and "category" columns. The tags may contain spaces, but I want to filter them by a query string that has dashes instead of the spaces (it comes from a URL slug).

I run the following query on mysql:

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

The result is:

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

Could someone explain why do the tags that are returned have dashes as well? I would expect (and hope) for the REPLACE to be used for the filtering by the HAVING statement, but the original value to be returned by the query. How would I achieve that?

Here's a fiddle for your reference: http://sqlfiddle.com/#!2/d7573/1

Thank you!

EDIT (by gordon):

For those interested, this problem occurs in this simpler version of the query:

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

As @Razvan points out, this is a reference issue. It doesn't happen with the group_concat() directly in the having.

Upvotes: 6

Views: 246

Answers (1)

Alexander
Alexander

Reputation: 3179

Although it is very intersting, I think you should just fill a bugreport to MySQL. They have a proprietary extensions which modifies the behavior of HAVING.
For example: The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

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

Upvotes: 1

Related Questions