Bug #97575 the max() is wrong
Submitted: 11 Nov 2019 9:28 Modified: 11 Nov 2019 12:47
Reporter: Roger li Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version: OS:CentOS
Assigned to: CPU Architecture:x86

[11 Nov 2019 9:28] Roger li 
Description:
why

How to repeat:
mysql> select a,b from t3 order by b desc limit 5;
+--------+---------------------+
| a      | b                   |
+--------+---------------------+
| 100000 | 2019-11-11 16:32:53 |
| 99999  | 2019-11-11 16:32:53 |
| 99998  | 2019-11-11 16:32:53 |
| 99997  | 2019-11-11 16:32:53 |
| 99996  | 2019-11-11 16:32:53 |
+--------+---------------------+
5 rows in set (0.00 sec)

mysql> 
mysql> 
mysql>  select max(a) from t3;
+--------+
| max(a) |
+--------+
| 99999  |
+--------+
1 row in set (0.00 sec)

mysql> select min(a) from t3;
+--------+
| min(a) |
+--------+
|        |
+--------+
1 row in set (0.00 sec)

mysql> select a,b from t3 limit 5;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|      | 2019-11-11 16:05:26 |
| 1    | 2019-11-11 16:05:30 |
| 2    | 2019-11-11 16:05:34 |
| 3    | 2019-11-11 16:05:37 |
| 4    | 2019-11-11 16:05:40 |
+------+---------------------+
5 rows in set (0.00 sec)

mysql>  select a,b from t3 order by b desc limit 5;
+--------+---------------------+
| a      | b                   |
+--------+---------------------+
| 100000 | 2019-11-11 16:32:53 |
| 99999  | 2019-11-11 16:32:53 |
| 99998  | 2019-11-11 16:32:53 |
| 99997  | 2019-11-11 16:32:53 |
| 99996  | 2019-11-11 16:32:53 |
+--------+---------------------+
5 rows in set (0.00 sec)

mysql> show create table t3\G;
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `a` char(20) DEFAULT NULL,
  `b` timestamp NULL DEFAULT NULL,
  UNIQUE KEY `idx_t3` (`a`),
  KEY `idx_t3_time` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql>  show index from t3;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t3    |          0 | idx_t3      |            1 | a           | A         |      100001 |     NULL | NULL   | YES  | BTREE      |         |               |
| t3    |          1 | idx_t3_time |            1 | b           | A         |         441 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

The mysql server is 5.7.23.
[11 Nov 2019 12:45] MySQL Verification Team 
Hello Mr. Li,

Thank you for your bug report.

In order to repeat it, we need all the necessary rows from that table.

Also, you are using an old release of our server. Hence, prior to proceeding further check our current release, which is 5.7.28. There were many bug fixes in that part of code since the release that you are currently using.
[11 Nov 2019 12:47] MySQL Verification Team 
Hi Mr. Li,

Actually, you do not have to do anything.

This is not a bug. Simply, column `a` is defined as a character domain, so sorting is done completely correctly. 

If you wish that sorting is done by numerical values, you should change the type of the column `a`.

Not a bug.