Suporte para agrupamento¶

Compreendendo o agrupamento¶

As cadeia de caracteres de texto do Snowflake são armazenadas usando o conjunto de caracteres UTF-8 e, por padrão, as cadeias são comparadas de acordo com os códigos Unicode que representam os caracteres na cadeia.

Entretanto, comparar cadeias de caracteres com base em suas representações de caracteres UTF-8 pode não fornecer o comportamento desejado ou esperado. Por exemplo:

• Se caracteres especiais em um determinado idioma não forem classificados de acordo com os padrões de classificação desse idioma, então a classificação pode retornar resultados inesperados.

• Você pode querer que as cadeias de caracteres sejam classificadas por outras regras, como ignorar a diferenciação entre maiúsculas ou minúsculas.

O agrupamento permite que você especifique explicitamente as regras a serem usadas para comparar cadeias de caracteres, com base em:

• Locais diferentes (ou seja, conjuntos de caracteres diferentes para idiomas diferentes).

• Diferenciação entre maiúsculas e minúsculas (isto é, usar comparações de cadeias de caracteres diferenciando ou não maiúsculas e minúsculas sem chamar explicitamente as funções UPPER ou LOWER para converter as cadeias de caracteres).

• Identificação de acentos (por exemplo, se Z, Ź e Ż são considerados a mesma letra ou letras diferentes).

• Identificação de pontuação (ou seja, se as comparações usam apenas letras ou incluem todos os caracteres). Por exemplo, se uma comparação não identificar pontuação, então A-B-C e ABC são tratados como equivalentes.

• Opções adicionais, como preferências de classificação baseadas na primeira letra de uma cadeia de caracteres e corte de espaços em branco no começo (à esquerda) e/ou no fim (à direita).

Utilizações de agrupamento¶

O agrupamento pode ser usado em uma grande variedade de operações, incluindo (mas não se limitando a):

Controle de agrupamento¶

O controle de agrupamento é granular. Você pode especificar explicitamente o agrupamento a ser usado para:

• Uma conta, usando o parâmetro de nível de conta DEFAULT_DDL_COLLATION.

• Todas as colunas em todas as tabelas adicionadas a um banco de dados, usando o comando ALTER DATABASE.

• Todas as colunas em todas as tabelas adicionadas a um esquema, usando o comando ALTER SCHEMA.

• Todas as colunas adicionadas a uma tabela, usando o comando ALTER TABLE.

• Colunas individuais em uma tabela, usando o comando CREATE TABLE.

• Uma comparação específica em uma instrução SQL (por exemplo, WHERE col1 = col2). Se forem aplicados múltiplos agrupamentos a uma instrução, o Snowflake determina o agrupamento a ser usado com base na precedência. Para obter mais detalhes sobre precedência, consulte Precedência de agrupamento em operações com várias cadeias de caracteres.

Cláusula COLLATE para definições de colunas da tabela¶

Adicionar a cláusula opcional COLLATE à definição de colunas de uma tabela indica que o agrupamento especificado é usado para comparações e outras operações relacionadas que são realizadas nos dados da coluna:

CREATE TABLE <table_name> ( <col_name> <col_type> COLLATE '<collation_specification>'
                            [ , <col_name> <col_type> COLLATE '<collation_specification>' ... ]
                            [ , ... ]
                          )

Se nenhuma cláusula COLLATE for especificada para uma coluna, o Snowflake usa o padrão, que compara as cadeias de caracteres com base em suas representações de caracteres UTF-8.

Além disso, o Snowflake oferece suporte à especificação de uma cadeia de caracteres vazia para a especificação de agrupamento (por exemplo, COLLATE ''), o que equivale a não especificar um agrupamento para a coluna.

Entretanto, devido à precedência, especificar COLLATE '' para uma coluna não tem o mesmo efeito que especificar explicitamente COLLATE 'utf8'. Para obter mais detalhes, consulte Precedência de agrupamento em operações com várias cadeias de caracteres.

Para ver se o agrupamento foi especificado para as colunas em uma tabela, use DESCRIBE TABLE (ou use a função COLLATION para exibir o agrupamento, se houver, para uma coluna específica).

Função COLLATE¶

A função COLLATE usa o agrupamento especificado na expressão da cadeia de caracteres de entrada:

COLLATE( <expression> , '<collation_specification>' )

Esta função também pode ser chamada usando a notação de infixo:

<expression> COLLATE '<collation_specification>'

Esta função é particularmente útil para especificar explicitamente um determinado agrupamento para uma determinada operação (por exemplo, classificação), mas também pode ser usada para:

• Permitir o agrupamento na cláusula SELECT de uma subconsulta, fazendo com que todas as operações na coluna especificada na consulta externa utilizem o agrupamento.

• Criar uma tabela usando CTAS com um agrupamento especificado.

Este exemplo avalia usando o agrupamento que não diferencia maiúsculas de minúsculas em inglês:

SELECT * FROM t1 WHERE COLLATE(col1 , 'en-ci') = 'Tango';

