Re: Allowing join removals for more join types

On Mon, May 19, 2014 at 9:22 PM, Dilip kumar <dilip(dot)kumar(at)huawei(dot)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