Bug #118033 Wrong Result of an and expression

• Submitted: 22 Apr 14:51
• Modified: 23 Apr 8:07
• Reporter: Zhaokun Xiang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.3.0
• OS: Any
• CPU Architecture: Any
• Tags: regression

[22 Apr 14:51] Zhaokun Xiang

Description:
Hi, MySQL developers.

I perform the following queries, and meet some wrong execution result.
We can see the expression `(NOT (IFNULL(t0.c0,t0.c0))) and ((((0.1) IN (t0.c0))))` is evaluated as false, but when putting it on where condition, it enables the second query produce 1 row instead of empty result.

I can reproduce it on MySQL 9.3. But on the version 9.0 or less, the query can execute correctly.

```
CREATE TABLE t0(c0 DECIMAL);
INSERT INTO t0(c0) VALUES(0);

select (NOT (IFNULL(t0.c0,t0.c0))) and ((((0.1) IN (t0.c0)))) from t0;
-- 0
select * from t0 where (NOT (IFNULL(t0.c0,t0.c0))) and ((((0.1) IN (t0.c0))));
-- 0
-- wrong result, should produce empty result

```

How to repeat:
```
CREATE TABLE t0(c0 DECIMAL);
INSERT INTO t0(c0) VALUES(0);

select (NOT (IFNULL(t0.c0,t0.c0))) and ((((0.1) IN (t0.c0)))) from t0;
-- 0
select * from t0 where (NOT (IFNULL(t0.c0,t0.c0))) and ((((0.1) IN (t0.c0))));
-- 0
-- wrong result, should produce empty result

```

[23 Apr 8:07] MySQL Verification Team

Hello Zhaokun Xiang,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh