Règles de type de données SQL

S’applique à : Databricks SQL Databricks Runtime

Azure Databricks utilise plusieurs règles pour résoudre les conflits entre les types de données :

• Promotion développe en toute sécurité un type vers un type plus étendu.
• Downcasting implicite réduit un type. Le contraire de la promotion.
• Crosscasting implicite transforme un type en un type d’une autre famille.

Vous pouvez aussi effectuer un cast explicite entre de nombreux types :

• La fonction cast force la plupart des types et retourne des erreurs si elle ne peut pas le faire.
• La fonction try_cast fonctionne comme la fonction cast, mais retourne NULL quand des valeurs non valides sont passées.
• D’autres fonctions intégrées effectuent un cast entre des types en utilisant les directives de format fournies.

Promotion de type

La promotion de type est le processus qui consiste à caster un type en un autre type de la même famille de types qui contient toutes les valeurs possibles du type d’origine. Par conséquent, la promotion de type est une opération sécurisée. Par exemple, TINYINT a une plage comprise entre -128 et 127. Toutes ses valeurs possibles peuvent être promues sans risque en INTEGER.

Liste de priorité des types

La liste de priorité des types définit si les valeurs d’un type de données donné peuvent être implicitement promues à un autre type de données.

Type de données	Liste de priorité (du plus étroit au plus large)
TINYINT	TINYINT -> SMALLINT -> INT -> BIGINT -> DECIMAL -> FLOAT (1) -> DOUBLE
SMALLINT	SMALLINT -> INT -> BIGINT -> DECIMAL -> FLOAT (1) -> DOUBLE
INT	INT -> BIGINT -> DECIMAL -> FLOAT (1) -> DOUBLE
BIGINT	BIGINT -> DECIMAL -> FLOAT (1) -> DOUBLE
DECIMAL	DECIMAL -> FLOAT (1) -> DOUBLE
FLOAT	FLOAT (1) -> DOUBLE
DOUBLE	DOUBLE
DATE	DATE -> TIMESTAMP
TIMESTAMP	timestamp
ARRAY	ARRAY (2)
BINARY	BINARY
BOOLEAN	BOOLEAN
INTERVAL	INTERVAL
MAP	MAP (2)
STRING	STRING
STRUCT	STRUCT (2)
VARIANT	VARIANT
OBJECT	OBJET (3)

(1) Pour la résolution du type le moins commun, FLOAT est ignoré pour éviter la perte de précision.

(2) Pour un type complexe, la règle de précédence s’applique de manière récursive à ses éléments constitutifs.

(3)OBJECT existe uniquement dans un VARIANT.

Chaînes et NULL

Des règles spéciales s’appliquent pour STRING et NULL non typé :

• NULL peut être promu en tout autre type.
• STRING peut être promu en BIGINT, BINARY, BOOLEAN, DATE, DOUBLE, INTERVAL et TIMESTAMP. Si la valeur réelle de la chaîne ne peut pas être castée en type le moins commun, Azure Databricks génère une erreur d’exécution. Lors de la promotion en INTERVAL, la valeur de la chaîne doit correspondre aux unités d’intervalle.

Graphique de priorité des types

Il s’agit d’une représentation graphique de la hiérarchie des priorités, qui combine la liste de priorité des types et les règles relatives aux chaînes et aux valeurs NULL.

Résolution du type le moins courant

Le type le moins courant d’un ensemble de types est le type le plus étroit accessible à partir du graphique de priorité des types par tous les éléments de l’ensemble de types.

La résolution du type le moins courant est utilisée pour :

• Décider si une fonction qui attend un paramètre d’un type donné peut être appelée à l’aide d’un argument d’un type plus étroit.
• Déterminer le type d’argument d’une fonction qui attend un type d’argument partagé pour plusieurs paramètres, tels que coalesce, in, least ou greatest.
• Déduire les types d’opérandes pour des opérateurs tels que des opérations arithmétiques ou des comparaisons.
• Déterminer le type de résultat pour des expressions telles que l’expression case.
• Déterminer les types élément, clé ou valeur pour les constructeurs array et map.
• Déterminer le type de résultat des opérateurs d’ensemble UNION, INTERSECT ou EXCEPT.

