ANSI_MODE

Область применения: Databricks SQL

Параметр конфигурации ANSI_MODE управляет ключевым поведением встроенных функций и операций приведения.

В этой статье описывается режим ANSI в Databricks SQL. Сведения о соответствии ANSI в Databricks Runtime см. в разделе "Соответствие ANSI" в Databricks Runtime.

Настройки

• ИСТИННЫЙ

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

• ЛОЖНЫЙ

  Databricks SQL поддерживает поведение, совместимое с Hive.

Этот параметр можно задать на уровне сеанса с помощью инструкции SET и на глобальном уровне с помощью параметров конфигурации SQL или API хранилища SQL.

Системное по умолчанию

Для учетных записей, созданных 19 октября 2022 г. или после этой даты, по умолчанию система использует TRUE.

Подробное описание

Справочная документация по Databricks SQL описывает стандартное поведение SQL.

В следующих разделах описываются различия между ANSI_MODETRUE (режимОМ ANSI) и FALSE (режим, отличный от ANSI).

Операторы

В режиме, отличном от ANSI, арифметические операции, выполняемые на числовых типах, могут возвращать переполненные значения или NULL, а в режиме ANSI такие операции возвращают ошибку.

Функции

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

Приведение правил

Правила и поведение, касающиеся CAST, являются более строгими в режиме ANSI. Их можно разделить на следующие три категории:

• Правила преобразования во время компиляции
• Ошибки среды выполнения
• Неявные правила приведения типов

Правила преобразования во время компиляции

Ошибки среды выполнения

Неявные правила приведения типов

В режиме ANSI_MODE = TRUE Databricks SQL использует четкие правила приведения типов данных SQL для:

• приведение типов
• нисходящего приведения
• кросс-дистрибьюция

В отличие от этого ANSI_MODE = FALSE характеризуется несогласованностью и меньшей строгостью. Например:

• При использовании типа STRING с любым арифметическим оператором строка неявно приводится к DOUBLE.
• При сравнении STRING с любым числовым типом строка неявно приводится к сравниваемому типу.
• При выполнении операций UNION, COALESCEили других операций, где необходимо определить наименее общий тип, все типы приводятся к STRING при наличии какого-либо типа STRING.

В Databricks рекомендуется использовать явную функцию cast или try_cast вместо использования ANSI_MODE = FALSE.

Примеры

> SET ansi_mode = true;

-- Protects against integral numeric overflow
> SELECT cast(12345 AS TINYINT);
  Casting 12345 to tinyint causes overflow

-- For invalid values raises errors instead of returning NULL.
> SELECT cast('a' AS INTEGER);
  Invalid input syntax for type numeric: a.
  To return NULL instead, use 'try_cast'

-- try_cast() is consistent for both modes
> SELECT try_cast('a' AS INTEGER);
  NULL

-- Does not allow ambiguous crosscasting.
> SELECT c1 + c2 FROM VALUES('5', '7.6') AS T(c1, c2);
  Cannot resolve '(T.c1 + T.c2)' due to data type mismatch:
  '(T.c1 + T.c2)' requires (numeric or interval day to second or interval year to month or interval) type, not string

-- Promotes STRING to least common type (STRING, INTEGER --> BIGINT) for arithmetic operation.
> SELECT typeof(5 - '3');
  bigint

-- Promotes STRING to least common type (INTEGER, STRING --> BIGINT) with runtime check
> SELECT c1 = c2 FROM VALUES(10, '10.1') AS T(c1, c2);
  Invalid input syntax for type numeric: 10.1. To return NULL instead, use 'try_cast'.

-- Promotes STRING to least common type (STRING, INTEGER --> BIGINT) for set operation with runtime check.
> SELECT typeof(c1) FROM (SELECT 5 UNION ALL SELECT '6') AS T(c1);
  bigint
  bigint

> SET ansi_mode = false;

-- Silent integral numeric overflow
> SELECT cast(12345 AS TINYINT);
  57

-- Returns NULL instead of an error
> SELECT cast('a' AS INTEGER);
  NULL

-- try_cast() is safe for both modes
> SELECT try_cast('a' AS INTEGER);
  NULL

-- Does allow ambiguous crosscasting using DOUBLE.
> SELECT c1 + c2 FROM VALUES('5', '7.6') AS T(c1, c2);
  1.6

-- Crosscasts STRING to DOUBLE for arithmetic operation.
> SELECT typeof(5 - '3');
  double

-- Implicitly casts STRING to INTEGER equating 10 with 10.1
> SELECT c1 = c2 FROM VALUES(10, '10.1') AS T(c1, c2);
  true

-- Promotes to string for set operations
> SELECT typeof(c1) FROM (SELECT 5 UNION ALL SELECT '6') AS T(c1);
  string
  string

Связанные

• Правила типа данных SQL
• RESET
• SET заявление
• Настройка параметров SQL
• API хранилища SQL