partition as subquery with <> all got wrong result under dynamic pruning mode

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set tidb_partition_prune_mode='dynamic';
drop table if exists t1, t2, 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);

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 root@172.16.5.103:table_partition> 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 |
+----+----+

3. What did you see instead (Required)

MySQL root@127.0.0.1:test> 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 |
+----+----+

4. What is your TiDB version? (Required)

MySQL root@127.0.0.1:test> select tidb_version()\G
***************************[ 1. row ]***************************
tidb_version() | Release Version: v5.5.0-alpha-234-g19a020c59-dirty
Edition: Community
Git Commit Hash: 19a020c59956d1f430c41ca9d168a7417dc33490
Git Branch: master
UTC Build Time: 2022-01-27 03:45:36
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[ichn-hu]

@aytrack does this issue affects 4.0?

[aytrack]

This does not affect 4.0, dynamic mode is supported from 5.0

[ichn-hu]

ok, I'll remove may-affects-4.0 then.

[bb7133]

Change it to major severity because dynamic pruning is experimental.

[mjonss]

/unlabel may-affects-4.0