List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 12 2000 11:03pm
Subject:Re: Is this a bug?
View as plain text  

>>>>> "Tim" == Tim Bunce <Tim.Bunce@stripped> writes:

Tim> On Sat, Mar 11, 2000 at 07:43:14PM +0200, Michael Widenius wrote:
>> Hi!
>> >>>>> "sasha" == sasha  <sasha@stripped> writes:
sasha> asrmj wrote:
>> >> 
>> >> I have a table with some columns defined as NOT NULL:
>> >> 
>> >> CREATE TABLE t (
>> >> a CHAR(32) NOT NULL
>> >> );
>> >> 
>> >> When I do an insert of a NULL field on this table I get an error, of
>> >> course.
>> >> 
>> >> 
>> >> When I do an UPDATE with NULL as the new value, no problem!
>> >> 
>> >> UPDATE t SET a=NULL WHERE a='somevalue';    -> OK!
>> >> 
>> >> My question is: is this a bug or some useful feature? How can this
>> >> behavior be fixed so that UPDATE of
>> >> NULL columns is made to fail?
>> >> 
sasha> I've just tried it and you are right, there is an inconsistency. Update of NOT
sasha> NULL column with NULL results in setting it to the default value, while similar
sasha> INSERT will give an error.
sasha> Monty, is this by design or just a bug? Is this what ANSI says we should do?
>> This is by design.  As MySQL doesn't have rollback, we can only check
>> and give things on things that are guaranteed to only affect one row
>> (like insert).  As an UPDATE may affect many rows, we do the update
>> 'as good as possible' and instead just count up the number of warnings
>> for every wrong update.
>> (ANSI would say that this is wrong, but ANSI haven't consider the
>> possibilities when you don't have rollback)

Tim> Will behaviour change when MySQL has transactions?

Note that with MySQL you will always have the option to not work with
transactional tables.  At least when you are running in 'ansi sql'
mode and using only transactional tables, we have to give an error
message for this.  In other case it's up for discussion :(

Is this a bug?asrmj11 Mar
  • Re: Is this a bug?sasha11 Mar
    • Re: Is this a bug?Michael Widenius11 Mar
      • Re: Is this a bug?Tim Bunce13 Mar
        • Re: Is this a bug?Michael Widenius13 Mar