Столкнулся с тем, что при сортировке по строковому полю странно влияет регистр символов.
MS SQL 2008 R2, collation — Cyrillic_General_CS_AS.

Пример выборки с ORDER BY:

абрикосовый джем
Абрикосовый джем
Абрикосовый_джем
абрикосовый-джем

Я понимаю, что пробелы, подчеркивания и др. знаки пунктуации влияют, но почему заглавные "А" оказались в середине?
Ведь используется case sensitive сравнение.
Может кто-нибудь объяснить или ткнуть в MSDN?

Здравствуйте, qaz77, Вы писали:

Q>Столкнулся с тем, что при сортировке по строковому полю странно влияет регистр символов.
Q>MS SQL 2008 R2, collation — Cyrillic_General_CS_AS.

Q>Пример выборки с ORDER BY:

Q>

Q>абрикосовый джем
Q>Абрикосовый джем
Q>Абрикосовый_джем
Q>абрикосовый-джем
Q>

Q>Я понимаю, что пробелы, подчеркивания и др. знаки пунктуации влияют, но почему заглавные "А" оказались в середине?
Q>Ведь используется case sensitive сравнение.
Q>Может кто-нибудь объяснить или ткнуть в MSDN?

Может у колонки другой collation, не такой как у базы?

Здравствуйте, bnk, Вы писали:
bnk>Может у колонки другой collation, не такой как у базы?

Нет. Это я в новой пустой базе добавляю таблицу.
Две колонки: целочисленный id — primary key и name с типом nvarchar[50].
Делаю все руками в ManagementStudio.
Потом запрос

select id, name from test order by name

дает приведенный эффект.

Здравствуйте, qaz77, Вы писали:

Q>Столкнулся с тем, что при сортировке по строковому полю странно влияет регистр символов.
Q>MS SQL 2008 R2, collation — Cyrillic_General_CS_AS.
...
Q>Я понимаю, что пробелы, подчеркивания и др. знаки пунктуации влияют, но почему заглавные "А" оказались в середине?
Q>Ведь используется case sensitive сравнение.
Q>Может кто-нибудь объяснить или ткнуть в MSDN?
Дело тут вообще не в SQL.
Cyrillic_General_CS_AS — это Windows collation (LCID = 1049, в дотнете соответствует ru-RU).
Это Windows так сравнивает. Потому, что такие правила сравнения записаны в этой локали.

Здравствуйте, vmpire, Вы писали:
V>Дело тут вообще не в SQL.
V>Cyrillic_General_CS_AS — это Windows collation (LCID = 1049, в дотнете соответствует ru-RU).
V>Это Windows так сравнивает. Потому, что такие правила сравнения записаны в этой локали.

Тут у меня есть сомнения.

Если я в своей программе на C++ делаю

std::collate<wchar_t>(std::locale("")).compare(...)

то порядок получается другой.
Ведь runtime библиотеки MSVC реализуют locale через Windows механизмы?

Если я делаю то же самое в PostgreSQL (под Windows) и задаю encoding='UTF8' и locale='Russian_Russia.1251',
то получаю другой порядок.

Кто виноват?
И что делать?

Здравствуйте, qaz77, Вы писали:

V>>Это Windows так сравнивает. Потому, что такие правила сравнения записаны в этой локали.
Q>Если я в своей программе на C++ делаю
Q>

Q>std::collate<wchar_t>(std::locale("")).compare(...)
Q>

Q>то порядок получается другой.
Q>Ведь runtime библиотеки MSVC реализуют locale через Windows механизмы?
Тут нужно смотреть, что именно использует рантайм. В Windows много разных функций сравнения строк
В C#, по крайней мере, ситуация воспроизводится в русской локали:

string[] jams = { "Абрикосовый джем", "абрикосовый джем",  "Абрикосовый_джем", "абрикосовый-джем" };
Array.Sort(jams);

Q>Если я делаю то же самое в PostgreSQL (под Windows) и задаю encoding='UTF8' и locale='Russian_Russia.1251',
Q>то получаю другой порядок.
Как сравнивает PostgreSQL я не знаю, может, у него свой механизм

