6

Under GROUP BY and HAVING with Hidden Columns, the MySQL manual documents (added emphasis):

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

Despite this explicit warning from the developers, some people continue to rely on undocumented behaviour to determine the value that will be selected from a hidden column.

In particular, MySQL often appears to select the "first" record from each group (where the notion of "first" is itself undocumented, such as the oldest record on some storage engines or according to some sort order applied to a materialised table from a subquery). I've seen this exploited to retrieve, for example, a groupwise maximum:

SELECT * FROM (
  SELECT * FROM my_table ORDER BY sort_col DESC
) t GROUP BY group_col

For completeness, the same can be accomplished in a standard and documented fashion with a simple join:

SELECT * FROM my_table NATURAL JOIN (
  SELECT   group_col, MAX(sort_col) sort_col
  FROM     my_table
  GROUP BY group_col
) t

I believe that one should never rely on undocumented behaviour because there may be unforeseen corner cases that cause that behaviour to break. For example, in satisfying a GROUP BY operation with an index, MySQL sorts the results and may thereby choose an unexpected value.

What other corner cases can break this behaviour? Or is it sufficiently reliable for production systems?

7
  • 5
    It's not reliable if the documentation states it shouldn't be relied on. It may change in future releases. This is another shining example of why MySQL is often a poor choice when compared to other RDBMS systems.
    – Philᵀᴹ
    Nov 19, 2012 at 13:12
  • 2
    It should be used, as you say, only when the results are determinate (usually when you have a GROUP BY table.pk) and only then. If I remember well, there's a documented case that the very exploitation you mention (ORDER BY in a derived table, and then GROUP BY in the external query) breaks with MariaDB 5.3 optimizer's enhancements. Nov 19, 2012 at 13:23
  • See this question at MariaDB Knowledgebase: GROUP BY trick has been optimized away. Notice the part that says "The EXPLAIN plan do longer shows a subquery, as if the inner ORDER BY has been thrown away." Nov 19, 2012 at 13:31
  • @ypercube: Thank you, that's a useful corner case to cite here for users of MariaDB. Hopefully there are some for pure MySQL too.
    – eggyal
    Nov 19, 2012 at 13:52
  • 1
    (I wrote the MariaDB comment.) I have not (yet) found a counterexample standard MySQL. But I suspect that it might happen with the new optimizations in 5.6.7.
    – Rick James
    Nov 20, 2012 at 0:36

1 Answer 1

1

I was thinking the NATURAL JOIN example you just used

SELECT * FROM my_table NATURAL JOIN (
  SELECT   group_col, MAX(sort_col) sort_col
  FROM     my_table
  GROUP BY group_col
) t

If you shift to another type of JOIN and impose WHERE, ordering can come and go without warning in spite of the ill-advised reliance on undocumented behavior of the GROUP BY.

For this example, I will

  • use Windows 7
  • use MySQL 5.5.12-log for Windows
  • create some sample data
  • impose a LEFT JOIN without a WHERE clause
  • impose a LEFT JOIN with a WHERE clause

For the DB Environment

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.12-log |
+------------+
1 row in set (0.00 sec)

mysql> show variables like '%version_co%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
3 rows in set (0.00 sec)

mysql>

Using this script to generate sample data

