MySQL Bugs: #8145: Query returns wrong result

Bug #8145	Query returns wrong result
Submitted:	26 Jan 2005 14:24	Modified:	2 Feb 2005 8:02
Reporter:	Jaromír Mothejzík	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	MySQL 5.0.0. alpha-nt	OS:	Windows (windows xp, linux)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
When we define both of:
KEY `IX_NAZEV` (`NAZEV`) end
collate latin2_czech_ci for column 'NAZEV'
we get wrong result using followed queries:
SELECT * FROM xtest8 WHERE NAZEV like 'LIBERK%'
or
SELECT * FROM xtest8 WHERE NAZEV like 'SVOR%'

How to repeat:
Run script:
-- MySQL dump 10.4
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.0.0-alpha-nt
/*!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 */;

--
-- Table structure for table `xtest8`
--

DROP TABLE IF EXISTS `xtest8`;
CREATE TABLE `xtest8` (
  `OBEC_KOD` int(11) NOT NULL default '0',
  `NAZEV` varchar(48) character set latin2 collate latin2_czech_ci default NULL,
  PRIMARY KEY  (`OBEC_KOD`),
  KEY `IX_NAZEV` (`NAZEV`)
);

--
-- Dumping data for table `xtest8`
--

/*!40000 ALTER TABLE `xtest8` DISABLE KEYS */;
LOCK TABLES `xtest8` WRITE;
INSERT INTO `xtest8` VALUES (553182,'Sviny'),(562131,'Svor'),(577731,'Svitavy'),(596868,'Svratka'),(563889,'Liberec'),(576450,'Liberk');
UNLOCK TABLES;
/*!40000 ALTER TABLE `xtest8` ENABLE KEYS */;

/*!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 */;

When we define both of:
KEY `IX_NAZEV` (`NAZEV`) end
collate latin2_czech_ci for column 'NAZEV'

we get wrong result using for examples these queries:
SELECT * FROM xtest8 WHERE NAZEV like 'LIBERK%'
or
SELECT * FROM xtest8 WHERE NAZEV like 'SVOR%'
returns 0 rows (error, should be 1 rows)

Verified in MySQL 5.0.2 nt

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

latin2_czech_ci name was wrong in 5.0.0.
This collation is actually case sensitive.
In later 5.x versions it was renamed to latin2_czech_cs.

The query results are expected.