Q>Кто виноват?
Возможно, Unicode
http://www.unicode.org/reports/tr10/#Multi_Level_Comparison
1.1 Multi-Level Comparison

To address the complexities of language-sensitive sorting, a multilevel comparison algorithm is employed. In comparing two words, the most important feature is the identity of the base letters—for example, the difference between an A and a B. Accent differences are typically ignored, if the base letters differ. Case differences (uppercase versus lowercase), are typically ignored, if the base letters or their accents differ
(там дальше ещё табличка с примером)

Как я понимаю, это означает, что сортировка идёт вначале без учёта регистра, а потом, в случае совпадения строк без учёта регистра (первые две строки в Вашем примере), производится сравнение регистров.
Разница между строками 1/2 и 3, 1/2 и 4 вычисляется на первом этапе и сравнение регистра не происходит.

Q>И что делать?
Смотря что хочется получить.

Здравствуйте, qaz77, Вы писали:
Q>Может кто-нибудь объяснить или ткнуть в MSDN?

Это не MSDN, это http://www.unicode.org/reports/tr10/#Collation_And_Code_Chart_Order

Стандарт, грубо говоря, такой:
сравниваем AI_CI, если разница есть, то это порядок сортировки, если нет
сравниваем AS_CI, если разница есть, то это порядок сортировки, если нет
сравниваем AS_CS

Здравствуйте, vmpire, Вы писали:
V>Как я понимаю, это означает, что сортировка идёт вначале без учёта регистра, а потом, в случае совпадения строк без учёта регистра (первые две строки в Вашем примере), производится сравнение регистров.

Ага. Только перед регистром еще сравнивается акцент (всякие умляуты и т.п.).

Здравствуйте, Ромашка, Вы писали:

Р>Здравствуйте, vmpire, Вы писали:
V>>Как я понимаю, это означает, что сортировка идёт вначале без учёта регистра, а потом, в случае совпадения строк без учёта регистра (первые две строки в Вашем примере), производится сравнение регистров.

Р>Ага. Только перед регистром еще сравнивается акцент (всякие умляуты и т.п.).
Ну, я не весь алгоритм описывал, а его применение к данному примеру. Так-то там он на нескольких страницах расписан.

Здравствуйте, vmpire, Вы писали:
Q>>И что делать?
V>Смотря что хочется получить.

Хочется получить эквивалентный результат сортировки при использовании разных СУБД.

Допустим, я помещаю одинаковые данные в базы MS SQL и PostgreSQL.
Потом выгружаю их SQL-запросами в какой-то инвариантный формат (например, XML).
Хочу получить побайтово одинаковые XML файлы.

Сейчас, если где-то есть order by по строковому полю, то в XML могут записываться ноды в разном порядке.
На функционал это не влияет, но для тестов такие артефакты очень мешают.

Postgres, судя по его докам, использует реализацию collation из libc для всех локалей, кроме C/POSIX.
Для русской локали Russian_Russia.1251 результат сортировки такой:

абрикосовый-джем
абрикосовый джем
Абрикосовый джем
Абрикосовый_джем

Видно, что Postgres по другому обрабатывает буквы в upper и lower case.
И вариант с "-" идет первым, а в MS SQL — последним.

Здравствуйте, qaz77, Вы писали:

Q>>>И что делать?
V>>Смотря что хочется получить.

Q>Хочется получить эквивалентный результат сортировки при использовании разных СУБД.
Тут нужно разбираться с постгресом. Я в нём не силён, так что не подскажу.

Q>Сейчас, если где-то есть order by по строковому полю, то в XML могут записываться ноды в разном порядке.
Q>На функционал это не влияет, но для тестов такие артефакты очень мешают.
А вот это уже интересно: правильно ли делать тесты, которые тестируют то, что не нужно при использовании приложения?
И правильно ли подгонять приложение под такие тесты?

Здравствуйте, vmpire, Вы писали:

