[MDEV-16196] GREATEST incorrectly checks a fields' datatype during calculations Created: 2018-05-16  Updated: 2018-06-20  Resolved: 2018-06-15

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.2.14
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Jacques Amar Assignee: Alexander Barkov
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Linux CentOS 7 64 bit

Issue Links:
Relates
relates to MDEV-14910 Unexpected "BIGINT UNSIGNED value is ... Open

 Description   

If a field is created with :
fieldname SMALLINT UNSIGNED DEFAULT 0

Then using

GREATEST(0, fieldname-1)

in SELECT or UPDATE
will trigger an error and not give results
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '`db`.`table`.`fieldname` - 1'

Actual example for SELECT:

select greatest(1,loc_count-1) from Custom_Lists;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '`pj_dev`.`Custom_Lists`.`loc_count` - 1'

same for UPDATE

The error should only be triggered in a direct field,not using GREATEST()

 Comments   
Comment by Alexander Barkov [ 2018-06-15 ]

This script demonstrates the problem:

CREATE OR REPLACE TABLE t1 (a SMALLINT UNSIGNED);
 
INSERT INTO t1 VALUES (0);
 
SELECT GREATEST(0, a-1) FROM t1;

Comment by Alexander Barkov [ 2018-06-15 ]

Also repeatable with COALESCE:

CREATE OR REPLACE TABLE t1 (a SMALLINT UNSIGNED);
 
INSERT INTO t1 VALUES (0);
 
SELECT COALESCE(a-1,0) FROM t1;

Comment by Alexander Barkov [ 2018-06-15 ]

Also repeatable with just subtraction:

CREATE OR REPLACE TABLE t1 (a SMALLINT UNSIGNED);
 
INSERT INTO t1 VALUES (0);
 
SELECT a-1 FROM t1;

Comment by Alexander Barkov [ 2018-06-15 ]

This is expected behavior.

Please use:

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

to get a signed result.

https://mariadb.com/kb/en/library/sql-mode/
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_unsigned_subtraction

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