← Back to team overview

maria-developers team mailing list archive

EXISTS-to-IN can work anywhere?



Currently, EXISTS-to-IN rewrite only works on WHERE's top level, or under NOT,
where the rewrite doesn't change semantics wrt NULL values.

It seems, it is possible to make it work in other contexts, too.  We need to
convert it into a "NULL-ignoring IN", a special kind of IN predicate.

For normal IN:

NULL IN (select that returns something) --> NULL
NULL IN (select that returns nothing) --> FALSE 

$value IN (select that doesn't return $value) --> FALSE
$value IN (select that doesn't return $value but returns NULL) --> NULL

null-ignoring-IN should return FALSE in all of the above cases.  In fact, the
optimizer is perfectly capable of doing this. What we need is just to disable
special handling for NULLs.

Let's discuss this tomorrow (Tuesday).

Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog