ANSI_MODE

Se aplica a: Databricks SQL

El parámetro de configuración ANSI_MODE controla los comportamientos principales de las funciones integradas y las operaciones de conversión.

En este artículo, se describe el modo ANSI en Databricks SQL. Para conocer el cumplimiento de ANSI en Databricks Runtime, consulte Cumplimiento de ANSI en Databricks Runtime.

Configuración

• TRUE

  Sigue el estándar de SQL en cuanto a cómo se ocupa de determinadas operaciones aritméticas y conversiones de tipos, de forma similar a la mayoría de las bases de datos y los almacenamientos de datos. Al seguir este estándar, se promueve una mejor calidad, integridad y portabilidad de los datos.

• FALSE

  Databricks SQL usa un comportamiento compatible con Hive.

Puede establecer este parámetro en el nivel de sesión mediante la instrucción SET y en el nivel global mediante los parámetros de configuración de SQL o la API de almacén de SQL.

Valor predeterminado del sistema

El valor predeterminado del sistema es TRUE para las cuentas agregadas en Databricks SQL 2022.35 y versiones posteriores.

Descripción detallada

La documentación de referencia de Databricks SQL describe el comportamiento estándar de SQL.

En las secciones siguientes, se describen las diferencias entre ANSI_MODE TRUE (modo ANSI) y FALSE (modo no ANSI).

Operadores

En el modo no ANSI, las operaciones aritméticas realizadas en tipos numéricos pueden devolver valores con desbordamiento o valores NULL, mientras que en el modo ANSI, estas operaciones devuelven un error.

Operador	Descripción	Ejemplo	ANSI_MODE = true	ANSI_MODE = false
dividend / divisor	Devuelve el dividendo dividido por el divisor.	1/0	Error	NULL
- expr	Devuelve el valor negado de expr.	-(-128y)	Error	-128y (Desbordamiento)
expr1 - expr2	Devuelve la resta de expr2 menos expr1.	-128y - 1y	Error	127y (Desbordamiento)
expr1 + expr2	Devuelve la suma de expr1 y expr2.	127y + 1y	Error	-128y (Desbordamiento)
dividend % divisor	Devuelve el resto del dividendo entre el divisor.	1 % 0	Error	NULL
multiplier * multiplicand	Devuelve el multiplicador multiplicado por el multiplicando.	100y * 100y	Error	16y (Desbordamiento)
arrayExpr[index]	Devuelve el elemento index de arrayExpr.	Índice de matriz no válido	Error	NULL
mapExpr[key]	Devuelve el valor de mapExpr para la clave key.	Clave de asignación no válida	Error	NULL
divisor div dividend	Devuelve la parte entera de la división del divisor por el dividendo.	1 div 0	Error	NULL

Functions

El comportamiento de algunas funciones integradas puede ser diferente en modo ANSI frente al modo no ANSI en las condiciones especificadas a continuación.

