May be we can convert my above example like below à in this case we have unique index on field a and we are limiting it by first 100 tuple (record are already order because of index)
Create table t1 (a int, b int);
Create table t2 (a int, b int);
Create unique index on t2(a);
create view v1 as
select x.a, y.b
from t1 x left join (select t2.a a1, b from t2 limit 100) as y on x.a=y.a1;
select a from v1; à for this query I think left join can be removed, But in view since non join field(b) is also projected so this cannot be simplified there.
Ok I see what you mean.
I guess then that if we did that then we should also support removals of join in subqueries of subqueries. e.g:
select t1.* from t1 left join (select t2.uniquecol from (select t2.uniquecol from t2 limit 1000) t2 limit 100) t2 on t1.id = t2.uniquecol
On my first round of thoughts on this I thought that we could keep looking into the sub queries until we find that the sub query only queries a single table or it is not a base relation. If we find one with a single table and the sub query has no distinct or group bys then I thought we could just look at the unique indexes similar to how it's done now for a direct table join. But after giving this more thought, I'm not quite sure if a lack of DISTINCT and GROUP BY clause is enough for us to permit removing the join. Would it matter if the sub query did a FOR UPDATE?
I started looking at is_simple_subquery() in prepjointree.c but if all those conditions were met then the subquery would have been pulled up to a direct join anyway.
I'm also now wondering if I need to do some extra tests in the existing code to ensure that the subquery would have had no side affects.
For example:
SELECT t1.* FROM t1
LEFT OUTER JOIN (SELECT id,some_function_that_does_something(id) FROM t2 GROUP BY id) t2 ON t1.id = t2.id;