the result returned doesn't exist 't2.a' column

[ChenPeng2013]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t1 (
  grp int(11) default NULL,
  a bigint(20) unsigned default NULL,
  c char(10) NOT NULL default ''
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,1,'a'),(2,2,'b'),(2,3,'c'),(3,4,'E'),(3,5,'C'),(3,6,'D'),(NULL,NULL,'');
create table t2 (id int, a bigint unsigned not null, c char(10), d int, primary key (a));
insert into t2 values (1,1,"a",1),(3,4,"A",4),(3,5,"B",5),(3,6,"C",6),(4,7,"D",7);
select t1.*, t2.* from t1 left join t2 using(a);

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

mysql 8.0.14

mysql> select t1.*, t2.* from t1 left join t2 using(a);
+------+------+---+------+------+------+------+
| grp  | a    | c | id   | a    | c    | d    |
+------+------+---+------+------+------+------+
|    1 |    1 | a |    1 |    1 | a    |    1 |
|    2 |    2 | b | NULL | NULL | NULL | NULL |
|    2 |    3 | c | NULL | NULL | NULL | NULL |
|    3 |    4 | E |    3 |    4 | A    |    4 |
|    3 |    5 | C |    3 |    5 | B    |    5 |
|    3 |    6 | D |    3 |    6 | C    |    6 |
| NULL | NULL |   | NULL | NULL | NULL | NULL |
+------+------+---+------+------+------+------+
7 rows in set (0.01 sec)

3. What did you see instead (Required)

mysql> select t1.*, t2.* from t1 left join t2 using(a);
+------+------+---+------+------+------+
| a    | grp  | c | id   | c    | d    |
+------+------+---+------+------+------+
|    1 |    1 | a |    1 | a    |    1 |
|    2 |    2 | b | NULL | NULL | NULL |
|    3 |    2 | c | NULL | NULL | NULL |
|    4 |    3 | E |    3 | A    |    4 |
|    5 |    3 | C |    3 | B    |    5 |
|    6 |    3 | D |    3 | C    |    6 |
| NULL | NULL |   | NULL | NULL | NULL |
+------+------+---+------+------+------+
7 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

master cd16de8 and release-4.0 c710000

SIG slack channel

#sig-exec

Score

• 900

Mentor

• @lzmhhh123

[lhy1024]

/label sig/execution

[asiafrank]

/pick-up

[ti-challenge-bot]

Pick up success.

[asiafrank]

/give-up

[ti-challenge-bot]

Give up success.

[XuHuaiyu]

/pick-up

[ti-challenge-bot]

Pick up success.

[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