global index: specify the table partition, but return all partitions' rows

[L-maple]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

  drop table if exists p;
  create table p (id int, c int) partition by range (c) (
       partition p0 values less than (4),
       partition p1 values less than (7),
       partition p2 values less than (10));
  alter table p add unique idx(id);
  insert into p values (1,3), (3,4), (5,6), (7,9);
  select * from p partition(p0) use index (idx);

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

when execturing the query: select * from p partition(p0) use index (idx)
the result should be: "1 3"

3. What did you see instead (Required)

[”1 3“， ”3 4“， ”5 6“, "7 9"]

4. What is your TiDB version? (Required)

version: release-5.0

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.0.0-414-g6ca79c09c3-dirty
Edition: Community
Git Commit Hash: 6ca79c09c34ecbda1a49a961c049b477eb8f6a65
UTC Build Time: 2023-01-10 08:47:50
GoVersion: go1.16.10
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.77 sec)

[L-maple]

I want to contribute this issue, and I have two solutions:

• When buildDataSource, remove the global index if the partitionName is specified.
• When IndexScan is done, only send tableScan request to the specified partitions.

Which solution is better in your opinion? @tiancaiamao

[L-maple]

I analyze the global index code logic, it seems global index would ignore partition table related logic in executorBuilder.buildIndexLookUpReader.
So I understand returning error directly is nice.

[tiancaiamao]

• When IndexScan is done, only send tableScan request to the specified partitions.

This is viable, let the index scan filter out the result belong to partition(p0)

[L-maple]

@tiancaiamao I pull the request, please review it.

[mjonss]

In master aa51b46 it does not select the index access, but table scan and gives correct result:

tidb> explain select * from p partition(p0) use index (idx);
+-----------------------+---------+-----------+---------------+----------------------+
| id                    | estRows | task      | access object | operator info        |
+-----------------------+---------+-----------+---------------+----------------------+
| TableReader_5         | 4.00    | root      | partition:p0  | data:TableFullScan_4 |
| └─TableFullScan_4 | 4.00    | cop[tikv] | table:p       | keep order:false     |
+-----------------------+---------+-----------+---------------+----------------------+
2 rows in set (0.00 sec)

tidb> select * from p partition(p0) use index (idx);
+------+------+
| id   | c    |
+------+------+
|    1 |    3 |
+------+------+
1 row in set (0.00 sec)

Note that to test this one needs to enable global index (which is not yet supported). Either by config file (adding enable-global-index = true) or enable it by conf.EnableGlobalIndex = true in a test file.

[L-maple]

@mjonss I find the fix PR: "planner, executor: fix query partition table with global unique index get wrong result (#22478)". The logic is not comfortable for me, as global index hint would be ignored for all situations, and tableFullScan is less efficient.

I still have the following questions：

• What should I do now, cherry-pick the PR above to release-5.0 @mjonss ?
• Why 40% for global index, what else should be handled for global index, and has further plan for global index? @tiancaiamao ?
  
• If index is nonunique，then it would not be global index. It's really unsuitable. I think OceanBase's syntax is good: when create global index, specify the global explicitly. @tiancaiamao @mjonss link：https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000361568

[L-maple]

In master aa51b46 it does not select the index access, but table scan and gives correct result:

tidb> explain select * from p partition(p0) use index (idx);
+-----------------------+---------+-----------+---------------+----------------------+
| id                    | estRows | task      | access object | operator info        |
+-----------------------+---------+-----------+---------------+----------------------+
| TableReader_5         | 4.00    | root      | partition:p0  | data:TableFullScan_4 |
| └─TableFullScan_4 | 4.00    | cop[tikv] | table:p       | keep order:false     |
+-----------------------+---------+-----------+---------------+----------------------+
2 rows in set (0.00 sec)

tidb> select * from p partition(p0) use index (idx);
+------+------+
| id   | c    |
+------+------+
|    1 |    3 |
+------+------+
1 row in set (0.00 sec)

Note that to test this one needs to enable global index (which is not yet supported). Either by config file (adding enable-global-index = true) or enable it by conf.EnableGlobalIndex = true in a test file.

@mjonss master doesn't solve the problem actually, because it ignore the global index in buildDataSource and the ut is wrong. I solve the problem in my PR: #41159