Wrong Ranges for partition pruning

[xhebox]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t;
CREATE TABLE `t` (
  `a` varchar(150) NOT NULL,
  `b` varchar(100) NOT NULL,
  `c` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`,`b`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
PARTITION BY LIST COLUMNS(`b`)
(PARTITION `p0` VALUES IN ('0'),
 PARTITION `p1` VALUES IN ('1'),
 PARTITION `p2` VALUES IN ('2'));

explain analyze select * from t where a = 'b' and b = '2';
explain analyze select * from t where a = 'b' and (b = '2');

tidb/pkg/executor/point_get.go

Lines 58 to 65 in 7569592

	if p.PrunePartitions(b.ctx) {
	// no matching partitions
	return &TableDualExec{
	BaseExecutorV2: exec.NewBaseExecutorV2(b.ctx.GetSessionVars(), p.Schema(), p.ID()),
	numDualRows: 0,
	numReturned: 0,
	}
	}

Here a safe guard is added by #49161, trying to prune and see if it succeeded.

But when there is a pair of (), it will not go through the fast path, falling back to findbesttask.

In the slow path, before partition pruning processor rule, the juding paths is already generated by

tidb/pkg/planner/core/stats.go

Lines 385 to 417 in 7569592

	func detachCondAndBuildRangeForPath(
	sctx base.PlanContext,
	path *util.AccessPath,
	conds []expression.Expression,
	histColl *statistics.HistColl,
	) error {
	if len(path.IdxCols) == 0 {
	path.TableFilters = conds
	return nil
	}
	res, err := ranger.DetachCondAndBuildRangeForIndex(sctx.GetRangerCtx(), conds, path.IdxCols, path.IdxColLens, sctx.GetSessionVars().RangeMaxSize)
	if err != nil {
	return err
	}
	path.Ranges = res.Ranges
	path.AccessConds = res.AccessConds
	path.TableFilters = res.RemainedConds
	path.EqCondCount = res.EqCondCount
	path.EqOrInCondCount = res.EqOrInCount
	path.IsDNFCond = res.IsDNFCond
	path.MinAccessCondsForDNFCond = res.MinAccessCondsForDNFCond
	path.ConstCols = make([]bool, len(path.IdxCols))
	if res.ColumnValues != nil {
	for i := range path.ConstCols {
	path.ConstCols[i] = res.ColumnValues[i] != nil
	}
	}
	path.CountAfterAccess, path.CorrCountAfterAccess, err = cardinality.GetRowCountByIndexRanges(sctx, histColl, path.Index.ID, path.Ranges)
	if path.CorrCountAfterAccess == 0 {
	path.CorrCountAfterAccess = path.CountAfterAccess
	}
	return err
	}

，which is using DetachCondAndBuildRangeForIndex.

While it should be processed by the pruning processor, and use DetachCondAndBuildRangeForPartition like

tidb/pkg/planner/core/rule_partition_processor.go

Lines 737 to 751 in 7569592

	func (l *listPartitionPruner) detachCondAndBuildRange(conds []expression.Expression, exprCols ...*expression.Column) ([]*ranger.Range, error) {
	cols := make([]*expression.Column, 0, len(exprCols))
	colLen := make([]int, 0, len(exprCols))
	for _, c := range exprCols {
	c = c.Clone().(*expression.Column)
	cols = append(cols, c)
	colLen = append(colLen, types.UnspecifiedLength)
	}
	detachedResult, err := ranger.DetachCondAndBuildRangeForPartition(l.ctx.GetRangerCtx(), conds, cols, colLen, l.ctx.GetSessionVars().RangeMaxSize)
	if err != nil {
	return nil, err
	}
	return detachedResult.Ranges, nil
	}

.

This eventually leads to the failure of that safety guard, returning table dual instead.

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

Both pruning results the same

3. What did you see instead (Required)

Different pruning results (with bad result as well, no rows would be found)

tidb> explain analyze select * from t where a = 'b' and b = '2';
+-------------+---------+---------+------+------------------------------------------------------+----------------------------------------------------------------------------------------------+---------------+--------+------+
| id          | estRows | actRows | task | access object                                        | execution info                                                                               | operator info | memory | disk |
+-------------+---------+---------+------+------------------------------------------------------+----------------------------------------------------------------------------------------------+---------------+--------+------+
| Point_Get_1 | 1.00    | 0       | root | table:t, partition:p2, clustered index:PRIMARY(a, b) | time:35µs, open:375ns, close:1.96µs, loops:1, RU:0.47, Get:{num_rpc:1, total_time:6.42µs} |               | N/A    | N/A  |
+-------------+---------+---------+------+------------------------------------------------------+----------------------------------------------------------------------------------------------+---------------+--------+------+
1 row in set (0.00 sec)

tidb> explain analyze select * from t where a = 'b' and (b = '2');
+-------------+---------+---------+------+--------------------------------------------------------+--------------------------------------------------------+---------------+--------+------+
| id          | estRows | actRows | task | access object                                          | execution info                                         | operator info | memory | disk |
+-------------+---------+---------+------+--------------------------------------------------------+--------------------------------------------------------+---------------+--------+------+
| Point_Get_5 | 1.00    | 0       | root | table:t, partition:dual, clustered index:PRIMARY(a, b) | time:1.33µs, open:83ns, close:250ns, loops:1, RU:0.00 |               | N/A    | N/A  |
+-------------+---------+---------+------+--------------------------------------------------------+--------------------------------------------------------+---------------+--------+------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

v8.5.1, but should affect all version after v8.0

[Defined2014]

And also has problem with range columns.

CREATE TABLE `t` (
  `a` varchar(150) COLLATE utf8mb4_general_ci NOT NULL,
  `b` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `c` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`,`b`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
PARTITION BY RANGE COLUMNS(`b`)
(PARTITION `p0` VALUES LESS THAN ('1'),
 PARTITION `p1` VALUES LESS THAN ('2'),
 PARTITION `p2` VALUES LESS THAN ('3'));

explain select * from t where a = 'a' and b = '2';
explain select * from t where a = 'a' and (b = '2');

[Defined2014]

The PrunePartitions function isn't a safe guard, it's for plan cache to relocated the partition.

[mjonss]

The issue seems to be that during stats/ranger code path, the range values are already converted to the columns collations sort key, which the partitioning code does not expect (it expects the original value, and will convert it self).

[mjonss]

Issue in (*PointGetPlan) PrunePartitions() is that if it comes from TryFastPlan then the IndexVals are the original values, and works, but if it came from convertToPointGet() then the IndexVals has already been converted to SortKey, which will then be converted to index SortKey again and not match any LIST partition.

[kennedy8312]

/type regression

[kennedy8312]

Regression Analysis
PR caused this regression: #49161
Related to issue/feature: #45532