ANSI_MODE

적용 대상: Databricks SQL

ANSI_MODE 구성 매개 변수는 기본 제공 함수 및 캐스트 작업의 주요 동작을 제어합니다.

이 문서에서는 Databricks SQL의 ANSI 모드에 대해 설명합니다. Databricks 런타임의 ANSI 규정 준수는 Databricks 런타임의 ANSI 규정 준수를 참조 하세요.

설정

• TRUE

  대부분의 데이터베이스 및 데이터 웨어하우스와 유사한 특정 산술 연산 및 형식 변환을 처리하는 방법에 대한 SQL 표준을 따릅니다. 이 표준을 따르면 데이터 품질, 무결성 및 이식성이 향상됩니다.

• FALSE

  Databricks SQL은 Hive 호환 동작을 사용합니다.

SET 문을 사용하여 세션 수준에서 그리고 SQL 구성 매개 변수 또는 SQL Warehouse API를 사용하여 전역 수준에서 이 매개 변수를 설정할 수 있습니다.

시스템 기본값

시스템 기본값은 TRUE Databricks SQL 2022.35 이상에 추가된 계정에 대한 것입니다.

자세한 설명

Databricks SQL 참조 설명서에서는 SQL 표준 동작에 대해 설명합니다.

다음 섹션에서는 ANSI_MODE TRUE(ANSI 모드)와 FALSE(비 ANSI 모드) 간의 차이점을 설명합니다.

연산자

ANSI가 아닌 모드에서는 숫자 형식에서 수행된 산술 연산이 오버플로된 값 또는 NULL을 반환할 수 있지만 ANSI 모드에서는 이러한 작업이 오류를 반환합니다.

Operator	설명	예시	ANSI_MODE = true	ANSI_MODE = false
dividend / divisor	피제수를 제수로 나눈 값을 반환합니다.	1/0	Error	NULL
- expr	expr의 부정 값을 반환합니다.	-(-128y)	Error	-128y오버플로
expr1 - expr2	expr1에서 expr2를 뺀 값을 반환합니다.	-128y - 1y	Error	127y오버플로
expr1 + expr2	expr1 및 expr2의 합계를 반환합니다.	127y + 1y	Error	-128y오버플로
dividend % divisor	피제수/제수 후 나머지를 반환합니다.	1 % 0	Error	NULL
multiplier * multiplicand	피승수와 승수를 곱한 값을 반환합니다.	100y * 100y	Error	16y오버플로
arrayExpr[index]	인덱스에 있는 arrayExpr의 요소를 반환합니다.	잘못된 배열 인덱스	Error	NULL
mapExpr[key]	키에 대한 mapExpr 값을 반환합니다.	잘못된 맵 키	Error	NULL
divisor div dividend	제수를 피제수로 나눈 값의 정수 부분을 반환합니다.	1 div 0	Error	NULL

함수

일부 기본 제공 함수의 동작은 아래 지정된 조건에서 ANSI 모드와 비 ANSI 모드에서 다를 수 있습니다.

