Is this substring_index behaviour documented or a bug?

Question

mysql> show variables like 'version';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| version       | 5.5.38-0ubuntu0.12.04.1 |
+---------------+-------------------------+
1 row in set (0.00 sec)

The following two examples should yield the same result by my reckoning, am I missing something obvious?

mysql> create table t as select '|A|B|' as foo;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> describe t;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| foo   | varchar(5) | NO   |     |         |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)

mysql> create table t(foo varchar(5) not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t(foo) values('|A|B|');
Query OK, 1 row affected (0.00 sec)

mysql> describe t;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| foo   | varchar(5) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| |                             |
+-------+-----------------------------+
1 row in set (0.00 sec)

score 1 · Answer 1 · edited Jun 15 '20 at 09:05

To me it would seem like it should be a bug. Yet, note the MySQL Documentation:

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

Here is an output of every count from -5 to 5

mysql> SET @foo = '|A|B|';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT x,SUBSTRING_INDEX(@foo,'|',x)
    -> FROM
    -> (
    ->     SELECT 0 x
    ->     UNION SELECT  1 UNION SELECT  2
    ->     UNION SELECT  3 UNION SELECT  4 UNION SELECT 5
    ->     UNION SELECT -1 UNION SELECT -2
    ->     UNION SELECT -3 UNION SELECT -4 UNION SELECT -5
    -> ) A ORDER BY x DESC;
+----+-----------------------------+
| x  | SUBSTRING_INDEX(@foo,'|',x) |
+----+-----------------------------+
|  5 | |A|B|                       |
|  4 | |A|B|                       |
|  3 | |A|B                        |
|  2 | |A                          |
|  1 |                             |
|  0 |                             |
| -1 |                             |
| -2 | B|                          |
| -3 | A|B|                        |
| -4 | |A|B|                       |
| -5 | |A|B|                       |
+----+-----------------------------+
11 rows in set (0.00 sec)
mysql>

Notice in both directions, since @foo only has 3 pipe characters, any position whose absolute value exceeds 3 should produce an error. Since the Documentation says final delimiter, it does not seem to enforcement counts outside the range of delimiters (at least in the Windows version).

I tried it out on my laptop running MySQL in Windows 8.1

mysql> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table t as select '|A|B|' as foo;
Query OK, 1 row affected (0.64 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)
mysql> drop table if exists t;
Query OK, 0 rows affected (0.13 sec)
mysql> create table t(foo varchar(5) not null);
Query OK, 0 rows affected (0.35 sec)
mysql> insert into t(foo) values('|A|B|');
Query OK, 1 row affected (0.03 sec)
mysql> select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)

What version of MySQL am I running ?

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.15                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.15                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
mysql>

My Windows version works ? I say you definitely have a bug in the Ubuntu version.

I reran it again, this time running SHOW CREATE TABLE t\G

mysql> drop table if exists t;
Query OK, 0 rows affected (0.11 sec)
mysql> create table t as select '|A|B|' as foo;
Query OK, 1 row affected (0.36 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE t (
  foo varchar(5) CHARACTER SET cp850 NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)
mysql> drop table if exists t;
Query OK, 0 rows affected (0.09 sec)
mysql> create table t(foo varchar(5) not null);
Query OK, 0 rows affected (0.31 sec)
mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE t (
  foo varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> insert into t(foo) values('|A|B|');
Query OK, 1 row affected (0.03 sec)
mysql> select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)
mysql>

The bug may involve the character set since the last line of the SUBSTRING_INDEX Documentation says This function is multibyte safe. Perhaps SUBSTRING_INDEX may be OK after all, but that's me messing around in Windows.

Run the script

SET @foo = '|A|B|';
SELECT x,SUBSTRING_INDEX(@foo,'|',x)
FROM
(
    SELECT 0 x
    UNION SELECT  1 UNION SELECT  2
    UNION SELECT  3 UNION SELECT  4 UNION SELECT 5
    UNION SELECT -1 UNION SELECT -2
    UNION SELECT -3 UNION SELECT -4 UNION SELECT -5
) A ORDER BY x DESC;

and see if you get the same output that I did.

GIVE IT A TRY !!!

score 1 · Answer 2 · answered Sep 07 '14 at 08:15

It is likely a bug in your version. I tested on:

MariaDB [test]> show variables like '%version%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.5.38-MariaDB-35.2 |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.39-MariaDB      |
| version_comment         | MariaDB Server      |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)

MariaDB [test]> create table t1 as select '|A|B|' as foo;
MariaDB [test]> create table t2(foo varchar(5) not null);
MariaDB [test]> insert into t2 (foo) values('|A|B|');
MariaDB [test]> create table t3(foo varchar(5) not null);
MariaDB [test]> insert into t3 select foo from t1;
MariaDB [test]> create table t4(foo varchar(5) not null);
MariaDB [test]> insert into t4 select foo from t2;

I created T3 and T4 in case there was a difference for T1 and T2.

MariaDB [test]> select foo, substring_index(foo,'|',-4) from t1;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select foo, substring_index(foo,'|',-4) from t2;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select foo, substring_index(foo,'|',-4) from t3;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select foo, substring_index(foo,'|',-4) from t4;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)

Is this substring_index behaviour documented or a bug?

2 Answers2

GIVE IT A TRY !!!