SQL 数据类型规则

适用于： Databricks SQL Databricks Runtime

Azure Databricks 使用几个规则来解决数据类型之间的冲突：

• 提升 可安全地将类型扩展到更广泛的类型。
• 隐式向下转换 会缩小类型范围。 与提升相反。
• 隐式交叉转换将 类型转换为属于另一个类型族的类型。

还可以在多种类型之间显式强制转换：

• 转换函数 在大多数类型之间转换，如果无法转换，则返回错误。
• try_cast函数 的工作方式类似于 强制转换函数 ，但在传递无效值时返回 NULL。
• 其他内置函数使用提供的格式指令在不同类型之间强制转换。

类型提升

类型提升是将类型转换为同一类型系列的另一个类型的过程，其中包含原始类型的所有可能值。 因此，类型提升是一项安全操作。 例如，TINYINT 的范围为 -128 到 127。 其所有可能值都可以安全地提升为 INTEGER。

类型优先级列表

类型优先级列表定义给定数据类型的值是否可以隐式提升为另一种数据类型。

（1） 跳过 最不常见的类型解析FLOAT 以避免精度损失。

（2） 对于复杂类型，优先规则以递归方式应用于其组件元素。

（3）OBJECT 仅限于一个 VARIANT.

字符串和 NULL

特殊规则适用于 STRING 和未键入 NULL：

• NULL 可以提升为任何其他类型。
• STRING 可以提升为 BIGINT、BINARY、BOOLEAN、DATE、DOUBLE、INTERVAL 和 TIMESTAMP。 如果无法将实际字符串值强制转换为最不常见类型，Azure Databricks 会引发运行时错误。 提升为 INTERVAL 时，字符串值必须与间隔单位匹配。

类型优先级图

这是优先级层次结构的图形描述，结合了类型优先级列表和字符串和 NULL 规则。

最不常见的类型解析

一组类型中最不常见的类型是在由该类型组的所有元素构成的类型优先级图中可达到的最窄类型。

最不常见的类型解析用于：

• 确定是否可以使用优先级较低类型的实参调用需要给定类型的形参的函数。
• 派生一个函数的参数类型，该函数需要多个参数的共享参数类型，例如 合并、 in、 最小或 最大。
• 派生运算符（如算术运算符或比较运算符）的操作数类型。
• 为表达式（如 case 表达式）推导结果类型。
• 确定 数组 和 映射 构造函数的元素类型、键类型或值类型。
• 派生 UNION、INTERSECT 或 EXCEPT 集运算符的结果类型。

如果最不常见的类型解析为 FLOAT，则应用特殊规则。 如果任何参与类型为精确的数值类型（TINYINT、SMALLINT、INTEGER、BIGINT 或 DECIMAL），则推出的最不常见类型均为 DOUBLE，以避免可能的数字丢失。

如果最不常见的类型是 STRING 则按照 排序规则优先规则计算排序规则。

隐式向下转换和交叉转换

Azure Databricks 仅在函数和运算符调用上使用这些形式的隐式强制转换，并且仅可明确确定意图。

• 隐式向下转换

  隐式向下类型转换会自动将较宽的类型转换为较窄的类型，而无需明确指定转换。 向下转换很方便，但如果实际值在窄类型中无法表示，则存在意外运行时错误的风险。

  向下转换按逆序应用类型优先级列表。 GEOGRAPHY 和 GEOMETRY 数据类型从不向下转换。

• 隐式交叉广播

  隐式交叉转换将值从一个类型系列强制转换到另一个类型系列，而无需显式指定强制转换。

  Azure Databricks 支持从以下位置进行隐式交叉转换：

  • 到 BINARY 的任何简单类型（GEOGRAPHY、GEOMETRY 和 STRING 除外）。
  • 将 STRING 应用于任何简单类型，但不包括 GEOGRAPHY 和 GEOMETRY。

对函数调用进行强制转换

给定解析的函数或运算符后，以下规则按其列出顺序应用于每个参数和参数对：

• 如果受支持的参数类型是参数类型优先级关系图的一部分，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()时使用两个STRING，Azure Databricks 会将第一个STRING交叉转换为DATE，将第二个STRING交叉转换为INTEGER。

• 如果函数需要数值类型（例如 INTEGER或DATE类型），但参数是更常规的类型，例如 DOUBLE 或 TIMESTAMP，Azure Databricks 将参数隐式 向下转换 到该参数类型。

  例如， date_add（日期、天） 需要一个 DATE 和一个 INTEGER。

  如果使用date_add()TIMESTAMP和BIGINT进行调用，则 Azure Databricks 会通过删除时间组件将向下转换为TIMESTAMP，并将BIGINT转换为INTEGER。

• 否则，Azure Databricks 会引发错误。

Examples

只要函数共享coalesce，该函数就接受任意一组参数类型。

结果类型是实参中的最不常见类型。

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

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

可以使用隐式向下转换调用TIMESTAMPBIGINT。

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

由于隐式交叉广播，可以使用 s 调用STRING。

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

Related

• ANSI_MODE
• cast
• 数据类型
• Functions
• try_cast