Поделиться через

Правила для типов данных SQL

  • Статья

Область применения: флажок Databricks SQL флажок Databricks Runtime

Azure Databricks использует несколько правил для устранения конфликтов между типами данных:

Также можно выполнить явное приведение между различными типами:

  • Функция cast выполняет приведение между большинством типов и возвращает ошибки, если это невозможно.
  • Функция try_cast работает аналогично функции cast, но возвращает значение NULL при передаче недопустимых значений.
  • Другие встроенные функции выполняют приведение между типами с помощью указанных директив форматирования.

Повышение уровня типа

Повышение типа — это процесс приведения типа к другому типу того же семейства, который содержит все возможные значения исходного типа. Таким образом, повышение типа является безопасной операцией. Например, TINYINT использует диапазон -128–127. Все возможные значения можно безопасно повысить до уровня INTEGER.

Список приоритета типов

Список приоритета типов определяет, можно ли неявно повысить уровень значений конкретного типа данных до другого типа данных.

Тип данных Список приоритета (от самых узких типов к самым широким)
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
ОБЪЕКТ OBJECT (3)

(1) Для разрешения наименее распространенных типов FLOAT пропускается, чтобы избежать потери точности.

(2) Для сложного типа правило приоритета применяется рекурсивно к элементам его компонентов.

(3) OBJECT существует только в пределах VARIANT.

Строки и NULL

Специальные правила применяются к STRING и нетипизированным значениям NULL:

  • NULL можно повысить до любого другого типа.
  • STRING можно повысить до уровней BIGINT, BINARY, BOOLEAN, DATE, DOUBLE, INTERVAL и TIMESTAMP. Если фактическое строковое значение не может быть приведение к наименее распространенному типу Azure Databricks вызывает ошибку среды выполнения. При повышении до уровня INTERVAL строковое значение должно совпадать с единицами интервалов.

Граф приоритета типов

Это графическое изображение иерархии приоритетов, объединяющее список приоритета типов и правила для строк и значений NULL.

Графическое представление правил приоритета

Разрешение наименьшего общего типа

Наименьший общий тип из набора типов является самым узким типом, который доступен всем элементам набора типов из графа приоритета типов.

Разрешение наименьшего общего типа используется в следующих целях:

  • Чтобы решить, можно ли вызвать функцию, ожидающую параметр данного типа, с помощью аргумента более узкого типа.
  • Чтобы создать производный тип аргумента для функции, которая ожидает тип общего аргумента для нескольких параметров, таких как coalesce, in, least или greatest.
  • Чтобы создать производные типы операндов для таких операций, как арифметические операции или сравнения.
  • Чтобы создать производный тип результата для таких выражений, как выражение CASE.
  • Чтобы создать производные типы элементов, ключей или значений для конструкторов массивов и схем.
  • Чтобы создать производный тип результата операторов наборов UNION, INTERSECT или EXCEPT.

Если наименьший общий тип разрешается в FLOAT, применяются специальные правила. Если любой из участвующих типов представляет собой точный числовой тип (TINYINT, SMALLINT, INTEGER, BIGINT или DECIMAL), для предотвращения возможной потери цифр за наименьший общий тип берется DOUBLE.

Неявное нисходящее приведение типа и кросскастинг

Azure Databricks использует эти формы неявного приведения только к вызову функции и оператора, и только в том случае, если он может однозначно определить намерение.

  • Неявное нисходящее приведение типа

    Неявное нисходящее приведение типа автоматически делает тип более узким, не требуя явно указывать приведение. Нисходящее приведение типа удобно, но оно связано с риском непредвиденных ошибок выполнения, если фактическое значение не может быть представлено в узком типе.

    Нисходящее приведение типа применяет список приоритета типов в обратном порядке.

  • Неявный кросскастинг

    Неявный кросскастинг приводит значение из одного семейства типов к типу из другого семейства, не требуя явно указывать приведение.

    Azure Databricks поддерживает неявную перекрестную рассылку из:

    • Любого простого типа, кроме BINARY, в STRING.
    • STRING к любому простому типу.

Приведение при вызове функции

При наличии разрешенной функции или оператора применяются следующие правила (в порядке их перечисления) для каждой пары "параметр-аргумент":

  • Если поддерживаемый тип параметра является частью графа приоритета типа аргумента, Azure Databricks способствует аргументу этого типа параметра.

    В большинстве случаев в описании функции явно указываются поддерживаемые типы или цепочка, например "любой числовой тип".

    Например, sin(expr) работает с DOUBLE, но принимает любое числовое значение.

  • Если ожидаемый тип параметра является и STRING аргументом является простой тип Azure Databricks перекрестно перекрестно аргумент в тип строкового параметра.

    Например, substr(str, start, len) ожидает, что типом str будет STRING. Вместо этого можно передать числовой тип или тип даты-времени.

  • Если тип аргумента является и STRING ожидаемый тип параметра является простым типом, Azure Databricks перекрестно переадресует аргумент строки в самый широкий поддерживаемый тип параметра.

    Например, date_add(date, days) ожидает DATE и INTEGER.

    При вызове date_add() с двумя STRINGs Azure Databricks перекрестно перекрестную STRING передачу первого и DATE второго STRING на объектINTEGER.

  • Если функция ожидает числовый тип, например INTEGERтип или DATE тип, но аргумент является более общим типом, например или TIMESTAMPDOUBLE , Azure Databricks неявно переключает аргумент в этот тип параметра.

    Например, date_add(date, days) ожидает DATE и INTEGER.

    При вызове date_add() с помощью TIMESTAMP a и a BIGINTAzure Databricks переадресовывается DATE TIMESTAMP путем удаления компонента времени и BIGINT объекта.INTEGER

  • В противном случае Azure Databricks вызывает ошибку.

Примеры

Функция объединения принимает любой набор типов аргументов, если они используют наименее распространенный тип.

Тип результата определяется наименьшим общим типом всех аргументов.

-- 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

Функция substring ожидает аргументы типа STRING для строки и типа INTEGER — для параметров начала и длины.

-- 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) допускает неявный кросскастинг в строковый тип.

-- 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 можно вызвать с помощью TIMESTAMP или BIGINT из-за неявного нисходящего приведения типа.

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

date_add можно вызвать с помощью STRING из-за неявного кросскастинга.

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