DROP DATABASE IF EXISTS eggyal;
CREATE DATABASE eggyal;
USE eggyal
CREATE TABLE groupby
(
    id int not null auto_increment,
    num int,
    primary key (id)
);
INSERT INTO groupby (num) VALUES
(floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
(floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
(floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
(floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp()));
INSERT INTO groupby (num) SELECT num FROM groupby;
SELECT * FROM groupby;

and these two queries for testing the GROUP BY subsequent use;

SELECT * FROM groupby A LEFT JOIN
(
    SELECT   num, MAX(id) id
    FROM     groupby
    GROUP BY num
) B USING (id);
SELECT * FROM groupby A LEFT JOIN
(
    SELECT   num, MAX(id) id
    FROM     groupby
    GROUP BY num
) B USING (id) WHERE B.num IS NOT NULL;

Let's test the durability of the GROUP BY's results;

STEP 01 : Create the Sample Data

mysql> DROP DATABASE IF EXISTS eggyal;
Query OK, 1 row affected (0.09 sec)

mysql> CREATE DATABASE eggyal;
Query OK, 1 row affected (0.00 sec)

mysql> USE eggyal
Database changed
mysql> CREATE TABLE groupby
    -> (
    ->     id int not null auto_increment,
    ->     num int,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO groupby (num) VALUES
    -> (floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
    -> (floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
    -> (floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
    -> (floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp()));
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO groupby (num) SELECT num FROM groupby;
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM groupby;
+----+------------+
| id | num        |
+----+------------+
|  1 |  269529129 |
|  2 |  387090406 |
|  3 | 1126864683 |
|  4 |  411160755 |
|  5 |   29173595 |
|  6 |  266349579 |
|  7 | 1244227156 |
|  8 |    6231766 |
|  9 |  269529129 |
| 10 |  387090406 |
| 11 | 1126864683 |
| 12 |  411160755 |
| 13 |   29173595 |
| 14 |  266349579 |
| 15 | 1244227156 |
| 16 |    6231766 |
+----+------------+
16 rows in set (0.00 sec)

STEP 02 : Use LEFT JOIN without a WHERE clause

mysql> SELECT * FROM groupby A LEFT JOIN
    -> (
    ->     SELECT   num, MAX(id) id
    ->     FROM     groupby
    ->     GROUP BY num
    -> ) B USING (id);
+----+------------+------------+
| id | num        | num        |
+----+------------+------------+
|  1 |  269529129 |       NULL |
|  2 |  387090406 |       NULL |
|  3 | 1126864683 |       NULL |
|  4 |  411160755 |       NULL |
|  5 |   29173595 |       NULL |
|  6 |  266349579 |       NULL |
|  7 | 1244227156 |       NULL |
|  8 |    6231766 |       NULL |
|  9 |  269529129 |  269529129 |
| 10 |  387090406 |  387090406 |
| 11 | 1126864683 | 1126864683 |
| 12 |  411160755 |  411160755 |
| 13 |   29173595 |   29173595 |
| 14 |  266349579 |  266349579 |
| 15 | 1244227156 | 1244227156 |
| 16 |    6231766 |    6231766 |
+----+------------+------------+
16 rows in set (0.00 sec)

mysql>

STEP 03 : Use LEFT JOIN with a WHERE clause

mysql> SELECT * FROM groupby A LEFT JOIN
    -> (
    ->     SELECT   num, MAX(id) id
    ->     FROM     groupby
    ->     GROUP BY num
    -> ) B USING (id) WHERE B.num IS NOT NULL;
+----+------------+------------+
| id | num        | num        |
+----+------------+------------+
| 16 |    6231766 |    6231766 |
| 13 |   29173595 |   29173595 |
| 14 |  266349579 |  266349579 |
|  9 |  269529129 |  269529129 |
| 10 |  387090406 |  387090406 |
| 12 |  411160755 |  411160755 |
| 11 | 1126864683 | 1126864683 |
| 15 | 1244227156 | 1244227156 |
+----+------------+------------+
8 rows in set (0.00 sec)

mysql>

ANALYSIS

Looking at the aforementioned results, here are two questions:

  • Why does a LEFT JOIN keep an ordering by id ?
  • Why in the world did using a WHERE impose a reordering ?
    • Was it during the JOIN phase ?
    • Did the Query Optimizer look ahead at the ordering of the subquery or ignore it ?

No one foresaw any of these effects because the behavior of explicit clauses was relied upon by the implicit behavior of the Query Optimizer.

CONCLUSION

From my perspective, corner cases can only be of an external nature. In light of this, developers must be willing to fully evaluate the results of a GROUP BY in conjunction with the following twelve(12) aspects:

  1. aggregate functions
  2. subquery usage
  3. JOINs clauses
  4. WHERE clauses
  5. sort order of results with no explicit ORDER BY clause
  6. query results using older GA releases of MySQL
  7. query results using newer beta releases of MySQL
  8. the current SQL_MODE setting in my.cnf
  9. the operating system the code was compiled for
  10. possibly the size of join_buffer_size with respect to its effect on the Query Optimizer
  11. possibly the size of sort_buffer_size with respect to its effect on the Query Optimizer
  12. possibly the storage engine being used (MyISAM vs InnoDB)

Here is the key thing to remember : Any instance of MySQL that works for your query in a specific environment is itself a corner case. Once you change one or more of the twelve(12) evaluation aspects, the corner case is due to break, especially given the first nine(9) aspects.

9
  • Rolando: Thank you once again for providing such a detailed answer, which does indeed show unexpected sort behaviour in a related query. However, I'm very much struggling to see how this answers the specific question that was being posed. Or did I misunderstand something?
    – eggyal
    Nov 26, 2012 at 21:37
  • The direction I chose to answer this question was to not to show any types of reliance on undocumented behavior but to show how reliance on such behavior is often taken for granted and how results are too quickly trusted as genuine. Nov 26, 2012 at 21:41
  • The fact that there is a local GROUP BY phenomenon and that there is a URL dedicated to it ( dev.mysql.com/doc/refman/5.5/en/group-by-extensions.html ) shows that corner cases should come as no surprise. Notice how this GROUP BY quirk is conveniently called a MySQL Extension. Imagine if Oracle now took MySQL and fixed that GROUP BY quirk. Imagine if the SQL_MODE was defaulted to a strict mode for the GROUP BY : dev.mysql.com/doc/refman/5.5/en/… . The possibilities for corner cases can just go on... Nov 26, 2012 at 21:47
  • I completely agree that "corner cases should come as no surprise", but I am struggling to identify a single such example that demonstrates failure of the specific query given in the question. That is really what I was after, so I'm afraid whilst your answer is definitely worthy of an upvote I really can't award the bounty to its current incarnation.
    – eggyal
    Nov 26, 2012 at 21:52
  • My answer just provided an example of two external operations: 1) imposing a WHERE clause and 2) using different type JOIN. The effects were immediately seen when the results were screened. In a high-volume production environment, any change, however slight the change may be, can reorder data against a developer's will. Corner cases cannot be documented because of the unpredictability of the GROUP BY's behavior. A bug fix that did not make it to release notes. A default setting changing on a new minor upgrade. These can make code change behavior as well. Nov 26, 2012 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.