List:General Discussion« Previous MessageNext Message »
From:Jianliang Zhao Date:October 10 2002 10:26pm
Subject:RE: NULL sometimes joins to NULL
View as plain text  
I am not convinced that index on nullable field is a design flaw. This is not the behavior
in the major databases. To match and join two nulls, IS NULL should be expected to be used
instead of a.col1 = b.col2.

-----Original Message-----
From: Jan Steinman [mailto:Jan@stripped]
Sent: Thursday, October 10, 2002 9:45 AM
To: mysql@stripped
Subject: Re: NULL sometimes joins to NULL


>From: jfield@stripped
>
>After adding a key to a nullable column, null values will
>successfully join through to null values in other tables.

And this is a problem because? :-)

Seriously, it may be a bug, but IMHO it is a design flaw to index a NULL field. Indexed
fields should always be NOT NULL, with the possible exception of FULLTEXT indeces.

But if one DOES have NULLs in an indexed field, I'd fully expect them to match and join
other NULLs.

---- SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL ----

-- 
: Jan Steinman -- nature photography: <http://www.Bytesmiths.com>
: Bytesmiths -- artists' services: <http://www.Bytesmiths.com/Services>
: Join the forums at <http://www.Bytesmiths.com/wiki>


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <mysql-thread121851@stripped>
To unsubscribe, e-mail <mysql-unsubscribe-jzhao=zaplet.com@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Thread
NULL sometimes joins to NULLjfield10 Oct
  • re: NULL sometimes joins to NULLVictoria Reznichenko11 Oct
  • re: NULL sometimes joins to NULLVictoria Reznichenko17 Oct
Re: NULL sometimes joins to NULLJan Steinman10 Oct
RE: NULL sometimes joins to NULLJianliang Zhao11 Oct
RE: NULL sometimes joins to NULLArtem Koltsov11 Oct