[MDEV-23032] FLOOR()/CEIL() incorrectly calculate the precision of a DECIMAL(M,D) column. Created: 2020-06-27  Updated: 2020-10-06  Resolved: 2020-08-04

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4.16, 10.5.7

Type: Bug Priority: Major
Reporter: Gagan Goel (Inactive) Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Attachments: File MDEV-23032.patch    
Issue Links:
Relates
relates to MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ... Closed
relates to MDEV-23320 Hex hybrid constants 0xHHHH work badl... Closed
relates to MDEV-23323 Rounding functions return a wrong dat... Closed
relates to MDEV-23337 Rounding functions create a wrong dat... Closed
relates to MDEV-23350 ROUND(bigint_22_or_longer) returns a ... Closed
relates to MCOL-641 Full DECIMAL support in ColumnStore Closed
relates to MDEV-23311 CEILING() and FLOOR() convert tempora... Closed
relates to MDEV-23351 Rounding functions return wrong data ... Closed
relates to MDEV-23366 ROUND(18446744073709551615,rand()*0) ... Closed
relates to MDEV-23367 ROUND(18446744073709551615,-1) return... Closed

 Description    

MariaDB [test]> create table i1 (a decimal(38,10));
 
Query OK, 0 rows affected (0.057 sec)
 
 
 
MariaDB [test]> create table i2 as select floor(a), ceil(a) from i1;
 
Query OK, 0 rows affected (0.019 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> show create table i2;
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                               |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
 
| i2    | CREATE TABLE `i2` (
 
  `floor(a)` decimal(28,0) DEFAULT NULL,
 
  `ceil(a)` decimal(28,0) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> insert into i1 values (9999999999999999999999999999.9999999999), (-9999999999999999999999999999.9999999999);
 
Query OK, 2 rows affected (0.002 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> create table i3 as select floor(a), ceil(a) from i1;
 
ERROR 1264 (22003): Out of range value for column 'ceil(a)' at row 1
 
MariaDB [test]> create table i3 as select floor(a) from i1;
 
ERROR 1264 (22003): Out of range value for column 'floor(a)' at row 2

For DECIMAL(M,0), i2 should have M=29, instead of M=28 as in the above. This is because M can exceed by 1 for FLOOR/CEIL functions, for example, when we insert the 2 rows into i1, the result of the functions would contain 29 digits, instead of 28.

 Comments   
Comment by Gagan Goel (Inactive) [ 2020-06-27 ]

MDEV-23032.patch

Attached is a quick patch I wrote to get this case working.

Comment by Alexander Barkov [ 2020-07-27 ]

Update: this problem was fixed by MDEV-23320

A related problem:

CREATE OR REPLACE TABLE t1 AS SELECT floor(0xffffffffff);


ERROR 1264 (22003): Out of range value for column 'floor(0xffffffffff)' at row 1

Comment by Alexander Barkov [ 2020-07-27 ]

A related problem (now reported in MDEV-23323):

CREATE OR REPLACE TABLE t1 (a BIT(64));
 
CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a) FROM t1;
 
DESC t2;


+----------+---------------+------+-----+---------+-------+
 
| Field    | Type          | Null | Key | Default | Extra |
 
+----------+---------------+------+-----+---------+-------+
 
| FLOOR(a) | decimal(64,0) | YES  |     | NULL    |       |
 
+----------+---------------+------+-----+---------+-------+

Looks redundant. A DECIMAL(19,0) or even a BIGINT() column would be enough.

Comment by Alexander Barkov [ 2020-07-27 ]

Update: This problem was fixed by MDEV-23323

A related problem:

CREATE OR REPLACE TABLE t1 (a SET('1000000000000000000000000000000000000000000000000000000000000000000000000000000000000'));
 
INSERT INTO t1 VALUES (1);
 
CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a) FROM t1;
 
SELECT * FROM t2;
 
DESC t2;


+----------+
 
| FLOOR(a) |
 
+----------+
 
|        1 |
 
+----------+


+----------+---------------+------+-----+---------+-------+
 
| Field    | Type          | Null | Key | Default | Extra |
 
+----------+---------------+------+-----+---------+-------+
 
| FLOOR(a) | decimal(65,0) | YES  |     | NULL    |       |
 
+----------+---------------+------+-----+---------+-------+

Looks redundant. In numeric context SET can return a value beween 0 and 0xFF or between and 0xFFFF, depending on the amount of values in SET. INT should be enough (or even TINYINT/SMALLINT).

Comment by Alexander Barkov [ 2020-07-27 ]

Update 2020-06-29: The problem described below was fixed under terms of MDEV-23311. FLOOR(TIME) and CEILING(TIME) now return TIME rather than BIGINT.

A related problem:

CREATE OR REPLACE TABLE t1 (a TIME(6));
 
INSERT INTO t1 VALUES ('10:20:30');
 
CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a) FROM t1;
 
SELECT * FROM t2;
 
DESC t2;


+----------+
 
| FLOOR(a) |
 
+----------+
 
|   102030 |
 
+----------+


+----------+------------+------+-----+---------+-------+
 
| Field    | Type       | Null | Key | Default | Extra |
 
+----------+------------+------+-----+---------+-------+
 
| FLOOR(a) | bigint(12) | YES  |     | NULL    |       |
 
+----------+------------+------+-----+---------+-------+

Looks redundant. FLOOR(time) should never return more than 9 digits (hhhmmss). So INT(9) should be enough.

Or even better TIME(0), like in this script:

CREATE OR REPLACE TABLE t1 (a TIME(6));
 
CREATE OR REPLACE TABLE t2 AS SELECT ROUND(a) FROM t1;
 
DESC t2;


+----------+------+------+-----+---------+-------+
 
| Field    | Type | Null | Key | Default | Extra |
 
+----------+------+------+-----+---------+-------+
 
| ROUND(a) | time | YES  |     | NULL    |       |
 
+----------+------+------+-----+---------+-------+

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