[MDEV-22184] UNIQUE key with USING HASH clause changes logic for partitioned tables Created: 2020-04-07  Updated: 2020-09-10

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Create Table, Partitioning
Affects Version/s: 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-22185 Failing assertion: node->pcur->rel_po... Closed
relates to MDEV-22262 Server crashes in ha_partition::open ... Confirmed
relates to MDEV-23530 ERROR 1071: Specified key was too lon... Open

 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.

 Comments   
Comment by Sergei Golubchik [ 2020-09-10 ]

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.

Generated at Thu Feb 08 09:12:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.