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
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• 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.