MySQL Bugs: #5573: Wrong/undocumented behaviour on UNIQUE column mass update

Bug #5573	Wrong/undocumented behaviour on UNIQUE column mass update
Submitted:	14 Sep 2004 18:58	Modified:	8 Apr 2008 18:28
Reporter:	Matthias Leich
Status:	Verified
Category:	Server: InnoDB	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:	Heikki Tuuri	Target Version:
Triage:	Triaged: D5 (Feature request)

Description:
If we have a modifying statement affecting several rows
MySQL seems to check CONSTRAINTs per every involved
row as single unit and not per all involved rows as set.

So the last update below gives:
ERROR 23000: Duplicate entry '2' for key 1

MySQL seems to treat the UPDATE as a set of
of atomic one row update units.  
The SQL standard and also the NIST test suite expect,
that this update is successful, because the UNIQUE
constraint is satisfied before and after the statement.
They treat the complete statement as atomic unit.

BTW: The transactional storage engines InnoDB and BDB 
show the same strange behaviour.

My environment:
   - Intel PC with Linux(SuSE 9.0)
   - MySQL 4.1 compiled from source
        last ChangeSet@1.1996.1.2, 2004-09-12

How to repeat:
use test ;

--disable_warnings
DROP TABLE IF EXISTS T1;
--enable_warnings

eval CREATE TABLE T1
(F1   DECIMAL(4) NOT NULL,
 F2   CHAR(8),
 UNIQUE (F1)) ;

INSERT INTO T1 VALUES (1,'CCA');
INSERT INTO T1 VALUES (2,'CCB');

UPDATE T1 SET F1 = F1 + 1;

Suggested fix:
Please implement a behaviour according to the SQL standard
at least for InnoDB or document the current MySQL behaviour
within the manual 
   1.8.5 MySQL Differences from Standard SQL.

This is an absolutely unacceptable behavior for InnoDB!

Hi!

I have now updated the online manual:

-------------------------------------------------
manual.texi:
  Add a note that InnoDB and MySQL check UNIQUE and FOREIGN KEY constraints row-by-row,
even in SQL statements that modify several rows; this differs from SQL st
andards, where the check should only be AFTER the SQL statement has been wholly
processed
-------------------------------------------------

and I am changing the status of this bug report to a feature request.

Thank you,

Heikki

Thank you for the bug report.

This is actually a storage engine bug, not MySQL bug.

Bug #35856 was marked as a duplicate of this one.