maria-developers team mailing list archive

Thread
Date

EXISTS-to-IN can work anywhere?

To: Oleksandr Byelkin <sanja@xxxxxxxxxxxxxxxx>, timour@xxxxxxxxxxxx
From: Sergei Petrunia <psergey@xxxxxxxxxxxx>
Date: Tue, 27 Nov 2012 00:49:22 +0400
Cc: maria-developers@xxxxxxxxxxxxxxxxxxx
User-agent: Mutt/1.5.18 (2008-05-17)

Hello,

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

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