Bug #97339 bin() function return Incorrect result set

• Submitted: 23 Oct 2019 8:06
• Modified: 29 Oct 2019 13:35
• Reporter: ashe sun (OCA)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.7.18, 5.7.28, 5.6.46
• OS: Any
• CPU Architecture: Any
• Tags: Contribution

[23 Oct 2019 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 2019 8:46] MySQL Verification Team

Hello ashe sun,

Thank you for the report and feedback.

regards,
Umesh

[29 Oct 2019 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 2019 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 2019 6:25] MySQL Verification Team

Thank you for the contribution.

regards,
Umesh

[23 Jul 2021 13:05] Frederic Descamps

This bug was fixed in 8.0.1