2

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(*)>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(*)>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.

Shawn
  • 21
  • 2

2 Answers2

2

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.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

This works OK, if subquery returns only one row:

SELECT * 
FROM TEST as N 
WHERE N.SKU NOT IN (
  CAST((select SKU from TEST group by SKU having count(*)>1) AS CHAR)
)

However, if it returns more then 1 row, error will be triggered. Another solution (if the subquery returns more than 1 row):

SELECT * 
FROM TEST as N 
WHERE N.SKU IN (
  (select SKU from TEST group by SKU having count(*) <= 1)
)
Boolean_Type
  • 101
  • 2