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

long unique does not work like unique key when using replace

Details

    Description

      REPLACE is supposed to remove all conflicting rows.
      For UNIQUE HASH, REPLACE only removes the first conflicting row, not all conflicting rows, and generates an error:

      Test case:

      create or replace table t1 (a int primary key, b int, c int, unique key `test` (b,c) using hash) engine=myisam;
      		 
      insert into t1 values (1,1,1),(2,2,2);
      		 
      replace into t1 values (3,1,1);
      		 
      select * from t1;
      		 
      --error 1062
      		 
      replace into t1 values (3,2,2);
      		 
      select * from t1;


      +---+------+------+
      		 
      | a | b    | c    |
      		 
      +---+------+------+
      		 
      | 3 |    1 |    1 |
      		 
      | 2 |    2 |    2 |
      		 
      +---+------+------+

      Test without USING HASH

      create or replace table t1 (a int primary key, b int, c int, unique key `test` (b,c)) engine=myisam;
      		 
      insert into t1 values (1,1,1),(2,2,2);
      		 
      replace into t1 values (3,1,1);
      		 
      select * from t1;
      		 
      replace into t1 values (3,2,2);
      		 
      select * from t1;


      +---+------+------+
      		 
      | a | b    | c    |
      		 
      +---+------+------+
      		 
      | 3 |    2 |    2 |
      		 
      +---+------+------+

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32839 LONG UNIQUE gives error when used with REPLACE

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            monty Michael Widenius created issue -
            monty Michael Widenius made changes -
            Field Original Value New Value
            Summary long unique does not work like unique quey with replace long unique does not work like unique key when using replace
            monty Michael Widenius made changes -
            monty Michael Widenius made changes -
            serg Sergei Golubchik made changes -
            Issue Type Task [ 3 ] Bug [ 1 ]
            serg Sergei Golubchik made changes -
            Affects Version/s 10.6 [ 24028 ]
            serg Sergei Golubchik made changes -
            Description REPLACE is supposed to remove all conflicting rows.
            For UNIQUE HASH, REPLACE only removes the first conflicting row, not all conflicting rows, and generates an error:

            Test case:
            create or replace table t1 (a int primary key, b int, c int, unique key `test` (b,c) using hash) engine=myisam;
            insert into t1 values (1,1,1),(2,2,2);
            # This works
            replace into t1 values (3,1,1);
            select * from t1;
            --error 1062
            replace into t1 values (3,2,2);
            select * from t1;
            {noformat}
            +---+------+------+
            | a | b | c |
            +---+------+------+
            | 3 | 1 | 1 |
            | 2 | 2 | 2 |
            +---+------+------+
            {noformat}

            # Test without USING HASH

            create or replace table t1 (a int primary key, b int, c int, unique key `test` (b,c)) engine=myisam;
            insert into t1 values (1,1,1),(2,2,2);
            replace into t1 values (3,1,1);
            select * from t1;
            replace into t1 values (3,2,2);
            select * from t1;
            {noformat}
            +---+------+------+
            | a | b | c |
            +---+------+------+
            | 3 | 2 | 2 |
            +---+------+------+
            {noformat}
            drop table t1;
            		REPLACE is supposed to remove all conflicting rows.
            For UNIQUE HASH, REPLACE only removes the first conflicting row, not all conflicting rows, and generates an error:

            Test case:
            {code:sql}
            create or replace table t1 (a int primary key, b int, c int, unique key `test` (b,c) using hash) engine=myisam;
            insert into t1 values (1,1,1),(2,2,2);
            replace into t1 values (3,1,1);
            select * from t1;
            --error 1062
            replace into t1 values (3,2,2);
            select * from t1;
            {code}
            {noformat}
            +---+------+------+
            | a | b | c |
            +---+------+------+
            | 3 | 1 | 1 |
            | 2 | 2 | 2 |
            +---+------+------+
            {noformat}

            Test without USING HASH
            {code:sql}
            create or replace table t1 (a int primary key, b int, c int, unique key `test` (b,c)) engine=myisam;
            insert into t1 values (1,1,1),(2,2,2);
            replace into t1 values (3,1,1);
            select * from t1;
            replace into t1 values (3,2,2);
            select * from t1;
            {code}
            {noformat}
            +---+------+------+
            | a | b | c |
            +---+------+------+
            | 3 | 2 | 2 |
            +---+------+------+
            {noformat}
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ]
            masonmariadb Mason Sharp (Inactive) made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            Affects Version/s 10.5 [ 23123 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.5 [ 23123 ]
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov added a comment -

            Hello serg,

            Please review a patch:

            https://github.com/MariaDB/server/commit/424ee6eac783dd91d08305a9efff9347da6a6b4a

            bar Alexander Barkov added a comment - Hello serg , Please review a patch: https://github.com/MariaDB/server/commit/424ee6eac783dd91d08305a9efff9347da6a6b4a
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov added a comment -

            Hello serg,

            Please review a new patch version:

            https://github.com/MariaDB/server/commit/af51be1106b6738f64ebe5ee7a18573a94fdf934

            bar Alexander Barkov added a comment - Hello serg , Please review a new patch version: https://github.com/MariaDB/server/commit/af51be1106b6738f64ebe5ee7a18573a94fdf934
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Sergei Golubchik [ serg ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.5.24 [ 29517 ]
            Fix Version/s 10.6.17 [ 29518 ]
            Fix Version/s 10.11.7 [ 29519 ]
            Fix Version/s 11.0.5 [ 29520 ]
            Fix Version/s 11.1.4 [ 29024 ]
            Fix Version/s 11.2.3 [ 29521 ]
            Fix Version/s 11.3.2 [ 29522 ]
            Fix Version/s 11.4.1 [ 29523 ]
            Fix Version/s 10.9.8 [ 29015 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 144370

            People

              bar Alexander Barkov
              monty Michael Widenius
              Votes:
              1 Vote for this issue
              Watchers:
              10 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.