Query returns wrong result if tidb_opt_derive_topn is on

[windtalker]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> show create table t;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `value1` int(11) DEFAULT NULL,
  `value2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id1`,`id2`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+-----+-----+--------+--------+
| id1 | id2 | value1 | value2 |
+-----+-----+--------+--------+
|   1 |   1 |      1 |      1 |
|   1 |   2 |      1 |      1 |
|   1 |   3 |      1 |      1 |
|   1 |   4 |      1 |      1 |
|   2 |   1 |      1 |      1 |
|   2 |   2 |      1 |      1 |
|   2 |   3 |      1 |      1 |
|   2 |   4 |      1 |      1 |
+-----+-----+--------+--------+
8 rows in set (0.00 sec)

mysql> set tidb_opt_derive_topn=off;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from (select *, row_number() over (partition by id1) as rownumber from t) DT where rownumber <= 3;
+-----+-----+--------+--------+-----------+
| id1 | id2 | value1 | value2 | rownumber |
+-----+-----+--------+--------+-----------+
|   1 |   1 |      1 |      1 |         1 |
|   1 |   2 |      1 |      1 |         2 |
|   1 |   3 |      1 |      1 |         3 |
|   2 |   1 |      1 |      1 |         1 |
|   2 |   2 |      1 |      1 |         2 |
|   2 |   3 |      1 |      1 |         3 |
+-----+-----+--------+--------+-----------+
6 rows in set (0.01 sec)

mysql> set tidb_opt_derive_topn=on;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from (select *, row_number() over (partition by id1) as rownumber from t) DT where rownumber <= 3;
+-----+-----+--------+--------+-----------+
| id1 | id2 | value1 | value2 | rownumber |
+-----+-----+--------+--------+-----------+
|   1 |   1 |      1 |      1 |         1 |
|   1 |   2 |      1 |      1 |         2 |
|   1 |   3 |      1 |      1 |         3 |
+-----+-----+--------+--------+-----------+
3 rows in set (0.00 sec)

2. What did you expect to see? (Required)

When set tidb_opt_derive_topn to on or off, the query result should be the same

3. What did you see instead (Required)

When set tidb_opt_derive_topn to on, query result is wrong.

4. What is your TiDB version? (Required)

master@ fa28db1