pingcap / pingcap/tidb

planner: secondary index ranges are not extended with an unsigned int handle primary key

Open
#70,573 0 comments 0 reactions 0 assignees View on GitHub
sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

A non-unique secondary index physically stores the row handle after its declared columns, so
predicates on a clustered primary key can be turned into index ranges instead of being left as a
filter. TiDB does this for signed integer handles and for common handles, but skips it when the
clustered primary key is an **unsigned** integer handle.

The guard is in `pkg/planner/core/operator/logicalop/logical_datasource.go`:

```go
handleCol := ds.GetPKIsHandleCol()
if handleCol == nil || mysql.HasUnsignedFlag(handleCol.RetType.GetFlag()) {
return nil, nil
}
```

### Repro

```sql
CREATE TABLE t (id BIGINT UNSIGNED PRIMARY KEY CLUSTERED, a INT, b INT, KEY ia(a));

EXPLAIN SELECT b FROM t USE INDEX (ia) WHERE a = 5 AND id = 7;
```

Expected (what the signed-handle equivalent produces): `range:[5 7,5 7]`, a seek straight to the row.

Actual: `range:[5,5]`, so the whole `a = 5` group is scanned and `id = 7` is applied afterwards. The
same happens for `id > ?`, `id BETWEEN ? AND ?`, and `id IN (...)`.

### Why the guard exists

The skip is not arbitrary: an integer handle suffix is not encoded like an ordinary index column.
`tablecodec.GenIndexKey` appends it as `codec.IntHandleFlag` plus the handle reinterpreted as an
`int64`:

```go
key = append(key, codec.IntHandleFlag)
key = codec.EncodeInt(key, h.IntValue())
```

`codec.EncodeKey` emits exactly those bytes for a `KindInt64` datum, which is why signed handles
already work. An unsigned datum encodes with a different flag byte (`uintFlag`) and a different
ordering, so simply removing the guard would build key ranges that do not match the stored index
keys.

Two properties have to be handled:

1. **Encoding.** The appended dimension must be encoded in the physical handle form, not as an
unsigned column datum.
2. **Ordering.** Unsigned values above `math.MaxInt64` reinterpret as negative `int64` and therefore
sort *before* `0..MaxInt64` inside the same declared-column prefix. A predicate such as
`a = 5 AND id > 10` is not one contiguous physical range; it has to be split at the boundary, and
the index must not be treated as providing SQL order on the appended handle.

Shapes that need coverage

```sql
SELECT * FROM t USE INDEX (ia) WHERE a = 5 AND id = 7;
SELECT * FROM t USE INDEX (ia) WHERE a = 5 AND id > 10;
SELECT * FROM t USE INDEX (ia) WHERE a = 5 AND id BETWEEN 9223372036854775800 AND 9223372036854775810;
SELECT * FROM t USE INDEX (ia) WHERE a = 5 AND id >= 9223372036854775808;
SELECT * FROM t USE INDEX (ia) WHERE a = 5 AND id IN (11, 22, 9223372036854775808);
SELECT * FROM t USE INDEX (ia) WHERE a = 5 AND id > 10 ORDER BY id;
SELECT * FROM t USE INDEX (ia) WHERE a = 5 AND id > 10 ORDER BY id DESC;
```

Ranges are also rebuilt after planning by the plan cache and by index joins, so the boundary
handling cannot rely on the values seen at planning time.

Contributor guide

Open the contributing guide

Research direction

Start in pkg/planner/core/operator/logicalop/logical_datasource.go at the unsigned-handle guard, then trace the signed-handle range construction through tablecodec.GenIndexKey and codec.EncodeKey. Cover equality, comparisons, BETWEEN, IN, boundary splitting, and ORDER BY behavior from the listed queries, including ranges rebuilt by plan cache and index joins. Done means unsigned-handle index ranges match physical keys without claiming SQL ordering.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, mysql
Domain
backend, databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.