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

INSERT and REPLACE rely on undefined behavior ordering

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • N/A
    • N/A
    • Documentation
    • None

    Description

      References

      Summary

      I am also relying on the assumption that each INSERT in this situation will be performed in the order of the SELECT clause result set.

      Test case

      Test case 1:

      DROP TEMPORARY TABLE IF EXISTS index_and_color;
      CREATE TEMPORARY TABLE index_and_color (`index` INT PRIMARY KEY, color TEXT);
      INSERT IGNORE INTO index_and_color SELECT 5, "Red" UNION ALL SELECT 5, "Blue";
      SELECT * FROM index_and_color;

      Test case 2:

      CREATE OR REPLACE TEMPORARY TABLE t2 (`index` INT PRIMARY KEY, color TEXT)
      IGNORE SELECT 5 AS `index`, "Red" AS color UNION ALL SELECT 5, "Blue";
      SELECT * FROM t2;

      Test case 3:

      DROP TEMPORARY TABLE IF EXISTS index_and_color;
      CREATE TEMPORARY TABLE index_and_color (`index` INT PRIMARY KEY, color TEXT);
      REPLACE INTO index_and_color SELECT 5, "Red" UNION ALL SELECT 5, "Blue";
      SELECT * FROM index_and_color;

      Intuitively, I see that the "first" row in the SELECT clause result set has (5, "Red") and then the "second" row is ignored, containing the same key with "Blue".

      From what I see, this is undefined behavior because another server implementing the same documentation could handle the rows in a different order.

      Recommendation

      Update above referenced:

      If the new table has a primary key or UNIQUE indexes, you can use IGNORE to handle duplicate key errors during the query. The newer values will not be inserted if an identical value already exists.

      into:

      If the new table has a primary key or UNIQUE indexes, you can use IGNORE to handle duplicate key errors during the query. Rows are handled in the same order as the SELECT result set. Any row insertion which would have caused a constraint violation (e.g. duplicate of a PRIMARY KEY) is silently skipped.

      And also update:

      If the new table has a primary key or UNIQUE indexes, you can use the IGNORE or REPLACE keywords to handle duplicate key errors during the query. IGNORE means that the newer values must not be inserted an identical value exists in the index. REPLACE means that older values must be overwritten.

      into:

      If the new table has a primary key or UNIQUE indexes, you can use the IGNORE or REPLACE keywords to handle duplicate key errors during the query. IGNORE means that the newer values must not be inserted an identical value exists in the index. REPLACE means that older values must be overwritten. Rows are handled in the same order as the SELECT result set.

      And also update:

      REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. If the table has more than one UNIQUE keys, it is possible that the new row conflicts with more than one row. In this case, all conflicting rows will be deleted.

      into:

      REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. If the table has more than one UNIQUE keys, it is possible that the new row conflicts with more than one row. In this case, all conflicting rows will be deleted. When using SELECT, rows are handled in the same order as the SELECT result set.

      Follow on work

      After closing this issue, consider to create a new issue to address language such as:

      ... if an identical value already exists

      ... has the same value as a new row

      across the documentation. These are referring to only one specific type of constraint violation. Instead, documentation should be generalized to refer to any type of constraint violation, e.g. NOT NULL, triggers, etc.

      Attachments

        Activity

          People

            greenman Ian Gilfillan
            fulldecent William Entriken
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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