CODEWITHSUNDEEP
mysqlaggregate-functions
Just a learner
Just a learner

Reputation: 28552

MySQL aggregate function problem

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)

Upvotes: 8

Views: 1342

Answers (1)

Martin Smith
Martin Smith

Reputation: 452947

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.

Upvotes: 7

Related Questions