Este exemplo classifica os resultados usando o agrupamento alemão:

SELECT * FROM t1 ORDER BY COLLATE(col1 , 'de');

Este exemplo cria uma tabela com uma coluna usando agrupamento francês:

CREATE TABLE t2 AS SELECT COLLATE(col1, 'fr') AS col1 FROM t1;

Este exemplo usa notação infixa para criar uma tabela com uma coluna usando agrupamento francês:

CREATE TABLE t2 AS SELECT col1 COLLATE 'fr' AS col1 FROM t1;

Função COLLATION¶

A função COLLATION retorna a especificação de agrupamento usada por uma expressão, incluindo uma coluna de tabela:

COLLATION( <expression> )

Se não tiver sido especificado um agrupamento para a expressão, a função retorna NULL.

Normalmente, se você usar essa função em um nome de coluna, é melhor usar DISTINCT para evitar obter uma linha de saída para cada linha na tabela. Por exemplo:

SELECT DISTINCT COLLATION(column1) FROM table1;

Exemplos de especificações¶

Alguns exemplos de cadeia de caracteres de especificação de agrupamento incluem:

• 'de': local Alemão (Deutsch).

• 'de-ci-pi': local Alemão, com comparações sem distinção de maiúsculas/minúsculas e sem identificação de pontuação.

• 'fr_CA-ai': local Francês canadense, com comparações sem identificação de acentos.

• 'en_US-trim': local Inglês US, com espaços iniciais e espaços finais cortados antes da comparação.

Você também pode especificar uma cadeia de caracteres vazia para uma especificação de agrupamento (por exemplo, COLLATE '' ou COLLATE(col1, '')), o que indica que não se deve usar agrupamento.

Especificadores com suporte¶

Local:

Especifica as regras específicas de idioma e de país a serem aplicadas.

Aceita cadeias de caracteres locais válidas, consistindo em um código de idioma (obrigatório) e código de país (opcional) na forma language_country. Alguns exemplos de local incluem:

• en – inglês.

• en_US – inglês dos Estados Unidos.

• fr – francês.

• fr_CA – francês canadense.

Além disso, a pseudolocalidade utf8 especifica a ordenação Unicode, que é o padrão. Para obter mais detalhes, consulte Diferenças na classificação ao usar UTF-8 ou agrupamento de localidade (neste tópico).

O especificador de local é opcional, mas, se usado, deve ser o primeiro especificador na cadeia de caracteres.

Para obter a lista completa de localidades suportadas pelo Snowflake, consulte Localidades de agrupamento compatíveis com Snowflake.

Diferenciação entre maiúsculas e minúsculas:

Determina se há distinção entre maiúsculas e minúsculas ao comparar valores. Possíveis valores:

• cs – Diferencia maiúsculas e minúsculas (padrão).

• ci – Não diferencia maiúsculas e minúsculas.

Por exemplo:

Especificação de agrupamento	Valor	Resultado
'en-ci'	Abc = abc	True
'en-cs' / en	Abc = abc	False

Identificação de acentos:

Determina se os caracteres acentuados são considerados iguais ou diferentes de seus caracteres base. Possíveis valores:

• as – Identifica acentos (padrão).

• ai – Não identifica acentos.

Por exemplo:

Especificação de agrupamento	Valor	Resultado	Notas
'fr-ai'	E = É	True
'fr-as' / 'fr'	E = É	False
'en-ai'	a = ą	True	Em inglês, essas letras são tratadas como se tivessem apenas diferenças de acento, portanto, especificar a insensibilidade ao acento resulta na comparação dos valores como iguais.
'pl-ai'	a = ą	False	Em polonês, estas letras são tratadas como letras-base separadas, de modo que sempre se comparam como desiguais, independentemente de haver a especificação para não identificar acentos.
'pl-as' / 'pl'	a = ą	False

As regras de sensibilidade a acento e agrupamento variam entre os idiomas. Por exemplo, em alguns idiomas, o agrupamento sempre identifica acentos e não é possível desativá-lo, mesmo especificando o agrupamento sem identificação de acentos.

Identificação de pontuação:

Determina se caracteres não letras são importantes. Possíveis valores:

• ps - Identifica pontuação.

• pi - Não identifica pontuação.

Observe que o padrão é específico do local (isto é, se a identificação de pontuação não for determinada, são usadas regras específicas do local). Na maioria dos casos, as regras são equivalentes a ps.

Por exemplo:

Especificação de agrupamento	Valor	Resultado	Notas
'en-pi'	A-B-C = ABC	True
'en-ps'	A-B-C = ABC	False

Preferência pela primeira letra:

Determina se, ao classificar, as letras maiúsculas ou minúsculas são as primeiras. Possíveis valores:

• fl - As letras minúsculas são classificadas primeiro.

• fu - As letras maiúsculas são classificadas primeiro.

O padrão é específico da localidade (ou seja, se nenhum valor for especificado, a ordenação específica da localidade será usada). Na maioria dos casos, a classificação é equivalente a fl.

