Dlaczego wyszukiwanie LIKE N '% %' pasuje do dowolnego znaku Unicode, a = N' 'pasuje do wielu?

DECLARE @T TABLE(
  Col NCHAR(1));

INSERT INTO @T
VALUES      (N'A'),
            (N'B'),
            (N'C'),
            (N'Ƕ'),
            (N'Ƿ'),
            (N'Ǹ');

SELECT *
FROM   @T
WHERE  Col LIKE N'%�%'

Zwroty

Col
A
B
C
Ƕ
Ƿ
Ǹ

SELECT *
FROM   @T
WHERE  Col = N'�' 

Zwroty

Col
Ƕ
Ƿ
Ǹ

Generowanie każdego możliwego dwubajtowego „znaku” za pomocą poniższego pokazuje, że =wersja pasuje do 21 229 z nich, a LIKE N'%�%'wersja do wszystkich (wypróbowałem kilka niebinarnych zestawień z tym samym wynikiem).

WITH T(I, N)
AS 
(
SELECT TOP 65536 ROW_NUMBER() OVER (ORDER BY @@SPID),
                 NCHAR(ROW_NUMBER() OVER (ORDER BY @@SPID))
FROM master..spt_values v1, 
     master..spt_values v2
)
SELECT I, N 
FROM T
WHERE N = N'�'  

Czy ktoś jest w stanie rzucić jakieś światło na to, co się tutaj dzieje?

Użycie COLLATE Latin1_General_BINnastępnie dopasowuje pojedynczy znak NCHAR(65533)- ale pytanie polega na zrozumieniu, jakich reguł używa w innym przypadku. Co jest specjalnego w tych 21 229 znakach, które pasują do siebie =i dlaczego wszystko pasuje do znaku wieloznacznego? Przypuszczam, że kryje się za tym jakiś powód.

nchar(65534)[i 21k innych] działa równie dobrze nchar(65533). Pytanie mogło zostać sformułowane przy użyciu nchar(502) jednakowego as �- zachowuje się tak samo jak LIKE N'%Ƕ%'(pasuje do wszystkiego) i w =przypadku. To prawdopodobnie dość duża wskazówka.

Zmiana SELECTostatniego zapytania, aby SELECT I, N, RANK() OVER(ORDER BY N)pokazać, że SQL Server nie może uszeregować znaków. Wygląda na to, że każda postać nieobsługiwana przez zestawienie jest uważana za równoważną.

Baza danych z Latin1_General_100_CS_ASzestawieniem tworzy 5840 dopasowań. znacznie Latin1_General_100_CS_ASogranicza =mecze, ale nie zmienia LIKEzachowania. Wygląda na to, że istnieje garść znaków, która zmniejszyła się w późniejszych zestawieniach, które wszystkie są sobie równe i są wtedy ignorowane podczas LIKEwyszukiwania symboli wieloznacznych .

Korzystam z programu SQL Server 2016. Symbol �jest znakiem zastępującym Unicode, ale jedyne niepoprawne znaki w kodowaniu UCS-2 to 55296 - 57343 AFAIK i wyraźnie pasują idealnie poprawne punkty kodowe, N'Ԛ'które nie znajdują się w tym zakresie.

Wszystkie te znaki zachowują się jak pusty ciąg znaków dla LIKEi =. Oceniają nawet jako równoważne. N'' = N'�'jest prawdą i można go upuścić w LIKEporównaniu pojedynczych spacji LIKE '_' + nchar(65533) + '_'bez efektu. LENporównania dają różne wyniki, więc prawdopodobnie są to tylko niektóre funkcje łańcuchowe.

Myślę, że LIKEzachowanie jest poprawne w tym przypadku; zachowuje się jak nieznana wartość (która może być dowolna). Dzieje się tak również w przypadku innych postaci:

• nchar(11217) (Znak niepewności)
• nchar(65532) (Znak zamiany obiektu)
• nchar(65533) (Postać zastępcza)
• nchar(65534) (Nie postać)

Więc jeśli chcę znaleźć wszystkie znaki, które reprezentują niepewność ze znakiem równości, użyłbym sortowania, które obsługuje dodatkowe znaki, takie jak Latin1_General_100_CI_AS_SC.

