Bug #95926 Row is not fetched when using a function expression that should yield TRUE

• Submitted: 21 Jun 2019 23:12
• Modified: 1 Apr 2020 9:55
• Reporter: Manuel Rigger
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.16, 5.7.26, 5.6.44
• OS: Ubuntu
• CPU Architecture: x86

[21 Jun 2019 23:12] Manuel Rigger

Description:
A row is not fetched although the WHERE condition should yield TRUE.

How to repeat:
CREATE TABLE t0(c0 INT);
INSERT INTO t0(c0) VALUES(1);
SELECT * FROM t0 WHERE IF(FALSE, t0.c0, 0.1); -- expected: row is fetched, actual: row is not fetched

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

SELECT IF(FALSE, t0.c0, 0.1) IS TRUE FROM t0; -- 1

If, for example, t0.c0 is replaced by some constant, which should not change the result, the result is as expected. As another example, if 0.1 is replaced by 1, the result is also as expected.

[21 Jun 2019 23:29] Manuel Rigger

I found a similar case:

CREATE TABLE t1(c0 INT);
INSERT INTO t1(c0) VALUES(1);
SELECT * FROM t1 WHERE COALESCE(0.4, t1.c0); -- unexpected: no row is fetched
SELECT COALESCE(0.4, t1.c0) IS TRUE FROM t1; -- 1

Is this the same bug, or a different one?

[22 Jun 2019 5:40] MySQL Verification Team

Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh

[23 Jul 2019 11:09] Manuel Rigger

I just tried this in 8.0.17 and it seems that the bug has been fixed.

[1 Apr 2020 9:55] Manuel Rigger

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