Details
Minor
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));
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 873142
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.
Repeatable in MySQL-5.7.8
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) .