MySQL Bugs: #14713: SP for caseinsensitive stringcomparison doesn't work

Bug #14713	SP for caseinsensitive stringcomparison doesn't work
Submitted:	7 Nov 2005 17:25	Modified:	16 Nov 2005 20:07
Reporter:	Oliver Peters	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.17-BK, 5.0.15	OS:	Linux (Linux, Windows2000)
Assigned to:		CPU Architecture:	Any

Description:
I want to do a ceseinsensitive stringcomparison by comparing a choosable stringlength of string 1  (starting at position1)  with string 2 and returning 1 if the string is found in str2. Next step is to go to string 1 and take the string from position2, compare it again with string 2 etc.

For the stringcomparison I used the function LOCATE because the documentation says "it is case sensitive only if either argument is a binary string"

The function LOCATE alone works as described (if I don't use it in a SP)

Here's my function

DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`fcschnitt`$$
CREATE FUNCTION  `test`.`fcschnitt`(Nameold CHAR, n INT, Namenew CHAR) RETURNS int(11)
BEGIN
DECLARE L1,L2,La,Lb,cnt,pos,E0,E1,E2 INT;
DECLARE NA,NN,Sub CHAR;
SET L1 = CHAR_LENGTH(Nameold); #Length Nameold
SET L2 = CHAR_LENGTH(Namenew); #Length Namenew
SET La = IF(L1>n,n,L1); # if Length Nameold has more signs than n (=length of chosen substr),than n else Length Nameold
SET Lb = IF(La>L2,L2,La); # if La has more signs than Length Namene than Length Namenew else La
SET cnt = 0;
SET pos = 1;
SET E0 = 0;
SET E1 = 0;
WHILE L1 - Lb - cnt >= 0 DO
SET Sub = SUBSTR(Nameold,pos,Lb);
SET E0 = IF(LOCATE(Sub,Namenew)>0,1,0);
SET E1 = E1 + E0;
SET cnt = cnt + 1;
SET pos = pos + 1;
END WHILE;
Return E1;
END $$

DELIMITER ;

Here's my dump for a testcase - I hope it's helpful

-- MySQL dump 10.10
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.0.15-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `strcomp`
--

DROP TABLE IF EXISTS `strcomp`;
CREATE TABLE `strcomp` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `Nameold` varchar(45) NOT NULL default '',
  `Namenew` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;

--
-- Dumping data for table `strcomp`
--

/*!40000 ALTER TABLE `strcomp` DISABLE KEYS */;
LOCK TABLES `strcomp` WRITE;
INSERT INTO `strcomp` VALUES (1,'MEIER','MEIER'),(2,'meier','meier'),(3,'MEIER','meier'),(4,'meier','MEIER');
UNLOCK TABLES;
/*!40000 ALTER TABLE `strcomp` ENABLE KEYS */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

How to repeat:
see above

maybe a helpful addition:

Doing this query

SELECT * ,fcschnitt(Nameold,3,Namenew), LOCATE('mei',Nameold,1)
FROM strcomp

I get this result

1, 'MEIER', 'MEIER', 3, 1
2, 'meier', 'meier', 3, 1
3, 'MEIER', 'meier', 0, 1
4, 'meier', 'MEIER', 0, 1

In the column where I only use LOCATE in every line I get a hit - for the SP I only get hits for the cases with "total" equality and that's what I assume is an error

Thank you for a bug report. I was able to verify it just as you described on 5.0.17-BK (ChangeSet@1.1957, 2005-11-09 20:31:01+03:00, ...) on Linux. But I had also created a simpler test function to demonstrate the bug on your table:

mysql> CREATE TABLE `strcomp` (
    ->   `id` smallint(5) unsigned NOT NULL auto_increment,
    ->   `Nameold` varchar(45) NOT NULL default '',
    ->   `Namenew` varchar(45) NOT NULL default '',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
Query OK, 0 rows affected (0,06 sec)

mysql> INSERT INTO `strcomp` VALUES
    -> (1,'MEIER','MEIER'),(2,'meier','meier'),(3,'MEIER','meier'),(4,'meier','MEIER');
Query OK, 4 rows affected (0,01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create function f2(oldn char(100), newn char(100)) returns int return LOCATE(oldn, newn, 1);
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT * , f2(Nameold,Namenew) as f2, LOCATE(Nameold,Namenew, 1) as l FROM strcomp;
+----+---------+---------+------+---+
| id | Nameold | Namenew | f2   | l |
+----+---------+---------+------+---+
|  1 | MEIER   | MEIER   |    1 | 1 |
|  2 | meier   | meier   |    1 | 1 |
|  3 | MEIER   | meier   |    0 | 1 |
|  4 | meier   | MEIER   |    0 | 1 |
+----+---------+---------+------+---+
4 rows in set (0,01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.17    |
+-----------+
1 row in set (0,00 sec)

So, LOCATE really works differently with function parameters than with column values...

ok, here is another supplement:

CREATE FUNCTION  `test`.`f3`(oldn char(100), newn char(100)) RETURNS int
return INSTR(oldn, newn);

mysql> SELECT *, f3(Nameold,Namenew) as f3, INSTR(Nameold,Namenew) l
    -> FROM strcomp;
+----+---------+---------+------+---+
| id | Nameold | Namenew | f3   | l |
+----+---------+---------+------+---+
|  1 | MEIER   | MEIER   |    1 | 1 |
|  2 | meier   | meier   |    1 | 1 |
|  3 | MEIER   | meier   |    0 | 1 |
|  4 | meier   | MEIER   |    0 | 1 |
+----+---------+---------+------+---+
4 rows in set (0.00 sec)

So, it's the same problem with the function INSTR

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

see BUG#12903