Bug #2838 query which AND's two nested queries returns all rows in a table instead of 0
Submitted: 17 Feb 2004 7:15 Modified: 16 Mar 2004 3:09
Reporter: Sinisa Milivojevic
Status: Closed
Category:Server Severity:S2 (Serious)
Version:4.1.2 OS:Any (any)
Assigned to: Oleksandr Byelkin Target Version:

[17 Feb 2004 7: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 16: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 3:09] Oleksandr Byelkin 
Thank you for bugreport. Patch for this bug is pushed to internal repository