Details
Description
If you have timestamp column with default current timestamp and you use INSERT with ON DUPLICATE KEY UPDATE, timestamp column is wrongly initialized to '0000-00-00 00:00:00' in case when some records in insert are inserted and some updated, when is no update timestamp is initialized correctly
drop table test; |
|
create table test( |
pk integer primary key, |
val varchar(20) not null, |
lt_timestamp timestamp not null default current_timestamp() on update current_timestamp()); |
|
insert into test(pk, val) values(1, 'val1'); |
|
select * from test; |
|
+----+------+---------------------+ |
| pk | val | lt_timestamp |
|
+----+------+---------------------+ |
| 1 | val1 | 2023-05-02 07:47:19 |
|
+----+------+---------------------+ |
|
-- when both are to insert works correctly
|
insert into test(pk, val) |
select 3, 'val3' from dual |
union |
select 4, 'val4' from dual |
on duplicate key update lt_timestamp=now(); |
|
+----+------+---------------------+ |
| pk | val | lt_timestamp |
|
+----+------+---------------------+ |
| 1 | val1 | 2023-05-02 07:47:19 |
|
| 3 | val3 | 2023-05-02 07:48:02 |
|
| 4 | val4 | 2023-05-02 07:48:02 |
|
+----+------+---------------------+ |
|
-- but when one is updated and one is inserted, the inserted ne is initialized to '0000-00-00 00:00:00'
|
insert into test(pk, val) |
select 1, 'val1' from dual |
union |
select 2, 'val2' from dual |
on duplicate key update lt_timestamp=now(); |
|
+----+------+---------------------+ |
| pk | val | lt_timestamp |
|
+----+------+---------------------+ |
| 1 | val1 | 2023-05-02 07:51:56 |
|
| 2 | val2 | 0000-00-00 00:00:00 |
|
| 3 | val3 | 2023-05-02 07:51:18 |
|
| 4 | val4 | 2023-05-02 07:51:18 |
|
+----+------+---------------------+ |
-- see inserted record 2 with wrongly initialized timestamp
|
-- when change order and insert second as first datetime is initialized correctly |