invalid plan for index on generated column

[lysu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t` (
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `v` int(11) DEFAULT NULL,
  `create_at` timestamp NULL DEFAULT NULL,
  `tidb_hidden_pk_shard` bigint(20) GENERATED ALWAYS AS ((vitess_hash(cast(`id1` as char)) + vitess_hash(cast(`id2` as char))) % 8) STORED NOT NULL,
  UNIQUE KEY `tidb_hidden_pk_shard` (`tidb_hidden_pk_shard`,`id1`,`id2`)
);

select * from t where (id1, id2) in ((1, 1))

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

use tidb_hidden_pk_shard

3. What did you see instead (Required)

explain select * from t where (id1, id2) in ((1, 1));
+-------------------------+----------+-----------+---------------+--------------------------------------+
| id                      | estRows  | task      | access object | operator info                        |
+-------------------------+----------+-----------+---------------+--------------------------------------+
| TableReader_7           | 0.01     | root      |               | data:Selection_6                     |
| └─Selection_6           | 0.01     | cop[tikv] |               | eq(test.t.id1, 1), eq(test.t.id2, 1) |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo       |
+-------------------------+----------+-----------+---------------+--------------------------------------+
3 rows in set (0.02 sec)

4. What is your TiDB version? (Required)

master

[lysu]

mysql> CREATE TABLE `t1` (
    ->   `id1` int(11) NOT NULL,
    ->   `v` int(11) DEFAULT NULL,
    ->   `create_at` timestamp NULL DEFAULT NULL,
    ->   `tidb_hidden_pk_shard` bigint(20) GENERATED ALWAYS AS (vitess_hash(cast(`id1` as char)) % 8) STORED NOT NULL,
    ->   UNIQUE KEY `tidb_hidden_pk_shard` (`tidb_hidden_pk_shard`,`id1`)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1 where id1 = 1;
Empty set (0.00 sec)

mysql> explain select * from t1 where id1 = 1;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 10.00    | root      |               | data:Selection_6               |
| └─Selection_6           | 10.00    | cop[tikv] |               | eq(test.t1.id1, 1)             |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

[ti-chi-bot]

@wanhanbo: The label(s) type/bug cannot be applied. These labels are supported: challenge-program, compatibility-breaker, first-time-contributor, contribution, require-LGT3, good first issue, correctness, duplicate, proposal, security, needs-more-info, needs-cherry-pick-4.0, needs-cherry-pick-5.0, needs-cherry-pick-5.1, needs-cherry-pick-5.2, needs-cherry-pick-5.3, needs-cherry-pick-5.4, affects-4.0, affects-5.0, affects-5.1, affects-5.2, affects-5.3, affects-5.4, may-affects-4.0, may-affects-5.0, may-affects-5.1, may-affects-5.2, may-affects-5.3, may-affects-5.4.

In response to this:

/label type/bug

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[ti-chi-bot]

@wanhanbo: The label(s) type/bug cannot be applied. These labels are supported: challenge-program, compatibility-breaker, first-time-contributor, contribution, require-LGT3, good first issue, correctness, duplicate, proposal, security, needs-more-info, needs-cherry-pick-4.0, needs-cherry-pick-5.0, needs-cherry-pick-5.1, needs-cherry-pick-5.2, needs-cherry-pick-5.3, needs-cherry-pick-5.4, affects-4.0, affects-5.0, affects-5.1, affects-5.2, affects-5.3, affects-5.4, may-affects-4.0, may-affects-5.0, may-affects-5.1, may-affects-5.2, may-affects-5.3, may-affects-5.4.

In response to this:

/label type/bug

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[wanhanbo]

/label needs-more-info

[ti-chi-bot]

@wanhanbo: The label(s) needs-more-info cannot be applied, because the repository doesn't have them.

In response to this:

/label needs-more-info

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[wanhanbo]

/assign

[wanhanbo]

/label good-first-issue

[ti-chi-bot]

@wanhanbo: The label(s) good-first-issue cannot be applied. These labels are supported: challenge-program, compatibility-breaker, first-time-contributor, contribution, require-LGT3, good first issue, correctness, duplicate, proposal, security, needs-more-info, needs-cherry-pick-4.0, needs-cherry-pick-5.0, needs-cherry-pick-5.1, needs-cherry-pick-5.2, needs-cherry-pick-5.3, needs-cherry-pick-5.4, affects-4.0, affects-5.0, affects-5.1, affects-5.2, affects-5.3, affects-5.4, may-affects-4.0, may-affects-5.0, may-affects-5.1, may-affects-5.2, may-affects-5.3, may-affects-5.4.

In response to this:

/label good-first-issue

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[wanhanbo]

/remove-type new-feature

[wanhanbo]

/type bug

[wanhanbo]

/unassign

[tiancaiamao]

As far as I know, the purpose of this query is to scatter the hotspot index using generated column.
Now we have a tidb_shard function designed for this scenario #31040, so I think it's ok to close this issue.

mysql> create table t1 (id int, hidden int as (tidb_shard(id)), unique key(hidden, id));

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `hidden` int(11) GENERATED ALWAYS AS (tidb_shard(`id`)) VIRTUAL,
  UNIQUE KEY `hidden` (`hidden`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from t1 where id = 5;
+-------------+---------+------+------------------------------------+---------------+
| id          | estRows | task | access object                      | operator info |
+-------------+---------+------+------------------------------------+---------------+
| Point_Get_5 | 1.00    | root | table:t1, index:hidden(hidden, id) |               |
+-------------+---------+------+------------------------------------+---------------+
1 row in set (0.00 sec)

mysql> explain select * from t1 where id in (5, 10);
+-------------------+---------+------+------------------------------------+------------------------------+
| id                | estRows | task | access object                      | operator info                |
+-------------------+---------+------+------------------------------------+------------------------------+
| Batch_Point_Get_5 | 2.00    | root | table:t1, index:hidden(hidden, id) | keep order:false, desc:false |
+-------------------+---------+------+------------------------------------+------------------------------+
1 row in set (0.00 sec)