Bug #95983 Query involving &, <, and AND operators computes incorrect result
Submitted: 25 Jun 2019 16:40 Modified: 20 Aug 2019 2:40
Reporter: Manuel Rigger Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.16, 5.7.26, 5.6.44 OS:Ubuntu
Assigned to: CPU Architecture:x86

[25 Jun 2019 16:40] Manuel Rigger 
Description:
The query below computes an incorrect result.

How to repeat:
CREATE TABLE t0(c0 INT, c1 TEXT);
INSERT INTO t0(c0, c1) VALUES(-1, "a");
SELECT * FROM t0 WHERE t0.c1 < (t0.c0 & t0.c0) AND t0.c0 = -1; -- expected: fetches row, actual: does not fetch the row

That the condition should yield TRUE can be verified with the following statement:

SELECT t0.c1 < (t0.c0 & t0.c0) AND t0.c0 = -1 FROM t0; -- 1
[26 Jun 2019 5:08] MySQL Verification Team 
Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh
[16 Jul 2019 13:12] Sveta Smirnova 
This is regression: returns a row in 5.7.25 (tested with Percona Server though)
[5 Aug 2019 19:18] Oleksandr Peresypkin 
The patches attached to https://bugs.mysql.com/bug.php?id=95964 fix this issue as well.
[20 Aug 2019 2:40] Jon Stephens 
Documented fix as follows in the MySQL 8.0.18 changelog:

    A query using WHERE text_col < (int_col & int_col) AND 
    int_col = -1 failed to return any rows as expected.

Closed.