Push down limit on IndexLookUp operator can be improved

[Reminiscent]

Enhancement

Related to #10668 and #25063.

Now, when we push down limit to indexLookUp, it has some restriction. We should unrestricted it by keeping the Limit or Projection operator.

Before the change(now):

mysql> create table t3(a int, b int, key(a));
Query OK, 0 rows affected (0.03 sec)

mysql> explain select * from t3 order by a limit 10000, 100;
+------------------------------------+----------+-----------+----------------------+--------------------------------+
| id                                 | estRows  | task      | access object        | operator info                  |
+------------------------------------+----------+-----------+----------------------+--------------------------------+
| Limit_12                           | 100.00   | root      |                      | offset:10000, count:100        |
| └─Projection_23                    | 10000.00 | root      |                      | test.t3.a, test.t3.b           |
|   └─IndexLookUp_22                 | 10000.00 | root      |                      |                                |
|     ├─Limit_21(Build)              | 10000.00 | cop[tikv] |                      | offset:0, count:10100          |
|     │ └─IndexFullScan_19           | 10000.00 | cop[tikv] | table:t3, index:a(a) | keep order:true, stats:pseudo  |
|     └─TableRowIDScan_20(Probe)     | 10000.00 | cop[tikv] | table:t3             | keep order:false, stats:pseudo |
+------------------------------------+----------+-----------+----------------------+--------------------------------+
6 rows in set (0.00 sec)

After the change(future):

mysql> explain select * from t3 order by a limit 10000, 100;
+------------------------------------+----------+-----------+----------------------+-----------------------------------------+
| id                                 | estRows  | task      | access object        | operator info                           |
+------------------------------------+----------+-----------+----------------------+-----------------------------------------+
| Limit_12                           | 100.00   | root      |                      | offset:10000, count:100                 |
| └─Projection_23                    | 100.00   | root      |                      | test.t3.a, test.t3.b                    |
|   └─IndexLookUp_22                 | 100.00   | root      |                      | limit embedded(offset:10000, count:100) |
|     ├─Limit_21(Build)              | 10000.00 | cop[tikv] |                      | offset:0, count:10100                   |
|     │ └─IndexFullScan_19           | 10000.00 | cop[tikv] | table:t3, index:a(a) | keep order:true, stats:pseudo           |
|     └─TableRowIDScan_20(Probe)     | 100.00   | cop[tikv] | table:t3             | keep order:false, stats:pseudo          |
+------------------------------------+----------+-----------+----------------------+-----------------------------------------+
6 rows in set (0.01 sec)

[winoros]

The example here is wrong?
Misread.