Hello.
On Mon, Apr 09, 2001 at 01:05:01PM -0400, [EMAIL PROTECTED] wrote:
> On 9 Apr 2001 [EMAIL PROTECTED] wrote:
>
> > >Description:
> >
> > Attempting to delete all records in a table containing NULL values in a
> > UNIQUE KEY field does not work as expected. Only a single record is deleted,
> > presumably because the server thinks that the table will only have one record
> > with a NULL value in it (as it is in a UNIQUE field).
Sounds like an optimization too much. ;-)
> > >How-To-Repeat:
[...]
> This is perfectly normal from Mysql's part. What you call a uniq_id
> in the table where you don't declare it as a unique key is merely
> another int field and a table scan will be performed to actually
> find ALL the occurences of the value you are deleting. On the other
> hand in the case where you actually declare the uniq_id to be unique
> by building a UNIQUE index on it, Mysql will of course use the
> asserted uniqueness and stop processing as soon as the first
> occurence of the value you want to delete is found. I.e. it assumes
> that as a unique field there will not be another occurence of this
> value. Of course on the other hand it allows you to insert multiple
> cases of NULL even though it is a unique field, because NULL is just
> SO DAMN NULL that it is NOT EVEN EQUAL TO ITSELF :-)
You explanation what happens inside is correct, but it is a
misbehaviour. He is not using "col = NULL" but "col IS NULL" and the
latter one is defined to match all NULL values.
So I think MySQL optimizes to much (stopping after the first found
value when using indexes). You see that it is bogus also from the fact
that the result is different when used with resp. without indexes,
which may never happen (as indexes have to behave transparently
regarding the result set in SQL).
> So when you have a NULL in a unique field and add another NULL
> Mysql will not complain about it cause Null!=Null whereas it would
> complain if you had inserted a '1' and tried to insert another '1'.
> Clear as mud?
;-)
Bye,
Benjamin.
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
