[MDEV-21195] INSERT chooses wrong partition for RANGE partitioning by DECIMAL column Created: 2019-12-02  Updated: 2020-02-03  Resolved: 2020-02-03

Status: Closed
Project: MariaDB Server
Component/s: Partitioning
Affects Version/s: 10.3, 10.4
Fix Version/s: 10.3.23, 10.4.13

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-18501 Partition pruning doesn't work for hi... Closed

 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).

 Comments   
Comment by Alexander Barkov [ 2020-01-29 ]

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);

Comment by Alexander Barkov [ 2020-01-29 ]

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.

Generated at Thu Feb 08 09:05:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.