Além disso, este especificador não tem impacto nas comparações de igualdade.

Conversão de maiúsculas/minúsculas:

Resulta na conversão das cadeia de caracteres em minúsculas ou maiúsculas antes das comparações. Em algumas situações, isso é mais rápido do que um agrupamento completo específico de local. Possíveis valores:

• upper - Converte a cadeia de caracteres em maiúsculas antes das comparações.

• lower - Converte a cadeia de caracteres em minúsculas antes das comparações.

Este especificador não tem um padrão (ou seja, se nenhum valor for especificado, nenhuma das conversões ocorrerá).

Corte de espaço em branco:

Remove os espaços iniciais/finais das cadeia de caracteres antes das comparações. Esta funcionalidade pode ser útil para realizar comparações equivalentes (exceto em casos extremamente raros de cantos) na semântica para o tipo de dados SQL CHAR.

Possíveis valores:

• trim - Remove os espaços iniciais e finais antes das comparações.

• ltrim - Remove somente os espaços iniciais antes das comparações.

• rtrim - Remover somente os espaços finais antes das comparações.

Este especificador não tem um padrão (ou seja, se nenhum valor for especificado, o corte não será executado).

Por exemplo:

Especificação de agrupamento	Valor	Resultado	Notas
'en-trim'	__ABC_ = ABC	True	Para fins de exemplo, os caracteres sublinhados representam espaços em branco.
'en-ltrim'	__ABC_ = ABC	False
'en-rtrim'	__ABC_ = ABC	False
'en'	__ABC_ = ABC	False

Comparações sem diferenciar maiúsculas e minúsculas¶

As seções a seguir descrevem comparações que não diferenciam maiúsculas de minúsculas:

• Diferenças ao comparar cadeias de caracteres maiúsculas e cadeias de caracteres originais

• Pesos dos caracteres

CREATE OR REPLACE TABLE test_table (col1 VARCHAR, col2 VARCHAR);
INSERT INTO test_table VALUES ('ı', 'i');

SELECT col1 = col2,
       COLLATE(col1, 'lower') = COLLATE(col2, 'lower'),
       COLLATE(col1, 'upper') = COLLATE(col2, 'upper')
  FROM test_table;

+-------------+-------------------------------------------------+-------------------------------------------------+
| COL1 = COL2 | COLLATE(COL1, 'LOWER') = COLLATE(COL2, 'LOWER') | COLLATE(COL1, 'UPPER') = COLLATE(COL2, 'UPPER') |
|-------------+-------------------------------------------------+-------------------------------------------------|
| False       | False                                           | True                                            |
+-------------+-------------------------------------------------+-------------------------------------------------+

Diferenças na classificação ao usar UTF-8 ou agrupamento de localidade¶

As cadeias de caracteres são sempre armazenadas internamente no Snowflake em UTF-8 e podem representar qualquer caractere em qualquer linguagem compatível por UTF-8. Portanto, o agrupamento padrão é UTF-8 (ou seja, 'utf8').

O agrupamento UTF-8 é baseado na representação numérica do caractere, e não na ordem alfabética do caractere.

Isso é análogo à classificação pelo valor ordinal de cada caractere ASCII, o que é importante notar porque as letras maiúsculas têm valores ordinais inferiores às letras minúsculas:

Como resultado:

• Se você classificar na ordem UTF-8, todas as letras maiúsculas são retornadas antes de todas as letras minúsculas:

  A , B , … , Y , Z , … , a , b , … , y , z

• Em contraste, a especificação de agrupamento 'en' classifica alfabeticamente (em vez de usar a representação interna UTF-8), fazendo com que A e a sejam retornados antes de B e b:

  a , A , b , B , …

Além disso, as diferenças entre os especificadores cs e ci de diferenciação de maiúsculas e minúsculas afetam a classificação:

• cs (diferencia maiúsculas e minúsculas) sempre retorna a versão em minúsculas de uma letra antes da versão em maiúsculas da mesma letra. Por exemplo, usando 'en-cs':

  a , A , b , B , …

  A diferenciação entre maiúsculas e minúsculas é o padrão e, portanto, 'en-cs' e 'en' são equivalentes.

• ci (case-insensitive, ou seja, sem diferenciar maiúsculas de minúsculas) retorna versões maiúsculas e minúsculas das letras aleatoriamente em relação uma à outra, mas ainda antes das versões maiúsculas e minúsculas das letras posteriores. Por exemplo, usando 'en-ci':

  A , a , b , B , …

Alguns caracteres não alfabéticos também podem ser classificados de forma diferente, dependendo da configuração de agrupamento. O exemplo a seguir mostra que o caractere de mais (+) e o caractere de menos (-) são classificados de forma diferente para diferentes configurações de agrupamento:

Crie a tabela:

CREATE OR REPLACE TABLE demo (
    no_explicit_collation VARCHAR,
    en_ci VARCHAR COLLATE 'en-ci',
    en VARCHAR COLLATE 'en',
    utf_8 VARCHAR collate 'utf8');
