Bug #111264 Query return wrong result

• Submitted: 3 Jun 2:37
• Modified: 6 Jun 12:14
• Reporter: Doris Li
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.33
• OS: Ubuntu (20.04.2)
• CPU Architecture: x86 (x86_64)

[3 Jun 2:37] Doris Li

Description:
select 1 from data_a_tmp where not data_a_tmp.a2;
-- Empty set (0.00 sec)

select * from (select (not data_a_tmp.a2) from data_a_tmp) t0(c0);
+------+
| c0   |
+------+
|    0 |
|    0 |
|    0 |
|    0 |
|    0 |
|    0 |
|    0 |
|    0 |
|    0 |
|    0 |
+------+
10 rows in set (0.00 sec)

These two queries should return the same result, but they don't.

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);

select 1 from data_a_tmp where not data_a_tmp.a2;
-- Empty set (0.00 sec)

select * from (select (not data_a_tmp.a2) from data_a_tmp) t0(c0);
+------+
| c0   |
+------+
|    0 |
|    0 |
|    0 |
|    0 |
|    0 |
|    0 |
|    0 |
|    0 |
|    0 |
|    0 |
+------+
10 rows in set (0.00 sec)

[3 Jun 3:46] Doris Li

These two queries should return the same result as they are equivalent.

[5 Jun 7:48] Doris Li

Please verify if it is a true bug, thx.

[6 Jun 12:14] MySQL Verification Team

Hi Mrs. Li,

This report is a duplicate of the following bug:

https://bugs.mysql.com/bug.php?id=111265