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

INSERT chooses wrong partition for RANGE partitioning by DECIMAL column

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

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18501 Partition pruning doesn't work for historical queries

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            midenok Aleksey Midenkov made changes -
            Field Original Value New Value
            Status Open [ 1 ] In Progress [ 3 ]
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            Summary Unexpected behavior of RANGE partitioning by DECIMAL column INSERT into wrong partition for RANGE partitioning by DECIMAL column
            midenok Aleksey Midenkov made changes -
            Summary INSERT into wrong partition for RANGE partitioning by DECIMAL column INSERT chooses wrong partition for RANGE partitioning by DECIMAL column
            midenok Aleksey Midenkov made changes -
            Assignee Aleksey Midenkov [ midenok ] Alexander Barkov [ bar ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            bar Alexander Barkov added a comment - - edited

            Hi midenok,

            I think your patch does not fix the following scenarios:

            create or replace table t (
            		 
              d decimal(2,1)) partition by range (d+1)
            		 
              (partition p1 values less than (10),
            		 
               partition p2 values less than (20)
            		 
            );
            		 
             
            		 
            insert into t values (8.9);
            		 
            select * from t partition (p1);
            		 
            select * from t partition (p2);


            SET time_zone='+00:00';
            		 
            create or replace table t (
            		 
              d timestamp(1)) partition by range (unix_timestamp(d))
            		 
              (partition p1 values less than (1577836800),
            		 
               partition p2 values less than (2000000000)
            		 
            );
            		 
             
            		 
            insert into t values (from_unixtime(1577836799.9));
            		 
            select * from t partition (p1);
            		 
            select * from t partition (p2);

            bar Alexander Barkov added a comment - - edited Hi midenok , I think your patch does not fix the following scenarios: create or replace table t ( d decimal (2,1)) partition by range (d+1) (partition p1 values less than (10), partition p2 values less than (20) );   insert into t values (8.9); select * from t partition (p1); select * from t partition (p2); SET time_zone= '+00:00' ; create or replace table t ( d timestamp (1)) partition by range (unix_timestamp(d)) (partition p1 values less than (1577836800), partition p2 values less than (2000000000) );   insert into t values (from_unixtime(1577836799.9)); select * from t partition (p1); select * from t partition (p2);
            bar Alexander Barkov made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Aleksey Midenkov [ midenok ]
            bar Alexander Barkov added a comment - - edited

            I don't think we need another virtual method val_int(decimal_round_mode round_type).
            Let's add switch(item_expr->cmp_type()) inside part_val_int() and in case of DECIMAL_RESULT call val_decimal() followed by rounding with TRUNCATE.

            bar Alexander Barkov added a comment - - edited I don't think we need another virtual method val_int(decimal_round_mode round_type) . Let's add switch(item_expr->cmp_type()) inside part_val_int() and in case of DECIMAL_RESULT call val_decimal() followed by rounding with TRUNCATE.
            midenok Aleksey Midenkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            midenok Aleksey Midenkov made changes -
            Fix Version/s 10.3.23 [ 24222 ]
            Fix Version/s 10.4.13 [ 24223 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 101542 ] MariaDB v4 [ 157035 ]

            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.