ANSI_MODE

S’applique à : Databricks SQL

Le paramètre de configuration ANSI_MODE contrôle les comportements clés des fonctions intégrées et des opérations de cast.

Cet article décrit le mode ANSI dans Databricks SQL. Pour la conformité ANSI dans Databricks Runtime, consultez Conformité ANSI dans Databricks Runtime.

Paramètres

• TRUE

  Suit la norme SQL dans la façon dont elle traite certaines opérations arithmétiques et conversions de type, comme pour la plupart des bases de données et des entrepôts de données. Le respect de cette norme favorise la qualité, l’intégrité et la portabilité des données.

• FALSE

  Databricks SQL utilise un comportement compatible Hive.

Vous pouvez définir ce paramètre au niveau de la session à l’aide de l’instruction SET et au niveau global à l’aide des paramètres de configuration SQL ou de l’API d’entrepôt SQL.

Valeur système par défaut

La valeur système par défaut est TRUE pour les comptes ajoutés dans Databricks SQL 2022.35 et versions ultérieures.

Description détaillée

La documentation de référence de Databricks SQL décrit le comportement SQL standard.

Les sections suivantes décrivent les différences entre ANSI_MODE TRUE (mode ANSI) et FALSE (mode non ANSI).

Opérateurs

En mode non ANSI, les opérations arithmétiques effectuées sur des types numériques peuvent retourner des valeurs débordées ou NULL, tandis qu’en mode ANSI, ces opérations retournent une erreur.

Opérateur	Description	Exemple	ANSI_MODE = true	ANSI_MODE = false
dividend / divisor	Retourne le dividende divisé par le diviseur.	1/0	Error	NULL
- expr	Retourne la valeur négative de expr.	-(-128y)	Error	-128y (dépassement)
expr1 - expr2	Retourne la soustraction entre expr1 et expr2.	-128y - 1y	Error	127y (dépassement)
expr1 + expr2	Retourne la somme d’expr1 et d’expr2.	127y + 1y	Error	-128y (dépassement)
dividend % divisor	Retourne le reste après le dividende/diviseur.	1 % 0	Error	NULL
multiplier * multiplicand	Renvoie un multiplicateur multiplié par le multiplicande.	100y * 100y	Error	16y (dépassement)
arrayExpr[index]	Retourne l’élément d’un arrayExpr à l’index.	Index de tableau non valide	Error	NULL
mapExpr[key]	Retourne la valeur de mapExpr pour la clé.	Clé de carte non valide	Error	NULL
divisor div dividend	Renvoie la partie intégrale de la division du diviseur par le dividende.	1 div 0	Error	NULL

Fonctions

Le comportement de certaines fonctions intégrées peut être différent en mode ANSI et en mode non ANSI dans les conditions spécifiées ci-dessous.

Opérateur	Description	Condition	ANSI_MODE = true	ANSI_MODE = false
abs(expr)	Retourne la valeur absolue de la valeur numérique dans expr.	abs(-128y)	Error	-128y (dépassement)
element_at(mapExpr, key)	Retourne la valeur de mapExpr pour la clé.	Clé de carte non valide	Error	NULL
element_at(arrayExpr, index)	Retourne l’élément d’un arrayExpr à l’index.	Index de tableau non valide	Error	NULL
elt(index, expr1 [, …] )	Retourne la nième expression.	Index non valide	Error	NULL
make_date(y,m,d)	Crée une date à partir des champs année, mois et jour.	Date de résultat non valide	Error	NULL
make_timestamp(y,m,d,h,mi,s[,tz])	Crée un timestamp à partir des champs.	Timestamp de résultat non valide	Error	NULL
make_interval(y,m,w,d,h,mi,s)	Crée un intervalle à partir des champs.	Intervalle de résultat non valide	Error	NULL
mod(dividend, divisor)	Retourne le reste après le dividende/diviseur.	mod(1, 0)	Error	NULL
next_day(expr,dayOfWeek)	Retourne la première date postérieure à expr et nommée comme dans dayOfWeek.	Jour de la semaine non valide	Error	NULL
parse_url(url, partToExtract[, key])	Extrait une partie de url.	URL non valide	Error	NULL
pmod(dividend, divisor)	Retourne le reste positif après le dividende/diviseur.	pmod(1, 0)	Error	NULL
size(expr)	Retourne la cardinalité de expr.	size(NULL)	NULL	-1
to_date(expr[,fmt])	Retourne un cast de expr en date utilisant une mise en forme facultative.	Chaîne de format ou d’expr non valide	Error	NULL
to_timestamp(expr[,fmt])	Retourne un cast de expr en timestamp utilisant une mise en forme facultative.	Chaîne de format ou d’expr non valide	Error	NULL
to_unix_timestamp(expr[,fmt])	Retourne le timestamp dans expr en tant que timestamp UNIX.	Chaîne de format ou d’expr non valide	Error	NULL
unix_timestamp([expr[, fmt]])	Retourne le timestamp UNIX de l’heure actuelle ou spécifiée.	Chaîne de format ou d’expr non valide	Error	NULL

Règles de transtypage

Les règles et les comportements concernant CAST sont plus stricts en mode ANSI. Nous pouvons les classer dans les trois catégories suivantes :

• Règles de conversion au moment de la compilation
• Erreurs d’exécution
• Règles de contrainte de type implicite

Règles de conversion au moment de la compilation

Type de source	Type cible	Exemple	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érique intégral	Binary	cast(15 AS BINARY)	Error	représentation binaire

Erreurs d’exécution

Type de source	Type cible	Condition	Exemple	ANSI_MODE = true	ANSI_MODE = false
String	Non-chaîne	entrée non valide	cast('a' AS INTEGER)	Error	NULL
Tableau, Struct, Map	Tableau, Struct, Map	entrée non valide	cast(ARRAY('1','2','3') AS ARRAY<DATE>)	Error	NULL
Numérique	Numérique	Dépassement	cast(12345 AS BYTE)	Error	NULL
Numérique	Numérique intégral	Troncation	cast(5.1 AS INTEGER)	Error	5

Notes

Pour chacun de ces casts, vous pouvez utiliser try_cast au lieu d’effectuer un cast pour retourner NULL plutôt qu’une erreur.

Règles de contrainte de type implicite

Sous ANSI_MODE = TRUE, Databricks SQL utilise des règles claires de cast de type de données SQL pour :

• Promotion de type
• cast descendant
• crosscasting

En revanche ANSI_MODE = FALSE est incohérent et plus lénient. Par exemple :

• Lorsque vous utilisez un type STRING avec n’importe quel opérateur arithmétique, la chaîne est implicitement convertie en DOUBLE.
• Lors de la comparaison d’un STRING à un type numérique quelconque, la chaîne est implicitement convertie au type auquel elle est comparée.
• Lors de l’exécution de UNION, COALESCE, ou d’autres opérations où le type le moins commun doit être trouvé, tous les types sont convertis en STRING si un type STRING est présent.

Databricks recommande d’utiliser la fonction cast ou try_cast explicite au lieu de se fier à ANSI_MODE = FALSE.

Exemples

> 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

Rubriques connexes

• Règles de type de données SQL
• RESET
• SET, instruction
• Configurer les paramètres SQL
• API d’entrepôt SQL