List:General Discussion« Previous MessageNext Message »
From:D. Dante Lorenso Date:December 29 2009 4:56am
Subject:Re: Is anything ever equal to NULL?
View as plain text  
Well, if nothing can ever equal null, then why isn't MySQL query parser 
smart enough to reduce my queries to something more sensible?  If I'm 
saying this:

   SELECT *
   FROM sometable
   WHERE somecolumn = NULL OR somecolumn = 'abc';

Why isn't it able to reduce the query to something more like this:

   SELECT *
   FROM sometable
   WHERE somecolumn = 'abc';

Since it already should know that somecolumn = NULL will always evaluate 
to FALSE (or is it NULL? ... either way, it's not "TRUE")?  If I run the 
first query above, the query takes about 15 seconds to run against 40 
million records, but if I run the second query, it takes about .050 
seconds.  The test for NULL seems to cause the query to skip use of an 
index because I doubt NULL values are indexed.

Am I expecting too much of the parser?

-- Dante


Martijn Tonies wrote:
> Hi,
> 
>> Will anything ever be equal to NULL in a SELECT query?
> 
> No, never.
> 
> Null also means "unknown", if you design your tables well enough,
> there should be no NULLs -stored- (different from a resultset,
> where there can be nulls, for example in LEFT JOINs), because it's no 
> use to store what you don't know. The only case when you want to store a 
> null is when you do want to -know- you don't know a value.
> 
> A column can have two states: null or not null. It either has
> data (a value, depending on the datatype), or no data (null),
> which is where <column> IS NULL (has no data) or
> <column> IS NOT NULL (has data) comes into play.
> 
> Null is not the same as empty. An empty string, for example, is not 
> equal to null (which is "unknown"), you cannot compare
> anything to what you don't know, which is why your comparison
> fails.
> 
> NULL = NULL fails, so does NULL <> NULL in the strict
> sense.
> 
> 
>>   SELECT *
>>   FROM sometable
>>   WHERE somecolumn = NULL;
>>
>> I have a real-life query like this:
>>
>>   SELECT *
>>   FROM sometable
>>   WHERE somecolumn = NULL OR somecolumn = 'abc';
>>
>> The 'sometable' contains about 40 million records and in this query, 
>> it appears that the where clause is doing a sequential scan of the 
>> table to  find a condition where 'somecolumn' = NULL.  Shouldn't the 
>> query parser be smart enough to rewrite the above query like this:
> 
> You mean the "optimizer", perhaps it should, perhaps it shouldn't. Yet,
> your query is not really the smartest, as you should avoid writing
> <column> = NULL, as this doesn't make sense.
> 
>>   SELECT *
>>   FROM sometable
>>   WHERE FALSE OR somecolumn = 'abc';
>>
>> And therefor use the index I have on 'somecolumn'?  When I manually 
>> rewrite the query, I get the performance I expect but when I leave it 
>> as it was, it's 100 times slower.
>>
>> What's so special about NULL?
> 
> Quite a bit ;-)
> 
> With regards,
> 
> Martijn Tonies
> Upscene Productions
> http://www.upscene.com
> 
> Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
> Anywhere, MySQL, InterBase, NexusDB and Firebird!
> 
> Database questions? Check the forum:
> http://www.databasedevelopmentforum.com
> 


-- 
----------
D. Dante Lorenso
Thread
Is anything ever equal to NULL?D. Dante Lorenso28 Dec
  • Re: Is anything ever equal to NULL?Michael Dykman28 Dec
  • Re: Is anything ever equal to NULL?David Giragosian28 Dec
    • Re: Is anything ever equal to NULL?Carsten Pedersen28 Dec
      • Re: Is anything ever equal to NULL?David Giragosian29 Dec
  • Re: Is anything ever equal to NULL?Martijn Tonies28 Dec
    • Re: Is anything ever equal to NULL?D. Dante Lorenso29 Dec
    • Re: Is anything ever equal to NULL?Joerg Bruehe29 Dec
  • Re: Is anything ever equal to NULL?Martijn Tonies29 Dec