mysql [localhost:8035] {msandbox} (test) > tee /tmp/bug113587-test2-2-condition-1.txt
Logging to file '/tmp/bug113587-test2-2-condition-1.txt'
mysql [localhost:8035] {msandbox} (test) > SHOW CREATE TABLE a1\G
  `order_id` int unsigned NOT NULL AUTO_INCREMENT,
  `order_revision_id` int unsigned DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `order_revision_id` (`order_revision_id`)
) ENGINE=InnoDB AUTO_INCREMENT=196607 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql [localhost:8035] {msandbox} (test) > SHOW CREATE TABLE a2\G
       Table: a2
Create Table: CREATE TABLE `a2` (
  `order_revision_id` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`order_revision_id`)
) ENGINE=InnoDB AUTO_INCREMENT=655351 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql [localhost:8035] {msandbox} (test) > PAGER tail -5
PAGER set to 'tail -5'
mysql [localhost:8035] {msandbox} (test) > TRUNCATE TABLE a1; INSERT INTO a1(order_revision_id) SELECT order_revision_id FROM t1 LIMIT 181894; -- OK
Query OK, 0 rows affected (0.03 sec)

Query OK, 181894 rows affected (1.00 sec)
Records: 181894  Duplicates: 0  Warnings: 0

mysql [localhost:8035] {msandbox} (test) > EXPLAIN SELECT DISTINCT t1.order_id FROM a1 t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
| id | select_type | table | partitions | type   | possible_keys             | key     | key_len | ref                       | rows   | filtered | Extra                                             |
+----+-------------+-------+------------+--------+---------------------------+---------+---------+---------------------------+--------+----------+---------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index  | PRIMARY,order_revision_id | PRIMARY | 4       | NULL                      | 181894 |   100.00 | Using where; Backward index scan; Using temporary |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY                   | PRIMARY | 4       | test.t1.order_revision_id |      1 |   100.00 | Using index; Distinct                             |
+----+-------------+-------+------------+--------+---------------------------+---------+---------+---------------------------+--------+----------+---------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost:8035] {msandbox} (test) >         SELECT DISTINCT t1.order_id FROM a1 t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
|        4 |
|        3 |
|        2 |
|        1 |
+----------+
181894 rows in set (0.37 sec)

mysql [localhost:8035] {msandbox} (test) > INSERT INTO a1(order_revision_id) SELECT order_revision_id FROM t1 LIMIT 1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql [localhost:8035] {msandbox} (test) > EXPLAIN SELECT DISTINCT t1.order_id FROM a1 t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
| id | select_type | table | partitions | type   | possible_keys             | key     | key_len | ref                       | rows   | filtered | Extra                                             |
+----+-------------+-------+------------+--------+---------------------------+---------+---------+---------------------------+--------+----------+---------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index  | PRIMARY,order_revision_id | PRIMARY | 4       | NULL                      | 182161 |   100.00 | Using where; Backward index scan; Using temporary |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY                   | PRIMARY | 4       | test.t1.order_revision_id |      1 |   100.00 | Using index; Distinct                             |
+----+-------------+-------+------------+--------+---------------------------+---------+---------+---------------------------+--------+----------+---------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost:8035] {msandbox} (test) >         SELECT DISTINCT t1.order_id FROM a1 t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
|   181892 |
|   181893 |
|   181894 |
|   196606 |
+----------+
181895 rows in set (0.44 sec)

mysql [localhost:8035] {msandbox} (test) > DELETE FROM a1 LIMIT 1;
Query OK, 1 row affected (0.01 sec)

mysql [localhost:8035] {msandbox} (test) > EXPLAIN SELECT DISTINCT t1.order_id FROM a1 t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
| id | select_type | table | partitions | type   | possible_keys             | key     | key_len | ref                       | rows   | filtered | Extra                                             |
+----+-------------+-------+------------+--------+---------------------------+---------+---------+---------------------------+--------+----------+---------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index  | PRIMARY,order_revision_id | PRIMARY | 4       | NULL                      | 182160 |   100.00 | Using where; Backward index scan; Using temporary |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY                   | PRIMARY | 4       | test.t1.order_revision_id |      1 |   100.00 | Using index; Distinct                             |
+----+-------------+-------+------------+--------+---------------------------+---------+---------+---------------------------+--------+----------+---------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost:8035] {msandbox} (test) >         SELECT DISTINCT t1.order_id FROM a1 t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
|        5 |
|        4 |
|        3 |
|        2 |
+----------+
181894 rows in set (0.25 sec)