>>>>> "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
>> INSERT INTO t SET a=NULL; -> ERROR
>> 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)