optimizer access table unnecessarily

[mzhang77]

Bug Report

1. Minimal reproduce step (Required)

CREATE TABLE tb ( 
  object_id bigint(20), 
  a bigint(20) , 
  b bigint(20) , 
  c bigint(20) , 
  PRIMARY KEY (object_id), 
  KEY ab (a,b)
);

explain analyze select count(1) from (select /*+ force_index(tb, ab) */ 1 from tb where a=1 and b=1 limit 100) a;

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

The execution plan should use index only without accessing table data.

3. What did you see instead (Required)

explain analyze select count(1) from (select /*+ force_index(tb, ab) */ 1 from tb where a=1 and b=1 limit 100) a;
+----------------------------------+---------+---------+-----------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| id                               | estRows | actRows | task      | access object            | execution info                                                                                                                                                                                                                                                                                                                              | operator info                                   | memory    | disk |
+----------------------------------+---------+---------+-----------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| StreamAgg_11                     | 1.00    | 1       | root      |                          | time:1.49ms, loops:2, RU:0.482109                                                                                                                                                                                                                                                                                                           | funcs:count(1)->Column#6                        | 1.49 KB   | N/A  |
| └─IndexLookUp_17                 | 0.10    | 0       | root      |                          | time:1.48ms, loops:1                                                                                                                                                                                                                                                                                                                        | limit embedded(offset:0, count:100)             | 243 Bytes | N/A  |
|   ├─Limit_16(Build)              | 0.10    | 0       | cop[tikv] |                          | time:1.34ms, loops:1, cop_task: {num: 1, max: 1.28ms, proc_keys: 0, tot_proc: 21.3µs, tot_wait: 26.4µs, rpc_num: 1, rpc_time: 1.26ms, copr_cache_hit_ratio: 0.00, build_task_duration: 19.6µs, max_distsql_concurrency: 1}, tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 8.75µs, rocksdb: {block: {}}}     | offset:0, count:100                             | N/A       | N/A  |
|   │ └─IndexRangeScan_14          | 0.10    | 0       | cop[tikv] | table:tb, index:ab(a, b) | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                                                                                                                                | range:[1 1,1 1], keep order:false, stats:pseudo | N/A       | N/A  |
|   └─TableRowIDScan_15(Probe)     | 0.10    | 0       | cop[tikv] | table:tb                 |                                                                                                                                                                                                                                                                                                                                             | keep order:false, stats:pseudo                  | N/A       | N/A  |
+----------------------------------+---------+---------+-----------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
5 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

select @@version;
+--------------------+
| @@version          |
+--------------------+
| 8.0.11-TiDB-v7.5.1 |
+--------------------+
1 row in set (0.00 sec)

[tiancaiamao]

 {
            "type":"DataSource",
            "property":"",
            "info":"table:tb",
            "children":[
               
            ],
            "id":1,
            "cost":0,
            "selected":false
         },
         {
            "type":"Aggregation",
            "property":"",
            "info":"funcs:count(1), firstrow(test.tb.object_id), firstrow(test.tb.a), firstrow(test.tb.b), firstrow(test.tb.c)",
            "children":[
               1
            ],
            "id":3,
            "cost":0,
            "selected":false
         },
         {
            "type":"Limit",
            "property":"",
            "info":"offset:0, count:100",
            "children":[
               3
            ],
            "id":9,
            "cost":0,
            "selected":false
         },
         {
            "type":"Aggregation",
            "property":"",
            "info":"funcs:count(1)",
            "children":[
               9
            ],
            "id":6,
            "cost":0,
            "selected":false
         },
         {
            "type":"Projection",
            "property":"",
            "info":"Column#6",
            "children":[
               6
            ],
            "id":7,
            "cost":0,
            "selected":false
         }

It seems that the outer-most count(1) cause the inner count(1) to get all fields

"funcs:count(1), firstrow(test.tb.object_id), firstrow(test.tb.a), firstrow(test.tb.b), firstrow(test.tb.c)"

Since the index only contains column tb.a and tb.b, IndexLookup is used.