Re: Embarassing GROUP question

Corey Tisdale <corey(at)eyewantmedia(dot)com> writes:
> We're coming from mysql 4, and changing the sort order changes the
> values of all columns as you would expect, given that you would expect
> a sort statement to affect grouping. This certainly isn't the only
> time I've used this syntax. I've been mysql user for ten years, and
> the outcome has been consistant across hundreds of tables and millions
> of rows and thousands of queries. If you ever have to use or modify a
> mysql db, just keep this in mind in case it saves you some time.

Okay, I got sufficiently interested to drag out the nearest copy of
mysql and try it ...

mysql> create table t (f1 int, f2 int, f3 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(1,11,111), (1,22,222), (1,44,444), (1,33,333);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into t values(2,55,555), (2,22,222), (2,44,444), (2,33,333);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into t values(3,55,555), (3,22,222), (3,44,444), (3,77,777);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from t group by f1 order by f2;
+------+------+------+
| f1 | f2 | f3 |
+------+------+------+
| 1 | 11 | 111 |
| 2 | 55 | 555 |
| 3 | 55 | 555 |
+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from t group by f1 order by f2 desc;
+------+------+------+
| f1 | f2 | f3 |
+------+------+------+
| 2 | 55 | 555 |
| 3 | 55 | 555 |
| 1 | 11 | 111 |
+------+------+------+
3 rows in set (0.00 sec)

Looks to me like we're arbitrarily getting the physically-first row in
each f1 group. It's certainly not looking for the minimum or maximum f2.

The above is with 5.1.37, but I find essentially the same wording in
the 3.x/4.x manual as in the 5.1 manual.

Now it's certainly possible that in particular circumstances you might
happen to get the right results --- for example, a scan that was using
an index might happen to deliver the rows in the right order. But I
don't see any evidence that mysql is reliably producing groupwise
minimums or maximums with this syntax. The long discussions in the
comments here:
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html
don't suggest that anyone else believes it works, either.

regards, tom lane