Why are HAVING and GROUP_CONCAT not working together in this query?

Question

This query:

 SELECT name,
 REPLACE(REPLACE(REPLACE(LOWER(name),' ',''),'-',''),':','') AS formattedName,
 GROUP_CONCAT(service)
 FROM movies
 GROUP BY formattedName
 HAVING COUNT(CASE WHEN name like "%von brom%" THEN 1 END) > 0;

Gives me the following result:

+---------------------------------------------+---------------------------------------+---------------------+
|name                                         |formattedName                          |GROUP_CONCAT(service)|
+---------------------------------------------+---------------------------------------+---------------------+
|LasseMajas detektivbyrå: Von Broms hemlighet |lassemajasdetektivbyråvonbromshemlighet|sfanytime            |
+---------------------------------------------+---------------------------------------+---------------------+
|LasseMajas detektivbyrå - Von Broms hemlighet|lassemajasdetektivbyråvonbromshemlighet|boxer                |
+---------------------------------------------+---------------------------------------+---------------------+
|LasseMajas detektivbyrå - Von Broms hemlighet|lassemajasdetektivbyråvonbromshemlighet|viaplay              |
+---------------------------------------------+---------------------------------------+---------------------+

All the values in the "formattedName" column look identical, so why aren't the rows grouped together?

If I use WHERE to filter the rows, instead of HAVING, it actually seems to work. It gives me one row as result, with sfanytime,boxer,viaplay in the GROUP_CONCAT(service) column. But I need to have the filter in HAVING, because else I won't get all the information I need in some cases.

It also works if I replace GROUP_CONCAT(service) with just service. It only fails if I both use GROUP_CONCAT and HAVING

All of this is from phpMyAdmin on a database on https://www.one.com. If I try to do the same thing on a identical local database on my own computer with the MySQL workbench, everything works as expected, and the rows are grouped together in all of the cases (HAVING/WHERE,with or without GROUP_CONCAT).

What causes this weird behavior? Could it maybe have something to do with the special character "å"?

My character_set_server is set to utf8 on the local database, and to latin1 on the one.com database.

Calling SELECT @@version; on the one.com database gives me:

5.5.45-MariaDB-1~wheezy

And on the local database it gives me:

5.7.9-log

