Re: Allowing join removals for more join types

On 19 May 2014 12:15 David Rowley Wrote,

>I think you are right here, it would be correct to remove that join, but I also think that the query in question could be quite easily be written as:

>select t1.a from t1 left join t2 on t1.a=t2.b;

>Where the join WILL be removed. The distinct clause here technically is a no-op due to all the columns of a unique index being present in the clause. Can you think of a use case for this where the sub query couldn't have been written out as a direct join to the relation?

>What would be the reason to make it a sub query with the distinct? or have I gotten something wrong here?

>I'm also thinking here that if we made the join removal code remove these joins, then the join removal code would end up smarter than the rest of the code as the current code seems not to remove the distinct clause for single table queries where a subset of the columns of a distinct clause match all the columns of a unique index.

>Can you think of a similar example where the subquery could not have been written as a direct join to the relation?

I think, you are write that above given query and be written in very simple join.

But what my point is, In any case when optimizer cannot pull up the subquery (because it may have aggregate, group by, order by, limit, distinct etc.. clause),
That time even, It will check Whether join is removable or not only when distinct or group by clause is there if it has unique index then it will not be check, is there no scenario where it will be useful ?

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.

In your patch, anyway we are having check for distinct and group clause inside subquery, can’t we have check for unique index also ?

Regards,
Dilip