Probing into the problem of directly using the having statement without group by in Mysql _mysql

Today, a classmate gave me a response, there is a table, ID is the primary key, so that the wording can return a record:

  "SELECT * FROM T has id=min (ID);"

But instead of changing min to Max, the return is empty:

  "SELECT * FROM T has Id=max (ID);"

What is this for?

Let's start with a test to verify the situation.

This is the table structure, initialize two records, and then experiment:

ROOT@LOCALHOST:PLX 10:25:10> Show CREATE TABLE t2g
*************************** 1. Row ************************* * *
    table:t2
Create table:create Table ' T2 ' (
 ' a ' int () DEFAULT null,
 ' id ' int (a) unsigned not NULL AU To_increment,
 PRIMARY KEY (' id ')
) engine=innodb auto_increment=5 DEFAULT Charset=utf8
 
root@localhost: PLX 10:25:15> select * from T2;
+------+----+
| a  | id |
+------+----+
|  1 | 1 |
|  1 | 3 |
+------+----+
2 rows in Set (0.00 sec)
 
ROOT@LOCALHOST:PLX 10:25:20> SELECT * to T2 having id=min (ID) 
   +------+----+
| a  | id |
+------+----+
|  1 | 1 |
+------+----+
1 row in Set (0.00 sec)
 
ROOT@LOCALHOST:PLX 10:25:30> SELECT * to T2 having Id=max (ID) 
   
    empty Set (0.00 sec)


   

At first glance, it seems to be really like this, how can this be?

I'll try again, change the A field to 10, and then try the A field:

ROOT@LOCALHOST:PLX 10:26:58> select * from T2;
+------+----+
| a  | id |
+------+----+
|  10 | 1 |
|  1 | 3 |
+------+----+
2 rows in Set (0.00 sec)
 
ROOT@LOCALHOST:PLX 10:28:20> SELECT * to T2 having A=max (a);
+------+----+
| a  | id |
+------+----+
|  10 | 1 |
+------+----+
1 row in Set (0.00 sec)
 
ROOT@LOCALHOST:PLX 10:28:28> SELECT * to T2 having a=min (a);
Empty Set (0.00 sec)

I rub, this time Max can return, Min can not, this is why?

Narrator

In general, having clauses are used in conjunction with group by, and using the having itself is not in conformity with the specification,

But MySQL will do an override, plus a GROUP by NULL, "SELECT * from T has id=min (ID)" will be rewritten as "select * from T GROUP by NULL have id=min (ID)", This syntax is in line with the specification.

Go on......

But what happens to this GROUP by NULL? By looking at the code and experimenting, you can prove that GROUP by NULL is equivalent to LIMIT 1:

ROOT@LOCALHOST:PLX 10:25:48> SELECT * from T2 GROUP by NULL;
+------+----+
| a  | id |
+------+----+
|  10 | 1 |
+------+----+
1 row in Set (0.00 sec)

That is, after group by NULL, there is only one group, with the first row of data in it.

But if so, Min, max results should be consistent, it should not be max and min a result, a no results ah, this is why, then do a test.

Modify the data and view the Min/max value directly:

ROOT@LOCALHOST:PLX 10:26:58> select * from T2;
+------+----+
| a  | id |
+------+----+
|  10 | 1 |
|  1 | 3 |
+------+----+
2 rows in Set (0.00 sec)
 
ROOT@LOCALHOST:PLX 10:27:04> SELECT * to T2 GROUP by NULL;
+------+----+
| a  | id |
+------+----+
|  10 | 1 |
+------+----+
1 row in Set (0.00 sec)
 
ROOT@LOCALHOST:PLX 10:30:21> SELECT MAX (a), Min (a), MAX (id), min (id) FR OM T2 GROUP by NULL;
+--------+--------+---------+---------+
| MAX (a) | MIN (a) | MAX (ID) | MIN (ID) |
+--------+--------+---------+---------+
|   Ten |   1 |    3 |    1 |
+--------+--------+---------+---------+
1 row in Set (0.00 sec)

Did you find the problem?

The Max/min function value is global, not limit 1 within the group.

Therefore, when group by NULL, the Max/min function is to take the maximum and minimum values in all the data!

So, "SELECT * from T has id=min (ID)" is essentially "select * from t have Id=1″, can return a record, and" select * from t have Id=max (ID) "essentially "SELECT * from T has Id=3″, of course, does not return records, which is the root of the problem."

Test group by a, which is right, each group has only one row, so max/min is the same size, this time to get the maximum and minimum values in the group.

ROOT@LOCALHOST:PLX 11:29:49> SELECT MAX (a), Min (a), Max (id), min (id) from T2 GROUP by A;
+--------+--------+---------+---------+
| MAX (a) | MIN (a) | MAX (ID) | MIN (ID) |
+--------+--------+---------+---------+
|   1 |   1 |    3 |    3 |
|   Ten |   Ten |    5 |    5 |
+--------+--------+---------+---------+
2 rows in Set (0.00 sec)

The max/min behavior of GROUP by NULL is the nature of the problem, so, try to use standard syntax, and before you play with the pattern SQL, be sure to understand whether its behavior is consistent with the understanding.