MySQL Bugs: #95958: AND/OR/XOR compute wrong result for small floating-point numbers in TEXT columns

Bug #95958	AND/OR/XOR compute wrong result for small floating-point numbers in TEXT columns
Submitted:	24 Jun 2019 18:30	Modified:	1 Apr 2020 9:57
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

Description:
OR, XOR, and AND compute incorrect results for TEXT column arguments that contain small floating-point numbers.

How to repeat:
CREATE TABLE t0(c0 TEXT);
INSERT INTO t0(c0) VALUES("0.5");
SELECT * FROM t0 WHERE t0.c0 AND 1; -- expected: 0.5, actual: no row is fetched
SELECT * FROM t0 WHERE t0.c0 OR 0; -- expected: 0.5, actual: no row is fetched
SELECT * FROM t0 WHERE t0.c0 XOR 0;  -- expected: 0.5, actual: no row is fetched

This bug seems similar to a bug that I previously reported on a XOR misbehavior (see https://bugs.mysql.com/bug.php?id=95927). However, the trigger for the XOR are literals. The AND and OR operators work correctly for literals, but not for column arguments, which is why I think that this bug is unique:

SELECT * FROM t0 WHERE "0.5" AND 1; -- 0.5
SELECT * FROM t0 WHERE "0.5" OR 0; -- 0.5

That the expressions should be TRUE can be verified with the following statement:

SELECT t0.c0 AND 1, t0.c0 OR 0, t0.c0 XOR 0 FROM t0; -- 1 | 1 | 0

Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh

I could just verify in 8.0.17 that this issue has been fixed and that the test case works as expected. Thanks for fixing this!

I'm closing this issue, since this is working now.