A query with IN subquery that can be converted to a semi-join may return a wrong result in maridb-5.3 if the where clause of the subquery contains OR condition.

The following test case provides such a query.

create table t1 (a int, b int);
insert into t1 values (7,5), (3,3), (5,4), (9,3);

create table t2 (a int, b int, index i_a(a));

insert into t2 values
(4,2), (7,9), (7,4), (3,1), (5,3), (3,1), (9,4), (8,1);

set optimizer_switch='semijoin=on,materialization=on';
select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1);

The query in from the test case returns a wrong result if the optimizer switch flags 'semijoin' and 'materialization' are set to 'on', a it returns the correct answer if these flags are set to 'off'.

MariaDB [test]> set optimizer_switch='semijoin=on,materialization=on';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1);
----------+

----------+

----------+
4 rows in set (0.00 sec)

MariaDB [test]> set optimizer_switch='semijoin=off,materialization=off';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1);
----------+

----------+

----------+
2 rows in set (0.00 sec)

The warning returned by EXPLAIN EXTENDED executed for the query with
optimizer_switch set to 'semijoin=on,materialization=on'
shows that it happens because in this case the optimizer generates an invalid execution plan:

MariaDB [test]> set optimizer_switch='semijoin=on,materialization=on';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain extended
-> select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1);
--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
3 rows in set, 1 warning (0.00 sec)

MariaDB [test]> show warnings;
------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)