Sądzę, że jest to grupa „nieważonych znaków” wymienionych w dokumentacji, obsłudze sortowania i obsługi Unicode .

Porównanie jednej „postaci” (która może składać się z wielu Punktów Kodowych: par zastępczych, łączenia znaków itp.) Z inną, opiera się na dość złożonym zestawie reguł. Jest to tak skomplikowane, że wymaga uwzględnienia wszystkich różnych (a czasem „zwariowanych”) reguł znalezionych we wszystkich językach reprezentowanych w specyfikacji Unicode . Ten system ma zastosowanie do niebinarnych zestawień dla wszystkich NVARCHARdanych oraz dla VARCHARdanych korzystających z sortowania Windows, a nie z SQL Server Collation (zaczynającego się od SQL_). Ten system nie ma zastosowania do VARCHARdanych korzystających z sortowania SQL Server, ponieważ używają one prostych mapowań.

Większość reguł jest zdefiniowanych w algorytmie sortowania Unicode (UCA) . Niektóre z tych zasad, a niektóre nieobjęte UCA, to:

1. Domyślna kolejność / waga podane w allkeys.txtpliku (odnotowane poniżej)
2. Jakie wrażliwości i opcje są używane (np. Czy rozróżnia małe i duże litery? A jeśli jest wrażliwe, to czy najpierw jest pisana wielkimi lub małymi literami?)
3. Wszelkie przesłonięcia oparte na ustawieniach regionalnych.
4. Używana jest wersja standardu Unicode.
5. Czynnik „ludzki” (tj. Unicode jest specyfikacją, a nie oprogramowaniem, dlatego też każdy dostawca musi ją wdrożyć)

Podkreśliłem, że ostatnia kwestia dotycząca czynnika ludzkiego powinna wyjaśnić, że nie należy oczekiwać, że SQL Server zawsze zachowa się w 100% zgodnie ze specyfikacją.

Czynnikiem nadrzędnym w tym przypadku jest waga przypisana do każdego punktu kodowego oraz fakt, że wiele punktów kodowych może mieć tę samą specyfikację wagi. Podstawowe wagi (bez zastąpień specyficznych dla ustawień narodowych) można znaleźć tutaj (uważam, że 100serią zestawień jest Unicode v 5.0 - nieformalne potwierdzenie w komentarzach do elementu Microsoft Connect ):

http://www.unicode.org/Public/UCA/5.0.0/allkeys.txt

Punkt kodowy, o którym mowa - U + FFFD - jest zdefiniowany jako:

FFFD  ; [*0F12.0020.0002.FFFD] # REPLACEMENT CHARACTER

Zapis ten jest zdefiniowany w sekcji 9.1 Format pliku wszystkich klawiszy UCA:

<entry>       := <charList> ';' <collElement>+ <eol>
<charList>    := <char>+
<collElement> := "[" <alt> <weight> "." <weight> "." <weight> ("." <weight>)? "]"
<alt>         := "*" | "."

Collation elements marked with a "*" are variable.

Ta ostatnia linia jest ważna, ponieważ punkt kodowy, na który patrzymy, ma specyfikację zaczynającą się od „*”. W sekcji 3.6 Ważenie zmiennych zdefiniowano cztery możliwe zachowania, oparte na wartościach konfiguracji sortowania, do których nie mamy bezpośredniego dostępu (są one zakodowane na stałe w implementacji Microsoft dla każdego sortowania, np. Czy rozróżniana jest wielkość liter w pierwszej kolejności czy po pierwsze, właściwość, która różni się między VARCHARdanymi za pomocą SQL_sortowania i wszystkimi innymi odmianami).