Sounds like an incompatibility between MariaDB and real MySQL. — Barmar, Dec 28 '15 at 20:24
Are you sure there are no non-printing characters in the `name` field? — Uueerdo, Dec 28 '15 at 20:30
Are you sure that is the whole formattedName?Maybe your client of choice doesnt present the whole row.Also try `GROUP BY TRIM(formattedName) ` — Mihai, Dec 28 '15 at 22:14
@Uueerdo Why would it then work if i removed `GROUP_CONCAT`? — Istlemin, Dec 28 '15 at 22:15
When you remove `GROUP_CONCAT` are you leaving the `GROUP BY`? Also, if moving that condition from the `HAVING` to the `WHERE` changes the result, then it is probably not the appropriate condition for your query. `name` in the `HAVING` clause will just be an arbitrary value that `REPLACE`s to formattedName. — Uueerdo, Dec 28 '15 at 22:22
@Mihai I'm pretty sure it is the entire name, and I have never seen neither phpMyAdmin nor the mySql workbench not show the entire value of any field. Using `TRIM` did not make any difference. And also, if all the formatted names weren't identical, why would it then work when I removed `GROUP_CONCAT`. — Istlemin, Dec 28 '15 at 22:24
Didnt see the issue with GROUP_CONCAT.Given all that,I think you could send some sample data with your query to the MariaDB team,prolly a bug. — Mihai, Dec 28 '15 at 22:27
As far as checking for non-printing characters, I've never had the need to check for "any non-printing" but something like this might help. `name REGEX '[^:print:]'` should be true if the value contains such fields. http://dev.mysql.com/doc/refman/5.7/en/regexp.html#operator_regexp — Uueerdo, Dec 28 '15 at 22:29
@Uueerdo Yes, I'm leaving the `GROUP BY`. Have you seen my change in the query? When I use `HAVING` it looks like this: `FROM movies GROUP BY formattedName HAVING COUNT(CASE WHEN name like "%von brom%" THEN 1 END) > 0;` And when I use `WHERE` it looks like this: `FROM movies WHERE name like "%von brom%" GROUP BY formattedName` This way, name won't just be an arbitrary value. I know that the logic changes the way I do it in `WHERE`, and that's why I need to use `HAVING` — Istlemin, Dec 28 '15 at 22:45
Oh, I didn't notice the COUNT portion of the HAVING condition. Though COUNT should probably be SUM, and I am not sure why you are using 'CASE' (without an ELSE), when an `IF()` would have more clarity... and you still might have issues wrongly catching 'von bromeldygook' and missing 'von brom'. — Uueerdo, Dec 28 '15 at 22:52
@Uueerdo Lets say there is a movie in my table called "Foo vonbrombar". If your doing it that way, then it will match that movie too. That's wrong, because I only want to match the movies that contain the string "von brom" (with the space) — Istlemin, Dec 28 '15 at 23:00
I'm running this off target, sorry; to get back to the main issue... what kind of results do you get if try the query on each server but use `CONVERT` inside the innermost `REPLACE` to make the character set the same as the default of the other server? http://dev.mysql.com/doc/refman/5.7/en/charset-convert.html — Uueerdo, Dec 28 '15 at 23:07
@Uueerdo Converting to another charset makes no difference. It still works on the local server, and not on the one.com server. — Istlemin, Dec 29 '15 at 09:49
Well, I'm out of ideas. It must be a incompatibility/bug as Barmar initially suggested. — Uueerdo, Dec 29 '15 at 17:29
Please provide `SHOW CREATE TABLE`; we need to see the `CHARACTER SET` and `COLLATION`. — Rick James, Dec 30 '15 at 00:45

Norbert · Answer 1 · 2015-12-28T20:27:54.403

0

You are supposed to use WHERE instead of HAVING if you do not use a GROUP function on your data.

All returned data in the SELECT should either be in the GROUP BY statement or have a GROUP function applied to it.

After applying these two rules the query becomes:

SELECT name,
 REPLACE(REPLACE(REPLACE(LOWER(name),' ',''),'-',''),':','') AS formattedName,
 GROUP_CONCAT(service)
 FROM movies
 WHERE name LIKE  "%von brom%"
 GROUP BY formattedName,name;

edited Dec 28 '15 at 20:27

answered Dec 28 '15 at 20:07

Norbert

6,026
3
17
40

You definitely **can** group on an alias. – Barmar Dec 28 '15 at 20:23
His question was how to get all the rows with the same formatted name to be combined. How does your answer do that when it groups by `name`? – Barmar Dec 28 '15 at 20:23
@Barmar: `GROUP BY` alias: Apparently it is possible, older versions or other DBMS did not allow this (so it is just my knowledge which needed the adjustment :) ). Performance: It will be crap since no index can be used anymore, so it might not be advisable to `GROUP` on an alias after functions have been applied to it. – Norbert Dec 28 '15 at 20:29
If there's no column in the table that contains the information you want to group on, then obviously it can't be indexed. But you may still need to do it. If the DB doesn't support it directly, you can do it with a subquery: `SELECT * FROM (SELECT AS alias, ...) GROUP BY alias` – Barmar Dec 28 '15 at 20:32
I've change it so that I'm using a group function when filtering the name, but it still gives my exactly the same result. I can't use WHERE, because then I won't get all the information I need in some cases. And also, if everything you said is correct, why is it working on my local database? – Istlemin Dec 28 '15 at 21:58
I tried to select `GROUP_CONCAT(name)` instead of name, which then makes the query satisfy all your requirements, but it still doesn't work. – Istlemin Dec 28 '15 at 22:11

Why are HAVING and GROUP_CONCAT not working together in this query?

1 Answers1