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

Table partitioning with only default partition bypasses data type limitations

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.5, 10.6, 10.11, 11.1(EOL), 11.2, 11.4, 10.4(EOL), 11.0(EOL), 11.3(EOL)
    • 10.5, 10.6, 10.11, 11.2
    • Partitioning

    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.

      Attachments

        Activity

          People

            holyfoot Alexey Botchkov
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.