Règles relatives aux types de données SQL (Databricks SQL)

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

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

  • La fonction cast caste 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 lui 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)

(1) Pour la résolution du type le moins courant, 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.

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 courant, Databricks SQL 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.

Graphical representation of precedence rules

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éterminer les types d’opérandes pour les opérateurs tels que les opérations arithmétiques ou les 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.

Downcasting et crosscasting implicites

Databricks SQL emploie ces formes de forçage 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.

    Databricks SQL 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, Databricks SQL 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, Databricks SQL 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, Databricks SQL 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, Databricks SQL effectue un crosscasting du premier STRING vers DATE et du second STRING vers un INTEGER.

  • Si la fonction attend un type numérique, tel qu’un INTEGER, ou un type DATE, mais que l’argument est un type plus général, tel qu’un DOUBLE ou TIMESTAMP, Databricks SQL 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, Databricks SQL effectue un downcasting du TIMESTAMP vers DATE en supprimant la composante temporelle et du BIGINT vers un INTEGER.

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

Exemples

La fonction coalesce (Databricks SQL) accepte tout ensemble de types d’arguments tant qu’ils partagent le type le moins courant.

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: Incompatible types [INT, DATE]

-- 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: 6.1 is not a BIGINT

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

La fonction substring (Databricks SQL) 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