ANSI_MODE
Aplica-se a:
SQL do Databricks
O parâmetro de configuração ANSI_MODE controla os principais comportamentos de funções internas e operações de conversão.
Este artigo descreve o modo ANSI no Databricks SQL. Para informações sobre conformidade com o ANSI no Databricks Runtime, confira Conformidade com o ANSI no Databricks Runtime.
Configurações
TRUE
Segue o padrão SQL em termos de como lida com determinadas 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.
FALSE
O Databricks SQL usa comportamento compatível com Hive.
Você pode definir esse parâmetro no nível da sessão usando a instrução SET e no nível global usando os parâmetros de configuração do SQL ou a API do SQL Warehouse.
Padrão do sistema
O valor padrão do sistema para contas adicionadas no Databricks SQL 2022.35 e posterior é TRUE.
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 com estouro ou NULL, enquanto no modo ANSI essas operações retornam um erro.
| Operador | Descrição | Exemplo | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| dividend / divisor | Retorna o dividendo dividido pelo divisor. | 1/0 |
Erro | NULL |
| - expr | Retorna o valor negado de expr. | -(-128y) |
Erro | -128y (estouro) |
| expr1 - expr2 | Retorna a subtração de expr2 de expr1. | -128y - 1y |
Erro | 127y (estouro) |
| expr1 + expr2 | Retorna a soma de expr1 e expr2. | 127y + 1y |
Erro | -128y (estouro) |
| dividend % divisor | Retorna o restante após dividendo/divisor. | 1 % 0 |
Erro | NULL |
| multiplier * multiplicand | Retorna o multiplicador multiplicado pelo multiplicando. | 100y * 100y |
Erro | 16y (estouro) |
| arrayExpr[index] | Retorna o elemento de um arrayExpr no índice. | Índice de matriz inválido | Erro | NULL |
| mapExpr[key] | Retorna o valor de mapExpr para a chave. | Chave de mapa inválida | Erro | NULL |
| divisor div dividend | Retorna a parte integral da divisão do divisor pelo dividendo. | 1 div 0 |
Erro | NULL |
Funções
O comportamento de algumas funções internas pode ser diferente no modo ANSI e no modo não ANSI nas condições especificadas abaixo.
| Operador | Descrição | Condição | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| abs(expr) | Retorna o valor absoluto do valor numérico em expr. | abs(-128y) |
Erro | -128y (estouro) |
| element_at(mapExpr, key) | Retorna o valor de mapExpr para a chave. | Chave de mapa inválida | Erro | NULL |
| element_at(arrayExpr, index) | Retorna o elemento de um arrayExpr no índice. | Índice de matriz inválido | Erro | NULL |
| elt(index, expr1 [, …] ) | Retorna a enésima expressão. | Índice inválido | Erro | NULL |
| make_date(y,m,d) | Cria uma data usando os campos de dia, mês e ano. | Data resultante inválida | Erro | NULL |
| make_timestamp(y,m,d,h,mi,s[,tz]) | Cria um carimbo de data/hora com base nos campos. | Carimbo de data/hora resultante inválido | Erro | NULL |
| make_interval(y,m,w,d,h,mi,s) | Cria um intervalo com base nos campos. | Intervalo de resultados inválido | Erro | NULL |
| mod(dividend, divisor) | Retorna o restante após dividendo/divisor. | mod(1, 0) |
Erro | NULL |
| next_day(expr,dayOfWeek) | Retorna a primeira data que é posterior a expr e nomeada como em dayOfWeek. | Dia da semana inválido | Erro | NULL |
| parse_url(url, partToExtract[, key]) | Extrai uma parte de url. | URL inválida | Erro | NULL |
| pmod(dividend, divisor) | Retorna o restante positivo após dividendo/divisor. | pmod(1, 0) |
Erro | NULL |
| size(expr) | Retorna a cardinalidade de expr. | size(NULL) |
NULL |
-1 |
| to_date(expr[,fmt]) | Retorna a conversão de expr para uma data usando uma formatação opcional. | Cadeia de caracteres de formato ou expr inválida | Erro | NULL |
| to_timestamp(expr[,fmt]) | Retorna a conversão de expr para um carimbo de data/hora usando uma formatação opcional. | Cadeia de caracteres de formato ou expr inválida | Erro | NULL |
| to_unix_timestamp(expr[,fmt]) | Retorna o carimbo de data/hora em expr como um carimbo UNIX. | Cadeia de caracteres de formato ou expr inválida | Erro | NULL |
| unix_timestamp([expr[, fmt]]) | Retorna o carimbo de data/hora UNIX da hora atual ou especificada. | Cadeia de caracteres de formato ou expr inválida | Erro | NULL |
Regras de conversão
As regras e comportamentos relativos a CAST são mais rigorosos no modo ANSI. Elas podem ser divididas nas três seguintes categorias:
Regras de conversão em tempo de compilação
| Tipo de origem | Tipo de destino | Exemplo | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| Boolean | Timestamp | cast(TRUE AS TIMESTAMP) |
Erro | 1970-01-01 00:00:00.000001 UTC |
| Data | Boolean | cast(DATE'2001-08-09' AS BOOLEAN) |
Erro | NULL |
| Timestamp | Boolean | cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN) |
Erro | FALSE |
| Numérico integral | Binário | cast(15 AS BINARY) |
Erro | representação binária |
Erros em runtime
| Tipo de origem | Tipo de destino | Condição | Exemplo | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|---|
| String | Não cadeia de caracteres | Entrada inválida | cast('a' AS INTEGER) |
Erro | NULL |
| Matriz, Struct, Mapa | Matriz, Struct, Mapa | Entrada inválida | cast(ARRAY('1','2','3') AS ARRAY<DATE>) |
Erro | NULL |
| Numérica | Numérica | Estouro | cast(12345 AS BYTE) |
Erro | NULL |
| Numérico | Numérico integral | Truncation | cast(5.1 AS INTEGER) |
Erro | 5 |
Observação
Para cada uma dessas conversões, você pode usar try_cast em vez de cast para retornar NULL em vez de um erro.
Regras implícitas de coerção de tipo
Em ANSI_MODE = TRUE, o Databricks SQL usa regras claras de conversão de tipo de dados SQL para:
Por outro lado, ANSI_MODE = FALSE é inconsistente e mais brando. Por exemplo:
- Ao usar um tipo
STRINGcom 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 no tipo ao qual se compara. - Ao executar um
UNION,COALESCEou outras operações em que um tipo menos comum deve ser encontrado, todos os tipos serão convertidos emSTRINGse houver algum tipoSTRINGpresente.
O Databricks recomenda usar a função try_cast ou cast explícita em vez de depender de 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