INSERT INTO demo (no_explicit_collation) VALUES
    ('-'),
    ('+');
UPDATE demo SET
    en_ci = no_explicit_collation,
    en = no_explicit_collation,
    utf_8 = no_explicit_collation;

Consultar os dados:

SELECT MAX(no_explicit_collation), MAX(en_ci), MAX(en), MAX(utf_8)
  FROM demo;

+----------------------------+------------+---------+------------+
| MAX(NO_EXPLICIT_COLLATION) | MAX(EN_CI) | MAX(EN) | MAX(UTF_8) |
|----------------------------+------------+---------+------------|
| -                          | +          | +       | -          |
+----------------------------+------------+---------+------------+

Precedência de agrupamento em operações com várias cadeias de caracteres¶

Ao realizar uma operação em duas (ou mais) cadeias de caracteres, podem ser especificados diferentes agrupamentos para diferentes cadeias. A determinação do agrupamento a ser aplicado depende de como o agrupamento foi especificado para cada entrada e da precedência de cada especificador.

Existem três níveis de precedência (do mais alto ao mais baixo):

Função:

O agrupamento é especificado usando Função COLLATE em uma instrução SQL.

Coluna:

O agrupamento foi especificado na definição de coluna.

Nenhum:

Nenhum agrupamento é/foi especificado para uma determinada expressão/coluna, ou é/foi utilizado um agrupamento com uma especificação vazia (por exemplo, COLLATE(col1, '') ou col1 STRING COLLATE '').

Ao determinar o agrupamento a ser usado, é utilizada a especificação de agrupamento com a precedência mais alta. Se vários agrupamentos forem especificados com o mesmo nível de precedência, seus valores serão comparados e, se não forem iguais, um erro será retornado.

Por exemplo, considere uma tabela com as seguintes especificações de agrupamento no nível da coluna:

CREATE OR REPLACE TABLE collation_precedence_example(
  col1    VARCHAR,               -- equivalent to COLLATE ''
  col2_fr VARCHAR COLLATE 'fr',  -- French locale
  col3_de VARCHAR COLLATE 'de'   -- German locale
);

Se a tabela for utilizada em uma instrução comparando duas cadeia de caracteres, o agrupamento é aplicado da seguinte forma:

• Esta comparação usa o agrupamento 'fr' porque a precedência para col2_fr é maior que a precedência para col1:

  ... WHERE col1 = col2_fr ...
  

  Copy

• Esta comparação usa o agrupamento 'en', porque ela é explicitamente especificada na instrução, que tem precedência sobre o agrupamento para col2_fr:

  ... WHERE col1 COLLATE 'en' = col2_fr ...
  

  Copy

• Esta comparação retorna um erro porque as expressões têm diferentes agrupamentos no mesmo nível de precedência:

  ... WHERE col2_fr = col3_de ...
  

  Copy

• Esta comparação usa o agrupamento 'de' porque o agrupamento para col2_fr foi removido:

  ... WHERE col2_fr COLLATE '' = col3_de ...
  

  Copy

• Esta comparação retorna um erro porque as expressões têm diferentes agrupamentos no mesmo nível de precedência:

  ... WHERE col2_fr COLLATE 'en' = col3_de COLLATE 'de' ...
  

  Copy

Mesmo que o agrupamento padrão do Snowflake seja 'utf8', especificar uma cadeia de caracteres vazia (ou não especificar um agrupamento) é diferente de especificar explicitamente 'utf8', porque o agrupamento explícito tem maior precedência do que o não agrupamento. As duas últimas instruções nos exemplos de código abaixo mostram a diferença:

Por exemplo, considere uma tabela com as seguintes especificações de agrupamento no nível da coluna:

CREATE OR REPLACE TABLE collation_precedence_example2(
  s1 STRING COLLATE '',
  s2 STRING COLLATE 'utf8',
  s3 STRING COLLATE 'fr'
);

Se a tabela for utilizada em uma instrução comparando duas cadeia de caracteres, o agrupamento é aplicado da seguinte forma:

• Esta comparação usa 'utf8' porque s1 não tem agrupamento e 'utf8' é o padrão:

  ... WHERE s1 = 'a' ...
  

  Copy

• Esta comparação usa 'utf8' porque s1 não tem nenhum agrupamento e s2 tem um agrupamento 'utf8' explícito.

  ... WHERE s1 = s2 ...
  

  Copy

• Esta comparação executa sem erro porque s1 não tem nenhum agrupamento, e s3 tem agrupamento fr explícito, portanto, o agrupamento explícito tem precedência:

  ... WHERE s1 = s3 ...
  

  Copy

• Esta comparação causa um erro porque s2 e s3 têm diferentes agrupamentos especificados no mesmo nível de precedência:

  ... WHERE s2 = s3 ...
  

  Copy

  002322 (42846): SQL compilation error: Incompatible collations: 'fr' and 'utf8'
  

Suporte limitado para agrupamento em funções internas¶

