subquery with explicit partition selection

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
drop table if exists t1, t3;
create table t1 (a int, b tinyint, primary key (a)) partition by range (a) (
partition p0 values less than (5),
partition p1 values less than (20),
partition p2 values less than (30),
partition p3 values less than (40),
partition p4 values less than MAXVALUE
);
insert into t1 values (0, 0), (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (10, 10), (11, 11), (12, 12), (13, 13), (14, 14), (15, 15), (20, 20), (21, 21), (22, 22), (23, 23), (24, 24), (25, 25), (30, 30), (31, 31), (32, 32), (33, 33), (34, 34), (35, 35), (36, 36), (40, 40), (50, 50), (80, 80), (90, 90), (100, 100);
create table t3 (a int, b mediumint, primary key (a));
insert into t3 values (0, 0), (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10), (11, 11), (12, 12), (13, 13), (14, 14), (15, 15), (16, 16), (17, 17), (18, 18), (19, 19), (20, 20), (21, 21), (22, 22), (23, 23);

set tidb_partition_prune_mode=static;
select * from t3 where t3.a <> ALL (select t1.a from t1 partition (p0)) order by t3.a;

set tidb_partition_prune_mode=dynamic;
select * from t3 where t3.a <> ALL (select t1.a from t1 partition (p0)) order by t3.a;

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

mysql> set tidb_partition_prune_mode=static;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t3 where t3.a <> ALL (select t1.a from t1 partition (p0)) order by t3.a;
+----+------+
| a  | b    |
+----+------+
|  5 |    5 |
|  6 |    6 |
|  7 |    7 |
|  8 |    8 |
|  9 |    9 |
| 10 |   10 |
| 11 |   11 |
| 12 |   12 |
| 13 |   13 |
| 14 |   14 |
| 15 |   15 |
| 16 |   16 |
| 17 |   17 |
| 18 |   18 |
| 19 |   19 |
| 20 |   20 |
| 21 |   21 |
| 22 |   22 |
| 23 |   23 |
+----+------+
19 rows in set (0.00 sec)

mysql>
mysql> set tidb_partition_prune_mode=dynamic;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t3 where t3.a <> ALL (select t1.a from t1 partition (p0)) order by t3.a;
+----+------+
| a  | b    |
+----+------+
|  5 |    5 |
|  6 |    6 |
|  7 |    7 |
|  8 |    8 |
|  9 |    9 |
| 10 |   10 |
| 11 |   11 |
| 12 |   12 |
| 13 |   13 |
| 14 |   14 |
| 15 |   15 |
| 16 |   16 |
| 17 |   17 |
| 18 |   18 |
| 19 |   19 |
| 20 |   20 |
| 21 |   21 |
| 22 |   22 |
| 23 |   23 |
+----+------+
19 rows in set (0.00 sec)

3. What did you see instead (Required)

release-5.0 & release-5.1 & master

mysql> set tidb_partition_prune_mode=static;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t3 where t3.a <> ALL (select t1.a from t1 partition (p0)) order by t3.a;
+----+------+
| a  | b    |
+----+------+
|  5 |    5 |
|  6 |    6 |
|  7 |    7 |
|  8 |    8 |
|  9 |    9 |
| 10 |   10 |
| 11 |   11 |
| 12 |   12 |
| 13 |   13 |
| 14 |   14 |
| 15 |   15 |
| 16 |   16 |
| 17 |   17 |
| 18 |   18 |
| 19 |   19 |
| 20 |   20 |
| 21 |   21 |
| 22 |   22 |
| 23 |   23 |
+----+------+
19 rows in set (0.00 sec)

mysql>
mysql> set tidb_partition_prune_mode=dynamic;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t3 where t3.a <> ALL (select t1.a from t1 partition (p0)) order by t3.a;
+----+------+
| a  | b    |
+----+------+
|  8 |    8 |
|  9 |    9 |
| 16 |   16 |
| 17 |   17 |
| 18 |   18 |
| 19 |   19 |
+----+------+
6 rows in set (0.01 sec)

4. What is your TiDB version? (Required)

Release Version: v5.1.1
Edition: Community
Git Commit Hash: 797bddd25310ed42f0791c8eccb78be8cce2f502
Git Branch: release-5.1
UTC Build Time: 2021-07-30 08:46:42
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[qw4990]

dynamic-mode is an experimental feature, so I degrade its severity to moderate and I'll fix it next sprint.

[mjonss]

/label component/tablepartition

[ti-chi-bot]

@mjonss: The label(s) component/tablepartition 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-3.0, needs-cherry-pick-3.1, 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, affects-4.0, affects-5.0, affects-5.1, affects-5.2, affects-5.3, backport-4.0.14, backport-4.0.15, backport-5.0.3, backport-5.0.4, backport-5.1.0, backport-5.1.1, backport-5.1.2.

In response to this:

/label component/tablepartition

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.

[mjonss]

/component tablepartition

[mjonss]

Duplicate of issue #32007

[mjonss]

/close

[ti-chi-bot]

@mjonss: Closing this issue.

In response to this:

/close

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 kubernetes/test-infra repository.