Bug #105860 UNION with JOIN USING puts results under wrong column
Submitted: 10 Dec 2021 10:17 Modified: 10 Dec 2021 13:41
Reporter: Ray Cheung Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: DML, join, UNION, USING

[10 Dec 2021 10:17] Ray Cheung
Description:
If you execute the following, you will find that a T2.T2_Val (40.4) will be mapped to a T1.T1_Val column when doing a 'JOIN USING' construct, but 'JOIN ON' works as expected. Also, 'JOIN USING' results in inaccuracy in the floating point output:

create table T1 ( Id int auto_increment primary key, T1_Val int );
create table T2 ( Id int auto_increment primary key, T2_Val float );
insert into T1 values ( 1, 1 ), ( 2, 2 ), ( 3, 3 );
insert into T2 values ( 2, 20.2 ), ( 3, 30.3 ), ( 4, 40.4 );

(  select * from T1 left join T2 using( id ) )
union
( select * from T1 right join T2 using( Id ) where T1.Id is null )
;

(  select * from T1 left join T2 on T2.Id = T1.Id )
union
( select * from T1 right join T2 on T2.Id = T1.Id where T1.Id is null )
;

How to repeat:
This same behaviour is found on Window 10 MySQL 8.0.26 as well as on AWS RDS MySQL 8.0.23.
[10 Dec 2021 13:41] MySQL Verification Team
Hi Mr. Cheung,

Thank you for your bug report.

We managed to repeat the behaviour on the latest 8.0:

Id	t1_Val	t2_Val
1	1	NULL
2	2	20.200000762939453
3	3	30.299999237060547
4	40.400001525878906	NULL
Id	t1_Val	Id	t2_Val
1	1	NULL	NULL
2	2	2	20.2
3	3	3	30.3
NULL	NULL	4	40.4

This bug shows up in both UNIONs, the distinct one and non-distinct one ......

Verified as reported.