Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Reproducible in maria-5.3, maria-5.3-mwl89 regardless of switch. Not reproducible in maria-5.2, mysql-5.5
The following query:
SELECT STRAIGHT_JOIN *
FROM t1
JOIN t2 ON t2.f2 = t1.f1
WHERE ( t1.f1 ) IN ( SELECT f1 FROM t1 )
AND t1.f1 = t2.f1
OR t1.f1 = 9;
returns no rows even though there is one row for which t2.f2 = t1.f1 and t1.f1 = 9 is TRUE , hence the entire WHERE clause is true.
I was unable to remove STRAIGHT_JOIN even if I rotated the places of t1 and t2.
EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 2 Using where
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index
test case:
CREATE TABLE t1 ( f1 int, f2 int, PRIMARY KEY (f1)) ;
INSERT IGNORE INTO t1 VALUES ('9','4'),('10','9');
CREATE TABLE t2 ( f1 int, f2 int, PRIMARY KEY (f1)) ;
INSERT IGNORE INTO t2 VALUES ('9','4'),('10','9');
SELECT STRAIGHT_JOIN *
FROM t1
JOIN t2 ON t2.f2 = t1.f1
WHERE ( t1.f1 ) IN ( SELECT f1 FROM t1 )
AND t1.f1 = t2.f1
OR t1.f1 = 9;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 729067
Re: Wrong result with STRAIGHT_JOIN + OR + IN subquery in maria-5.3, maria-5.3-mwl89
This bug could be related to
https://bugs.launchpad.net/maria/+bug/730466
Both this, and BUG#730466 require STRAIGHT_JOIN
to force "range checked for each record".