MySQL Bugs: #118982: Unexpected result of IN operator

Bug #118982	Unexpected result of IN operator
Submitted:	10 Sep 6:55	Modified:	26 Sep 11:36
Reporter:	chi zhang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	9.4.0	OS:	Linux
Assigned to:		CPU Architecture:	Any

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;
```

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

Thank you for the report.

Posted by developer:
 
This is not a bug.

Due to the automatic type conversions, both the '\6' value and the 'A' value are converted to double precision values, and thus this is
interpreted as

  0 NOT IN (0, 449007834)

which is FALSE.

The reason why the prepared statement ends with a different result is that the type of the parameter is deduced from other values in the operation,
and hence conversion is slightly different. This deduction is closer to the SQL standard, than the non-standard conversions in the non-prepared case.

When mixing numeric and non-numeric types in an expression, it is recommended to use explicit CAST operators to reduce number of surprises.

Hi,

Thanks for your reply!

However, in the prepared statement, there is already a type cast `CAST(? AS DECIMAL)` to specify the type of the argument, which is the same as the normal statement, so why do they still have inconsistent behaviour?

I have another small question: in the prepared statement, why '\6' value and the 'A' value not converted to the same double precision values

Maybe it is the latter that is causing the problem here. There may be an issue that causes '\\' to be handled differently in regular and prepared statements, since the prepared statement is specified within quotes.