Bug #111265 Return wrong result

• Submitted: 3 Jun 2023 3:40
• Modified: 6 Jun 2023 12:10
• Reporter: Doris Li
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: Ubuntu (20.04.2)
• CPU Architecture: x86 (x86_64)

[3 Jun 2023 3:40] Doris Li

Description:
select 1 from data_b_tmp right join data_a_tmp using(a3) where char(data_b_tmp.a3) > -1;
Empty set (0.00 sec)

select 1 from data_b_tmp right join data_a_tmp using(a3);
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+---+
11 rows in set (0.00 sec)

select * from data_b_tmp;
Empty set (0.00 sec)

since data_b_tmp is empty, these two queries should return the same result. The first query return the wrong result.

How to repeat:
create table data_a_tmp(a1 float, a2 float, a3 float, a4 float);
insert into data_a_tmp(a1, a2, a3, a4) values (6.97,2.13,4.5,4.0);
insert into data_a_tmp(a1, a2, a3, a4) values (9.52,4.87,5.33,7.21);
insert into data_a_tmp(a1, a2, a3, a4) values (4.69,3.82,5.66,7.22);
insert into data_a_tmp(a1, a2, a3, a4) values (3.8,7.55,5.18,7.82);
insert into data_a_tmp(a1, a2, a3, a4) values (9.95,5.0,5.26,6.09);
insert into data_a_tmp(a1, a2, a3, a4) values (3.98,6.8,5.93,6.8);
insert into data_a_tmp(a1, a2, a3, a4) values (3.3,5.89,5.44,6.22);
insert into data_a_tmp(a1, a2, a3, a4) values (2.07,7.09,3.01,6.82);
insert into data_a_tmp(a1, a2, a3, a4) values (4.98,4.9,3.04,5.11);
insert into data_a_tmp(a1, a2, a3, a4) values (8.98,8.46,5.93,4.28);
insert into data_a_tmp(a1, a2, a3, a4) values (6.97,2.13,4.5,NULL);

create table data_b_tmp(b1 float, b2 float, a3 float);

[3 Jun 2023 3:43] Doris Li

select 1 from (select char(data_b_tmp.a3) > -1  from data_b_tmp right join data_a_tmp using (a3)) tmp(a3);
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+---+
11 rows in set (0.00 sec)
This query also return the correct result.

[5 Jun 2023 7:49] Doris Li

Please verify if it is a true bug, thx.

[6 Jun 2023 12:10] MySQL Verification Team

Hi Mrs. Li,

Thank you for your bug report.

However, it is not a bug.

You can not compare floating point values with equality operator ('=') or with USING. You can do that with INTeger values. For floating point types, you have to check the values in tuples within a range.

This is explained in our Reference Manual, in the section on Data Types.

Not a bug.