Bug #95975 Unexpected result for IN operator and constants
Submitted: 25 Jun 2019 10:49 Modified: 31 Jul 2019 11:45
Reporter: Manuel Rigger Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0.16, 5.7.26, 5.6.44 OS:Ubuntu
Assigned to: CPU Architecture:x86

[25 Jun 2019 10:49] Manuel Rigger
Description:
For an IN expression "expr IN (value,...)", the documentation states that "if all values are constants, they are evaluated according to the type of expr and sorted."

For an expression "a" IN (0), I would expect that it evaluates to FALSE, since expr="a", expr has a TEXT type, 0 should get converted to a text type, making the expression equivalent to "a" IN (CAST(0 AS CHAR)). However, while "a" IN (CAST(0 AS CHAR)) yields FALSE, "a" IN (0) unexpectedly yields TRUE.

Note that the documentation explicitly states this behavior for constants only. "Otherwise, type conversion takes place according to the rules described in Section 12.2, “Type Conversion in Expression Evaluation”, but applied to all the arguments." It seems that for this expression, the type conversion results of 12.2 are applied, for which both the type of expr and the values are considered, so that the constants "are evaluated according to the type of expr" seems to be incorrect.

I somehow suspect that this is a documentation bug or ambiguity, and that the type conversions applied for constants and non-constants are the same.

How to repeat:
SELECT "a" IN (0); -- expected: 0, actual: 1
[25 Jun 2019 11:09] MySQL Verification Team
Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh
[31 Jul 2019 11:45] Paul Dubois
Posted by developer:
 
You're correct, type conversion applies as needed for all arguments. I've updated the IN() description.