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

[7 Nov 2005 17:25] Oliver Peters
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
[8 Nov 2005 7:29] Oliver Peters
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
[10 Nov 2005 15:17] Valeriy Kravchuk
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...
[10 Nov 2005 17:05] Oliver Peters
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
[16 Nov 2005 20:07] Sergei Golubchik
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