MySQL Bugs: #95975: Unexpected result for IN operator and constants

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

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

Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh

Posted by developer:
 
You're correct, type conversion applies as needed for all arguments. I've updated the IN() description.