MySQL Bugs: #3430: SUBSTRING_INDEX Returning 0 Results with WHERE clause

Bug #3430	SUBSTRING_INDEX Returning 0 Results with WHERE clause
Submitted:	9 Apr 2004 20:06	Modified:	9 Apr 2004 22:42
Reporter:	Nate Sanden	Email Updates:
Status:	Not a Bug
Category:	Server: MyISAM	Severity:	S2 (Serious)
Version:	4.1.1-alpha-nt	OS:	Microsoft Windows (Windows XP)
Assigned to:		Target Version:

Description:
I have tested this query in mysql 4.0.18 and it returns 1 result but mysql 4.1.1-alpha-nt i get 0 results.

table

IPAddress
111.111.111.111

SELECT * FROM a WHERE SUBSTRING_INDEX(IPAddress, '.', -3) = '111.111.111'

returns 0 results

How to repeat:
CREATE TABLE `a` (
  `IPAddress` varchar(20) NOT NULL default '',
) TYPE=MyISAM;
INSERT INTO `a` VALUES('111.111.111.111');

SELECT * FROM a WHERE SUBSTRING_INDEX(IPAddress, '.', -3) = '111.111.111'

I tested your sample with 4.1.1a package release and 4.1.2:

D:\start\4.1.1a>"d:\arquivos de programas\4.1.1a\bin\mysql" --defaults-file="d:\
arquivos de programas\4.1.1a\my.cnf" -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.1-alpha-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> CREATE TABLE `a` (
    ->   `IPAddress` varchar(20) NOT NULL default '',
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> INSERT INTO `a` VALUES('111.111.111.111');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> SELECT * FROM a WHERE SUBSTRING_INDEX(IPAddress, '.', -3) =
    -> '111.111.111';
+-----------------+
| IPAddress       |
+-----------------+
| 111.111.111.111 |
+-----------------+
1 row in set (0.01 sec)

I just tested it again with this (exact copy from my database) and get 0 results still...

CREATE TABLE `a` (
  `IPAddress` varchar(20) NOT NULL default '',
) TYPE=MyISAM

INSERT INTO `a` VALUES ('67.168.108.145');

SELECT * FROM `a` WHERE SUBSTRING_INDEX(IPAddress, '.', -3) = '67.168.108';

Am I doing something stupid?

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.1.2-alpha-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `a` (
    ->   `IPAddress` varchar(20) NOT NULL default '',
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql>
mysql>
mysql> INSERT INTO `a` VALUES ('67.168.108.145');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> -- Notice third SUBSTRING_INDEX argument
mysql>
mysql> SELECT * FROM `a` WHERE SUBSTRING_INDEX(IPAddress, '.', 3) =
    -> '67.168.108';
+----------------+
| IPAddress      |
+----------------+
| 67.168.108.145 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT  SUBSTRING_INDEX(IPAddress, '.', -3) from a;
+-------------------------------------+
| SUBSTRING_INDEX(IPAddress, '.', -3) |
+-------------------------------------+
| 168.108.145                         |
+-------------------------------------+
1 row in set (0.00 sec)

Sorry, it was a stupid mistake, i was using -3 instead of 3.