Is this substring_index behaviour documented or a bug? mysql&gt; 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&gt; create table t as select '|A|B|' as foo;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

mysql&gt; 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&gt; create table t(foo varchar(5) not null);
Query OK, 0 rows affected (0.02 sec)

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

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

mysql&gt; select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| |                             |
+-------+-----------------------------+
1 row in set (0.00 sec)
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&gt; SET @foo = '|A|B|';
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT x,SUBSTRING_INDEX(@foo,'|',x)
    -&gt; FROM
    -&gt; (
    -&gt;     SELECT 0 x
    -&gt;     UNION SELECT  1 UNION SELECT  2
    -&gt;     UNION SELECT  3 UNION SELECT  4 UNION SELECT 5
    -&gt;     UNION SELECT -1 UNION SELECT -2
    -&gt;     UNION SELECT -3 UNION SELECT -4 UNION SELECT -5
    -&gt; ) 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&gt;

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&gt; drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql&gt; 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&gt; drop table if exists t;
Query OK, 0 rows affected (0.13 sec)

mysql&gt; create table t(foo varchar(5) not null);
Query OK, 0 rows affected (0.35 sec)

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

mysql&gt; 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&gt; 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&gt;

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&gt; drop table if exists t;
Query OK, 0 rows affected (0.11 sec)

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

mysql&gt; 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&gt; 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&gt; drop table if exists t;
Query OK, 0 rows affected (0.09 sec)

mysql&gt; create table t(foo varchar(5) not null);
Query OK, 0 rows affected (0.31 sec)

mysql&gt; 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&gt; insert into t(foo) values('|A|B|');
Query OK, 1 row affected (0.03 sec)

mysql&gt; 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&gt;

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 !!!