inl_merge_join and inl_hash_join have different results

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
source test.txt

select /*+ inl_merge_join(t1) */ count(*) from table_400_undef_undef t1 right join table_400_undef_undef t2 on t1. `col_int_key_signed` = t2. `col_enum_key_signed` and t1. `col_char(20)_key_signed` < t2. `col_char(20)_key_signed` where t1. `col_bigint_key_signed` != 53 order by t1.pk, t2.pk;

select /*+ inl_hash_join(t1) */ count(*) from table_400_undef_undef t1 right join table_400_undef_undef t2 on t1. `col_int_key_signed` = t2. `col_enum_key_signed` and t1. `col_char(20)_key_signed` < t2. `col_char(20)_key_signed` where t1. `col_bigint_key_signed` != 53 order by t1.pk, t2.pk;

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

mysql> select /*+ inl_merge_join(t1) */ count(*) from table_400_undef_undef t1 right join table_400_undef_undef t2 on t1. `col_int_key_signed` = t2. `col_enum_key_signed` and t1. `col_char(20)_key_signed` < t2. `col_char(20)_key_signed` where t1. `col_bigint_key_signed` != 53 order by t1.pk, t2.pk;
+----------+
| count(*) |
+----------+
|       97 |
+----------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> select /*+ inl_hash_join(t1) */ count(*) from table_400_undef_undef t1 right join table_400_undef_undef t2 on t1. `col_int_key_signed` = t2. `col_enum_key_signed` and t1. `col_char(20)_key_signed` < t2. `col_char(20)_key_signed` where t1. `col_bigint_key_signed` != 53 order by t1.pk, t2.pk;
+----------+
| count(*) |
+----------+
|       97 |
+----------+
1 row in set, 1 warning (0.00 sec)

3. What did you see instead (Required)

mysql> select /*+ inl_merge_join(t1) */ count(*) from table_400_undef_undef t1 right join table_400_undef_undef t2 on t1. `col_int_key_signed` = t2. `col_enum_key_signed` and t1. `col_char(20)_key_signed` < t2. `col_char(20)_key_signed` where t1. `col_bigint_key_signed` != 53 order by t1.pk, t2.pk;
+----------+
| count(*) |
+----------+
|       97 |
+----------+
1 row in set, 2 warnings (0.13 sec)

mysql>
mysql> select /*+ inl_hash_join(t1) */ count(*) from table_400_undef_undef t1 right join table_400_undef_undef t2 on t1. `col_int_key_signed` = t2. `col_enum_key_signed` and t1. `col_char(20)_key_signed` < t2. `col_char(20)_key_signed` where t1. `col_bigint_key_signed` != 53 order by t1.pk, t2.pk;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.05 sec)

4. What is your TiDB version? (Required)

Release Version: v5.2.0-3-gcd8fb24c5
Edition: Community
Git Commit Hash: cd8fb24c5f7ebd9d479ed228bb41848bd5e97445
Git Branch: release-5.2
UTC Build Time: 2021-09-08 10:01:42
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'backport-x.y.z',
and then remove 'needs-more-info' label.