partition table does not return the right rows by quering by _tidb_rowid

[lcwangchao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

> create table tt(id int) PARTITION BY LIST(id) (partition p0 values in (0));
> insert into tt values(0),(0);
> select _tidb_rowid from tt;
+-------------+
| _tidb_rowid |
+-------------+
| 1           |
| 2           |
+-------------+
2 rows in set
Time: 0.007s
> select * from tt where _tidb_rowid=1;

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

select * from tt where _tidb_rowid=1 should return one row.

3. What did you see instead (Required)

> select * from tt where _tidb_rowid=1;
+----+
| id |
+----+
+----+
0 rows in set
Time: 0.007s

4. What is your TiDB version? (Required)

master

[lcwangchao]

The plan is wrong:

> desc select * from tt where _tidb_rowid=1;
+-------------+---------+------+--------------------------+---------------+
| id          | estRows | task | access object            | operator info |
+-------------+---------+------+--------------------------+---------------+
| Point_Get_1 | 1.00    | root | table:tt, partition:dual | handle:1      |
+-------------+---------+------+--------------------------+---------------+
1 row in set
Time: 0.005s

[Defined2014]

partition table + _tidb_rowid shoudn't support Point_Get, because we can't use _tidb_rowid to locate a partition.

[lcwangchao]

partition table + _tidb_rowid shoudn't support Point_Get, because we can't use _tidb_rowid to locate a partition.

TTL is using delete from tt partition(p0) where _tidb_rowid in (...) to delete rows. I think it is reasonable to support the point get here because partition is specified.

[Defined2014]

The problem is caused by https://github.com/pingcap/tidb/pull/49161/files#diff-7ab669fbd07f777ce6a8b33025e95960e50d6c6be00a0781d33aafca2150bf6bL2585.

So do you have any comment about this issue? @mjonss

[mjonss]

https://github.com/pingcap/tidb/pull/49161/files#diff-7ab669fbd07f777ce6a8b33025e95960e50d6c6be00a0781d33aafca2150bf6bL2585

So do you have any comment about this issue? @mjonss

@Defined2014 Did it work before? How?

I think it could work if also the partition is specified, like SELECT * FROM t PARTITION (p) WHERE _tidb_rowid = N
But I don't really see it useful.
When is 'WHERE _tidb_rowid =` used? If mostly for debugging etc. I'm OK with not allowing it as Point_Select for partitioned tables.

[Defined2014]

@Defined2014 Did it work before? How?

Yes. Before this PR, we do prune in planner phase and if remained more than one partition, we will forbid PointGet plan.

I think it could work if also the partition is specified, like SELECT * FROM t PARTITION (p) WHERE _tidb_rowid = N But I don't really see it useful.

I agree with it.

When is 'WHERE _tidb_rowid =` used? If mostly for debugging etc. I'm OK with not allowing it as Point_Select for partitioned tables.

TTL will use it to delete rows. But I think tableRangeScan maybe acceptable.

[mjonss]

TTL will use it to delete rows. But I think tableRangeScan maybe acceptable.

There may be duplicates of _tidb_rowid, so I don't think it is acceptable, since it cannot distinguish which row (i.e. from which partition) to delete. If it still using the explicit partition selection FROM t PARTITION(p) is it OK.

[Defined2014]

TTL will use it to delete rows. But I think tableRangeScan maybe acceptable.

There may be duplicates of _tidb_rowid, so I don't think it is acceptable, since it cannot distinguish which row (i.e. from which partition) to delete. If it still using the explicit partition selection FROM t PARTITION(p) is it OK.

Yeah, it will explicit selection FROM t PARTITION(p). The acceptable is about performance of TableRangeScan.