O agrupamento oferece suporte apenas a um subconjunto de funções de cadeia de caracteres. Funções que poderiam ser razoavelmente esperadas para implementar o agrupamento, mas que ainda não oferecem suporte a agrupamento, retornam um erro quando usadas com o agrupamento. Estas mensagens de erro são exibidas não apenas ao chamar a função COLLATE, mas também ao chamar uma função de cadeia de caracteres em uma coluna que tenha sido definida como agrupada na instrução CREATE TABLE ou ALTER TABLE que criou aquela coluna.

Atualmente, o agrupamento influencia apenas as operações de comparação simples. Por exemplo, POSITION('abc' in COLLATE('ABC', 'en-ci')) não encontra abc em ABC, mesmo que seja especificado um agrupamento que não diferencia maiúsculas e minúsculas.

Consequências de desempenho do uso do agrupamento¶

O uso do agrupamento pode afetar o desempenho de várias operações do banco de dados:

• As operações que envolvem comparações podem ser mais lentas.

  Isso pode afetar cláusulas WHERE simples, bem como junções, classificações, operações GROUPBY etc.

• Quando usada com algumas funções em predicados WHERE, a remoção de micropartição pode ser menos eficiente.

• A utilização de um predicado WHERE que seja diferente do agrupamento especificado para a coluna pode resultar em uma eficiência de remoção reduzida ou a eliminação completa da remoção.

Considerações adicionais para o uso do agrupamento¶

• Lembre-se de que, apesar da semelhança em seus nomes, as seguintes funções de agrupamento retornam resultados diferentes:

  • COLLATE especifica explicitamente qual agrupamento usar.

  • COLLATION mostra qual agrupamento é usado se nenhum for especificado explicitamente.

• Uma coluna com especificação de agrupamento pode usar caracteres que não são do local para o agrupamento, o que pode causar impacto na classificação.

  Por exemplo, se uma coluna for criada com uma cláusula COLLATE 'en', os dados na coluna podem conter o caractere É não existente em inglês. Nessa situação, o caractere É é classificado próximo a E.

• Você pode especificar operações de agrupamento que não são necessariamente significativas.

  Por exemplo, você pode especificar para que os dados poloneses sejam comparados com os dados franceses usando o agrupamento alemão:

  SELECT ... WHERE COLLATE(French_column, 'de') = Polish_column;
  

  Copy

  Entretanto, o Snowflake recomenda não utilizar o recurso dessa forma porque pode retornar resultados inesperados ou não intencionais.

• Depois que uma coluna da tabela é definida, você não pode alterar a agrupamento da coluna. Em outras palavras, após uma coluna ter sido criada com um determinado agrupamento usando uma instrução CREATE TABLE, não é possível usar ALTER TABLE para alterar o agrupamento.

  Entretanto, é possível especificar um agrupamento diferente em uma instrução DML, como uma instrução SELECT, que faz referência à coluna.

Diferenças nas comparações de larguras, espaços e scripts¶

Durante as comparações de cadeias de caracteres, a especificação do agrupamento ci reconhece que diferentes representações visuais de um caractere ainda podem se referir ao mesmo caractere e as trata adequadamente. Para permitir comparações mais eficientes, as especificações de agrupamento upper e lower não reconhecem essas diferentes representações visuais de um caractere como o mesmo caractere.

Especificamente, a especificação de agrupamento ci ignora algumas diferenças nas seguintes categorias, enquanto as especificações de agrupamento upper e lower não as ignoram:

• Larguras dos caracteres

• Tipos de espaços

• Scripts de caracteres

As seções a seguir incluem exemplos que ilustram essas diferenças.

CREATE OR REPLACE TABLE different_widths(codepoint STRING, description STRING);

INSERT INTO different_widths VALUES
  ('a', 'ASCII a'),
  ('A', 'ASCII A'),
  ('ａ', 'Full-width a'),
  ('Ａ', 'Full-width A');

SELECT codepoint VISUAL_CHAR,
       'U+'  || TO_CHAR(UNICODE(codepoint), '0XXX') codepoint_representation,
       description
  FROM different_widths;

+-------------+--------------------------+--------------+
| VISUAL_CHAR | CODEPOINT_REPRESENTATION | DESCRIPTION  |
|-------------+--------------------------+--------------|
| a           | U+0061                   | ASCII a      |
| A           | U+0041                   | ASCII A      |
| ａ          | U+FF41                   | Full-width a |
| Ａ          | U+FF21                   | Full-width A |
+-------------+--------------------------+--------------+

SELECT COUNT(*) NumRows,
       COUNT(DISTINCT UNICODE(codepoint)) DistinctCodepoints,
       COUNT(DISTINCT codepoint COLLATE 'en-ci') DistinctCodepoints_EnCi,
       COUNT(DISTINCT codepoint COLLATE 'upper') DistinctCodepoints_Upper,
       COUNT(DISTINCT codepoint COLLATE 'lower') DistinctCodepoints_Lower
  FROM different_widths;

