Partilhar via

ANSI_MODE

  • Artigo

Aplica-se a: Marque Sim 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

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 STRING tipo com qualquer operador aritmético, a cadeia de caracteres é implicitamente convertida em DOUBLE.
  • Ao comparar um STRING com 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 para STRING se houver algum STRING tipo 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