Bug #97339 bin() function return Incorrect result set
Submitted: 23 Oct 8:06 Modified: 29 Oct 13:35
Reporter: ashe sun (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7.18, 5.7.28, 5.6.46 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[23 Oct 8:06] ashe sun
Description:
Result set of  select bin(-1) don't match  select bin(-1) union all select bin(-1).
----------------------------------------------------------------------------------
mysql> \s
--------------
mysql  Ver 8.0.17 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

Connection id:		777
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.18-log MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/tmp/mysql_13307.sock
Uptime:			1 day 5 hours 5 min 56 sec

Threads: 1  Questions: 15630122  Slow queries: 0  Opens: 308  Flush tables: 2  Open tables: 104  Queries per second avg: 149.205
--------------

mysql>
mysql> select bin(-1);
+------------------------------------------------------------------+
| bin(-1)                                                          |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111111 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select bin(-1) union select bin(-1);
+------------------+
| bin(-1)          |
+------------------+
| 1111111111111111 |
+------------------+
1 row in set (0.00 sec)

mysql> select bin(-1) union all select bin(-1);
+------------------+
| bin(-1)          |
+------------------+
| 1111111111111111 |
| 1111111111111111 |
+------------------+
2 rows in set (0.00 sec)

How to repeat:
As description.

Suggested fix:
The root cause is character sets.  
mysql> set names latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> select bin(-1);
+------------------------------------------------------------------+
| bin(-1)                                                          |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111111 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select bin(-1) union all select bin(-1);
+------------------------------------------------------------------+
| bin(-1)                                                          |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111111 |
| 1111111111111111111111111111111111111111111111111111111111111111 |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)
[23 Oct 8:46] Umesh Shastry
Hello ashe sun,

Thank you for the report and feedback.

regards,
Umesh
[29 Oct 13:31] ashe sun
bin() function return Incorrect result set

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: Item_func_conv.patch (application/octet-stream, text), 647 bytes.

[29 Oct 13:35] ashe sun
mysql> select bin(-2);
+------------------------------------------------------------------+
| bin(-2)                                                          |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select bin(-2) union all select bin(-2);
+------------------------------------------------------------------+
| bin(-2)                                                          |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select bin(-2);
+------------------------------------------------------------------+
| bin(-2)                                                          |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select bin(-2) union all select bin(-2);
+------------------------------------------------------------------+
| bin(-2)                                                          |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> select bin(-2);
+------------------------------------------------------------------+
| bin(-2)                                                          |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select bin(-2) union all select bin(-2);
+------------------------------------------------------------------+
| bin(-2)                                                          |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)
[30 Oct 6:25] Umesh Shastry
Thank you for the contribution.

regards,
Umesh