Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3208) Bug#54437

[Guilhem Bichot]

Hello,

Guilhem Bichot a écrit, Le 07.07.2010 09:33:
> Hello,
> 
> Guilhem Bichot a écrit, Le 01.07.2010 16:34:
>> Hello Jorgen,
>>
>> Jørgen Løland a écrit, Le 30.06.2010 15:03:
>>> Guilhem,
>>>
>>> I'm doing my best to understand the consequences of removing the 
>>> for(;;) loop and tried a few queries. Here's what I found so far:
>>>
>>> #Your query
>>> select t2.a from t2 left join t3 on t2.a=t3.a;
>>> a
>>> 1
>>> 1
>>> select * from t1 where t1.a in (select t2.a from t2 left join t3 on 
>>> t2.a=t3.a);
>>> a
>>> 1
>>> 1
>>>
>>> #Modified query with inner cross-join - should be same result
>>> select t2.a from t2 left join (t2 as t2inner,t3) on t2.a=t3.a;
>>> a
>>> 1
>>> 1
>>> select * from t1 where t1.a in (select t2.a from t2 left join (t2 as 
>>> t2inner,t3) on t2.a=t3.a);
>>> a
>>> 1
>>>
>>> Do you agree that the result of the last query misses a '1' row?
<cut>
> After forcing Duplicates weedout to include table t1, or, which is 
> equivalent, after applying the patch from Sergey Petrunia for BUG#49129, 
> I get a segfault in the MEMORY engine. This is because we try to insert 
> a malformed record into the duplicates weedout temporary table. Which is 
> because the tables "included" in this table are t1,t2inner,t3 (note: t2 
> isn't). t2inner and t3 should not be included; there is a test for this 
> in sj_table_is_included(): if the table is in a semijoin nest (i.e. 
> join_tab->emb_sj_nest!=NULL), don't include it; but for t2inner and t3, 
> join_tab->emb_sj_nest is NULL. Why? because:
> - they are in a nested join
> - we have a semijoin nest which contains t2 and the nested join above
> - the loop which sets emb_sj_nest, in pull_out_semijoin_tables(), stays 
> on first level: it sets it for t2, then looks at the nested join object, 
> which has tbl->table==NULL, so doesn't set emb_sj_nest for the nested 
> join, and the loop doesn't dive into the nested join. So nested join 
> tables don't get their emb_sj_nest properly set, they are left as "not 
> in a semijoin nest".
> After fixing this (with a simple recursive function to go down into the 
> nested join and set emb_sj_nest), finally (phew) I get good results for 
> your testcase.

I filed this crash-in-MEMORY as BUG#55955.