Description:
The query with distinct + order by (see "how to repeat") returns wrong order.
This problem reproduce after MySQL 5.7.9.
And this problem does not reproduce latest MySQL 5.6 and before MySQL 5.7.8.
It's a regression because 'order by' works correctly until 5.7.8.
Same queries don't return same result after 'analyze table'.
I think this problem is different from https://bugs.mysql.com/bug.php?id=33087.
How to repeat:
Exec below commands.
```
CREATE DATABASE my_test_db;
use my_test_db;
CREATE TABLE `a` (
`_id` bigint(20) NOT NULL AUTO_INCREMENT,
`col_int_1` int(11) NOT NULL DEFAULT '1',
`col_int_2` int(11) DEFAULT '1',
PRIMARY KEY (`_id`),
KEY `key` (`col_int_1`)
)
ENGINE = InnoDB;
CREATE TABLE `b` (
`_id` bigint(20) NOT NULL AUTO_INCREMENT,
`col_bigint` bigint(20) DEFAULT 1,
`col_char_1` char(255) DEFAULT 'hello',
`col_char_2` char(255) DEFAULT 'world',
PRIMARY KEY (`_id`),
KEY `key` (`col_bigint`)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;
INSERT INTO a (_id) VALUES (NULL), (NULL), (NULL);
DELIMITER //
CREATE PROCEDURE insert_multi_and_select(in x int)
begin
declare i int;
set i = 0;
while i < x do
set i = i + 1;
INSERT INTO b SET _id=NULL;
end while;
SELECT DISTINCT b._id, b.col_char_1, b.col_char_2 FROM b INNER JOIN a ON b.col_bigint = a._id AND a.col_int_1 = 1 ORDER BY b._id DESC LIMIT 0, 21;
end
//
DELIMITER ;
CALL insert_multi_and_select(10000);
analyze table b;
SELECT DISTINCT b._id, b.col_char_1, b.col_char_2 FROM b INNER JOIN a ON b.col_bigint = a._id AND a.col_int_1 = 1 ORDER BY b._id DESC LIMIT 0, 21;
DROP DATABASE my_test_db;
```
# Result
## MySQL 5.7.27
```
mysql> CALL insert_multi_and_select(10000); -- Ordered DESCENDING collectly.
+-------+------------+------------+
| _id | col_char_1 | col_char_2 |
+-------+------------+------------+
| 10000 | hello | world |
| 9999 | hello | world |
| 9998 | hello | world |
| 9997 | hello | world |
| 9996 | hello | world |
| 9995 | hello | world |
| 9994 | hello | world |
| 9993 | hello | world |
| 9992 | hello | world |
| 9991 | hello | world |
| 9990 | hello | world |
| 9989 | hello | world |
| 9988 | hello | world |
| 9987 | hello | world |
| 9986 | hello | world |
| 9985 | hello | world |
| 9984 | hello | world |
| 9983 | hello | world |
| 9982 | hello | world |
| 9981 | hello | world |
| 9980 | hello | world |
+-------+------------+------------+
21 rows in set (1 min 9.35 sec)
Query OK, 0 rows affected (1 min 9.36 sec)
mysql> analyze table b;
+--------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| my_test_db.b | analyze | status | OK |
+--------------+---------+----------+----------+
1 row in set (0.02 sec)
mysql> SELECT DISTINCT b._id, b.col_char_1, b.col_char_2 FROM b INNER JOIN a ON b.col_bigint = a._id AND a.col_int_1 = 1 ORDER BY b._id DESC LIMIT 0, 21; -- After ANALYZE TABLE, ordered ASCENDING wrongly
+-----+------------+------------+
+-----+------------+------------+
| _id | col_char_1 | col_char_2 |
+-----+------------+------------+
| 1 | hello | world |
| 2 | hello | world |
| 3 | hello | world |
| 4 | hello | world |
| 5 | hello | world |
| 6 | hello | world |
| 7 | hello | world |
| 8 | hello | world |
| 9 | hello | world |
| 10 | hello | world |
| 11 | hello | world |
| 12 | hello | world |
| 13 | hello | world |
| 14 | hello | world |
| 15 | hello | world |
| 16 | hello | world |
| 17 | hello | world |
| 18 | hello | world |
| 19 | hello | world |
| 20 | hello | world |
| 21 | hello | world |
+-----+------------+------------+
21 rows in set (0.80 sec)
```
## MySQL 5.7.9
```
mysql> CALL insert_multi_and_select(10000); -- Ordered DESCENDING collectly.
+-------+------------+------------+
| _id | col_char_1 | col_char_2 |
+-------+------------+------------+
| 10000 | hello | world |
| 9999 | hello | world |
| 9998 | hello | world |
| 9997 | hello | world |
| 9996 | hello | world |
| 9995 | hello | world |
| 9994 | hello | world |
| 9993 | hello | world |
| 9992 | hello | world |
| 9991 | hello | world |
| 9990 | hello | world |
| 9989 | hello | world |
| 9988 | hello | world |
| 9987 | hello | world |
| 9986 | hello | world |
| 9985 | hello | world |
| 9984 | hello | world |
| 9983 | hello | world |
| 9982 | hello | world |
| 9981 | hello | world |
| 9980 | hello | world |
+-------+------------+------------+
21 rows in set (1 min 8.88 sec)
Query OK, 0 rows affected (1 min 8.88 sec)
mysql> analyze table b;
+--------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| my_test_db.b | analyze | status | OK |
+--------------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> SELECT DISTINCT b._id, b.col_char_1, b.col_char_2 FROM b INNER JOIN a ON b.col_bigint = a._id AND a.col_int_1 = 1 ORDER BY b._id DESC LIMIT 0, 21;
+-----+------------+------------+
| _id | col_char_1 | col_char_2 |
+-----+------------+------------+
| 1 | hello | world |
| 2 | hello | world |
| 3 | hello | world |
| 4 | hello | world |
| 5 | hello | world |
| 6 | hello | world |
| 7 | hello | world |
| 8 | hello | world |
| 9 | hello | world |
| 10 | hello | world |
| 11 | hello | world |
| 12 | hello | world |
| 13 | hello | world |
| 14 | hello | world |
| 15 | hello | world |
| 16 | hello | world |
| 17 | hello | world |
| 18 | hello | world |
| 19 | hello | world |
| 20 | hello | world |
| 21 | hello | world |
+-----+------------+------------+
21 rows in set (0.80 sec)
mysql>
mysql> DROP DATABASE my_test_db;
Query OK, 2 rows affected (0.09 sec)
```
## MySQL 5.7.8
```
mysql> CALL insert_multi_and_select(10000);
+-------+------------+------------+
| _id | col_char_1 | col_char_2 |
+-------+------------+------------+
| 10000 | hello | world |
| 9999 | hello | world |
| 9998 | hello | world |
| 9997 | hello | world |
| 9996 | hello | world |
| 9995 | hello | world |
| 9994 | hello | world |
| 9993 | hello | world |
| 9992 | hello | world |
| 9991 | hello | world |
| 9990 | hello | world |
| 9989 | hello | world |
| 9988 | hello | world |
| 9987 | hello | world |
| 9986 | hello | world |
| 9985 | hello | world |
| 9984 | hello | world |
| 9983 | hello | world |
| 9982 | hello | world |
| 9981 | hello | world |
| 9980 | hello | world |
+-------+------------+------------+
21 rows in set (1 min 30.47 sec)
Query OK, 0 rows affected (1 min 30.47 sec)
mysql> analyze table b;
+--------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| my_test_db.b | analyze | status | OK |
+--------------+---------+----------+----------+
1 row in set (0.03 sec)
mysql> SELECT DISTINCT b._id, b.col_char_1, b.col_char_2 FROM b INNER JOIN a ON b.col_bigint = a._id AND a.col_int_1 = 1 ORDER BY b._id DESC LIMIT 0, 21; -- In MySQL 5.7.8, Ordered DESCENDING collectly before/after ANALYZE TABLE.
+-------+------------+------------+
| _id | col_char_1 | col_char_2 |
+-------+------------+------------+
| 10000 | hello | world |
| 9999 | hello | world |
| 9998 | hello | world |
| 9997 | hello | world |
| 9996 | hello | world |
| 9995 | hello | world |
| 9994 | hello | world |
| 9993 | hello | world |
| 9992 | hello | world |
| 9991 | hello | world |
| 9990 | hello | world |
| 9989 | hello | world |
| 9988 | hello | world |
| 9987 | hello | world |
| 9986 | hello | world |
| 9985 | hello | world |
| 9984 | hello | world |
| 9983 | hello | world |
| 9982 | hello | world |
| 9981 | hello | world |
| 9980 | hello | world |
+-------+------------+------------+
21 rows in set (0.33 sec)
```
## MySQL5.6.45
```
mysql> CALL insert_multi_and_select(10000);
+-------+------------+------------+
| _id | col_char_1 | col_char_2 |
+-------+------------+------------+
| 10000 | hello | world |
| 9999 | hello | world |
| 9998 | hello | world |
| 9997 | hello | world |
| 9996 | hello | world |
| 9995 | hello | world |
| 9994 | hello | world |
| 9993 | hello | world |
| 9992 | hello | world |
| 9991 | hello | world |
| 9990 | hello | world |
| 9989 | hello | world |
| 9988 | hello | world |
| 9987 | hello | world |
| 9986 | hello | world |
| 9985 | hello | world |
| 9984 | hello | world |
| 9983 | hello | world |
| 9982 | hello | world |
| 9981 | hello | world |
| 9980 | hello | world |
+-------+------------+------------+
21 rows in set (1 min 7.12 sec)
Query OK, 0 rows affected (1 min 7.13 sec)
mysql> analyze table b;
+--------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| my_test_db.b | analyze | status | OK |
+--------------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> SELECT DISTINCT b._id, b.col_char_1, b.col_char_2 FROM b INNER JOIN a ON b.col_bigint = a._id AND a.col_int_1 = 1 ORDER BY b._id DESC LIMIT 0, 21; -- In MySQL 5.6, Ordered DESCENDING collectly before/after ANALYZE TABLE.
+-------+------------+------------+
| _id | col_char_1 | col_char_2 |
+-------+------------+------------+
| 10000 | hello | world |
| 9999 | hello | world |
| 9998 | hello | world |
| 9997 | hello | world |
| 9996 | hello | world |
| 9995 | hello | world |
| 9994 | hello | world |
| 9993 | hello | world |
| 9992 | hello | world |
| 9991 | hello | world |
| 9990 | hello | world |
| 9989 | hello | world |
| 9988 | hello | world |
| 9987 | hello | world |
| 9986 | hello | world |
| 9985 | hello | world |
| 9984 | hello | world |
| 9983 | hello | world |
| 9982 | hello | world |
| 9981 | hello | world |
| 9980 | hello | world |
+-------+------------+------------+
21 rows in set (0.26 sec)
```
