Bug #79978 Func SUBSTRING_INDEX works incorrectly when the count param is a bigint unsigned

• Submitted: 14 Jan 2016 7:39
• Modified: 8 Feb 2016 16:42
• Reporter: Su Dylan
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8, 5.5.48, 5.6.28, 5.7.10
• OS: Any
• CPU Architecture: Any

[14 Jan 2016 7:39] Su Dylan

Description:
Output:
=====
mysql> select SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775807 ), SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775808 )  ;
+------------------------------------------------------+------------------------------------------------------+
| SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775807 ) | SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775808 ) |
+------------------------------------------------------+------------------------------------------------------+
| xyz                                                  |                                                      |
+------------------------------------------------------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
=====
SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775808 ) is expected to return 'xyz'.

How to repeat:
select SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775807 ), SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775808 )  ;

Suggested fix:
'xyz' is returned.

[14 Jan 2016 7:47] Su Dylan

Actually, the following example demonstrates that a value which is in range of bigint signed also returns ''(empty).

This is really a bug:
=====
mysql> select SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775807 ), SUBSTRING_INDEX( 'xyz', 'abc', 6998875295910461440 ), 9223372036854775807 > 6998875295910461440  ;
+------------------------------------------------------+------------------------------------------------------+-------------------------------------------+
| SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775807 ) | SUBSTRING_INDEX( 'xyz', 'abc', 6998875295910461440 ) | 9223372036854775807 > 6998875295910461440 |
+------------------------------------------------------+------------------------------------------------------+-------------------------------------------+
| xyz                                                  |                                                      |                                         1 |
+------------------------------------------------------+------------------------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

[14 Jan 2016 8:28] MySQL Verification Team

Hello Su Dylan,

Thank you for the report and test case.
Observed that 5.5.48, 5.6.28 and 5.7.10 are affected.

Thanks,
Umesh

[8 Feb 2016 16:42] Paul DuBois

Noted in 5.8.0 changelog.

SUBSTRING_INDEX(str, delim, count) did not properly handle count
values larger than 32 bits.