Des règles spéciales sont appliquées si le type le moins courant se résout en FLOAT. Si l’un des types contribuant est un type numérique exact (TINYINT, SMALLINT, INTEGER, BIGINT ou DECIMAL), le type le moins courant est poussé vers DOUBLE pour éviter la perte potentielle de chiffres.

Lorsque le type le moins courant est un STRING le classement est calculé en suivant les règles de précédence de classement .

Downcasting et crosscasting implicites

Azure Databricks emploie ces formes de cast de type implicite uniquement lors de l’appel de fonctions et d’opérateurs, et uniquement lorsqu’il peut déterminer l’intention sans ambiguïté.

• Downcasting implicite

  Le downcasting implicite caste automatiquement un type plus large en un type plus étroit sans que vous ayez à spécifier explicitement le cast. Le downcasting est pratique, mais il comporte le risque d’erreurs d’exécution inattendues si la valeur réelle n’est pas représentable dans le type étroit.

  Le downcasting applique la liste de priorité des types dans l’ordre inverse.

• Crosscasting implicite

  Le crosscasting implicite caste une valeur d’une famille de types à une autre sans que vous ayez besoin de spécifier explicitement le cast.

  Azure Databricks prend en charge le crosscasting implicite à partir de :

  • Tout type simple, à l’exception de BINARY, vers STRING.
  • Un type STRING vers n’importe quel type simple.

Cast lors l’appel d’une fonction

Étant donné une fonction ou un opérateur résolu, les règles suivantes s’appliquent, dans l’ordre dans lequel elles sont répertoriées, pour chaque paire de paramètres et d’arguments :

• Si un type de paramètre pris en charge fait partie du graphique de priorité des types de l’argument, Azure Databricks promeut l’argument vers ce type de paramètre.

  Dans la plupart des cas, la description de la fonction indique explicitement les types pris en charge ou la chaîne, comme « tout type numérique ».

  Par exemple, sin(expr) opère sur DOUBLE, mais acceptera tout type numérique.

• Si le type de paramètre attendu est un STRING et que l’argument est un type simple, Azure Databricks effectue un crosscasting de l’argument vers le type de paramètre string.

  Par exemple, substr(str, start, len) s’attend à ce que str soit un type STRING. Au lieu de cela, vous pouvez transmettre un type numérique ou DateHeure.

• Si le type d’argument est un STRING et que le type de paramètre attendu est un type simple, Azure Databricks effectue un crosscasting de l’argument string vers le type de paramètre le plus largement pris en charge.

  Par exemple, date_add(date, days) attend un DATE et un INTEGER.

  Si vous appelez date_add() avec deux STRING, Azure Databricks effectue un crosscasting du premier STRING vers DATE et du deuxième STRING vers un INTEGER.

• Si la fonction attend un type numérique, tel que INTEGER, ou un type DATE, mais que l’argument est un type plus général, tel que DOUBLE ou TIMESTAMP, Azure Databricks effectue un downcasting implicite de l’argument vers ce type de paramètre.

  Par exemple, un date_add(date, days) attend un DATE et un INTEGER.

  Si vous appelez date_add() avec un TIMESTAMP et un BIGINT, Azure Databricks effectue un downcasting de TIMESTAMP vers DATE en supprimant la composante temporelle et BIGINT vers INTEGER.

• Sinon, Azure Databricks génère une erreur.

Exemples

La fonction fusionner accepte tout ensemble de types d’arguments tant qu’ils partagent le type le moins commun.

Le type du résultat est le type le moins courant des arguments.

-- The least common type of TINYINT and BIGINT is BIGINT
> SELECT typeof(coalesce(1Y, 1L, NULL));
  BIGINT

