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

UNIQUE key with USING HASH clause changes logic for partitioned tables

Details

    Description

      Starting from 10.4, the following CREATE statement works:

      MariaDB [test]> CREATE TABLE t1 (a INT, b INT, UNIQUE (b) USING HASH) PARTITION BY KEY (a) PARTITIONS 2;
      		 
      Query OK, 0 rows affected (0.433 sec)
      		 
       
      		 
      MariaDB [test]> show create table t1;
      		 
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Table | Create Table                                                                                                                                                                              |
      		 
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | t1    | CREATE TABLE `t1` (
      		 
        `a` int(11) DEFAULT NULL,
      		 
        `b` int(11) DEFAULT NULL,
      		 
        UNIQUE KEY `b` (`b`) USING HASH
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      		 
       PARTITION BY KEY (`a`)
      		 
      PARTITIONS 2 |
      		 
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      Without USING HASH it fails as it probably should:

      MariaDB [test]> CREATE TABLE t1 (a INT, b INT, UNIQUE (b)) PARTITION BY KEY (a) PARTITIONS 2;
      		 
      ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

      It also fails with and without USING HASH in earlier versions.

      If it's something that was implemented intentionally in 10.4, it needs to be documented.

      Reproducible with at least InnoDB, MyISAM.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22185 Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON or ER_KEY_NOT_FOUND or Assertion `inited==NONE' failed in handler::ha_index_init

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22262 Server crashes in ha_partition::open or handler::ha_table_flags upon UPDATE with partition pruning

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23530 ERROR 1071: Specified key was too long; max key length is 1000/2000 bytes on CREATE TABLE ... UNIQUE KEY ... USING HASH

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23713 Replication stops with "Index for table is corrupt", table with HASH index, assertion: !cursor->index->is_committed() fails in row_ins_sec_index_entry_by_modify

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik added a comment - - edited

            I think it not only needs to be documented, I'd even say that USING HASH should be automatically applied to any UNIQUE constraint in a partitioned table (unless there's explicit USING BTREE).

            Not having unique constraints in a partitioned table was a long-time limitation, and it's good that it's lifted now.

            serg Sergei Golubchik added a comment - - edited I think it not only needs to be documented, I'd even say that USING HASH should be automatically applied to any UNIQUE constraint in a partitioned table (unless there's explicit USING BTREE ). Not having unique constraints in a partitioned table was a long-time limitation, and it's good that it's lifted now.
            midenok Aleksey Midenkov added a comment - - edited

            I suggest to add explanation for ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF here:

            Since each partition is checked for unique constraint indepenedently by the underlying storage engine, partitioning KEY guarantees all duplicate keys going into the same partition where storage engine fails unique constraint. This limitation does not apply to unique hash index whose constraint is checked not by the storage engine, but by SQL layer.

            midenok Aleksey Midenkov added a comment - - edited I suggest to add explanation for ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF here : Since each partition is checked for unique constraint indepenedently by the underlying storage engine, partitioning KEY guarantees all duplicate keys going into the same partition where storage engine fails unique constraint. This limitation does not apply to unique hash index whose constraint is checked not by the storage engine, but by SQL layer.

            People

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