Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-31164

default current_timestamp() not working when used INSERT ON DUPLICATE KEY in some cases

    XMLWordPrintable

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
      

      Attachments

        Activity

          People

            serg Sergei Golubchik
            kvaderlipa Lubos Takac
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.