Hi!
On Fri, Dec 26, 2008 at 8:54 PM, Ann W. Harrison <[email protected]> wrote:
>> The thing about this whole discussion that people seem to miss is that you
>> have to choose to use the IGNORE keyword. We are not talking about default
>> behavior. If you use the IGNORE keyword, you are accepting responsibility
>> for what happens.
>
> What I thought we were discussing was which errors IGNORE ignores.
> If it ignores totally duplicate records, that's one thing. If it
I thought that in this thread, the whole IGNORE thing was introduced
by Jay on december 22 when he said
"
Can we just stick to throwing warnings where the standard says to do
so, and errors likewise?
**IMPORTANT**:
This would likely mean the dropping of the IGNORE keyword from
Drizzle's SQL syntax.
"
I interpreted this as "If drizzle's error/warning policy would conform
(more to) standard SQL, maybe this means the IGNORE syntax needs to
go". I think Jay incuded the IGNORE case in this thread to discuss
whether throwing a warning when ignoring a row is in any way
compatible with a more standard warning/error behaviour.
(Jay, is this indeed why you brought up IGNORE in this thread?)
(Personally, I believe a case can be made for IGNORE. In my mind it is
defensible to say that the extra explicit syntax (IGNORE keyword)
moderates or attenuates the otherwise, default, standard behaviour,
although I would like it even better of it would be an ON CONFLICT
clause that offers more explicit control over how exactly non-standard
behaviour is asked for).
> ignores records that violate a constraint like UNIQUE or PRIMARY
> KEY, that's another. If it can violate one constraint, why not
> another - throwing out records that have NULLS in NOT NULL fields
> makes as much sense to me as throwing out those that have duplicate
This is not the case I believe - IGNORE does not allow the violation
to occur. It does *not* leave the DB in a state were any constraints
are violated. When using ignore, rows that do not violate any
constraints are rejected, throwing a warning in the process, whereas
the normal behaviour (without explicit IGNORE keyword) is to throw an
error and abort the statement (and depending on whether the engine is
transactional or not, rollback the statement's changes or leave the
work done so far in place).
> keys. Changing values is another issue - it would be possible,
> for example, to turn the NULL into a default value or find a new
Turning NULL into an implicit default ("the data type default"
whatever that means) does occur in two cases in MySQL, but the
behaviour is not tied to IGNORE AFAIK. It occurs when doing
1) INSERT...INTO...SELECT and the sql_mode does not include a 'strict'
mode like TRADITIONAL
2) LOAD DATA INFILE, regardless of any sql_mode.
> unique value for the PRIMARY KEY / UNIQUE constraint...
This does not occur (and I'm glad for that...)
I hope this helps.
kind regards, Roland
>
> Cheers,
>
> Ann
>
> _______________________________________________
> Mailing list: https://launchpad.net/~drizzle-discuss
> Post to : [email protected]
> Unsubscribe : https://launchpad.net/~drizzle-discuss
> More help : https://help.launchpad.net/ListHelp
>
--
Roland Bouman
http://rpbouman.blogspot.com/
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
