Hi Roy,
On 07/05/2011 11:38 AM, Roy Lyseng wrote:
> Hi Gluh,
>
> The patch in itself makes sense and is hereby approved.
Thanks!
>
> However, I think that you should file a new bug for longer-term
> cleanup of this feature. As far as I can see, the information
> requested is still not aggregated in the most appropriate place.
I would prefer to add TODO comment instead,
let me know if it's ok.
>
> The basic problem here is that used table information from different
> select_lex objects are not compatible and should not be aggregated
> into the same field.
>
> I think that a correct fix would look something like this:
>
> - Aggregate used tables information for selected expressions into the
> select_lex object where the
> tables are referenced in the from clause.
>
> - If there is no such select_lex object (for single-table insert,
> update and delete), aggregate the
> information on the LEX object instead.
The only statement where used_tables info also is used is
INSERT(mysql_insert() func).
select_lex for INSERT is always created. So we could use select_lex object.
>
> - This information should be sufficient for proper optimization of a
> UNION query, such as:
> SELECT t11.a, t11.b FROM t11 JOIN t12 USING(c1)
> UNION
> SELECT t21.a, t22.b FROM t21 JOIN t22 USING(c2);
> In the current code, the information aggregated into
> lex->used_tables is [0,1].
> This is correct for the second query of the UNION, but incorrect for
> the first query.
> Hence, the desired optimization is not carried out for the first query.
>
> - The proposed change also means that used tables information is
> propagated correctly also for
> subqueries, but notice that the general subquery optimizations are
> more useful than this optimization
> in this case.
>
> - For semijoin transformation, it is not necessary to propagate used
> tables information
> from subqueries into the parent level.
>
> Thanks,
> Roy
>
Regards,
Gluh
