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

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

                Dates

                Created:
                Updated:
                Resolved: