limit and offset computation may eliminate and simplify the entire plan

[AilinKid]

Enhancement

drop table if exists t;
create table t(a int primary key, b int);
insert into t values (1, 11), (4, 44), (2, 22), (3, 33);
set session tidb_executor_concurrency = 4;
set @@session.tidb_hash_join_concurrency = 5;
set @@session.tidb_distsql_scan_concurrency = 15;

mysql> explain select a from (select a from t order by a limit 3 offset 5) as t1 order by a limit 3 offset 5;
+------------------------------+---------+-----------+---------------+-------------------------------+
| id                           | estRows | task      | access object | operator info                 |
+------------------------------+---------+-----------+---------------+-------------------------------+
| Limit_17                     | 3.00    | root      |               | offset:5, count:3             |
| └─Limit_41                   | 3.00    | root      |               | offset:5, count:3             |
|   └─TableReader_46           | 4.00    | root      |               | data:Limit_45                 |
|     └─Limit_45               | 4.00    | cop[tikv] |               | offset:0, count:8             |
|       └─TableFullScan_26     | 4.00    | cop[tikv] | table:t       | keep order:true, stats:pseudo |
+------------------------------+---------+-----------+---------------+-------------------------------+
5 rows in set (0.01 sec)

after the subQ's application, we could say the result set is about 3 rows at most, while from the outer query block's imply, current all rows could be eliminated and abandoned for another offset 5 which is impossible, we can generate a table dual with row=0 directly.

TableDual_11 0.00 root rows:0