[MDEV-15924] Wrong field type for CAST(temporal_column AS [UN]SIGNED) Created: 2018-04-18  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.2, 10.3
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-4912 Data type plugin API version 1 Closed
relates to MDEV-13232 Assertion `(&(&share->intern_lock)->m... Closed
Epic Link: Data type cleanups

 Description    

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 AS SELECT
 
  CAST(TIME'10:10:10.123456' AS SIGNED) AS c1,
 
  CAST(TIME'10:10:10.123456' AS UNSIGNED) AS c2;
 
DESC t1;


+-------+---------------------+------+-----+---------+-------+
 
| Field | Type                | Null | Key | Default | Extra |
 
+-------+---------------------+------+-----+---------+-------+
 
| c1    | bigint(17)          | NO   |     | NULL    |       |
 
| c2    | bigint(20) unsigned | NO   |     | NULL    |       |
 
+-------+---------------------+------+-----+---------+-------+

Looks excessive. TIME can have only 7 digits in the integer part: hhhmmss.

 Comments   
Comment by Alexander Barkov [ 2018-04-18 ]

A similar problem is repeatable when casting from signed int to signed int:

DROP TABLE IF EXISTS t1,t2;
 
CREATE TABLE t1 (a INT(1));
 
CREATE TABLE t2 AS SELECT
 
  CAST(a AS SIGNED) AS c1,
 
  COALESCE(a) AS c2
 
FROM t1;
 
DESC t2;


+-------+------------+------+-----+---------+-------+
 
| Field | Type       | Null | Key | Default | Extra |
 
+-------+------------+------+-----+---------+-------+
 
| c1    | bigint(11) | YES  |     | NULL    |       |
 
| c2    | int(11)    | YES  |     | NULL    |       |
 
+-------+------------+------+-----+---------+-------+

Notice, the data type for the column c1 was erroneously detected as bigint(11). We're casting from SIGNED INT to SIGNED INT, so nothing should change. The expected data type should be int, not bigint. Notice, COALESCE() does preserve int.

Comment by Alexander Barkov [ 2018-04-18 ]

A similar problem is repeatable when casting from unsigned int to unsigned int:
Notice, the data type for the column c1 was erroneously detected as bigint(11). We're casting from UNSIGNED INT to UNSIGNED INT, so nothing should change. The expected data type should be int unsigned, not bigint unsigned. Notice, COALESCE() does preserve int unsigned.

DROP TABLE IF EXISTS t1,t2;
 
CREATE TABLE t1 (a INT(11) UNSIGNED);
 
CREATE TABLE t2 AS SELECT
 
  CAST(a AS UNSIGNED) AS c1,
 
  COALESCE(a) AS c2
 
FROM t1;
 
DESC t2;


+-------+---------------------+------+-----+---------+-------+
 
| Field | Type                | Null | Key | Default | Extra |
 
+-------+---------------------+------+-----+---------+-------+
 
| c1    | bigint(11) unsigned | YES  |     | NULL    |       |
 
| c2    | int(11) unsigned    | YES  |     | NULL    |       |
 
+-------+---------------------+------+-----+---------+-------+

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