ANSI_MODE
적용 대상:
Databricks SQL
ANSI_MODE 구성 매개 변수는 기본 제공 함수 및 캐스트 작업의 주요 동작을 제어합니다.
이 문서에서는 Databricks SQL의 ANSI 모드에 대해 설명합니다. Databricks 런타임의 ANSI 규정 준수는 Databricks 런타임의 ANSI 규정 준수를 참조 하세요.
설정
TRUE
대부분의 데이터베이스 및 데이터 웨어하우스와 유사한 특정 산술 연산 및 형식 변환을 처리하는 방법에 대한 SQL 표준을 따릅니다. 이 표준을 따르면 데이터 품질, 무결성 및 이식성이 향상됩니다.
FALSE
Databricks SQL은 Hive 호환 동작을 사용합니다.
세션 수준에서는 SET 문을(를) 사용하여, 전역 수준에서는 SQL 구성 parameters 또는 SQL Warehouse API을(를) 사용하여 이 매개 변수를 set 수 있습니다.
시스템 기본값
시스템 기본값은 TRUE Databricks SQL 2022.35 이상에 추가된 계정에 대한 것입니다.
자세한 설명
Databricks SQL 참조 설명서에서는 SQL 표준 동작에 대해 설명합니다.
다음 섹션에서는 ANSI_MODETRUE(ANSI 모드)와 FALSE(비 ANSI 모드)의 차이점을 설명합니다.
연산자
ANSI가 아닌 모드에서는 숫자 형식에서 수행된 산술 연산이 오버플로된 values 또는 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을(를) 숫자 형식과 비교하면 문자열은 암시적으로 비교되는 형식으로 캐스팅됩니다. - 가장 일반적인 형식을 where
UNION,COALESCE또는 기타 작업을 수행하는 경우 모든 형식이STRING형식이 있는 경우STRING캐스팅됩니다.
에 의존하는 대신 명시적 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);
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