Bug #95938 Function return value with newline yields wrong result when used as a boolean
Submitted: 24 Jun 2019 6:51 Modified: 23 Jul 2019 11:46
Reporter: Manuel Rigger Email Updates:
Status: Verified 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

[24 Jun 2019 6:51] Manuel Rigger 
Description:
A number stored in a string that contains a newline at the beginning yields an incorrect result when that string is returned from a function such as COALESCE() or IFNULL() and is then used in a boolean context.

How to repeat:
CREATE TABLE t0(c0 TEXT);
INSERT IGNORE INTO t0(c0) VALUES("\n123");
SELECT * FROM t0 WHERE COALESCE(t0.c0); -- expected: row is fetched, actual: no row is fetched
SELECT * FROM t0 WHERE IFNULL(t0.c0, 1); -- expected: row is fetched, actual: no row is fetched

SELECT * FROM t0 WHERE COALESCE(t0.c0) IS TRUE; -- row is fetched
SELECT * FROM t0 WHERE IFNULL(t0.c0, 1) IS TRUE; -- row is fetched
SELECT * FROM t0 WHERE t0.c0; -- row is fetched
[24 Jun 2019 7:05] MySQL Verification Team 
Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh
[21 Jul 2019 19:11] Oleksandr Peresypkin 
The patches in the ticket https://bugs.mysql.com/bug.php?id=95960 fix the issue as well.
[22 Jul 2019 16:14] Manuel Rigger 
Thanks a lot for fixing this!
[23 Jul 2019 11:20] Manuel Rigger 
I could just verify that your patches fixed this issue in 8.0.17. Thanks again!
[23 Jul 2019 11:46] Manuel Rigger 
I just found that the test cases in https://bugs.mysql.com/bug.php?id=95960 still fail, so it seems that the patches have not been merged yet, and that the bug reported here might have been fixed independently.