partition prune has index out of range problem

[AilinKid]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (`a`)
(PARTITION `p0` VALUES LESS THAN (2021),
PARTITION `p1` VALUES LESS THAN (3000))

insert into t select * from t where a=3000;

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

Success

3. What did you see instead (Required)

tidb> insert into t select * from t where a=3000;
ERROR 1105 (HY000): runtime error: index out of range [2] with length 2

4. What is your TiDB version? (Required)

master

[lcwangchao]

Seems here has a bug:

tidb/planner/core/rule_partition_processor.go

Line 875 in 36a9810

if start > 0 && pruner.lessThan.data[start-1] == dataForPrune.c && (pruner.lessThan.data[start]-1) == dataForPrune.c {

When a condition a=xxx cannot match any partition, the start will get a value with len(pruner.lessThan.data)

[AilinKid]

Seems here has a bug:

tidb/planner/core/rule_partition_processor.go

Line 875 in 36a9810

if start > 0 && pruner.lessThan.data[start-1] == dataForPrune.c && (pruner.lessThan.data[start]-1) == dataForPrune.c {

When a condition a=xxx cannot match any partition, the start will get a value with len(pruner.lessThan.data)

Yes, better use a=x for easy reproduction. x is the upper bound of your last partition (NO MAXVALUE)

[zimulala]

@lcwangchao Do you interest to fix this issue?

[mjonss]

I think the optimization for removing a prune condition should be removed. It was added in #22079, and has reported issues: #33966.

The only time it would be beneficial is when there is only a single value mapping to a partition (like a single value RANGE or LIST partition) and it is not a part of an index. And the benefit is only one less evaluation, so I think the optimization and the added switch tidb_keep_pruned_conds should be removed.

[yiwen92]

The root cause here is for this sql-"insert into t select * from t where a=3000; " the 'a= 3000' is out of the range of defined partition value? So what will the behavior for the Minimal reproduce be like?

[mjonss]

The root cause here is for this sql-"insert into t select * from t where a=3000; " the 'a= 3000' is out of the range of defined partition value? So what will the behavior for the Minimal reproduce be like?

Since the select * from t where a=3000 would not find a matching row (due to no such partition defined), it will not return any row, so the insert would not insert any row either. The issue here was an optimization that did not handle this case, and that optimization was considered to be useful for only a very limited use case, and it was not fully implemented, which could be seen in this and other bugs.