[MDEV-657] LP:873142 - GREATEST() does not always return same signness of argument types. Created: 2011-10-13  Updated: 2015-10-01  Resolved: 2015-10-01

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5.36, 10.0.9
Fix Version/s: 10.1.8

Type: Bug Priority: Minor
Reporter: Antony T Curtis (Inactive) Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: Launchpad, upstream

Attachments: XML File LPexportBug873142.xml    
Sprint: 10.1.8-4

 Description   

Description:
There is a problem on all platforms; to varying degrees.

Windows 32bit and Windows 64bit:
When supplied BIGINT UNSIGNED, the return type is a BIGINT SIGNED,

Others:
The return type appears to be valid however for BIGINT values, the comparison is always
performed as if the values were signed.

Workaround would be to use IF(x>y,x,y) function instead.

How to repeat:
To reproduce this bug, create this simple table.

DROP TABLE `cma`;
 
CREATE TABLE `cma` (
 
  `a` binary(16) NOT NULL,
 
  `b` bigint(20) unsigned NOT NULL,
 
  PRIMARY KEY (`a`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
-- Execute the following statement twice
 
 
 
INSERT INTO cma
 
(a, b)
 
VALUES ('foobar',13836376518955650385)
 
ON DUPLICATE KEY UPDATE
 
b=GREATEST(b,VALUES(b));



 Comments   
Comment by Antony T Curtis (Inactive) [ 2011-10-13 ]

Win32/Win64: GREATEST() does not always return same signness of argument types.
This problem occurs on Windows 32bit and Windows 64bit.

To reproduce this bug, create this simple table.

DROP TABLE `cma`;
CREATE TABLE `cma` (
`a` binary(16) NOT NULL,
`b` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

– Execute the following statement twice

INSERT INTO cma
(a, b)
VALUES ('foobar',13836376518955650385)
ON DUPLICATE KEY UPDATE
b=GREATEST(b,VALUES(b));

A workaround may be to use CAST(GREATEST(...) AS UNSIGNED) .

Comment by Rasmus Johansson (Inactive) [ 2011-10-13 ]

Launchpad bug id: 873142

Comment by Alexander Barkov [ 2015-10-01 ]

A script that demonstrates the problem:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a bigint(20) unsigned NOT NULL PRIMARY KEY);
 
INSERT INTO t1 (a) VALUES (13836376518955650385) ON DUPLICATE KEY UPDATE a=GREATEST(a,VALUES(a));
 
INSERT INTO t1 (a) VALUES (13836376518955650385) ON DUPLICATE KEY UPDATE a=GREATEST(a,VALUES(a));
 
SHOW WARNINGS;
 
SELECT * FROM t1;

returns a warning:

+---------+------+--------------------------------------------+
 
| Level   | Code | Message                                    |
 
+---------+------+--------------------------------------------+
 
| Warning | 1264 | Out of range value for column 'a' at row 1 |
 
+---------+------+--------------------------------------------+

and this result set:

+---+
 
| a |
 
+---+
 
| 0 |
 
+---+

The expected result is to produce not warnings and return 13836376518955650385.

Comment by Alexander Barkov [ 2015-10-01 ]

Repeatable in MySQL-5.7.8

Generated at Thu Feb 08 06:30:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.