Bug #83160 binary in (char) return inconsistent results
Submitted: 27 Sep 2016 7:22 Modified: 2 Jan 2020 22:42
Reporter: 帅 Bang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6 OS:Linux
Assigned to: CPU Architecture:Any

[27 Sep 2016 7:22] 帅 Bang
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.
[27 Sep 2016 7:53] MySQL Verification Team
Hello Bang,

Thank you for the report.
Observed this with 5.5.52, 5.6.33 and 5.7.15.

Thanks,
Umesh
[2 Jan 2020 22:42] Roy Lyseng
Posted by developer:
 
Fixed in 8.0.18.
Now, LEAST converts both arguments to binary string before performing the minimum operation.