Bug #118982 Unexpected result of IN operator

• Submitted: 10 Sep 6:55
• Modified: 11 Sep 12:57
• Reporter: chi zhang
• Status: Analyzing
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 9.4.0
• OS: Linux
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[10 Sep 6:55] chi zhang

Description:
Hi,

In the following test case

```
CREATE TABLE t0(c0 CHAR(100));
INSERT INTO t0 VALUES ('A');
SELECT (('
6' IS TRUE) NOT IN (t0.c0, CAST(449007834 AS DECIMAL))) FROM t0;
```

There is only one value `A` in the column c0, so I expected the expression `(('
6' IS TRUE) NOT IN (t0.c0, CAST(449007834 AS DECIMAL)))` to be true, however, the MySQL returns `0`

The equivalent prepared statement can return the correct result on this case:
```
SET @a = 449007834;
PREPARE prepare_query FROM "SELECT (('
6' IS TRUE) NOT IN (t0.c0, CAST(? AS DECIMAL))) FROM t0";
EXECUTE prepare_query USING @a;
```

How to repeat:
```
CREATE TABLE t0(c0 CHAR(100));
INSERT INTO t0 VALUES ('A');
SELECT (('
6' IS TRUE) NOT IN (t0.c0, CAST(449007834 AS DECIMAL))) FROM t0;
```

[10 Sep 14:27] chi zhang

The test case seems to be formatted, there should be two "\" symbols before "6".