MySQL Bugs: #95908: The negation of a "<=>" comparison malfunctions depending on the column's type

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

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

Thank you for the bug report.

Posted by developer:
 
Fixed in 8.0.18.

Use of the <=> operator could yield incorrect results for comparisons
involving very large constants.

See also BUG#96032.