[MDEV-14910] Unexpected "BIGINT UNSIGNED value is out of range" for minus Created: 2018-01-10  Updated: 2024-01-03

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

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

Issue Links:
Duplicate
is duplicated by MDEV-23481 BIGINT UNSIGNED value is out of range... Closed
Relates
relates to MDEV-5847 MySQL Bug#13596893 - "ERROR 1690 (220... Confirmed
relates to MDEV-12858 Out-of-range error for CREATE..SELECT... Closed
relates to MDEV-16196 GREATEST incorrectly checks a fields'... Closed
Epic Link: Data type cleanups

 Description   

This script returns an error unexpectedly:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (
 
  c1 BIGINT UNSIGNED,
 
  c2 BIGINT UNSIGNED
 
);
 
INSERT INTO t1 VALUES (20030516235329,20000127231341);
 
SELECT c2-c1 FROM t1;


ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`t1`.`c2` - `test`.`t1`.`c1`)'

It tries to create a BIGIN UNSIGNED column as a result:

DROP TABLE IF EXISTS t2;
 
CREATE TABLE t2 AS SELECT c2-c1 FROM t1 LIMIT 0;
 
SHOW CREATE TABLE t2;


+-------+-------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                          |
 
+-------+-------------------------------------------------------------------------------------------------------+
 
| t2    | CREATE TABLE `t2` (
 
  `c2-c1` bigint(21) unsigned DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+-------------------------------------------------------------------------------------------------------+

The choice beween BIGINT and UNSIGNED BIGINT is controlled by the NO_UNSIGNED_SUBTRACTION flag in sql_mode.
There should probably be a way to create a DECIMAL column instead.

 Comments   
Comment by Alexander Barkov [ 2018-01-10 ]

The same problem is repeatable with TIMESTAMP columns, which are treated as UNSIGNED:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (
 
  `c1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 
  `c2` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
 
);
 
INSERT INTO t1 VALUES ('2003-05-16 23:53:29','2000-01-27 23:13:41');
 
SELECT c2-c1 FROM t1;

Comment by Tadas BalaiĊĦis [ 2024-01-03 ]

The error reproduced in 11.1.2-MariaDB. Very undesired behavior. Workaround is to use cast function:

SELECT cast(c2 AS INT)-cast(c1 AS INT) FROM t1;

Badly that 

SELECT cast(c2 AS INT)-c1 FROM t1;

still produces 1690 error.

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