Bug #95908 The negation of a "<=>" comparison malfunctions depending on the column's type
Submitted: 20 Jun 2019 21:59 Modified: 16 Jul 2019 16:53
Reporter: Manuel Rigger Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.16 OS:Ubuntu
Assigned to: CPU Architecture:x86
Tags: regression

[20 Jun 2019 21:59] Manuel Rigger
Description:
If a column is compared with a constant value using the <=> operator, and if that constant value is larger than what the column's data type can represent, then the negation of this comparison yields FALSE, while it should yield TRUE.

How to repeat:
CREATE TABLE t0(c0 TINYINT);
INSERT INTO t0(c0) VALUES(NULL);
SELECT * FROM t0 WHERE NOT(t0.c0 <=> 2035382037); -- expected: row is fetched, actual: no row is fetched

In this example, if c0 is TINYINT OR MEDIUMINT, the WHERE expression yields 0, but for INT or BIGINT the expression yields 1. The expression should also yield 1 for TINYINT and MEDIUMINT:

SELECT NOT(t0.c0 <=> 2035382037) FROM t0; -- yields a row with value 1

Note that both the negated and not-negated versions of the query fetch no rows:

SELECT * FROM t0 WHERE NOT(t0.c0 <=> 2035382037); -- expected: row is fetched, actual: no row is fetched
SELECT * FROM t0 WHERE t0.c0 <=> 2035382037; -- no row is fetched
[20 Jun 2019 22:51] MySQL Verification Team
Thank you for the bug report.
[16 Jul 2019 16:53] Paul DuBois
Posted by developer:
 
Fixed in 8.0.18.

Use of the <=> operator could yield incorrect results for comparisons
involving very large constants.
[23 Jul 2019 22:03] Jon Stephens
See also BUG#96032.