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

REPLACE/IGNORE does not work with historical records in InnoDB

    XMLWordPrintable

    Details

      Description

      I've seen somewhere in the review exchange a note about REPLACE which should work as possible and fail if not, don't remember anything about IGNORE, and can't find anything in the patch tests. Anyway, it's strange that the result is different for different engines, but if that's expected, please feel free to close, just check that the tests have it.

      For InnoDB tables, INSERT IGNORE, REPLACE, LOAD IGNORE, LOAD REPLACE fail when duplicate key situation occurs (and fail with a rather unfortunate ER_FOREIGN_DUPLICATE_KEY_WITH_CHILD_INFO (not included in the test case, it's meant to be run with --force --force to get the complete result, and with --mysqld=--default-storage-engine=....

      --source include/have_innodb.inc
       
      # To make all engines equal in this regard
      SET sql_mode='STRICT_ALL_TABLES';
       
      # Create an outfile with two identical historical records
       
      create or replace table t (a int) with system versioning;
      set system_versioning_insert_history= on;
      insert into t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
      select a,row_start,row_end into outfile 'f' from t for system_time all;
       
      # Run with default engine of your choice
       
      create or replace table t (a int primary key) with system versioning;
      load data infile 'f' replace into table t (a,row_start,row_end);
      select a,row_start,row_end from t for system_time all;
       
      create or replace table t (a int primary key) with system versioning;
      load data infile 'f' ignore into table t (a,row_start,row_end);
      select a,row_start,row_end from t for system_time all;
       
      create or replace table t (a int primary key) with system versioning;
      replace into t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
      select a,row_start,row_end from t for system_time all;
       
      create or replace table t (a int primary key) with system versioning;
      insert ignore into t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
      select a,row_start,row_end from t for system_time all;
       
      # Cleanup
      --let $datadir= `select @@datadir`
      --remove_file $datadir/test/f
      drop table t;
      

      With InnoDB:

      bb-10.11-MDEV-16546 32090722c7

      MariaDB [test]> load data infile 'f' replace into table t (a,row_start,row_end);
      ERROR 1761 (23000): Foreign key constraint for table 't', record '1' would lead to a duplicate entry in table 't', key 'PRIMARY'
      MariaDB [test]> select a,row_start,row_end from t for system_time all;
      Empty set (0.001 sec)
      ...
      MariaDB [test]> load data infile 'f' ignore into table t (a,row_start,row_end);
      ERROR 1761 (23000): Foreign key constraint for table 't', record '1' would lead to a duplicate entry in table 't', key 'PRIMARY'
      MariaDB [test]> select a,row_start,row_end from t for system_time all;
      Empty set (0.001 sec)
      ...
      MariaDB [test]> replace into t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
      ERROR 1761 (23000): Foreign key constraint for table 't', record '1' would lead to a duplicate entry in table 't', key 'PRIMARY'
      MariaDB [test]> select a,row_start,row_end from t for system_time all;
      Empty set (0.001 sec)
      ...
      MariaDB [test]> insert ignore into t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
      ERROR 1761 (23000): Foreign key constraint for table 't', record '1' would lead to a duplicate entry in table 't', key 'PRIMARY'
      MariaDB [test]> select a,row_start,row_end from t for system_time all;
      Empty set (0.001 sec)
      

      With MyISAM:

      MariaDB [test]> load data infile 'f' replace into table t (a,row_start,row_end);
      Query OK, 3 rows affected (0.001 sec)                
      Records: 2  Deleted: 1  Skipped: 0  Warnings: 0
       
      MariaDB [test]> select a,row_start,row_end from t for system_time all;
      +---+----------------------------+----------------------------+
      | a | row_start                  | row_end                    |
      +---+----------------------------+----------------------------+
      | 1 | 2022-01-01 00:00:00.000000 | 2023-01-01 00:00:00.000000 |
      | 1 | 2022-01-01 00:00:00.000000 | 2022-10-17 19:25:03.949777 |
      +---+----------------------------+----------------------------+
      ...
      MariaDB [test]> load data infile 'f' ignore into table t (a,row_start,row_end);
      Query OK, 1 row affected, 1 warning (0.001 sec)      
      Records: 2  Deleted: 0  Skipped: 1  Warnings: 1
       
      MariaDB [test]> select a,row_start,row_end from t for system_time all;
      +---+----------------------------+----------------------------+
      | a | row_start                  | row_end                    |
      +---+----------------------------+----------------------------+
      | 1 | 2022-01-01 00:00:00.000000 | 2023-01-01 00:00:00.000000 |
      +---+----------------------------+----------------------------+
      ...
      MariaDB [test]> replace into t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
      Query OK, 3 rows affected (0.001 sec)
      Records: 2  Duplicates: 1  Warnings: 0
       
      MariaDB [test]> select a,row_start,row_end from t for system_time all;
      +---+----------------------------+----------------------------+
      | a | row_start                  | row_end                    |
      +---+----------------------------+----------------------------+
      | 1 | 2022-01-01 00:00:00.000000 | 2023-01-01 00:00:00.000000 |
      | 1 | 2022-01-01 00:00:00.000000 | 2022-10-17 19:25:04.048847 |
      +---+----------------------------+----------------------------+
      ...
      MariaDB [test]> insert ignore into t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
      Query OK, 1 row affected, 1 warning (0.002 sec)
      Records: 2  Duplicates: 1  Warnings: 1
       
      MariaDB [test]> select a,row_start,row_end from t for system_time all;
      +---+----------------------------+----------------------------+
      | a | row_start                  | row_end                    |
      +---+----------------------------+----------------------------+
      | 1 | 2022-01-01 00:00:00.000000 | 2023-01-01 00:00:00.000000 |
      +---+----------------------------+----------------------------+
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.