Operador	Descripción	Condición	ANSI_MODE = true	ANSI_MODE = false
abs(expr)	Devuelve el valor absoluto del valor numérico de expr.	abs(-128y)	Error	-128y (Desbordamiento)
element_at(mapExpr, key)	Devuelve el valor de mapExpr para la clave key.	Clave de asignación no válida	Error	NULL
element_at(arrayExpr, index)	Devuelve el elemento index de arrayExpr.	Índice de matriz no válido	Error	NULL
elt(index, expr1 [, …] )	Devuelve la enésima expresión.	Índice no válido	Error	NULL
make_date(y,m,d)	Crea una fecha a partir de los campos de año, mes y día.	Fecha de resultado no válida	Error	NULL
make_timestamp(y,m,d,h,mi,s[,tz])	Crea una marca de tiempo a partir de los campos.	Marca de tiempo de resultado no válida	Error	NULL
make_interval(y,m,w,d,h,mi,s)	Crea un intervalo a partir de los campos.	Intervalo de resultado no válido	Error	NULL
mod(dividend, divisor)	Devuelve el resto del dividendo entre el divisor.	mod(1, 0)	Error	NULL
next_day(expr,dayOfWeek)	Devuelve la primera fecha que es posterior a expr y se llama como dayOfWeek.	Día de la semana no válido	Error	NULL
parse_url(url, partToExtract[, key])	Extrae una parte de una dirección URL.	Dirección URL no válida	Error	NULL
pmod(dividend, divisor)	Devuelve el resto positivo del dividendo entre el divisor.	pmod(1, 0)	Error	NULL
size(expr)	Devuelve la cardinalidad de expr.	size(NULL)	NULL	-1
to_date(expr[,fmt])	Devuelve la conversión de expr a una fecha con un formato opcional.	Cadena de formato o expresión no válida	Error	NULL
to_timestamp(expr[,fmt])	Devuelve la conversión de expr a una marca de tiempo con un formato opcional.	Cadena de formato o expresión no válida	Error	NULL
to_unix_timestamp(expr[,fmt])	Devuelve la marca de tiempo expr como una marca de tiempo de UNIX.	Cadena de formato o expresión no válida	Error	NULL
unix_timestamp([expr[, fmt]])	Devuelve la marca de tiempo UNIX del tiempo actual o especificado.	Cadena de formato o expresión no válida	Error	NULL

Reglas de conversión

Las reglas y comportamientos relacionados con CAST son más estrictos en el modo ANSI. Se pueden dividir en las tres categorías siguientes:

• Reglas de conversión en tiempo de compilación
• Errores en tiempo de ejecución
• Reglas de conversión de tipo implícito

Reglas de conversión en tiempo de compilación

Tipo de origen	Tipo de destino	Ejemplo	ANSI_MODE = true	ANSI_MODE = false
Boolean	Timestamp	cast(TRUE AS TIMESTAMP)	Error	1970-01-01 00:00:00.000001 UTC
Date	Boolean	cast(DATE'2001-08-09' AS BOOLEAN)	Error	NULL
Timestamp	Boolean	cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN)	Error	FALSE
Numérico integral	Binary	cast(15 AS BINARY)	Error	Representación binaria

Errores de tiempo de ejecución

Tipo de origen	Tipo de destino	Condición	Ejemplo	ANSI_MODE = true	ANSI_MODE = false
String	No de tipo cadena	Entrada no válida	cast('a' AS INTEGER)	Error	NULL
Matriz, estructura, mapa	Matriz, estructura, mapa	Entrada no válida	cast(ARRAY('1','2','3') AS ARRAY<DATE>)	Error	NULL
Numérica	Numérica	Desbordamiento	cast(12345 AS BYTE)	Error	NULL
Numeric	Numérico integral	Truncamiento	cast(5.1 AS INTEGER)	Error	5

Nota:

Para cada una de estas conversiones, puede usar try_cast en lugar de cast para devolver NULL en lugar de un error.

Reglas de conversión de tipo implícito

En ANSI_MODE = TRUE, Databricks SQL usa reglas de conversión de tipos de datos de SQL claras para:

• Promoción de tipos
• convertir a tipo heredado
• Conversión cruzada

Por el contrario, ANSI_MODE = FALSE es incoherente y más permisivo. Por ejemplo:

• Cuando se usa un tipo STRING con cualquier operador aritmético, la cadena se convierte implícitamente en DOUBLE.
• Al comparar un tipo STRING con cualquier tipo numérico, la cadena se convierte implícitamente al tipo con el que se compara.
• Al realizar una operación UNION, COALESCE u otras operaciones en las que se debe encontrar un tipo menos común, todos los tipos se convierten en STRING si hay algún tipo STRING presente.

Databricks recomienda usar la función explícita cast o try_cast en lugar de confiar en ANSI_MODE = FALSE.

Ejemplos

> 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

Temas relacionados

• Reglas de tipo de datos de SQL
• RESET
• Instrucción SET
• Configuración de parámetros de SQL
• API de almacén de SQL