MySQL bug: NOT IN + subquery using GROUP BY HAVING returns nothing I'm querying on this table:

SKU
aaaa
bbbb
bbbb
NULL


Here's the query:

select *
from TEST as N                                      
where  N.SKU NOT IN (select SKU
                 from TEST
                 group by SKU
                 having count(*)&gt;1);


I expect the query returns 'aaaa', however, it returns nothing.

The reason I expect that is because the subquery below only returns 'bbbb':

select SKU
from TEST
group by SKU
having count(*)&gt;1   


Therefore, 'aaaa' NOT IN the subquery result.

To show the bug please copy and paste these statements in your MySQL IDE to create schema:

drop table if exists TEST;
create table TEST(
SKU varchar(255)
);
insert into TEST values('aaaa'),('bbbb'),('bbbb'),(NULL);


Even more confusing, try this new table below, re-run the query, and check result:

drop table if exists TEST;
create table TEST(
SKU varchar(255)
);
insert into TEST values('aaaa'),('cccc'),('dddd'),('bbbb'),('bbbb'),(NULL);


Expect 'aaaa', 'cccc', 'dddd' are all returned, however 'aaaa' is missing, yet 'cccc' and 'dddd' are returned.
This problem looks weird.

It reminds me of a post I wrote 7 years ago : Problem with MySQL subquery

Sometimes, values in subquery results may disappear intermittently while processing.

This problem was usually associated with non-SELECT queries involving subqueries.

This looks like something even worse. You should file a bug report on this one.