got wrong result because of choosing wrong partition

[XuHuaiyu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

drop table if exists t1, t2, t3;
create table t1 (a int, b tinyint)  partition by range (a) (
    partition p0 values less than (10),
    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(NULL, NULL), (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 t2 (a int, b bigint) partition by hash(a) partitions 10;
insert into t2 values (NULL, NULL), (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);

create table t3 (a int, b mediumint);
insert into t3 values (NULL, NULL), (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 /*+ HASH_JOIN(t1, t2) */ * from t1 partition (p0) left join t2 partition (p1) on t1.a = t2.a where t1.a = 6 order by t1.a, t1.b, t2.a, t2.b;

2. What did you expect to see?

+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    6 |    6 | NULL | NULL |
+------+------+------+------+
1 row in set, 1 warning (0.00 sec)

3. What did you see instead?

+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    6 |    6 |    6 |    6 |
+------+------+------+------+
1 row in set (0.00 sec)

4. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

tidb> select tidb_version();                                                        +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-beta.2-343-ge607157ef
Git Commit Hash: e607157efef0f76878d18a667b233061ce43545a
Git Branch: master
UTC Build Time: 2020-04-26 07:46:48
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[XuHuaiyu]

select /*+ HASH_JOIN(t1, t2) */ * from t2 partition (p1) left join t1 partition (p0) on t2.a = t1.a where t2.a = 6 order by t1.a, t1.b, t2.a, t2.b;

tidb got:

+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    6 |    6 |    6 |    6 |
+------+------+------+------+
1 row in set (0.00 sec)

mysql got:

Empty set, 1 warning (0.00 sec)