Bug #102373 Order by "desc" does not work after analyze table.
Submitted: 26 Jan 2021 7:53 Modified: 27 Jan 2021 13:54
Reporter: YUYA KAWABATA Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: orderby distinct regression

[26 Jan 2021 7:53] YUYA KAWABATA
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)
```
[27 Jan 2021 13:54] MySQL Verification Team
Hi Mr. KAWABATA,

Thank you for your bug report.

I have managed to verify it with 5.7.33. Here are my results:

_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

Table	Op	Msg_type	Msg_text
my_test_db.b	analyze	status	OK

_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

Thanks again.

Verified as reported.

I have also tested 8.0.23 and discovered that 8.0 version IS NOT IMPACTED by this bug !!!!
[27 Jan 2021 13:56] MySQL Verification Team
Setting correct version because this bug does NOT IMPACT 8.0.

It impacts only 5.7 after release 5.7.9.