maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

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