MySQL aggregate function problem

Question

In the following example, why does the min() query return results, but the max() query does not?

mysql> create table t(id int, a int);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t(id, a) values(1, 1);
Query OK, 1 row affected (0.03 sec)

mysql> insert into t(id, a) values(1, 2);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t
    -> ;
+------+------+
| id   | a    |
+------+------+
|    1 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from t where a < 4;
+------+------+
| id   | a    |
+------+------+
|    1 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from t where a < 4 having a = max(a);
Empty set (0.00 sec)

mysql> select * from t where a < 4 having a = min(a);
+------+------+
| id   | a    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

The query is of course invalid in all other RDBMSs. What do you expect it to return? — Martin Smith
– Martin Smith, Commented Sep 4, 2011 at 5:54

Martin Smith · Accepted Answer · 2011-09-04 10:12:18Z

7

The HAVING clause is used to filter groups of rows. You reference min(a) and max(a) which (in the absence of any GROUP BY clause) aggregate over all a values in the table but then use a comparison against a single a value.

So which a value is MySQL supposed to use? All other RDBMSs that I know of would throw an error at this point however MySQL does allow this. From the docs

Standard SQL does not permit the HAVING clause to name any column not found in the GROUP BY clause unless it is enclosed in an aggregate function. MySQL permits the use of such columns to simplify calculations. This extension assumes that the nongrouped columns will have the same group-wise values. Otherwise, the result is indeterminate.

So in your case from the results you are getting it appears that it ended up using 1 as the scalar value for a but this behaviour is not guaranteed and it could equally well have used 2 or any other existing a value.

edited Sep 4, 2011 at 10:12

answered Sep 4, 2011 at 6:04

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NullUserException Over a year ago

And to prove the indeterminacy of the results, you can add a row which has a=0. Now both your statements will return no rows.

NullUserException Over a year ago

The correct way to do this is to use a subquery: select * from t where a = (select min(a) from t);

Collectives™ on Stack Overflow

MySQL aggregate function problem

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related