MySQL: Total GROUP BY WITH ROLLUP curiosity

Question

I have two queries. One of them makes sense to me, the other don't. First one:

SELECT gender AS 'Gender', count(*) AS '#'
    FROM registrations 
    GROUP BY gender WITH ROLLUP

That gives me this:

Gender       #
Female      20
Male        19
NULL        39

So, I get the count, and the total count. What I expected. Next one:

SELECT c.printable_name AS 'Country', count(*) AS '#' 
    FROM registrations r 
    INNER JOIN country c ON r.country = c.country_id 
    GROUP BY country WITH ROLLUP

Country         #
Denmark         9
Norway         10
Sweden         18
United States   1
Uzbekistan      1
Uzbekistan     39

Same result. But why do I get Uzbekistan for the total??

Answer 1

Coz when you use JOIN method, the following NULL element of array will have value of previous NOT NULL element. But Im not sure. Thats my experience when I use it in PHP.

hm... there is another problem... 'Country' canot be because it is name of table. So change for something else. Then last result will display NULL. Here is my proposal:

$result = mysql_query("SELECT c.printable_name AS 'countryp', count(*) AS '#'
FROM registrations r, country c WHERE r.country = c.country_id
GROUP BY countryp WITH ROLLUP");

while($row = @mysql_fetch_array($result)) {
  $r1 = $row["countryp"];
  $r2 = $row["#"];
  if ($r1 == NULL) $r1 = 'Total';
  echo "$r1 $r2<br />";
}

Answer 2

But why do I get Uzbekistan for the total??

Because you're not SELECTing the item that you're GROUPing BY. If you said:

GROUP BY c.printable_name

You'd get the expected NULL. However you're grouping by a different column so MySQL doesn't know that printable_name is taking part in a rollup-group, and selects any old value from that column, in the join of all registrations. (So it is possible you will see other countries than Uzbekistan.)

This is part of a wider problem with MySQL being permissive on what you can SELECT in a GROUP BY query. For example, you can say:

SELECT gender FROM registrations GROUP BY country;

and MySQL will happily pick one of the gender values for a registration from each country, even though there is no direct causal link (aka “functional dependency”) between country and gender. Other DBMSs will refuse the above command on the grounds that there isn't guaranteed to be one gender per country.(*)

Now, this:

SELECT c.printable_name AS 'Country', count(*) AS '#' 
FROM registrations r 
INNER JOIN country c ON r.country = c.country_id 
GROUP BY country

is OK, because there's a functional dependency between r.country and c.printable_name (assuming you have correctly described your country_id as a PRIMARY KEY).

However MySQL's WITH ROLLUP extension is a bit of a hack in the way it works. On the rollup row stage at the end, it runs over the entire pre-grouping result set to grab its values, and then sets the group-by column to NULL. It doesn't also null other columns that have a functional dependency on that column. It probably should, but MySQL currently doesn't really understand the whole thing about functional dependencies.

So if you select c.printable_name it will show you whichever country name value it randomly picked, and if you select c.country_id it will show you whichever country ID it randomly picked — even though c.country_id is the join criterion, so must be the same as r.country, which is NULL!

What you can do to work around the problem is:

• group by printable_name instead; should be OK if printable_names are unique, or
• select “r.country” as well as printable_name, and check that for being NULL, or
• forget WITH ROLLUP and do a separate query for the end sum. This will be a little slower but it will also be ANSI SQL-92 compliant so your app could work on other databases.

(*: MySQL has an SQL_MODE option ONLY_FULL_GROUP_BY that is supposed to address this issue, but it goes much too far and only lets you select columns from the GROUP BY, not columns that have a functional dependency on the GROUP BY. So it will make valid queries fail as well, making it generally useless.)

Answer 3

This would be an SQL error in any other database.