[MDEV-23323] Rounding functions return a wrong data type for a BIT, ENUM, SET argument - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.4.14, 10.5.5
Component/s: Data types
Labels:
None

Description

CREATE OR REPLACE TABLE t1 (a BIT(64));

INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF);

CREATE OR REPLACE TABLE t2 AS

  SELECT a, FLOOR(a), CEILING(a), TRUNCATE(a,0), ROUND(a) FROM t1;

SHOW CREATE TABLE t2;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                                                                                    |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| t2    | CREATE TABLE `t2` (

  `a` bit(64) DEFAULT NULL,

  `FLOOR(a)` decimal(64,0) DEFAULT NULL,

  `CEILING(a)` decimal(64,0) DEFAULT NULL,

  `TRUNCATE(a,0)` bigint(64) unsigned DEFAULT NULL,

  `ROUND(a)` decimal(64,0) unsigned DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The data type for all columns with functions are wrong. The expected data type is BIGINT(20) UNSIGNED.

Attachments

Issue Links

relates to

MDEV-23032 FLOOR()/CEIL() incorrectly calculate the precision of a DECIMAL(M,D) column.

Closed

Activity

Alexander Barkov added a comment - 2020-07-29 07:37 - edited

The same problem is repeatable with ENUM:

CREATE OR REPLACE TABLE t1 (a ENUM('999999999999999999999999999999999999999999999999999999999999'));

INSERT INTO t1 VALUES (1);

CREATE OR REPLACE TABLE t2 AS

  SELECT a, FLOOR(a), CEILING(a), TRUNCATE(a,0), ROUND(a) FROM t1;

SHOW CREATE TABLE t2;

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                                                                                                                                |

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| t2    | CREATE TABLE `t2` (

  `a` enum('999999999999999999999999999999999999999999999999999999999999') DEFAULT NULL,

  `FLOOR(a)` decimal(60,0) DEFAULT NULL,

  `CEILING(a)` decimal(60,0) DEFAULT NULL,

  `TRUNCATE(a,0)` double(17,0) DEFAULT NULL,

  `ROUND(a)` double(17,0) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The same problem is repeatable with SET:

CREATE OR REPLACE TABLE t1 (a SET('999999999999999999999999999999999999999999999999999999999999'));

INSERT INTO t1 VALUES (1);

CREATE OR REPLACE TABLE t2 AS

  SELECT a, FLOOR(a), CEILING(a), TRUNCATE(a,0), ROUND(a) FROM t1;

SHOW CREATE TABLE t2;

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                                                                                                                               |

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| t2    | CREATE TABLE `t2` (

  `a` set('999999999999999999999999999999999999999999999999999999999999') DEFAULT NULL,

  `FLOOR(a)` decimal(60,0) DEFAULT NULL,

  `CEILING(a)` decimal(60,0) DEFAULT NULL,

  `TRUNCATE(a,0)` double(17,0) DEFAULT NULL,

  `ROUND(a)` double(17,0) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Alexander Barkov added a comment - 2020-07-29 07:37 - edited The same problem is repeatable with ENUM: CREATE OR REPLACE TABLE t1 (a ENUM( '999999999999999999999999999999999999999999999999999999999999' )); INSERT INTO t1 VALUES (1); CREATE OR REPLACE TABLE t2 AS SELECT a, FLOOR(a), CEILING(a), TRUNCATE (a,0), ROUND(a) FROM t1; SHOW CREATE TABLE t2; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `a` enum('999999999999999999999999999999999999999999999999999999999999') DEFAULT NULL, `FLOOR(a)` decimal(60,0) DEFAULT NULL, `CEILING(a)` decimal(60,0) DEFAULT NULL, `TRUNCATE(a,0)` double(17,0) DEFAULT NULL, `ROUND(a)` double(17,0) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ The same problem is repeatable with SET: CREATE OR REPLACE TABLE t1 (a SET ( '999999999999999999999999999999999999999999999999999999999999' )); INSERT INTO t1 VALUES (1); CREATE OR REPLACE TABLE t2 AS SELECT a, FLOOR(a), CEILING(a), TRUNCATE (a,0), ROUND(a) FROM t1; SHOW CREATE TABLE t2; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `a` set('999999999999999999999999999999999999999999999999999999999999') DEFAULT NULL, `FLOOR(a)` decimal(60,0) DEFAULT NULL, `CEILING(a)` decimal(60,0) DEFAULT NULL, `TRUNCATE(a,0)` double(17,0) DEFAULT NULL, `ROUND(a)` double(17,0) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

MariaDB Server

Rounding functions return a wrong data type for a BIT, ENUM, SET argument

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration