Bug #99120 Incorrect result for query that uses an AND operator on floats
Submitted: 31 Mar 8:48 Modified: 1 Apr 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

[31 Mar 8:48] Manuel Rigger
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).
[31 Mar 10:04] MySQL Verification Team
Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh
[1 Apr 7:40] Roy Lyseng
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;