From: Sergey Glukhov
Date: July 7 2011 10:05am
Subject: Re: bzr commit into mysql-5.1 branch (sergey.glukhov:3649) Bug#11766594
List-Archive: http://lists.mysql.com/commits/140225
Message-Id: <4E1584EE.2000304@oracle.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------010102020509010907060808"

--------------010102020509010907060808
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------010102020509010907060808--