Bug #89581 Incorrect truncation value by UNION with bin in multi-byte charset
Submitted: 8 Feb 2018 3:02 Modified: 12 Apr 2018 14:30
Reporter: tsubasa tanaka (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.59, 5.6.39, 5.7.21 OS:CentOS (7.2)
Assigned to: CPU Architecture:Any

[8 Feb 2018 3:02] tsubasa tanaka
Description:
When using character_set_connection "utf8", "utf8mb4" and "ujis", result of following query is incorrect.

SELECT bin(inet_aton('10.0.0.1')) UNION DISTINCT SELECT bin(inet_aton('127.0.0.1'));

Result should be (when character_set_connection=latin1, we got this result),

```
+---------------------------------+
| bin(inet_aton('10.0.0.1'))      |
+---------------------------------+
| 1010000000000000000000000001    |
| 1111111000000000000000000000001 |
+---------------------------------+
2 rows in set (0.00 sec)
```

But we got (when character_set_connection=utf8mbh4)

```
+----------------------------+
| bin(inet_aton('10.0.0.1')) |
+----------------------------+
| 1010000000000000           |
| 1111111000000000           |
+----------------------------+
2 rows in set (0.00 sec)
```

How to repeat:
mysql57 13> SELECT @@character_set_connection;
+----------------------------+
| @@character_set_connection |
+----------------------------+
| utf8mb4                    |
+----------------------------+
1 row in set (0.00 sec)

mysql57 13> SELECT bin(inet_aton('10.0.0.1'));
+------------------------------+
| bin(inet_aton('10.0.0.1'))   |
+------------------------------+
| 1010000000000000000000000001 |
+------------------------------+
1 row in set (0.01 sec)

mysql57 13> SELECT bin(inet_aton('127.0.0.1'));
+---------------------------------+
| bin(inet_aton('127.0.0.1'))     |
+---------------------------------+
| 1111111000000000000000000000001 |
+---------------------------------+
1 row in set (0.00 sec)

mysql57 13> SELECT bin(inet_aton('10.0.0.1')) UNION DISTINCT SELECT bin(inet_aton('127.0.0.1'));
+----------------------------+
| bin(inet_aton('10.0.0.1')) |
+----------------------------+
| 1010000000000000           |
| 1111111000000000           |
+----------------------------+
2 rows in set (0.00 sec)

mysql57 13> SET character_set_connection = 'latin1';
Query OK, 0 rows affected (0.00 sec)

mysql57 13> SELECT @@character_set_connection;
+----------------------------+
| @@character_set_connection |
+----------------------------+
| latin1                     |
+----------------------------+
1 row in set (0.00 sec)

mysql57 13> SELECT bin(inet_aton('10.0.0.1')) UNION DISTINCT SELECT bin(inet_aton('127.0.0.1'));
+---------------------------------+
| bin(inet_aton('10.0.0.1'))      |
+---------------------------------+
| 1010000000000000000000000001    |
| 1111111000000000000000000000001 |
+---------------------------------+
2 rows in set (0.00 sec)

Suggested fix:
Handle multi-byte character set correctly.

This is not occurred in 8.0.4-rc.
[8 Feb 2018 3:06] tsubasa tanaka
Test case for Bug#89581

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

Contribution: bug89581.test (application/octet-stream, text), 274 bytes.

[8 Feb 2018 3:06] tsubasa tanaka
Result set for testcase.

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

Contribution: bug89581.result (application/octet-stream, text), 449 bytes.

[8 Feb 2018 3:07] tsubasa tanaka
Test result.

5.7.21

```
$ ./mysql-test-run --do-test=bug89581
Logging: ./mysql-test-run  --do-test=bug89581
2018-02-08T03:04:55.201322Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2018-02-08T03:04:55.201570Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
MySQL Version 5.7.21
Checking supported features...
 - SSL connections supported
Using suites: main,sys_vars,binlog,federated,gis,rpl,innodb,innodb_gis,innodb_fts,innodb_zip,innodb_undo,perfschema,funcs_1,opt_trace,parts,auth_sec,query_rewrite_plugins,gcol,sysschema,test_service_sql_api,json,connection_control
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/home/yoku0825/mysql-5.7.21/mysql-test/var'...
Installing system database...
Using parallel: 1

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 301, with reserved ports 13010..13019
main.bug89581                            [ fail ]
        Test ended at 2018-02-08 12:05:06

CURRENT_TEST: main.bug89581
--- /home/yoku0825/mysql-5.7.21/mysql-test/r/bug89581.result    2018-02-08 06:00:29.891603943 +0300
+++ /home/yoku0825/mysql-5.7.21/mysql-test/var/log/bug89581.reject      2018-02-08 06:05:06.141070085 +0300
@@ -6,5 +6,5 @@
 SET @@session.character_set_connection = 'utf8mb4';
 SELECT bin(inet_aton('10.0.0.1')) UNION DISTINCT SELECT bin(inet_aton('127.0.0.1'));
 bin(inet_aton('10.0.0.1'))
-1010000000000000000000000001
-1111111000000000000000000000001
+1010000000000000
+1111111000000000

mysqltest: Result length mismatch

safe_process[16334]: Child process: 16335, exit: 1

 - the logfile can be found in '/home/yoku0825/mysql-5.7.21/mysql-test/var/log/main.bug89581/bug89581.log'
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.000 of 13 seconds executing testcases

Completed: Failed 1/1 tests, 0.00% were successful.

Failing test(s): main.bug89581

The log files in var/log may give you some hint of what went wrong.

If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html
```

8.0.4,

```
$ ./mysql-test-run --do-test=bug89581
Logging: ./mysql-test-run  --do-test=bug89581
2018-02-08T03:04:33.241992Z 0 [Warning] [MY-010139] Changed limits: max_open_files: 1024 (requested 8161)
2018-02-08T03:04:33.242161Z 0 [Warning] [MY-010142] Changed limits: table_open_cache: 431 (requested 4000)
2018-02-08T03:04:33.242681Z 0 [System] [MY-010116] /home/yoku0825/mysql-8.0.4-rc/runtime_output_directory/mysqld (mysqld 8.0.4-rc-log) starting as process 16197 ...
MySQL Version 8.0.4
Checking supported features...
 - SSL connections supported
Using suites: main,sys_vars,binlog,binlog_gtid,binlog_nogtid,federated,gis,rpl,rpl_gtid,rpl_nogtid,innodb,innodb_gis,innodb_fts,innodb_zip,innodb_undo,perfschema,funcs_1,opt_trace,parts,auth_sec,query_rewrite_plugins,gcol,sysschema,test_service_sql_api,json,connection_control,test_services,collations,service_udf_registration,service_sys_var_registration,service_status_var_registration
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/home/yoku0825/mysql-8.0.4-rc/mysql-test/var'...
Installing system database...
Using parallel: 1

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.bug89581                            [ pass ]    294
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.294 of 37 seconds executing testcases

Completed: All 1 tests were successful.
```
[8 Feb 2018 5:35] Umesh Shastry
Hello Tanaka-San,

Thank you for the report and test case.
Verified as described with 5.7.21 build.

Thanks,
Umesh
[12 Apr 2018 14:30] Paul Dubois
Posted by developer:
 
Fixed in 8.0.1.

BIN() could produce incorrect truncation with multibyte character
sets when used within UNION.