Hi Sanja, On Wed, Apr 07, 2010 at 01:54:25PM +0300, Oleksandr Byelkin wrote:

I found 1 interesting test case:

create table t1 (a int, b int); insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6), (5,6),(4,5); create table t2 (c int, d int); insert into t2 values (2,3),(3,4),(5,6);

select a, b from t1 where b in (select d from t2);

It crashes but only if test case started with --debug. I assume this is with current lp:~maria-captains/maria/5.3-subqueries? I'll take a look.

Also could you give me example where Item_in_optimizer will be used for sure (in above example it looks like it is not used).

Basically, subquery must not be a semi-join. The easiest way to achieve this is to make it so that the subuqery is not an AND-part of ther WHERE anymore. The following will use Item_in_optimizer: select a, b, b in (select d from t2) as SUBQ from t1 ; select a, b from t1 where b in (select d from t2) or b < 3; If you want to get lots of test coverage fast, include the standard subselect*.test after having done: SET optimizer_switch='semijoin=off,materialization=off'; This will cause Item_in_optimizer to be used for every IN subquery. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog