Description:
When using DISTINCT in a subquery that involves a logical AND operation, and then filtering the result with IS NOT NULL, MySQL returns an incorrect result set.
The expected behavior is that NULL values should be excluded from the final output. However, NULL is still present in the query result, which seems to be incorrect.
How to repeat:
Create Test Table and Insert Sample Data:
create table t0 (
c_0 int,
c_1 int
);
insert into t0 values (1,NULL);
insert into t0 values (1,0);
insert into t0 values (1,1);
Execute the Following Query:
select
subq_0.c_0 as c_0
from
(select distinct ((ref_0.c_0) and (ref_0.c_1)) AS c_0 from (t0 ref_0))
as subq_0
where ((subq_0.c_0) is not null);
Expected Result:
+------+
| c_0 |
+------+
| 0 |
| 1 |
+------+
2 rows in set (0.01 sec)
Actual Result:
+------+
| c_0 |
+------+
| NULL |
| 0 |
| 1 |
+------+
3 rows in set (0.01 sec)
This result is incorrect because NULL should not be present when we apply IS NOT NULL in the WHERE clause.
Description: When using DISTINCT in a subquery that involves a logical AND operation, and then filtering the result with IS NOT NULL, MySQL returns an incorrect result set. The expected behavior is that NULL values should be excluded from the final output. However, NULL is still present in the query result, which seems to be incorrect. How to repeat: Create Test Table and Insert Sample Data: create table t0 ( c_0 int, c_1 int ); insert into t0 values (1,NULL); insert into t0 values (1,0); insert into t0 values (1,1); Execute the Following Query: select subq_0.c_0 as c_0 from (select distinct ((ref_0.c_0) and (ref_0.c_1)) AS c_0 from (t0 ref_0)) as subq_0 where ((subq_0.c_0) is not null); Expected Result: +------+ | c_0 | +------+ | 0 | | 1 | +------+ 2 rows in set (0.01 sec) Actual Result: +------+ | c_0 | +------+ | NULL | | 0 | | 1 | +------+ 3 rows in set (0.01 sec) This result is incorrect because NULL should not be present when we apply IS NOT NULL in the WHERE clause.