From: Kevin Lewis
Date: January 21 2009 6:26pm
Subject: Re: search for null values in indexed columns
List-Archive: http://lists.mysql.com/falcon/417
Message-Id: <497768EF.4080401@sun.com>
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=ISO-8859-1
Content-Transfer-Encoding: 7BIT

 > Vladislav Vaintroub wrote:
> Kevin, nulls are used by optimizer, and they are not *that* undeterministic.
> 
> In a query like
> select * from t where a is NULL;
> if a is indexed or and if you have multisegment index where a is the first
> member, optimizer will ask you for NULL.

The MySQL optimizer is counting on the fact that NULLs occur in the sort 
order before real values.  That is OK to set that requirement on MySQL 
storage engines.

> Also this condition (a = 1 and  b < 0 and c=1), that seemingly has nothing
> to do with NULLs, will be translated into range query  lower=(1,NULL,1)
> upper=(1,0,1) by optimizer , if there is (a,b,c) index.

That aught to work, if we were to sort our NULL values before all real 
values, even negative values.  But we seem to sort our NULLs with our 
zeros when there is a nultisegment key.