pingcap / pingcap/tidb

point query on unique index will make estimation row count inconsistent

Open
#55,569 1 comment 0 reactions 0 assignees View on GitHub
affects-6.5 affects-7.1 affects-8.1 epic/cardinality-estimation severity/moderate sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

create a table, inserting some rows. Make sure that there's modify_count after analyze.

Run a query that contains PointGet in normal execution.
like
```
CREATE TABLE `t` (
`a` varchar(20) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */
);

explain select /*+ HASH_JOIN(t1, t2) */ * from t t1, t t2 where t1.c=t2.c and t1.a='1' and t1.b > 1;
```

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

```
mysql> explain select /*+ HASH_JOIN(t1, t2) */ * from t t1, t t2 where t1.c=t2.c and t1.a='1' and t1.b > 1;
+-----------------------------+---------+-----------+-------------------------------------+--------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------+---------+-----------+-------------------------------------+--------------------------------------------+
| HashJoin_9 | 1.00 | root | | inner join, equal:[eq(test.t.c, test.t.c)] |
| ├─Selection_11(Build) | 1.00 | root | | gt(test.t.b, 1), not(isnull(test.t.c)) |
| │ └─Point_Get_10 | 1.00 | root | table:t, clustered index:PRIMARY(a) | |
| └─TableReader_14(Probe) | 1536.00 | root | | data:Selection_13 |
| └─Selection_13 | 1536.00 | cop[tikv] | | not(isnull(test.t.c)) |
| └─TableFullScan_12 | 1536.00 | cop[tikv] | table:t2 | keep order:false |
+-----------------------------+---------+-----------+-------------------------------------+--------------------------------------------+
6 rows in set (0.00 sec)
```

### 3. What did you see instead (Required)

```
mysql> explain select /*+ HASH_JOIN(t1, t2) */ * from t t1, t t2 where t1.c=t2.c and t1.a='1' and t1.b > 1;
+-----------------------------+---------+-----------+-------------------------------------+--------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------+---------+-----------+-------------------------------------+--------------------------------------------+
| HashJoin_9 | 1.50 | root | | inner join, equal:[eq(test.t.c, test.t.c)] |
| ├─Selection_11(Build) | 1.50 | root | | gt(test.t.b, 1), not(isnull(test.t.c)) |
| │ └─Point_Get_10 | 1.00 | root | table:t, clustered index:PRIMARY(a) | |
| └─TableReader_14(Probe) | 1536.00 | root | | data:Selection_13 |
| └─Selection_13 | 1536.00 | cop[tikv] | | not(isnull(test.t.c)) |
| └─TableFullScan_12 | 1536.00 | cop[tikv] | table:t2 | keep order:false |
+-----------------------------+---------+-----------+-------------------------------------+--------------------------------------------+
6 rows in set (0.00 sec)
```
Selection_11 got an estimation larger than PointGet_10

### 4. What is your TiDB version? (Required)

master still has this problem.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.