Bug #83178 substring(binary, 3) return inconsistent results

• Submitted: 28 Sep 2016 2:28
• Modified: 28 Sep 2016 6:46
• Reporter: 帅 Bang
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6, 5.6.33, 5.7.15
• OS: Linux
• CPU Architecture: Any

[28 Sep 2016 2:28] 帅 Bang

Description:
mysql> select binary"上海";
+----------------+
| binary"上海" |
+----------------+
| 上海         |
+----------------+
1 row in set (0.00 sec)

mysql> select substring(binary"上海", 3);
+------------------------------+
| substring(binary"上海", 3) |
+------------------------------+
| �海                         |
+------------------------------+
1 row in set (0.00 sec)

Yeah, it is ok now. But take a look at another example ,please:

mysql> select TRIM(  TRIM( SUBSTR( REPEAT( TRIM( SUBSTRING( CONVERT( SUBSTR( 'dyoqdxtl' , 1, LEAST( 254, 20 ) ), BINARY ) , 1 , LEAST( 254, 20 ) ) ) , 7 + 1 ) , 1 , LEAST( 254, 20 ) ) ) FROM SUBSTR( '上海'     , 1, LEAST( 254,     20 ) ) );

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TRIM(  TRIM( SUBSTR( REPEAT( TRIM( SUBSTRING( CONVERT( SUBSTR( 'dyoqdxtl' , 1, LEAST( 254, 20 ) ), BINARY ) , 1 , LEAST( 254, 20 ) ) ) , 7 + 1 ) , 1 , LEAST( 254, 20 ) ) ) FROM SUBSTR( '上海'     , 1, LEAST( 254,     20 ) ) ) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 上海                                                                                                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

A little messy but  not bad yet. OK, let us perform a substring on the result what the last statement returns, with the same parameter 3.

mysql>  select  SUBSTRING( TRIM(  TRIM( SUBSTR( REPEAT( TRIM( SUBSTRING( CONVERT( SUBSTR( 'dyoqdxtl' , 1, LEAST( 254, 20 ) ), BINARY ) , 1 , LEAST( 254, 20 ) ) ) , 7 + 1 ) , 1 , LEAST( 254, 20 ) ) ) FROM SUBSTR( '上海'     , 1, LEAST( 254,     20 ) ) ) , 3 );

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SUBSTRING( TRIM(  TRIM( SUBSTR( REPEAT( TRIM( SUBSTRING( CONVERT( SUBSTR( 'dyoqdxtl' , 1, LEAST( 254, 20 ) ), BINARY ) , 1 , LEAST( 254, 20 ) ) ) , 7 + 1 ) , 1 , LEAST( 254, 20 ) ) ) FROM SUBSTR( '上海'     , 1, LEAST( 254,     20 ) ) ) , 3 ) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                                                                                      |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

It returns an empty string.

//////////////////////////////////////////////////////////

FYI:

Let us dig into it to get some details. 

Connect the mysql with option --column-type-info, just like this:

mysql -uroot -hip -Pport --column-type-info; 

mysql> select binary"上海";
Field   1:  `binary"上海"`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING//yeah, string
Collation:  binary (63)//yeah, binary
Length:     6
Max_length: 6//both max_length and length are 6
Decimals:   31
Flags:      BINARY

+----------------+
| binary"上海" |
+----------------+
| 上海         |
+----------------+
1 row in set (0.00 sec)

mysql> select TRIM(  TRIM( SUBSTR( REPEAT( TRIM( SUBSTRING( CONVERT( SUBSTR( 'dyoqdxtl' , 1, LEAST( 254, 20 ) ), BINARY ) , 1 , LEAST( 254, 20 ) ) ) , 7 + 1 ) , 1 , LEAST( 254, 20 ) ) ) FROM SUBSTR( '上海'     , 1, LEAST( 254,     20 ) ) );
Field   1:  `TRIM(  TRIM( SUBSTR( REPEAT( TRIM( SUBSTRING( CONVERT( SUBSTR( 'dyoqdxtl' , 1, LEAST( 254, 20 ) ), BINARY ) , 1 , LEAST( 254, 20 ) ) ) , 7 + 1 ) , 1 , LEAST( 254, 20 ) ) ) FROM SUBSTR( '上海'     , 1, LEAST( 254,     20 ) ) )`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING //yeah, string
Collation:  binary (63)//yeah, binary
Length:     6
Max_length: 6//both max_length and length are 6
Decimals:   31
Flags:      BINARY

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TRIM(  TRIM( SUBSTR( REPEAT( TRIM( SUBSTRING( CONVERT( SUBSTR( 'dyoqdxtl' , 1, LEAST( 254, 20 ) ), BINARY ) , 1 , LEAST( 254, 20 ) ) ) , 7 + 1 ) , 1 , LEAST( 254, 20 ) ) ) FROM SUBSTR( '上海'     , 1, LEAST( 254,     20 ) ) ) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 上海                                                                                                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

That is to say, both statements return exactly same and identical results. 

So, When substring(return, 3) are performed, identical and consistent results are expected.

How to repeat:
select binary"上海";
select substring(binary"上海", 3);
select TRIM(  TRIM( SUBSTR( REPEAT( TRIM( SUBSTRING( CONVERT( SUBSTR( 'dyoqdxtl' , 1, LEAST( 254, 20 ) ), BINARY ) , 1 , LEAST( 254, 20 ) ) ) , 7 + 1 ) , 1 , LEAST( 254, 20 ) ) ) FROM SUBSTR( '上海'     , 1, LEAST( 254,     20 ) ) );
select  SUBSTRING( TRIM(  TRIM( SUBSTR( REPEAT( TRIM( SUBSTRING( CONVERT( SUBSTR( 'dyoqdxtl' , 1, LEAST( 254, 20 ) ), BINARY ) , 1 , LEAST( 254, 20 ) ) ) , 7 + 1 ) , 1 , LEAST( 254, 20 ) ) ) FROM SUBSTR( '上海'     , 1, LEAST( 254,     20 ) ) ) , 3 );

Suggested fix:
select substring(binary"上海", 3);

select  SUBSTRING( TRIM(  TRIM( SUBSTR( REPEAT( TRIM( SUBSTRING( CONVERT( SUBSTR( 'dyoqdxtl' , 1, LEAST( 254, 20 ) ), BINARY ) , 1 , LEAST( 254, 20 ) ) ) , 7 + 1 ) , 1 , LEAST( 254, 20 ) ) ) FROM SUBSTR( '上海'     , 1, LEAST( 254,     20 ) ) ) , 3 );

both return     �海

[28 Sep 2016 6:46] MySQL Verification Team

Hello Bang,

Thank you for the report.
Observed this with 5.6.33/5.7.15 builds.

Thanks,
Umesh