Q>>Хочется получить эквивалентный результат сортировки при использовании разных СУБД.
V>Тут нужно разбираться с постгресом. Я в нём не силён, так что не подскажу.
Судя по его исходникам там просто wcscoll и, если вернули 0, то memcmp представления в utf-8.
Чудеса в том, что делая такое же сравнение в тестовой программке, порядок сортировки получается другой.

Q>>На функционал это не влияет, но для тестов такие артефакты очень мешают.
V>А вот это уже интересно: правильно ли делать тесты, которые тестируют то, что не нужно при использовании приложения?
V>И правильно ли подгонять приложение под такие тесты?
Ну, тесты-то тестируют именно то, что нужно для работы приложения.

В моем случае, что данные не исказились, строки, например, не обрезались и т.п.
Просто оценить успешность прохождения теста сравнив тупо два файла гораздо проще, чем писать специальный код сравнения.
Специальный код оценки успешности теста должен учитывать, что какие-то элементы должны в точности совпадать, а какие-то могут идти в произвольном порядке.
Это увеличивает сложность кода тестов (а следовательно вероятность ошибки), добавляет необходимость правки при изменении структуры БД.

В моем случае XML файлы получаются в несколько гигабайт и затянуть их DOM парсер не получится.
Следовательно, только expat какой-нибудь и сложность теста начинает приближаться к сложности тестируемого кода.

Вне контекста тестов то же хотелось бы какой-то определенности.
Например, надо показать пользователю отсортированный список каких-то наименований.
Если я беру данные из СУБД с использованием order by, то будет один порядок.
Если — читаю из какого-то файла и сортирую сам, то — другой порядок.
Тут пользователь уже сможет справедливо упрекнуть, что в одной программе не единообразия сортировки.

Здравствуйте, qaz77, Вы писали:
Q>Хочется получить эквивалентный результат сортировки при использовании разных СУБД.

Эквивалентный результат должен получаться при эквивалентных collations. Это не твоя задача, это задача DBA. А ты, судя предыдущим сообщениям, программист.

PS Насколько понятно из быстрого рытья гугля, тебе нужна не Russian_Russia.1251, а ru_RU.UTF8, в которую PostgreSQL нагибается только после плясок с бубном.

Здравствуйте, Ромашка, Вы писали:
Р>PS Насколько понятно из быстрого рытья гугля, тебе нужна не Russian_Russia.1251, а ru_RU.UTF8, в которую PostgreSQL нагибается только после плясок с бубном.

Злые языки утверждают, что в реализациях стандартов unicode еще тот бардак — в линухе сортировку так и не доделали, а макось вообще забила на это с особым цинизмом. А так как PostgreSQL поставляется без collations, то твоя проблема, похоже, универсального решения не имеет.

Здравствуйте, Ромашка, Вы писали:
Р>>PS Насколько понятно из быстрого рытья гугля, тебе нужна не Russian_Russia.1251, а ru_RU.UTF8, в которую PostgreSQL нагибается только после плясок с бубном.

Р>Злые языки утверждают, что в реализациях стандартов unicode еще тот бардак — в линухе сортировку так и не доделали, а макось вообще забила на это с особым цинизмом. А так как PostgreSQL поставляется без collations, то твоя проблема, похоже, универсального решения не имеет.

Кодовые страницы типа 1251 в collation нужны только для однобайтных кодировок.
У меня случай Windows only, вроде бы все готовое для unicode есть.
По крайней мере lstrcmpW дает то же порядок сортировки, как и в MS SQL с _CS_AS.

В Postgres для случая ENCODING=UTF8 и под Windows сравнение без utf-8 происходит.
В исходниках видно, что они сначала перекодируют utf-8 в wchar_t с помощью MultiByteToWideChar,
а затем уже для wchar_t строк зовут wcscoll.
Т.е. в функцию сравнения уходят уже 16-битные символы.
Поэтому мне непонятно, как может Postgres'у помочь локаль ru_RU.UTF8.

Дистрибутив Postgres, который я использую, собран msvc10.
Поэтому полагаю, что за вызовом wcscoll стоит Win API функция CompareStringEx с какими-то флагами.
Но не такими, как для lstrcmpW.

В общем, согласен, что добиться одинаковых результатов в разных СУБД не получится и надо смириться.