MySQL Bugs: #99120: Incorrect result for query that uses an AND operator on floats

Bug #99120	Incorrect result for query that uses an AND operator on floats
Submitted:	31 Mar 2020 8:48	Modified:	1 Apr 2020 7:40
Reporter:	Manuel Rigger	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5, 5.6, 5.6.47, 5.7.29	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Consider the following statements:

How to repeat:
CREATE TABLE t0(c0 BOOL);
INSERT INTO t0 VALUES (0);
SELECT * FROM t0 WHERE 1 AND 0.4; -- expected: {0}, actual: {}

Unexpectedly, the SELECT does not fetch any rows. 1 AND 0.4 should evaluate to 1, which means that the whole predicate should evaluate to TRUE. The negated predicate seems to correctly evaluate to FALSE:

SELECT * FROM t0 WHERE NOT (1 AND 0.4); -- {}

This bug can be reproduced on versions 5.5 and 5.6, but not on the latest version 8.0.19. It was found while testing TiDB (see https://github.com/pingcap/tidb/issues/15743).

Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh

Posted by developer:
 
This problem is fixed in MySQL 8.0 by rewriting the predicates to complete predicates.
Workaround for previous releases is to do the same rewrite, e.g:

  SELECT * FROM t0 WHERE 1<>0 AND 0.4<>0;