wrong join result of set type

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists IDT_MULTI15955STROBJSTROBJ;
CREATE TABLE `IDT_MULTI15955STROBJSTROBJ` (  `COL1` set("A","B","C","") DEFAULT NULL,  `COL2` decimal(20,0) DEFAULT NULL,  `COL3` timestamp NULL DEFAULT NULL,  KEY `U_M_COL4` (`COL1`,`COL2`),  KEY `U_M_COL5` (`COL3`,`COL2`));
insert into IDT_MULTI15955STROBJSTROBJ(col1, col2) values (1, 1), (2, 1), (3, 1), (4, 1), (5, 2), (6, 2), (7, 2), (8, 2), (9, 3), (10, 3), (11, 3), (12, 3), (13, 4);
select t1.col1, t1.col2, t2.col1, t2.col2 from IDT_MULTI15955STROBJSTROBJ t1 join IDT_MULTI15955STROBJSTROBJ t2 on t1.col1 = t2.col1 and t1.col2 = t2.col2;

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

+-------+------+-------+------+
| col1  | col2 | col1  | col2 |
+-------+------+-------+------+
| A     | 1    | A     | 1    |
| B     | 1    | B     | 1    |
| A,B   | 1    | A,B   | 1    |
| C     | 1    | C     | 1    |
| A,C   | 2    | A,C   | 2    |
| B,C   | 2    | B,C   | 2    |
| A,B,C | 2    | A,B,C | 2    |
|       | 2    |       | 2    |
| A,    | 3    | A,    | 3    |
| B,    | 3    | B,    | 3    |
| A,B,  | 3    | A,B,  | 3    |
| C,    | 3    | C,    | 3    |
| A,C,  | 4    | A,C,  | 4    |
+-------+------+-------+------+

3. What did you see instead (Required)

+------+------+------+------+
| col1 | col2 | col1 | col2 |
+------+------+------+------+
| A    | 1    | A    | 1    |
| B    | 1    | B    | 1    |
| C    | 1    | C    | 1    |
| C,   | 3    | C,   | 3    |
+------+------+------+------+

4. What is your TiDB version? (Required)

master: ea1a1e7
release-5.1: 7784454

[aytrack]

more join cases
tidb：

MySQL root@127.0.0.1:test> select t1.col1, t1.col2, t2.col1, t2.col2 from IDT_MULTI15955STROBJSTROBJ  t1 right join IDT_MULTI15955STROBJSTROBJ  t2 on t1.col1 = t2.col1;
+--------+--------+-------+------+
| col1   | col2   | col1  | col2 |
+--------+--------+-------+------+
| A      | 1      | A     | 1    |
| B      | 1      | B     | 1    |
| <null> | <null> | A,B   | 1    |
| C      | 1      | C     | 1    |
| <null> | <null> | A,C   | 2    |
| <null> | <null> | B,C   | 2    |
| <null> | <null> | A,B,C | 2    |
| <null> | <null> |       | 2    |
| <null> | <null> | A,    | 3    |
| <null> | <null> | B,    | 3    |
| <null> | <null> | A,B,  | 3    |
| C,     | 3      | C,    | 3    |
| <null> | <null> | A,C,  | 4    |
+--------+--------+-------+------+


MySQL root@127.0.0.1:test> select t1.col1, t1.col2, t2.col1, t2.col2 from IDT_MULTI15955STROBJSTROBJ  t1 left join IDT_MULTI15955STROBJSTROBJ  t2 on t1.col1 = t2.col1;
+-------+------+--------+--------+
| col1  | col2 | col1   | col2   |
+-------+------+--------+--------+
| A     | 1    | A      | 1      |
| B     | 1    | B      | 1      |
| A,B   | 1    | <null> | <null> |
| C     | 1    | C      | 1      |
| A,C   | 2    | <null> | <null> |
| B,C   | 2    | <null> | <null> |
| A,B,C | 2    | <null> | <null> |
|       | 2    | <null> | <null> |
| A,    | 3    | <null> | <null> |
| B,    | 3    | <null> | <null> |
| A,B,  | 3    | <null> | <null> |
| C,    | 3    | C,     | 3      |
| A,C,  | 4    | <null> | <null> |
+-------+------+--------+--------+

tidb:

MySQL test>  select t1.col1, t1.col2, t2.col1, t2.col2 from IDT_MULTI15955STROBJSTROBJ  t1 left join IDT_MULTI15955STROBJSTROBJ  t2 on t1.col1 = t2.col1;
+-------+------+-------+------+
| col1  | col2 | col1  | col2 |
+-------+------+-------+------+
| A     | 1    | A     | 1    |
| B     | 1    | B     | 1    |
| A,B   | 1    | A,B   | 1    |
| C     | 1    | C     | 1    |
| A,C   | 2    | A,C   | 2    |
| B,C   | 2    | B,C   | 2    |
| A,B,C | 2    | A,B,C | 2    |
|       | 2    |       | 2    |
| A,    | 3    | A,    | 3    |
| B,    | 3    | B,    | 3    |
| A,B,  | 3    | A,B,  | 3    |
| C,    | 3    | C,    | 3    |
| A,C,  | 4    | A,C,  | 4    |
+-------+------+-------+------+

13 rows in set
Time: 1.406s
MySQL test>  select t1.col1, t1.col2, t2.col1, t2.col2 from IDT_MULTI15955STROBJSTROBJ  t1 right join IDT_MULTI15955STROBJSTROBJ  t2 on t1.col1 = t2.col1;
+-------+------+-------+------+
| col1  | col2 | col1  | col2 |
+-------+------+-------+------+
| A     | 1    | A     | 1    |
| B     | 1    | B     | 1    |
| A,B   | 1    | A,B   | 1    |
| C     | 1    | C     | 1    |
| A,C   | 2    | A,C   | 2    |
| B,C   | 2    | B,C   | 2    |
| A,B,C | 2    | A,B,C | 2    |
|       | 2    |       | 2    |
| A,    | 3    | A,    | 3    |
| B,    | 3    | B,    | 3    |
| A,B,  | 3    | A,B,  | 3    |
| C,    | 3    | C,    | 3    |
| A,C,  | 4    | A,C,  | 4    |
+-------+------+-------+------+

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions