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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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: 1 mysql> show warnings; +---------+------+-----------------------------+ | Level | Code | Message | +---------+------+-----------------------------+ | Warning | 1048 | Column 'val' cannot be null | +---------+------+-----------------------------+ 1 row in set (0.00 sec) mysql> select * from t1; +----+------+ | id | val | +----+------+ | 10 | 0 | | 20 | 10 | +----+------+ 2 rows in set (0.00 sec) |
In the above case, you have table t1 with ‘val’ column being NOT NULL; and t2 is trying to insert to t1 with NULL for val column as part of the INSERT, and ON DUPLICATE KEY; it actually keeps the old value and will not populate the NULL to t1 at all (common use case when you have a business logic to replace old with new or keep the old as is when new value is not appropriate or not modified, which in general is represented by NULL).
So; but as you can see; the validation of NULL happens before the ON DUPLICATE constraint checking; and in the above case, it should not as already primary key satisfies the duplicate constraint (before trigger of fill_records) and server should proceed with changes from what was specified from the ON DUPLICATE section (after trigger).
That is apart; and still you can see that it got a new value 0 from no where by replacing the old one 20 for column val, which is odd. The correct behavior is to just check only primary/unique constraint values when ON DUPLICATE KEY is specified in early binding and validate the rest in late binding by taking the final output from ON DUPLICATE clause… then the above should have been…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> insert into t1(id, val) select id1, val1 from t2 ON DUPLICATE KEY UPDATE conv=IF (VALUES(val) IS NULL, val, VALUES(val)); Query OK, 2 rows affected (0.00 sec) Records: 1 Duplicates: 1 Warnings: 0 mysql> select * from t1; +----+------+ | id | val | +----+------+ | 10 | 20 | | 20 | 10 | +----+------+ 2 rows in set (0.00 sec) |
As no other server supports ON DUPLICATE KEY, its hard to derive what is the right logic that needs to be applied here. But I still think early validation is wrong here as the value can change in the late binding due to other constraints that can happen from ON DUPLICATE KEY derivation.
The above is only one use case with NULL; this can be simulated to other constraints as well…
Venu,
it all depends on how you see it.. few people think early binding is right here and few might think late binding is right here by skipping the check of all values earlier (performance optimization as well, as no need to validate every thing upfront)
Did you file as a bug report to see what MySQL folks will decide ?