Package: general Severity: normal I have problem in mysql (5.0.32-Debian_7etch5-log Debian etch distribution) Better to view it on a wide screen.
It's all about cyrillic characters: Ёё comparing to Ее and Йй comparing to Ии characters (first code is cp1251): е 0xE5 = U+0435 : CYRILLIC SMALL LETTER IE Е 0xC5 = U+0415 : CYRILLIC CAPITAL LETTER IE ё 0xB8 = U+0451 : CYRILLIC SMALL LETTER IO Ё 0xA8 = U+0401 : CYRILLIC CAPITAL LETTER IO и 0xE8 = U+0438 : CYRILLIC SMALL LETTER I И 0xC8 = U+0418 : CYRILLIC CAPITAL LETTER I й 0xE9 = U+0439 : CYRILLIC SMALL LETTER SHORT I Й 0xC9 = U+0419 : CYRILLIC CAPITAL LETTER SHORT I from http://www.unicode.org/Public/MAPPINGS/VENDORS/MICSFT/WINDOWS/CP1251.TXT I compare several collations in cp1251 and utf8 character sets. SQL SELECT looks like (cp1251 hex codes): select 'ё' = 'Ё', 'й' = 'Й', 'z' = 'Z', 'е' = 'ё', 'и' = 'й', 'И' = 'Й', 'е' >= 'ё', 'Е' = 'Ё', 'и' > 'й', 'Ё' > 'ё', 'Й' > 'й', 'Б' > 'б', 'L' > 'l'; B8 A8 E9 C9 7A 5A E5 B8 E8 E9 C8 C9 E5 B8 C5 A8 E8 E9 A8 B8 C9 E9 C1 E1 4C 6C Compare case sensitive collations --------------------------------- mysql> set names cp1251 collate cp1251_general_cs; mysql> select 'ё' = 'Ё', 'й' = 'Й', 'z' = 'Z', 'е' = 'ё', 'и' = 'й', 'И' = 'Й', 'е' >= 'ё', 'Е' = 'Ё', 'и' > 'й', 'Ё' > 'ё', 'Й' > 'й', 'Б' > 'б', 'L' > 'l'; +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ | 'ё' = 'Ё' | 'й' = 'Й' | 'z' = 'Z' | 'е' = 'ё' | 'и' = 'й' | 'И' = 'Й' | 'е' >= 'ё' | 'Е' = 'Ё' | 'и' > 'й' | 'Ё' > 'ё' | 'Й' > 'й' | 'Б' > 'б' | 'L' > 'l' | +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ Correct mysql> set names utf8 collate utf8_bin; mysql> select 'ё' = 'Ё', 'й' = 'Й', 'z' = 'Z', 'е' = 'ё', 'и' = 'й', 'И' = 'Й', 'е' >= 'ё', 'Е' = 'Ё', 'и' > 'й', 'Ё' > 'ё', 'Й' > 'й', 'Б' > 'б', 'L' > 'l'; +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ | 'ё' = 'Ё' | 'й' = 'Й' | 'z' = 'Z' | 'е' = 'ё' | 'и' = 'й' | 'И' = 'Й' | 'е' >= 'ё' | 'Е' = 'Ё' | 'и' > 'й' | 'Ё' > 'ё' | 'Й' > 'й' | 'Б' > 'б' | 'L' > 'l' | +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ Wrong! The 1 (true value) in second result is a error! It has to be 0 (false) (sorting issue) Compare case insensitive collations ----------------------------------- mysql> set names cp1251 collate cp1251_general_ci; mysql> select 'ё' = 'Ё', 'й' = 'Й', 'z' = 'Z', 'е' = 'ё', 'и' = 'й', 'И' = 'Й', 'е' >= 'ё', 'Е' = 'Ё', 'и' > 'й', 'Ё' > 'ё', 'Й' > 'й', 'Б' > 'б', 'L' > 'l'; +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ | 'ё' = 'Ё' | 'й' = 'Й' | 'z' = 'Z' | 'е' = 'ё' | 'и' = 'й' | 'И' = 'Й' | 'е' >= 'ё' | 'Е' = 'Ё' | 'и' > 'й' | 'Ё' > 'ё' | 'Й' > 'й' | 'Б' > 'б' | 'L' > 'l' | +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ Correct mysql> set names utf8 collate utf8_general_ci; mysql> select 'ё' = 'Ё', 'й' = 'Й', 'z' = 'Z', 'е' = 'ё', 'и' = 'й', 'И' = 'Й', 'е' >= 'ё', 'Е' = 'Ё', 'и' > 'й', 'Ё' > 'ё', 'Й' > 'й', 'Б' > 'б', 'L' > 'l'; +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ | 'ё' = 'Ё' | 'й' = 'Й' | 'z' = 'Z' | 'е' = 'ё' | 'и' = 'й' | 'И' = 'Й' | 'е' >= 'ё' | 'Е' = 'Ё' | 'и' > 'й' | 'Ё' > 'ё' | 'Й' > 'й' | 'Б' > 'б' | 'L' > 'l' | +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ Wrong in columns (starting from 1): 1, 2, 7. First 3 columns have to be 1 and the rest - 0 mysql> set names utf8 collate utf8_unicode_ci; mysql> select 'ё' = 'Ё', 'й' = 'Й', 'z' = 'Z', 'е' = 'ё', 'и' = 'й', 'И' = 'Й', 'е' >= 'ё', 'Е' = 'Ё', 'и' > 'й', 'Ё' > 'ё', 'Й' > 'й', 'Б' > 'б', 'L' > 'l'; +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ | 'ё' = 'Ё' | 'й' = 'Й' | 'z' = 'Z' | 'е' = 'ё' | 'и' = 'й' | 'И' = 'Й' | 'е' >= 'ё' | 'Е' = 'Ё' | 'и' > 'й' | 'Ё' > 'ё' | 'Й' > 'й' | 'Б' > 'б' | 'L' > 'l' | +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | +-----------+-----------+-----------+-----------+-----------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+ Wrong in columns (starting from 1): 4, 5, 6, 7, 8. First 3 columns have to be 1 and the rest - 0 How to repeat: set names cp1251 collate cp1251_general_cs; select 'ё' = 'Ё', 'й' = 'Й', 'z' = 'Z', 'е' = 'ё', 'и' = 'й', 'И' = 'Й', 'е' >='ё', 'Е' = 'Ё', 'и' > 'й', 'Ё' > 'ё', 'Й' > 'й', 'Б' > 'б', 'L' > 'l'; set names utf8 collate utf8_bin; select 'ё' = 'Ё', 'й' = 'Й', 'z' = 'Z', 'е' = 'ё', 'и' = 'й', 'И' = 'Й', 'е' >='ё', 'Е' = 'Ё', 'и' > 'й', 'Ё' > 'ё', 'Й' > 'й', 'Б' > 'б', 'L' > 'l'; set names cp1251 collate cp1251_general_ci; select 'ё' = 'Ё', 'й' = 'Й', 'z' = 'Z', 'е' = 'ё', 'и' = 'й', 'И' = 'Й', 'е' >='ё', 'Е' = 'Ё', 'и' > 'й', 'Ё' > 'ё', 'Й' > 'й', 'Б' > 'б', 'L' > 'l'; set names utf8 collate utf8_general_ci; select 'ё' = 'Ё', 'й' = 'Й', 'z' = 'Z', 'е' = 'ё', 'и' = 'й', 'И' = 'Й', 'е' >='ё', 'Е' = 'Ё', 'и' > 'й', 'Ё' > 'ё', 'Й' > 'й', 'Б' > 'б', 'L' > 'l'; set names utf8 collate utf8_unicode_ci; select 'ё' = 'Ё', 'й' = 'Й', 'z' = 'Z', 'е' = 'ё', 'и' = 'й', 'И' = 'Й', 'е' >='ё', 'Е' = 'Ё', 'и' > 'й', 'Ё' > 'ё', 'Й' > 'й', 'Б' > 'б', 'L' > 'l'; -- System Information: Debian Release: 4.0 APT prefers stable APT policy: (500, 'stable') Architecture: i386 (i686) Shell: /bin/sh linked to /bin/bash Kernel: Linux 2.6.18-4-686 Locale: LANG=ru_RU.UTF-8, LC_CTYPE=ru_RU.UTF-8 (charmap=CP1251) -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
