I am not sure if this is a bug or how MySQL works on validating constraints in association with ON DUPLICATE KEY (late or early checking). For example, consider the following use case (this is irrepective of storage engine and MySQL version):
mysql> create table t1(id int not null primary key, val int not null) Engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t1 values(10,20);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values(20,10);
Query OK, 1 row affected (0.00 sec)
mysql> create table t2(id1 int not null primary key, val1 int) Engine=MyISAM;
Query OK, 0 rows affected (0.14 sec)
mysql> insert into t2 values(10,NULL);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(id, val) select id1, val1 from t2 ON DUPLICATE KEY UPDATE val=IF (VALUES(val) IS NULL, val, VALUES(val));
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 1 Duplicates: 1 Warnings: … |