[MDEV-33287] Table partitioning with only default partition bypasses data type limitations Created: 2024-01-21  Updated: 2024-01-21

Status: Open
Project: MariaDB Server
Component/s: Partitioning
Affects Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Alexey Botchkov
Resolution: Unresolved Votes: 0
Labels: upstream


 Description   

PARTITION BY LIST [COLUMNS] and PARTITION BY RANGE [COLUMNS] are limited to certain data types by design, for example, they don't support TIMESTAMP:

10.4 8e337e016ff882862d4d2f32488b5142370ff8da

 
MariaDB [test]> create or replace table t (a timestamp) partition by range columns (a) (partition p0 values less than ('2024-01-01'), partition p1 values less than (maxvalue));
 
ERROR 1659 (HY000): Field 'a' is of a not allowed type for this type of partitioning

However, if only the default partition is defined, the operation succeeds:

MariaDB [test]> create or replace table t (a timestamp) partition by range columns (a) (partition p1 values less than (maxvalue));
 
Query OK, 0 rows affected (0.040 sec)

Same for DEFAULT partition in PARTITION BY LIST COLUMNS.

It causes confusing errors afterwards, for example upon an attempt to reorganize the partition: 

MariaDB [test]> alter table t reorganize partition p1 into (partition p0 values less than ('2030-01-01'), partition p1 values less than (maxvalue));
 
ERROR 1659 (HY000): Field 'a' is of a not allowed type for this type of partitioning

Maybe there can be other unexpected effects which aren't obvious right away.

If the reason is that a partitioned table with only default partition is the same as a non-partitioned table, then this should also work, but it doesn't:

MariaDB [test]> create or replace table t (a timestamp) partition by range (a) (partition p1 values less than (maxvalue));
 
ERROR 1659 (HY000): Field 'a' is of a not allowed type for this type of partitioning

and in any case it is not true, it still counts as a partitioned table, with all their limitations etc.
Generated at Thu Feb 08 10:37:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.