Partager via

Règles relatives aux types de données SQL

S’applique à :case cochée oui Databricks SQL case cochée oui Databricks Runtime

Azure Databricks 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 :

Promotion de type

La promotion de type est le processus de conversion d’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 en toute sécurité vers INTEGER.

Liste de priorité des types

La liste de précédence de type définit si les valeurs d’un type de données donné peuvent être implicitement promues vers un autre type de données.

Type de données Liste de précédences (de la plus étroite au plus large)
TINYINT TINYINT -> SMALLINT -> INT -> BIGINT -> DÉCIMAL -> FLOAT (1) -> DOUBLE
SMALLINT SMALLINT -> INT -> BIGINT -> DÉCIMAL -> FLOAT (1) -> DOUBLE
INT INT -> BIGINT -> DÉCIMAL -> FLOAT (1) -> DOUBLE
BIGINT BIGINT -> DÉCIMAL -> FLOAT (1) -> DOUBLE
DECIMAL DÉCIMAL -> FLOAT (1) -> DOUBLE
FLOAT FLOAT (1) -> DOUBLE
DOUBLE DOUBLE
DATE DATE -> HORODATAGE
TIMESTAMP TIMESTAMP
ARRAY ARRAY (2)
BINARY BINARY
BOOLEAN BOOLEAN
INTERVAL INTERVAL
GÉOGRAPHIE GÉOGRAPHIE (TOUT)
GÉOMÉTRIE GÉOMÉTRIE(ANY)
MAP MAP (2)
STRING STRING
STRUCT STRUCT (2)
VARIANT VARIANT
OBJECT OBJECT (3)

(1) Pour une résolution de FLOAT, il est ignoré pour éviter une 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 de composant.

(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 de précédence, combinant la liste de précédence de type et les règles pour les chaînes et les NULLs.

Représentation graphique des règles de priorité

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 précédence de type 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érivez le type d’argument d’une fonction qui attend un type d’argument partagé pour plusieurs paramètres, tels que coalesce, in, le plus petit ou le plus grand.
  • Déduire les types d’opérandes pour des opérateurs tels que des opérations arithmétiques ou des comparaisons.
  • Dérivez le type de résultat pour les expressions telles que l’expression de cas.
  • Dérivez les types d’élément, de clé ou de valeur pour les constructeurs de tableau et de mappage .
  • Dérivez 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é.

  • Conversion implicite vers le bas

    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. Les types de données GEOGRAPHY et GEOMETRY ne font jamais l’objet d’un downcasting.

  • Transposition croisée 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, sauf BINARY, GEOGRAPHYet GEOMETRY, à STRING.
    • Une valeur STRING peut être convertie vers n’importe quel type simple, sauf GEOGRAPHY et GEOMETRY.

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 précédence de type de l’argument, Azure Databricks promeut l’argument en 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) fonctionne sur DOUBLE, mais accepte n’importe quelle valeur numérique.

  • Si le type de paramètre attendu est un STRING et que l’argument est un type simple, Azure Databricks convertit l’argument en type de paramètre chaîne.

    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 de l’argument est un STRING et que le type de paramètre attendu est un type simple, Azure Databricks convertit l’argument de chaîne en le type de paramètre pris en charge le plus large.

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

    Si vous appelez date_add() avec deux STRINGs, Azure Databricks convertit la première STRING en DATE et la seconde STRING en un INTEGER.

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

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

    Si vous invoquez date_add() avec un TIMESTAMP et un BIGINT, Azure Databricks effectue un downcast de TIMESTAMP en DATE en supprimant le composant temporel et le BIGINT en un INTEGER.

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

Examples

La coalesce fonction accepte tout ensemble de types d’arguments tant qu’ils partagent un type 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

-- Least common type between GEOGRAPHY(srid) and GEOGRAPHY(ANY)
> SELECT typeof(coalesce(st_geogfromtext('POINT(1 2)'), to_geography('POINT(3 4)'), NULL));
  geography(any)

-- Least common type between GEOMETRY(srid1) and GEOMETRY(srid2)
> SELECT typeof(coalesce(st_geomfromtext('POINT(1 2)', 4326), st_geomfromtext('POINT(3 4)', 3857), NULL));
  geometry(any)

-- Least common type between GEOMETRY(srid1) and GEOMETRY(ANY)
> SELECT typeof(coalesce(st_geomfromtext('POINT(1 2)', 4326), to_geometry('POINT(3 4)'), NULL));
  geometry(any)

La substring fonction attend des arguments de type STRING pour la chaîne et INTEGER pour les paramètres de début et de longueur.

-- 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 la conversion implicite en chaîne de caractères.

-- 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 invoqué avec un TIMESTAMP ou un BIGINT en raison d'une transformation implicite.

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

date_add peut être appelée avec STRING en raison du convertissement croisé implicite.

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