Bug #95942 TEXT column used as boolean incorrectly evaluates to false
Submitted: 24 Jun 2019 9:53 Modified: 1 Apr 2020 9:56
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 OS:Ubuntu
Assigned to: CPU Architecture:x86

[24 Jun 2019 9:53] Manuel Rigger 
Description:
When using a TEXT column directly in a WHERE condition, the expression incorrectly evaluates to FALSE for small floating-point numbers (such as 0.9 or -0.9) stored as a string.

How to repeat:
CREATE TABLE t0(c0 TEXT);
INSERT INTO t0(c0) VALUES(0.9);
SELECT * FROM t0 WHERE t0.c0; -- expected: row is fetched, actual: row is not fetched

This works as expected when directly using a text constant:

SELECT * FROM t0 WHERE "0.9"; -- row is fetched
SELECT * FROM t0 WHERE "0.9" IS TRUE; -- row is fetched
SELECT t0.c0 IS TRUE FROM t0; -- 1
[24 Jun 2019 10:09] MySQL Verification Team 
Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh
[23 Jul 2019 11:22] Manuel Rigger 
I could just verify that this now works correctly in 8.0.17. Thanks for fixing this!
[1 Apr 2020 9:56] Manuel Rigger 
I'm closing this issue, since this is working now.