Operator	설명	조건	ANSI_MODE = true	ANSI_MODE = false
abs(expr)	expr에 있는 숫자 값의 절대값을 반환합니다.	abs(-128y)	Error	-128y오버플로
element_at(mapExpr, key)	키에 대한 mapExpr 값을 반환합니다.	잘못된 맵 키	Error	NULL
element_at(arrayExpr, index)	인덱스에 있는 arrayExpr의 요소를 반환합니다.	잘못된 배열 인덱스	Error	NULL
elt(index, expr1 [, …] )	nth 식을 반환합니다.	잘못된 인덱스	Error	NULL
make_date(y,m,d)	연도, 월 및 일 필드로 날짜를 만듭니다.	잘못된 결과 날짜	Error	NULL
make_timestamp(y,m,d,h,mi,s[,tz])	필드에서 타임스탬프를 만듭니다.	잘못된 결과 타임스탬프	Error	NULL
make_interval(y,m,w,d,h,mi,s)	필드에서 간격을 만듭니다.	잘못된 결과 간격	Error	NULL
mod(dividend, divisor)	피제수/제수 후 나머지를 반환합니다.	mod(1, 0)	Error	NULL
next_day(expr,dayOfWeek)	expr보다 나중이고 이름이 dayOfWeek인 첫 번째 날짜를 반환합니다.	잘못된 요일	Error	NULL
parse_url(url, partToExtract[, key])	URL에서 일부를 추출합니다.	잘못된 URL	Error	NULL
pmod(dividend, divisor)	피제수/제수 후 나머지 양수를 반환합니다.	pmod(1, 0)	Error	NULL
size(expr)	expr의 카디널리티를 반환합니다.	size(NULL)	NULL	-1
to_date(expr[,fmt])	선택적 형식 지정을 사용하여 날짜로 캐스팅된 expr을 반환합니다.	잘못된 expr 또는 형식 문자열	Error	NULL
to_timestamp(expr[,fmt])	선택적 형식 지정을 사용하여 타임스탬프로 캐스팅된 expr을 반환합니다.	잘못된 expr 또는 형식 문자열	Error	NULL
to_unix_timestamp(expr[,fmt])	expr의 타임스탬프를 UNIX 타임스탬프로 반환합니다.	잘못된 expr 또는 형식 문자열	Error	NULL
unix_timestamp([expr[, fmt]])	현재 또는 지정된 시간의 UNIX 타임스탬프를 반환합니다.	잘못된 expr 또는 형식 문자열	Error	NULL

캐스팅 규칙

CAST와 관련된 규칙 및 동작은 ANSI 모드에서 더 엄격합니다. 차이점은 다음 세 범주로 구분됩니다.

• 컴파일 시간 변환 규칙
• 런타임 오류
• 암시적 형식 강제 변환 규칙

컴파일 시간 변환 규칙

소스 형식	대상 형식	예시	ANSI_MODE = true	ANSI_MODE = false
Boolean	타임스탬프	cast(TRUE AS TIMESTAMP)	Error	1970-01-01 00:00:00.000001 UTC
날짜	Boolean	cast(DATE'2001-08-09' AS BOOLEAN)	Error	NULL
타임스탬프	Boolean	cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN)	Error	FALSE
정수 숫자	이진	cast(15 AS BINARY)	Error	이진 표현

런타임 오류

소스 형식	대상 형식	조건	예시	ANSI_MODE = true	ANSI_MODE = false
문자열	문자열이 아닌 경우	잘못된 입력	cast('a' AS INTEGER)	Error	NULL
배열, 구조체, 맵	배열, 구조체, 맵	잘못된 입력	cast(ARRAY('1','2','3') AS ARRAY<DATE>)	Error	NULL
숫자	숫자	오버플로	cast(12345 AS BYTE)	Error	NULL
숫자	정수 숫자	잘림	cast(5.1 AS INTEGER)	Error	5

참고 항목

이러한 각 캐스트에 대해 캐스트 대신 try_cast를 사용하여 오류가 아닌 NULL을(를) 반환할 수 있습니다.

암시적 형식 강제 변환 규칙

ANSI_MODE = TRUE아래에서 Databricks SQL은 다음과 같은 명확한 SQL 데이터 형식 캐스팅 규칙을 사용합니다.

• 형식 승격
• downcasting
• crosscasting

반면 ANSI_MODE = FALSE은(는) 일관성이 없고 관대합니다. 예시:

• 산술 연산자가 있는 STRING 형식을 사용하는 경우 문자열은 암시적으로 DOUBLE(으)로 캐스팅됩니다.
• STRING을(를) 숫자 형식과 비교하면 문자열은 암시적으로 비교되는 형식으로 캐스팅됩니다.
• UNION, COALESCE 또는 최소 공통 형식을 찾아야 하는 기타 작업을 수행하는 경우 모든 형식이 STRING(으)로 캐스팅됩니다(STRING 형식이 있는 경우).

ANSI_MODE = FALSE에 의존하는 대신 명시적 cast 또는 try_cast 함수를 사용하는 것이 좋습니다.

예제

> 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);
  12.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 데이터 형식 규칙
• 재설정
• SET 문
• SQL 매개 변수 구성
• SQL Warehouse API