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 | Timestamp | cast(TRUE AS TIMESTAMP) |
Error | 1970-01-01 00:00:00.000001 UTC |
| 날짜 | Boolean | cast(DATE'2001-08-09' AS BOOLEAN) |
Error | NULL |
| Timestamp | 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 |
암시적 형식 강제 변환 규칙
ANSI_MODE = TRUE아래에서 Databricks SQL은 다음과 같은 명확한 SQL 데이터 형식 캐스팅 규칙을 사용합니다.
반면 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