Wrong result with STRAIGHT_JOIN + OR + IN subquery in maria-5.3, maria-5.3-mwl89

[Philip Stoev]

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;

[Timour Katchaounov (timour)]

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".