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.

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.

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

Erros de tempo de execução

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:

• tipo de promoção
• Downcasting
• fundição cruzada

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

Relacionada

• Regras de tipo de dados SQL
• RESET
• Declaração SET
• Configurar parâmetros SQL
• SQL Warehouse API