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

INSERT chooses wrong partition for RANGE partitioning by DECIMAL column

    XMLWordPrintable

Details

    Description

      Starting from this revision, MariaDB 10.3+ allows partitioning by range of a decimal column:

      commit c3f35ea55a7150b186284e2ef717d12e6f81e13e
      Author: Aleksey Midenkov
      Date:   Wed Aug 28 11:57:16 2019 +0300
       
          MDEV-18501 Partition pruning doesn't work for historical queries (refactoring)
          
          SYSTEM_TYPE partitioning: COLUMN properties removed. Partitioning is
          now pure RANGE based on UNIX_TIMESTAMP(row_end).
          
          DECIMAL type is now allowed as RANGE partitioning, we can partition by
          UNIX_TIMESTAMP() (but not for DATETIME which depends on local timezone
          of course).
      

      However, the behavior looks strange.

      MariaDB [test]> create or replace table t (d decimal(2,1)) partition by range (d) (partition p1 values less than (10), partition p2 values less than (20));
      Query OK, 0 rows affected (0.690 sec)
       
      MariaDB [test]> insert into t values (9.9);
      Query OK, 1 row affected (0.049 sec)
       
      MariaDB [test]> select * from t partition (p1);
      Empty set (0.000 sec)
       
      MariaDB [test]> select * from t partition (p2);
      +------+
      | d    |
      +------+
      |  9.9 |
      +------+
      1 row in set (0.000 sec)
      

      or another way to put it,

      MariaDB [test]> create or replace table t (d decimal(2,1)) partition by range (d) (partition p1 values less than (10));
      Query OK, 0 rows affected (0.639 sec)
       
      MariaDB [test]> insert into t values (9.9);
      ERROR 1526 (HY000): Table has no partition for value 10
      

      Obviously, it temporarily rounds the value before finding a partition, but I don't think the outcome is reasonable. I couldn't find any documentation, either in MariaDB KB or in the version of SQL standard which I have access to on how it should really behave (and MySQL doesn't support it even in 8.0, so can't compare with it either).

      Attachments

        Issue Links

          Activity

            People

              midenok Aleksey Midenkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.