ANSI_MODE
Aplica-se a: 
Databricks SQL
O ANSI_MODE parâmetro de configuração controla os principais comportamentos de funções internas e operações de transmissão.
Este artigo descreve o modo ANSI no Databricks SQL. Para conformidade ANSI no Databricks Runtime, consulte Conformidade ANSI no Databricks Runtime.
Definições
VERDADEIRO
Segue o padrão SQL em como ele lida com certas operações aritméticas e conversões de tipo, semelhante à maioria dos bancos de dados e data warehouses. Seguir esse padrão promove melhor qualidade, integridade e portabilidade dos dados.
FALSO
O Databricks SQL usa um comportamento compatível com o Hive.
Você pode definir esse parâmetro no nível da sessão usando a instrução SET e no nível global usando parâmetros de configuração SQL ou a API do SQL Warehouse.
Predefinição do sistema
O valor padrão do sistema é TRUE para contas adicionadas no Databricks SQL 2022.35 e superior.
Descrição detalhada
A documentação de referência do Databricks SQL descreve o comportamento padrão do SQL.
As seções a seguir descrevem as diferenças entre ANSI_MODE TRUE (modo ANSI) e FALSE (modo não-ANSI).
Operadores
No modo não-ANSI, as operações aritméticas executadas em tipos numéricos podem retornar valores estourados ou NULL, enquanto no modo ANSI essas operações retornam um erro.
| Operator | Description | Exemplo | ANSI_MODE = verdadeiro | ANSI_MODE = falso |
|---|---|---|---|---|
| dividendo / divisor | Devolve dividendo dividido por divisor. | 1/0 |
Erro | NULL |
| - EXPR | Devolve o valor negado de expr. | -(-128y) |
Erro | -128y (Transbordamento) |
| EXPR1 - EXPR2 | Devolve a subtração de expr2 de expr1. | -128y - 1y |
Erro | 127y (Transbordamento) |
| expr1 + expr2 | Devolve a soma de expr1 e expr2. | 127y + 1y |
Erro | -128y (Transbordamento) |
| dividendo % divisor | Devolve o restante após dividendo/divisor. | 1 % 0 |
Erro | NULL |
| multiplicador * multiplicador | Devolve multiplicador multiplicado por multiplicando. | 100y * 100y |
Erro | 16y (Transbordamento) |
| arrayExpr[index] | Retorna o elemento de um arrayExpr no índice. | Índice de matriz inválido | Erro | NULL |
| mapExpr[chave] | Retorna o valor de mapExpr para chave. | Chave de mapa inválida | Erro | NULL |
| divisor div dividendo | Devolve a parte integrante da divisão do divisor por dividendo. | 1 div 0 |
Erro | NULL |
Funções
O comportamento de algumas funções internas pode ser diferente no modo ANSI vs modo não-ANSI sob as condições especificadas abaixo.
| Operator | Description | Condição | ANSI_MODE = verdadeiro | ANSI_MODE = falso |
|---|---|---|---|---|
| abs(expr) | Devolve o valor absoluto do valor numérico em expr. | abs(-128y) |
Erro | -128y (Transbordamento) |
| element_at(mapExpr, chave) | Retorna o valor de mapExpr para chave. | Chave de mapa inválida | Erro | NULL |
| element_at(arrayExpr, índice) | Retorna o elemento de um arrayExpr no índice. | Índice de matriz inválido | Erro | NULL |
| elt(índice, expr1 [, ...] ) | Retorna a enésima expressão. | Índice inválido | Erro | NULL |
| make_date(y,m,d) | Cria uma data dos campos ano, mês e dia. | Data de resultado inválida | Erro | NULL |
| make_timestamp(y,m,d,h,mi,s[,tz]) | Cria um carimbo de data/hora a partir de campos. | Carimbo de data/hora de resultado inválido | Erro | NULL |
| make_interval(y,m,w,d,h,mi,s) | Cria um intervalo de campos. | Intervalo de resultados inválido | Erro | NULL |
| mod(dividendo, divisor) | Devolve o restante após dividendo/divisor. | mod(1, 0) |
Erro | NULL |
| next_day(expr,dayOfWeek) | Devolve a primeira data posterior a expr e nomeada como em dayOfWeek. | Dia da semana inválido | Erro | NULL |
| parse_url(url, partToExtract[, chave]) | Extrai uma parte do url. | URL Inválido | Erro | NULL |
| pmod(dividendo, divisor) | Devolve o restante positivo após dividendo/divisor. | pmod(1, 0) |
Erro | NULL |
| tamanho (expr) | Devolve a cardinalidade de expr. | size(NULL) |
NULL |
-1 |
| to_date(expr[,fmt]) | Retorna a conversão expr para uma data usando uma formatação opcional. | Cadeia de caracteres expr ou de formato inválida | Erro | NULL |
| to_timestamp(expr[,fmt]) | Retorna a conversão expr para um carimbo de data/hora usando uma formatação opcional. | Cadeia de caracteres expr ou de formato inválida | Erro | NULL |
| to_unix_timestamp(expr[,fmt]) | Retorna o carimbo de data/hora em expr como um carimbo de data/hora UNIX. | Cadeia de caracteres expr ou de formato inválida | Erro | NULL |
| unix_timestamp([expr[, fmt]]) | Retorna o carimbo de data/hora UNIX da hora atual ou especificada. | Cadeia de caracteres expr ou de formato inválida | Erro | NULL |
Regras de fundição
As regras e comportamentos em relação ao CAST são mais rigorosos no modo ANSI. Podem dividir-se nas seguintes três categorias:
- Regras de conversão em tempo de compilação
- Erros de tempo de execução
- Regras de coerção de tipo implícito
Regras de conversão em tempo de compilação
| Source type | Tipo de destino | Exemplo | ANSI_MODE = verdadeiro | ANSI_MODE = falso |
|---|---|---|---|---|
| Boolean | Carimbo de Data/Hora | cast(TRUE AS TIMESTAMP) |
Erro | 1970-01-01 00:00:00.000001 UTC |
| Date | Boolean | cast(DATE'2001-08-09' AS BOOLEAN) |
Erro | NULL |
| Carimbo de Data/Hora | Boolean | cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN) |
Erro | FALSE |
| Integral numérica | Binário | cast(15 AS BINARY) |
Erro | representação binária |
Erros de tempo de execução
| Source type | Tipo de destino | Condição | Exemplo | ANSI_MODE = verdadeiro | ANSI_MODE = falso |
|---|---|---|---|---|---|
| String | Não-string | Entrada inválida | cast('a' AS INTEGER) |
Erro | NULL |
| Matriz, Estrutura, Mapa | Matriz, Estrutura, Mapa | Entrada inválida | cast(ARRAY('1','2','3') AS ARRAY<DATE>) |
Erro | NULL |
| Numérico | Numérico | Capacidade Excedida | cast(12345 AS BYTE) |
Erro | NULL |
| Numérico | Integral numérica | Truncamento | cast(5.1 AS INTEGER) |
Erro | 5 |
Nota
Para cada uma dessas versões, você pode usar try_cast em vez de transmitir para retornar NULL em vez de um erro.
Regras de coerção de tipo implícito
Em ANSI_MODE = TRUE, o Databricks SQL usa regras claras de transmissão de tipo de dados SQL para:
Em contrapartida, ANSI_MODE = FALSE é inconsistente e mais brando. Por exemplo:
- Ao usar um
STRINGtipo com qualquer operador aritmético, a cadeia de caracteres é implicitamente convertida emDOUBLE. - Ao comparar um
STRINGcom qualquer tipo numérico, a cadeia de caracteres é implicitamente convertida para o tipo com o qual se compara. - Ao executar um
UNION,COALESCE, ou outras operações onde um tipo menos comum deve ser encontrado, todos os tipos são convertidos paraSTRINGse houver algumSTRINGtipo presente.
O Databricks recomenda o uso da função explícita cast ou try_cast em vez de confiar no ANSI_MODE = FALSE.
Exemplos
> SET ansi_mode = true;
-- Protects against integral numeric overflow
> SELECT cast(12345 AS TINYINT);
Casting 12345 to tinyint causes overflow
-- For invalid values raises errors instead of returning NULL.
> SELECT cast('a' AS INTEGER);
Invalid input syntax for type numeric: a.
To return NULL instead, use 'try_cast'
-- try_cast() is consistent for both modes
> SELECT try_cast('a' AS INTEGER);
NULL
-- Does not allow ambiguous crosscasting.
> SELECT c1 + c2 FROM VALUES('5', '7.6') AS T(c1, c2);
Cannot resolve '(T.c1 + T.c2)' due to data type mismatch:
'(T.c1 + T.c2)' requires (numeric or interval day to second or interval year to month or interval) type, not string
-- Promotes STRING to least common type (STRING, INTEGER --> BIGINT) for arithmetic operation.
> SELECT typeof(5 - '3');
bigint
-- Promotes STRING to least common type (INTEGER, STRING --> BIGINT) with runtime check
> SELECT c1 = c2 FROM VALUES(10, '10.1') AS T(c1, c2);
Invalid input syntax for type numeric: 10.1. To return NULL instead, use 'try_cast'.
-- Promotes STRING to least common type (STRING, INTEGER --> BIGINT) for set operation with runtime check.
> SELECT typeof(c1) FROM (SELECT 5 UNION ALL SELECT '6') AS T(c1);
bigint
bigint
> SET ansi_mode = false;
-- Silent integral numeric overflow
> SELECT cast(12345 AS TINYINT);
57
-- Returns NULL instead of an error
> SELECT cast('a' AS INTEGER);
NULL
-- try_cast() is safe for both modes
> SELECT try_cast('a' AS INTEGER);
NULL
-- Does allow ambiguous crosscasting using DOUBLE.
> SELECT c1 + c2 FROM VALUES('5', '7.6') AS T(c1, c2);
12.6
-- Crosscasts STRING to DOUBLE for arithmetic operation.
> SELECT typeof(5 - '3');
double
-- Implicitly casts STRING to INTEGER equating 10 with 10.1
> SELECT c1 = c2 FROM VALUES(10, '10.1') AS T(c1, c2);
true
-- Promotes to string for set operations
> SELECT typeof(c1) FROM (SELECT 5 UNION ALL SELECT '6') AS T(c1);
string
string