From: Roy Lyseng
Date: July 7 2011 10:42am
Subject: Re: bzr commit into mysql-5.1 branch (sergey.glukhov:3649) Bug#11766594
List-Archive: http://lists.mysql.com/commits/140227
Message-Id: <4E158D90.9090604@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Gluh,

On 07.07.11 12.05, Sergey Glukhov wrote:
> 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.

That's fine. Please make sure that sufficient information goes into the @todo.

>>
>> 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.

OK, thanks!

>>
>> - 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.

Roy