-- INTEGER and DATE do not share a precedence chain or support crosscasting in either direction.
> SELECT typeof(coalesce(1, DATE'2020-01-01'));
  Error: DATATYPE_MISMATCH.DATA_DIFF_TYPES

-- Both are ARRAYs and the elements have a least common type
> SELECT typeof(coalesce(ARRAY(1Y), ARRAY(1L)))
  ARRAY<BIGINT>

-- The least common type of INT and FLOAT is DOUBLE
> SELECT typeof(coalesce(1, 1F))
  DOUBLE

> SELECT typeof(coalesce(1L, 1F))
  DOUBLE

> SELECT typeof(coalesce(1BD, 1F))
  DOUBLE

-- The least common type between an INT and STRING is BIGINT
> SELECT typeof(coalesce(5, '6'));
  BIGINT

-- The least common type is a BIGINT, but the value is not BIGINT.
> SELECT coalesce('6.1', 5);
  Error: CAST_INVALID_INPUT

-- The least common type between a DECIMAL and a STRING is a DOUBLE
>  SELECT typeof(coalesce(1BD, '6'));
  DOUBLE

-- Two distinct explicit collations result in an error
>  SELECT collation(coalesce('hello' COLLATE UTF8_BINARY,
                             'world' COLLATE UNICODE));
  Error: COLLATION_MISMATCH.EXPLICIT

-- The resulting collation between two distinct implicit collations is indeterminate
>  SELECT collation(coalesce(c1, c2))
     FROM VALUES('hello' COLLATE UTF8_BINARY,
                 'world' COLLATE UNICODE) AS T(c1, c2);
  NULL

-- The resulting collation between a explicit and an implicit collations is the explicit collation.
> SELECT collation(coalesce(c1 COLLATE UTF8_BINARY, c2))
    FROM VALUES('hello',
                'world' COLLATE UNICODE) AS T(c1, c2);
  UTF8_BINARY

-- The resulting collation between an implicit and the default collation is the implicit collation.
> SELECT collation(coalesce(c1, ‘world’))
    FROM VALUES('hello' COLLATE UNICODE) AS T(c1, c2);
  UNICODE

-- The resulting collation between the default collation and the indeterminate collation is the default collation.
> SELECT collation(coalesce(coalesce(‘hello’ COLLATE UTF8_BINARY, ‘world’ COLLATE UNICODE), ‘world’));
  UTF8_BINARY

La fonction substring attend des arguments de type STRING pour la chaîne et INTEGER pour les paramètres start et length.

-- Promotion of TINYINT to INTEGER
> SELECT substring('hello', 1Y, 2);
 he

-- No casting
> SELECT substring('hello', 1, 2);
 he

-- Casting of a literal string
> SELECT substring('hello', '1', 2);
 he

-- Downcasting of a BIGINT to an INT
> SELECT substring('hello', 1L, 2);
 he

-- Crosscasting from STRING to INTEGER
> SELECT substring('hello', str, 2)
  FROM VALUES(CAST('1' AS STRING)) AS T(str);
 he

-- Crosscasting from INTEGER to STRING
> SELECT substring(12345, 2, 2);
 23

|| (CONCAT) permet le crosscasting implicite vers la chaîne.

-- A numeric is cast to STRING
> SELECT 'This is a numeric: ' || 5.4E10;
 This is a numeric: 5.4E10

-- A date is cast to STRING
> SELECT 'This is a date: ' || DATE'2021-11-30';
 This is a date: 2021-11-30

date_add peut être appelée avec un TIMESTAMP ou BIGINT en raison du downcasting implicite.

> SELECT date_add(TIMESTAMP'2011-11-30 08:30:00', 5L);
 2011-12-05

date_add peut être appelée avec des STRING en raison du crosscasting implicite.

> SELECT date_add('2011-11-30 08:30:00', '5');
  2011-12-05

Rubriques connexes

• ANSI_MODE
• cast
• Types de données
• Fonctions
• try_cast