Description:
mysql> create table sb(a binary, b char(123));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into sb values(0,"abc");
Query OK, 1 row affected (0.00 sec)
mysql> select * from sb where a in( "abc");
0 rows in set (0.00 sec) //empty set means that 0(binary) in "abc"(char) is false
This is quite ok since when we compare binary with char in mysql, string related rule is applied.
Ok, let's take a look at another example:
mysql> create table sc ( c1 decimal(36,18), c2 binary(47));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into sc values (123.4, 'abcd');
Query OK, 1 row affected (0.01 sec)
mysql> select * from sc where least(c1, c2) in ("abc") ;
+------------------------+-------------------------------------------------+
| c1 | c2 |
+------------------------+-------------------------------------------------+
| 123.400000000000000000 | abcd |
+------------------------+-------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
This means that least(c1, c2) in ("abc") is true. It maybe inconsistent. Why ?
Let us dig into it to see what is going on with mysql.
Connect mysql with the option --column-type-info:
mysql -uroot -hip -Pport --column-type-info;
mysql> select least(c1,c2) from sc;
Type: STRING //yeah, string.
Collation: binary (63) //yeah, binary
Length: 23
Max_length: 1
Decimals: 31
Flags: BINARY
+--------------+
| least(c1,c2) |
+--------------+
| 0 |
+--------------+
1 row in set, 1 warning (0.00 sec)
that is to say, the result what least(decimal, binary) returns is string(binary).
So, IMHO, In terms of least(c1,c2) in ("abc") which tends to mean 0(string/binary) in "abc"(string/var_string), string related rule rather than numeric related rule should be applied so false is expected.
How to repeat:
create table sb(a binary, b char(123));
insert into sb values(0,"abc");
create table sc ( c1 decimal(36,18), c2 binary(47));
insert into sc values (123.4, 'abcd');
select * from sb where a in( "abc");
select * from sc where least(c1, c2) in ("abc") ;
Suggested fix:
both return empty set or both return one record.
Anyway, consistency is of much importance and significance.