+---------+--------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | DISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+--------------------+-------------------------+--------------------------+--------------------------|
|       4 |                  4 |                       1 |                        2 |                        2 |
+---------+--------------------+-------------------------+--------------------------+--------------------------+

CREATE OR REPLACE TABLE different_whitespaces(codepoint STRING, description STRING);

INSERT INTO different_whitespaces VALUES
  (' ', 'ASCII space'),
  ('\u00A0', 'Non-breaking space'),
  (' ', 'Ogham space mark'),
  (' ', 'en space'),
  (' ', 'em space');

SELECT codepoint visual_char,
       'U+'  || TO_CHAR(unicode(codepoint), '0XXX')
       codepoint_representation, description
  FROM different_whitespaces;

+-------------+--------------------------+--------------------+
| VISUAL_CHAR | CODEPOINT_REPRESENTATION | DESCRIPTION        |
|-------------+--------------------------+--------------------|
|             | U+0020                   | ASCII space        |
|             | U+00A0                   | Non-breaking space |
|             | U+1680                   | Ogham space mark   |
|             | U+2002                   | en space           |
|             | U+2003                   | em space           |
+-------------+--------------------------+--------------------+

SELECT COUNT(*) NumRows,
       COUNT(DISTINCT UNICODE(codepoint)) NumDistinctCodepoints,
       COUNT(DISTINCT codepoint COLLATE 'en-ci') DistinctCodepoints_EnCi,
       COUNT(DISTINCT codepoint COLLATE 'upper') DistinctCodepoints_Upper,
       COUNT(DISTINCT codepoint COLLATE 'lower') DistinctCodepoints_Lower
  FROM different_whitespaces;

+---------+-----------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | NUMDISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+-----------------------+-------------------------+--------------------------+--------------------------|
|       5 |                     5 |                       1 |                        5 |                        5 |
+---------+-----------------------+-------------------------+--------------------------+--------------------------+

CREATE OR REPLACE TABLE different_scripts(codepoint STRING, description STRING);

INSERT INTO different_scripts VALUES
  ('1', 'ASCII digit 1'),
  ('¹', 'Superscript 1'),
  ('₁', 'Subscript 1'),
  ('①', 'Circled digit 1'),
  ('੧', 'Gurmukhi digit 1'),
  ('௧', 'Tamil digit 1');

SELECT codepoint VISUAL_CHAR,
       'U+'  || TO_CHAR(UNICODE(codepoint), '0XXX') codepoint_representation,
       description
  FROM different_scripts;

+-------------+--------------------------+------------------+
| VISUAL_CHAR | CODEPOINT_REPRESENTATION | DESCRIPTION      |
|-------------+--------------------------+------------------|
| 1           | U+0031                   | ASCII digit 1    |
| ¹           | U+00B9                   | Superscript 1    |
| ₁           | U+2081                   | Subscript 1      |
| ①           | U+2460                   | Circled digit 1  |
| ੧           | U+0A67                   | Gurmukhi digit 1 |
| ௧           | U+0BE7                   | Tamil digit 1    |
+-------------+--------------------------+------------------+

SELECT COUNT(*) NumRows,
       COUNT(DISTINCT UNICODE(codepoint)) DistinctCodepoints,
       COUNT(DISTINCT codepoint COLLATE 'en-ci') DistinctCodepoints_EnCi,
       COUNT(DISTINCT codepoint COLLATE 'upper') DistinctCodepoints_Upper,
       COUNT(DISTINCT codepoint COLLATE 'lower') DistinctCodepoints_Lower
  FROM different_scripts;

+---------+--------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | DISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+--------------------+-------------------------+--------------------------+--------------------------|
|       6 |                  6 |                       1 |                        6 |                        6 |
+---------+--------------------+-------------------------+--------------------------+--------------------------+

Diferenças no tratamento de pontos de código ignoráveis¶

O algoritmo de agrupamento Unicode especifica que os elementos de agrupamento (pontos de código) podem ser ignoráveis, o que significa que um ponto de código não é considerado durante a comparação e classificação de cadeias de caracteres.

• Com a especificação de agrupamento ci, esses pontos de código são ignorados. Isso pode dificultar a busca ou substituição de pontos de código ignoráveis.

• Com as especificações de agrupamento upper e lower, esses pontos de código não são ignorados.

Por exemplo, o ponto de código U+0001 é ignorável. Se você comparar esse ponto de código a uma cadeia de caracteres vazia com a especificação de agrupamento en-ci, o resultado será TRUE porque U+0001 será ignorado:

SELECT '\u0001' = '' COLLATE 'en-ci';

+-------------------------------+
| '\U0001' = '' COLLATE 'EN-CI' |
|-------------------------------|
| True                          |
+-------------------------------+

Por outro lado, se você usar a especificação de agrupamento upper ou lower, o resultado será FALSE porque U+0001 não será ignorado:

SELECT '\u0001' = '' COLLATE 'upper';

+-------------------------------+
| '\U0001' = '' COLLATE 'UPPER' |
|-------------------------------|
| False                         |
+-------------------------------+

