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:
Por el contrario, ANSI_MODE = FALSE es incoherente y más permisivo. Por ejemplo:
- Cuando se usa un tipo
STRINGcon cualquier operador aritmético, la cadena se convierte implícitamente enDOUBLE. - Al comparar un tipo
STRINGcon cualquier tipo numérico, la cadena se convierte implícitamente al tipo con el que se compara. - Al realizar una operación
UNION,COALESCEu otras operaciones en las que se debe encontrar un tipo menos común, todos los tipos se convierten enSTRINGsi hay algún tipoSTRINGpresente.
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