Nie mam czasu na pełne zbadanie, które ścieżki są brane, i na ustalenie, które opcje są stosowane, aby można było podać bardziej solidny dowód, ale można bezpiecznie powiedzieć, że w ramach każdej specyfikacji Code Point, czy coś uważane za „równe” nie zawsze będzie korzystało z pełnej specyfikacji. W tym przypadku mamy „0F12.0020.0002.FFFD” i najprawdopodobniej wykorzystywane są tylko poziomy 2 i 3 (tj . .0020.0002. ). Wykonanie „Count” w Notepad ++ dla „.0020.0002”. znajduje 12 581 dopasowań (w tym dodatkowe postacie, z którymi jeszcze nie mieliśmy do czynienia). Wykonanie „Count” na „[*” zwraca 4049 dopasowań. Wykonywanie RegEx „Find” / „Count” przy użyciu wzorca\[\*\d{4}\.0020\.0002zwraca 832 dopasowania. Więc gdzieś w tej kombinacji, a także niektóre inne reguły, których nie widzę, a także niektóre szczegóły dotyczące implementacji specyficzne dla Microsoft, to pełne wyjaśnienie tego zachowania. I dla jasności, zachowanie jest takie samo dla wszystkich pasujących znaków, ponieważ wszystkie one pasują do siebie, ponieważ wszystkie mają taką samą wagę po zastosowaniu reguł (co oznacza, że ​​to pytanie można było zadać na temat każdego z nich, a nie koniecznie Mr. �).

Możesz zobaczyć z zapytaniem poniżej i zmienić COLLATEklauzulę zgodnie z wynikami pod zapytaniem, jak działają różne wrażliwości w dwóch wersjach Collation:

;WITH cte AS
(
  SELECT     TOP (65536) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1 AS [Num]
  FROM       [master].sys.columns col
  CROSS JOIN [master].sys.objects obj
)
SELECT cte.Num AS [Decimal],
       CONVERT(VARBINARY(2), cte.Num) AS [Hex],
       NCHAR(cte.Num) AS [Character]
FROM   cte
WHERE  NCHAR(cte.Num) = NCHAR(0xFFFD) COLLATE Latin1_General_100_CS_AS_WS --N'�'
ORDER BY cte.Num;

Różne liczby pasujących znaków w różnych układach znajdują się poniżej.

Latin1_General_100_CS_AS_WS   =   5840
Latin1_General_100_CS_AS      =   5841 (The "extra" character is U+3000)
Latin1_General_100_CI_AS      =   5841
Latin1_General_100_CI_AI      =   6311

Latin1_General_CS_AS_WS       = 21,229
Latin1_General_CS_AS          = 21,230
Latin1_General_CI_AS          = 21,230
Latin1_General_CI_AI          = 21,537

We wszystkich wymienionych powyżej zestawieniach N'' = N'�'również ma wartość true.

AKTUALIZACJA

Byłem w stanie przeprowadzić trochę więcej badań i oto, co znalazłem:

Jak to „prawdopodobnie” powinno działać

Korzystając z ICU Collation Demo , ustawiłem ustawienia regionalne na „en-US-u-va-posix”, ustawiłem siłę na „podstawową”, zaznaczone pokaż „klucze sortowania” i wkleiłem 4 następujące znaki, które skopiowałem z wyniki powyższego zapytania (przy użyciu Latin1_General_100_CI_AIsortowania):

�
Ԩ
ԩ
Ԫ

i to zwraca:

Ԫ
    60 2E 02 .
Ԩ
    60 7A .
ԩ
    60 7A .
�
    FF FD .

Następnie sprawdź właściwości znaku dla „ ” na stronie http://unicode.org/cldr/utility/character.jsp?a=fffd i sprawdź, czy klucz sortowania na poziomie 1 (tj. FF FD) Odpowiada właściwości „uca”. Kliknięcie tej właściwości „uca” prowadzi do strony wyszukiwania - http://unicode.org/cldr/utility/list-unicodeset.jsp?a=%5B%3Auca%3DFFFD%3A%5D - pokazując tylko 1 dopasowanie. I w pliku allkeys.txt waga sortowania poziomu 1 jest pokazana jako 0F12i istnieje tylko 1 dopasowanie do tego.

Aby upewnić się, że interpretujemy to zachowanie poprawnie, spojrzałem na inny znak: GRECKI LITER OMICRON Z VARIĄ Ὸna stronie http://unicode.org/cldr/utility/character.jsp?a=1FF8, który ma „uca” ( tzn. sortowanie / sortowanie na poziomie 1) 5F30. Kliknięcie tego „5F30” prowadzi nas do strony wyszukiwania - http://unicode.org/cldr/utility/list-unicodeset.jsp?a=%5B%3Auca%3D5F30%3A%5D - pokazuje 30 dopasowań, 20 z mieszczą się w zakresie 0–65535 (tj. U + 0000 - U + FFFF). Patrząc w allkeys.txt plik dla Code Point 1FF8 widzimy poziom 1 Ciężar sortowania 12E0. Wykonanie „Count” w Notepad ++ na12E0. pokazuje 30 dopasowań (odpowiada to wynikom z Unicode.org, choć nie jest to gwarantowane, ponieważ plik dotyczy Unicode v 5.0, a strona korzysta z danych Unicode v 9.0).

W SQL Server następujące zapytanie zwraca 20 dopasowań, takich samych jak wyszukiwanie Unicode.org podczas usuwania 10 znaków uzupełniających:

;WITH cte AS
(
  SELECT TOP (65535) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [Num]
  FROM   [master].sys.columns col
  CROSS JOIN [master].sys.objects obj
)
SELECT cte.Num AS [Decimal],
       CONVERT(VARCHAR(50), CONVERT(VARBINARY(2), cte.Num), 2) AS [Hex],
       NCHAR(cte.Num) AS [Character]
FROM cte
WHERE NCHAR(cte.Num) = NCHAR(0x1FF8) COLLATE Latin1_General_100_CI_AI
ORDER BY cte.Num;

I, dla pewności, wracając do strony ICU Collation Demo i zastępując znaki w polu „Input” następującymi 3 znakami pobranymi z listy 20 wyników z SQL Server:

Ὂ
𝜪
Ὸ

pokazuje, że rzeczywiście wszystkie mają taką samą 5F 30wagę sortowania poziomu 1 (pasującą do pola „uca” na stronie właściwości postaci).

SO, z pewnością wydaje się, że ta konkretna postać nie powinna pasować do niczego innego.

Jak to faktycznie działa (przynajmniej w Microsoft-land)

W przeciwieństwie do SQL Server, .NET umożliwia wyświetlanie klucza sortowania dla ciągu za pomocą metody CompareInfo.GetSortKey . Używając tej metody i przekazując tylko znak U + FFFD, zwraca klucz sortowania 0x0101010100. Następnie, iterując wszystkie znaki z zakresu od 0 do 65535, aby zobaczyć, które z nich mają klucz sortowania 0x0101010100zwróconych 4529 dopasowań. To nie jest dokładnie zgodne z 5840 zwróconym w SQL Server (podczas korzystania z Latin1_General_100_CS_AS_WSsortowania), ale jest to najbliższe, jakie możemy uzyskać (na razie), biorąc pod uwagę, że korzystam z systemu Windows 10 i .NET Framework w wersji 4.6.1, który używa Unicode v 6.3.0 zgodnie z wykresem dla klasy CharUnicodeInfo(w „Uwaga dla dzwoniących”, w sekcji „Uwagi”). W tej chwili używam funkcji SQLCLR, więc nie mogę zmienić docelowej wersji Framework. Kiedy będę miał okazję, stworzę aplikację konsolową i użyję docelowej wersji Framework 4.5, która używa Unicode v 5.0, który powinien pasować do Collations serii 100.

Ten test pokazuje, że nawet bez dokładnie takiej samej liczby dopasowań między .NET a SQL Server dla U + FFFD, jest całkiem jasne, że nie jest to zachowanie specyficzne dla SQL Server, i czy celowe lub nadzór z wykonaną implementacją przez Microsoft znak U + FFFD rzeczywiście pasuje do kilku znaków, nawet jeśli nie powinien być zgodny ze specyfikacją Unicode. A biorąc pod uwagę, że ta postać pasuje do U + 0000 (zero), prawdopodobnie jest to tylko kwestia braku wag.

RÓWNIEŻ

Jeśli chodzi o różnicę w zachowaniu w =zapytaniu w porównaniu z LIKE N'%�%'zapytaniem, ma to związek z symbolami wieloznacznymi i brakującymi (zakładam) wagami dla tych (tj. � Ƕ Ƿ Ǹ) Znaków. Jeśli LIKEwarunek zostanie zmieniony na zwykły, LIKE N'�'wówczas zwróci te same 3 wiersze co =warunek. Jeśli problem ze znakami wieloznacznymi nie wynika z „brakujących” wag (nie ma 0x00zwracanego klucza sortowania przez CompareInfo.GetSortKey, btw), może to być spowodowane tym, że te znaki mogą mieć właściwość, która pozwala na zmianę klucza sortowania w zależności od kontekstu (tj. Znaki otaczające ).