Da mesma forma, suponha que você chame a função REPLACE para remover esse ponto de código de uma cadeia de caracteres. Se você usar a especificação de agrupamento en-ci, a função não removerá o ponto de código porque U+0001 será ignorado.

Conforme mostrado no exemplo abaixo, a cadeia de caracteres retornada pela função REPLACE tem o mesmo comprimento que a cadeia de caracteres passada para a função porque a função não remove o caractere U+0001.

SELECT
  LEN('abc\u0001') AS original_length,
  LEN(REPLACE('abc\u0001' COLLATE 'en-ci', '\u0001')) AS length_after_replacement;

+-----------------+--------------------------+
| ORIGINAL_LENGTH | LENGTH_AFTER_REPLACEMENT |
|-----------------+--------------------------|
|               4 |                        4 |
+-----------------+--------------------------+

Por outro lado, se você usar a especificação de agrupamento upper ou lower, a função removerá o ponto de código da cadeia de caracteres, retornando uma cadeia de caracteres mais curta.

SELECT
  LEN('abc\u0001') AS original_length,
  LEN(REPLACE('abc\u0001' COLLATE 'upper', '\u0001')) AS length_after_replacement;

+-----------------+--------------------------+
| ORIGINAL_LENGTH | LENGTH_AFTER_REPLACEMENT |
|-----------------+--------------------------|
|               4 |                        3 |
+-----------------+--------------------------+

Diferenças quando os caracteres são representados por diferentes pontos de código¶

Em Unicode, diferentes sequências de pontos de código podem representar o mesmo caractere. Por exemplo, a letra minúscula grega Iota com Dialytika e Tonos pode ser representada pelo caractere pré-composto com o ponto de código U+0390 ou pela sequência de pontos de código U+03b9 U+0308 U+0301 para os caracteres decompostos.

Se você usar a especificação de agrupamento ci, as diferentes sequências de pontos de código de um caractere serão tratadas como o mesmo caractere. Por exemplo, o ponto de código U+0390 e a sequência de pontos de código U+03b9 U+0308 U+0301 são tratados como equivalentes:

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'en-ci';

+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'EN-CI' |
|-------------------------------------------------|
| True                                            |
+-------------------------------------------------+

Para melhorar o desempenho das especificações de agrupamento upper e lower, as sequências não são tratadas da mesma maneira. Duas sequências de pontos de código são consideradas equivalentes apenas se resultarem na mesma representação binária após serem convertidas para maiúsculas ou minúsculas.

Por exemplo, usar a especificação upper com o ponto de código U+0390 e a sequência de pontos de código U+03b9 U+0308 U+0301 resulta em caracteres que são tratados como iguais:

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'upper';

+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'UPPER' |
|-------------------------------------------------|
| True                                            |
+-------------------------------------------------+

Usar a especificação lower resulta em caracteres diferentes:

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'lower';

+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'LOWER' |
|-------------------------------------------------|
| False                                           |
+-------------------------------------------------+

É menos provável que essas diferenças ocorram ao usar upper (em vez de lower) porque há apenas um ponto de código composto em maiúsculas (U+0130), em comparação com mais de 100 pontos de código compostos em minúsculas.

Diferenças com sequências de pontos de código que representam um único caractere¶

Nos casos em que uma sequência de pontos de código representa um único caractere, a especificação de agrupamento ci reconhece que a sequência representa um único caractere e não corresponde a pontos de código individuais na sequência.

Por exemplo, a sequência de pontos de código U+03b9 U+0308 U+0301 representa um único caractere (a letra minúscula grega Iota com Dialytika e Tonos). U+0308 e U+0301 representam acentos aplicados a U+03b9.

Para a especificação de agrupamento ci, se você usar a função CONTAINS para determinar se a sequência U+03b9 U+0308 contém U+03b9 ou U+0308, a função retornará FALSE porque a sequência U+03b9 U+0308 é tratada como um único caractere:

SELECT CONTAINS('\u03b9\u0308', '\u03b9' COLLATE 'en-ci');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U03B9' COLLATE 'EN-CI') |
|----------------------------------------------------|
| False                                              |
+----------------------------------------------------+

SELECT CONTAINS('\u03b9\u0308', '\u0308' COLLATE 'en-ci');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U0308' COLLATE 'EN-CI') |
|----------------------------------------------------|
| False                                              |
+----------------------------------------------------+

Para melhorar o desempenho, as especificações upper e lower não tratam essas sequências como um único caractere. No exemplo acima, a função CONTAINS retorna TRUE porque essas especificações tratam a sequência de pontos de código como caracteres separados:

SELECT CONTAINS('\u03b9\u0308', '\u03b9' COLLATE 'upper');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U03B9' COLLATE 'UPPER') |
|----------------------------------------------------|
| True                                               |
+----------------------------------------------------+

SELECT CONTAINS('\u03b9\u0308', '\u0308' COLLATE 'upper');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U0308' COLLATE 'UPPER') |
|----------------------------------------------------|
| True                                               |
+----------------------------------------------------+

