[#MDEV-12670] Merge of derived tables / views is not transparent for semi-join optimizations

For any query Q for which a semi-join optimization is used the equivalent query Q1 with derived table

SELECT * FROM ( Q ) t

is always executed without semi-join optimization.
In a general case any merge of a derived table / view blocks usage of semi-join optimizations
for it.
Here' s a test case that demonstrate this problem.

create table t1 (a int) engine=myisam;

insert into t1  values (5),(3),(2),(7),(2),(5),(1);

create table t2 (b int, index idx(b)) engine=myisam;

insert into t2 values (2),(3),(2),(1),(3),(4);

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

analyze table t1,t2;

explain select a from t1 where a in (select b from t2);

explain select * from (select a from t1 where a in (select b from t2)) t;

For the EXPLAINs from the testcase the following is returned:

+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra                       |

+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+

|    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL      |    7 | Using where                 |

|    1 | PRIMARY     | t2    | ref  | idx           | idx  | 5       | test.t1.a |  140 | Using index; FirstMatch(t1) |

+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+

MariaDB [test]> explain select * from (select a from t1 where a in (select b from t2)) t;

+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+

| id   | select_type        | table | type           | possible_keys | key  | key_len | ref  | rows | Extra       |

+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+

|    1 | SIMPLE             | t1    | ALL            | NULL          | NULL | NULL    | NULL |    7 | Using where |

|    3 | DEPENDENT SUBQUERY | t2    | index_subquery | idx           | idx  | 5       | func |  140 | Using index |

+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+

This is not expected as the merge transformation applied to t converts Q1 to Q.

[MDEV-12670] Merge of derived tables / views is not transparent for semi-join optimizations Created: 2017-05-03 Updated: 2017-05-17 Resolved: 2017-05-10
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	None
Fix Version/s:	5.5.57

[MDEV-12670] Merge of derived tables / views is not transparent for semi-join optimizations Created: 2017-05-03 Updated: 2017-05-17 Resolved: 2017-05-10