SQL データ型のルール

適用対象: Databricks SQL Databricks Runtime

Azure Databricks では、複数のルールを使用してデータ型間の競合を解決します。

• 上位変換では、1 つの型を、より広い型に安全に拡張します。
• 暗黙的なダウンキャストでは、型が絞り込まれます。 上位変換の逆です。
• 暗黙的なクロスキャストでは、1 つの型が別の型ファミリの型に変換されます。

また、次の多くの型間で明示的にキャストすることもできます。

• cast 関数は、ほとんどの型間のキャストを行います。キャストできない場合はエラーを返します。
• try_cast 関数は cast 関数と同様に機能しますが、無効な値が渡されると NULL を返します。
• その他の組み込み関数は、指定された形式ディレクティブを使用して型間でキャストされます。

型の昇格型の上位変換

型の上位変換は、1 つの型を、元の型の可能な値がすべて含まれる、同じ型ファミリの別の型にキャストするプロセスです。 したがって、型の上位変換は安全な操作です。 たとえば、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	VARIANT
OBJECT	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 であると予期されています。 代わりに、数値型または datetime 型を渡すことができます。

• 引数型が STRING で、予期されるパラメーター型が単純型である場合、Azure Databricks では、文字列型の引数を、サポートされている最も広いパラメーター型にクロスキャストします。

  たとえば date_add(date, days) では、DATE と INTEGER が予期されています。

  2 つの STRING を使用して date_add() を呼び出すと、Azure Databricks により、最初の STRING が DATE に、2 番目の STRING が INTEGER にクロスキャストされます。

• 関数が INTEGER などの数値型や DATE 型を予期していても、引数が DOUBLE や TIMESTAMP などのより一般的な型である場合、その引数は Azure Databricks によって暗黙的に、そのパラメーター型にダウンキャストされます。

  たとえば date_add(date, days) では、DATE と INTEGER が予期されています。

  TIMESTAMP と BIGINT を使用して date_add() を呼び出すと、Azure Databricks では、時刻の部分を削除することで TIMESTAMP を DATE に、BIGINT を INTEGER にダウンキャストします。

• それ以外の場合は、Azure Databricks でエラーが発生します。

例

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

関連項目

• ANSI_MODE
• cast
• データ型
• 関数
• try_cast