Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Repeatable in maria-5.3 and maria-5.3-mwl90 regardless of the switch. Not reproducible on maria-5.2 and mysql-5.5
The following query:
SELECT * FROM t1 WHERE EXISTS ( SELECT f1 FROM t2 WHERE t1.f1 = t2.f1 );
Returns rows even though the tables contain only NULL values and for no rows the equality t1.f1 = t2.f1 is TRUE. The problem goes away if the tables have 1 row or if IGNORE INDEX is used to prevent the index on f1 from being used.
explain:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL f1 4 NULL 2 Using where; Using index
2 DEPENDENT SUBQUERY t2 ref f1 f1 4 test.t1.f1 2 Using index
test case:
CREATE TABLE t1 ( f1 varchar(1) , KEY (f1)) ;
INSERT IGNORE INTO t1 VALUES (NULL),(NULL);
CREATE TABLE t2 ( f1 varchar(1) , KEY (f1)) ;
INSERT IGNORE INTO t2 VALUES (NULL),(NULL);
SELECT *
FROM t1
WHERE EXISTS (
SELECT f1
FROM t2
WHERE t1.f1 = t2.f1
);
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 729039