On Mon, May 19, 2014 at 9:22 PM, Dilip kumar <dilip.ku...@huawei.com> wrote:
> On 19 May 2014 12:15 David Rowley Wrote, > > > > > > 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; Regards David Rowley
