Bug #2838 query which AND's two nested queries returns all rows in a table instead of 0
Submitted: 17 Feb 2004 6:15 Modified: 16 Mar 2004 2:09
Reporter: SINISA MILIVOJEVIC Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.2 OS:Any (any)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[17 Feb 2004 6:15] SINISA MILIVOJEVIC 
Description:
Query  has two conditions tied to a single column.

Of they type: 

where x =  3 and x = 5

returns all rows in the table instead of the empty set.

How to repeat:
create table t1(val varchar(10));
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
select count(*) from t1 as w1 where w1.val in (select w2.val from t1 as w2 where w2.val like 'm%');
select count(*) from t1 as w1 where w1.val in (select w3.val from t1 as w3 where w3.val like 'e%');
select count(*) from t1 as w1 where w1.val in (select w2.val from t1 as w2 where w2.val like 'm%') and w1.val in (select w3.val from t1 as w3 where w3.val like 'e%');
drop table if exists t1;
[17 Feb 2004 15:10] Oleksandr Byelkin 
ChangeSet 
  1.1717 04/02/18 01:08:52 bell@sanja.is.com.ua +13 -0 
  revision of fix_fields() calls (BUG2838)
[16 Mar 2004 2:09] Oleksandr Byelkin 
Thank you for bugreport. Patch for this bug is pushed to internal repository