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

UPDATE .. FOR PORTION leads to duplicate hash keys in partitioned tables

    XMLWordPrintable

Details

    Description

      --source include/have_partition.inc
       
      CREATE TABLE t (a INT, f BLOB, s date, e date, PERIOD FOR p(s,e), UNIQUE KEY f (f))
        ENGINE=MyISAM PARTITION BY KEY (a) PARTITIONS 2;
      INSERT INTO t VALUES
        (1,'foo','1980-01-01','2000-01-02'),(1,'bar','1980-00-01','2000-01-02');
       
      --error ER_DUP_ENTRY
      UPDATE t FOR PORTION OF p FROM '1990-01-01' TO '2000-01-02' SET a = 100;
      SELECT * FROM t;
       
      # Cleanup
      DROP TABLE t;
      

      Until 11.1 UPDATE returned the ER_DUP_ENTRY, which is expected as it splits the existing periods without modifying the unique key.
      In newer versions it succeeds, and duplicate records are indeed created.

      11.4 3f114a09306f85dbe3ce00ad268b8d36ef24a1d1

      mysqltest: At line 9: query 'UPDATE t FOR PORTION OF p FROM '1990-01-01' TO '2000-01-02' SET a = 100' succeeded - should have failed with error ER_DUP_ENTRY (1062)...
      ...
      SELECT * FROM t;
      a	f	s	e
      1	foo	1980-01-01	1990-01-01
      1	bar	1980-00-01	1990-01-01
      100	foo	1990-01-01	2000-01-02
      100	bar	1990-01-01	2000-01-02
      

      The failure appears to be specific to partitioning.

      The problem appeared after a set of commits related to MDEV-28883 / MDEV-7487 (3a9358a4106 - 554278e24db). I can't bisect more precisely as they either don't build or crash upon bootstrap already.

      Attachments

        Activity

          People

            nikitamalyavin Nikita Malyavin
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.