Diferenças quando alterações no caso resultam em vários pontos de código¶

Para alguns caracteres compostos, a versão maiúscula ou minúscula do caractere é representada por uma sequência de pontos de código. Por exemplo, o caractere maiúsculo do caractere alemão ß é uma sequência de dois caracteres S (SS).

Embora ß e SS sejam equivalentes, quando você usa a especificação de agrupamento upper, as pesquisas de ß e SS retornam resultados diferentes. As sequências produzidas pela conversão de letra maiúscula e minúscula correspondem totalmente ou não correspondem.

SELECT CONTAINS('ß' , 's' COLLATE 'upper');

+--------------------------------------+
| CONTAINS('SS' , 'S' COLLATE 'UPPER') |
|--------------------------------------|
| False                                |
+--------------------------------------+

SELECT CONTAINS('ss', 's' COLLATE 'upper');

+-------------------------------------+
| CONTAINS('SS', 'S' COLLATE 'UPPER') |
|-------------------------------------|
| True                                |
+-------------------------------------+

Diferenças na ordem de classificação¶

A classificação para as especificações de agrupamento upper e lower funciona de maneira diferente da classificação para a especificação ci:

• Com a especificação ci, as cadeias de caracteres são classificadas por chave de agrupamento. Em geral, a chave de agrupamento pode levar em conta a distinção entre maiúsculas e minúsculas, sensibilidade ao acento, localidade etc.

• Com as especificações upper e lower, as cadeias de caracteres são classificadas por ponto de código para melhorar o desempenho.

Por exemplo, alguns caracteres dentro do intervalo ASCII (como + e -) são classificados de forma diferente:

SELECT '+' < '-' COLLATE 'en-ci';

+---------------------------+
| '+' < '-' COLLATE 'EN-CI '|
|---------------------------|
| False                     |
+---------------------------+

SELECT '+' < '-' COLLATE 'upper';

+---------------------------+
| '+' < '-' COLLATE 'UPPER' |
|---------------------------|
| True                      |
+---------------------------+

Como outro exemplo, cadeias de caracteres com pontos de código ignorados são classificados em uma ordem diferente:

SELECT 'a\u0001b' < 'ab' COLLATE 'en-ci';

+-----------------------------------+
| 'A\U0001B' < 'AB' COLLATE 'EN-CI' |
|-----------------------------------|
| False                             |
+-----------------------------------+

SELECT 'a\u0001b' < 'ab' COLLATE 'upper';

+-----------------------------------+
| 'A\U0001B' < 'AB' COLLATE 'UPPER' |
|-----------------------------------|
| True                              |
+-----------------------------------+

Além disso, os emojis são classificados de maneira diferente:

SELECT 'abc' < '❄' COLLATE 'en-ci';

+-----------------------------+
| 'ABC' < '❄' COLLATE 'EN-CI' |
|-----------------------------|
| False                       |
+-----------------------------+

SELECT 'abc' < '❄' COLLATE 'upper';

+-----------------------------+
| 'ABC' < '❄' COLLATE 'UPPER' |
|-----------------------------|
| True                        |
+-----------------------------+

O agrupamento é compatível apenas com cadeias de caracteres de até 8 MB.¶

Embora o tipo de dados VARCHAR Snowflake ofereça suporte a cadeias de caracteres de até 16 MB, o Snowflake oferece suporte a agrupamento apenas quando a cadeia de caracteres resultante é 8 MB ou menos. (Algumas operações de agrupamento podem tornar uma cadeia de caracteres mais longa).

Agrupamento não suportado com UDFs¶

O Snowflake não oferece suporte para agrupamento com UDFs (funções definidas pelo usuário):

• Não é possível retornar um valor de cadeia de caracteres agrupado de uma UDF; o servidor relata que o tipo de retorno real é incompatível com o tipo de retorno declarado.

• Se você passar um valor de cadeia de caracteres agrupado para uma UDF, a informação de agrupamento não é passada; a UDF vê a cadeia de caracteres como uma cadeia não agrupada.

O agrupamento não é compatível com cadeias de caracteres em valores VARIANT, ARRAY ou OBJECT¶

Cadeias de caracteres armazenadas dentro de um valor VARIANT, OBJECT ou ARRAY não incluem uma especificação de agrupamento. Portanto:

• A comparação desses valores sempre usa o agrupamento 'utf8'.

• Quando um valor VARCHAR com uma especificação de agrupamento é usado para construir um valor de ARRAY, OBJECT ou VARIANT, a especificação de agrupamento não é preservada.

• Ainda é possível comparar um valor armazenado dentro de um ARRAY, OBJECT ou VARIANT extraindo o valor, convertendo-o para VARCHAR e adicionando uma especificação de agrupamento. Por exemplo:

  COLLATE(VARIANT_COL:fld1::VARCHAR, 'en-ci') = VARIANT_COL:fld2::VARCHAR
  

  Copy