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

UNIQUE key with USING HASH clause changes logic for partitioned tables

    XMLWordPrintable

    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

            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:

                  Git Integration