ANSI_MODE

適用於: 勾選“是” 磚的SQL

的ANSI_MODE配置參數控製內置函數和強製轉換操作的關鍵行為。

設置

真正的
在處理某些算術操作和類型轉換方麵遵循SQL標準，類似於大多數數據庫和數據倉庫。遵循此標準可以提高數據質量、完整性和可移植性。
假
Databricks SQL使用與hive兼容的行為。

屬性可以在會話級別設置此參數設置語句在全球範圍內使用SQL配置參數或全局SQL倉庫API．

係統默認值

係統默認值為真正的用於在Databricks SQL 2022.35及更高版本上添加的帳戶。

詳細描述

Databricks SQL參考文檔描述了SQL標準行為。

下麵幾節描述ANSI_MODE之間的區別真正的(ANSI模式)和假(non-ANSI模式)。

運營商

在非ANSI模式下，對數字類型執行的算術操作可能返回溢出值或NULL，而在ANSI模式下，此類操作返回錯誤。

操作符	描述	例子	ANSI_MODE = true	ANSI_MODE = false
除數/除數	返回紅利除以除數。	`1/0`	錯誤	`零`
- - - - - - expr	返回expr的負數。	`- y (-128)`	錯誤	`-128 y`(溢出)
Expr1 - expr2	返回expr1減去expr2。	`-128 y-1 y`	錯誤	`127 y`(溢出)
Expr1 + expr2	返回expr1和expr2的和。	`127 y+1 y`	錯誤	`-128 y`(溢出)
除數%	返回除/除數後的餘數。	`1％0`	錯誤	`零`
乘數*乘數	返回乘數乘以乘數。	`100 y＊100 y`	錯誤	`16個y`(溢出)
arrayExpr(指數)	返回arrayExpr對象的元素。	無效的數組索引	錯誤	`零`
mapExpr(例子)	返回mapExpr的key值。	無效的映射鍵	錯誤	`零`
除數div紅利	返回除數除以被除數的整數部分。	`1div0`	錯誤	`零`

功能

在下麵指定的條件下，某些內置函數在ANSI模式下與非ANSI模式下的行為可能不同。

操作符	描述	條件	ANSI_MODE = true	ANSI_MODE = false
abs (expr)	返回expr中數值的絕對值。	`abs (-128 y)`	錯誤	`-128 y`(溢出)
element_at (mapExpr鍵)	返回mapExpr的key值。	無效的映射鍵	錯誤	`零`
element_at (arrayExpr指數)	返回arrayExpr對象的元素。	無效的數組索引	錯誤	`零`
Elt (index, expr1[，…])	返回第n個表達式。	無效的指數	錯誤	`零`
make_date (y, m, d)	從年、月和日字段創建日期。	無效結果日期	錯誤	`零`
make_timestamp (y、m, d, h mi, s (tz))	從字段中創建時間戳。	無效的結果時間戳	錯誤	`零`
make_interval (y、m、w d h mi, s)	從字段中創建一個間隔。	無效的結果間隔	錯誤	`零`
國防部(股息,除數)	返回除/除數後的餘數。	`國防部(1,0）`	錯誤	`零`
next_day (expr dayOfWeek)	返回第一個晚於expr且命名為dayOfWeek的日期。	無效日期	錯誤	`零`
parse_url(url, partToExtract[， key])	從url中提取一個部分。	無效的URL	錯誤	`零`
pmod(股息,除數)	返回除/除數後的正餘數。	`pmod (1,0）`	錯誤	`零`
大小(expr)	返回expr的基數。	`大小(空)`	`零`	`－1`
to_date (expr (fmt))	使用可選格式返回轉換為日期的expr。	無效的表達式或格式字符串	錯誤	`零`
to_timestamp (expr (fmt))	使用可選格式將expr轉換為時間戳。	無效的表達式或格式字符串	錯誤	`零`
to_unix_timestamp (expr (fmt))	將expr中的時間戳作為UNIX時間戳返回。	無效的表達式或格式字符串	錯誤	`零`
unix_timestamp ([expr (fmt)))	返回當前或指定時間的UNIX時間戳。	無效的表達式或格式字符串	錯誤	`零`

鑄造的規則

關於CAST的規則和行為在ANSI模式中更為嚴格。它們可分為以下三類:

編譯時轉換規則

源類型	目標類型	例子	ANSI_MODE = true	ANSI_MODE = false
布爾	時間戳	`鑄造(真作為時間戳)`	錯誤	`1970-01-0100:00:00.000001UTC`
日期	布爾	`鑄造(日期“2001-08-09”作為布爾型)`	錯誤	`零`
時間戳	布爾	`(時間戳的1970-01-0100:00:00Z”作為布爾型)`	錯誤	`假`
積分數值	二進製	`鑄造(15作為二進製)`	錯誤	二進製表示

運行時錯誤

源類型	目標類型	條件	例子	ANSI_MODE = true	ANSI_MODE = false
字符串	Non-string	無效的輸入	`鑄造(' a '作為整數)`	錯誤	`零`
數組，結構，映射	數組，結構，映射	無效的輸入	`鑄造(數組(' 1 ',' 2 ',' 3 ')作為數組<日期>)`	錯誤	`零`
數字	數字	溢出	`演員(12345作為字節)`	錯誤	`零`
數字	積分數值	截斷	`鑄造(5.1作為整數)`	錯誤	`5`

請注意

對於這些類型轉換都可以使用try_cast而不是投返回零而不是錯誤。

隱式強製規則

下ANSI_MODE＝真正的， Databricks SQL使用clearSQL數據類型轉換規則:

相比之下ANSI_MODE＝假不一致，更寬容。例如:

當使用字符串類型使用任何算術運算符，則將字符串隱式轉換為雙．
當比較字符串對於任何數字類型，字符串都隱式轉換為與之比較的類型。
當執行聯盟，合並，或必須找到所有類型都轉換為最小公共類型的其他操作字符串如果有的話字符串類型。

Databricks建議使用顯式投或try_cast功能而不是依賴ANSI_MODE＝假．

例子

             >集ansi_mode＝真正的；——防止整數溢出>選擇投（12345作為非常小的整數）;鑄造12345來非常小的整數原因溢出—對於無效值拋出錯誤，而不是返回NULL。>選擇投（“一個”作為整數）;無效的輸入語法為類型數字：一個．來返回零而不是，使用“try_cast”——try_cast()對於兩種模式都是一致的>選擇try_cast（“一個”作為整數）;零不允許有歧義的交叉轉換。>選擇c1+c2從值（“5”，“7.6”）作為T（c1，c2）;不能解決(T.c1 + T.c2)由於來數據類型不匹配：(T.c1 + T.c2)需要（數字或時間間隔一天來第二個或時間間隔一年來月或時間間隔）類型，不字符串——將STRING提升為最小通用類型(STRING, INTEGER——> BIGINT)用於算術運算。>選擇typeof（5-“3”）;長整型數字——通過運行時檢查將STRING提升為最小通用類型(INTEGER, STRING——> BIGINT)>選擇c1＝c2從值（10，“10.1”）作為T（c1，c2）;無效的輸入語法為類型數字：10．1．來返回零而不是，使用“try_cast”．——將STRING提升為最小通用類型(STRING, INTEGER——> BIGINT)，用於集操作和運行時檢查。>選擇typeof（c1）從（選擇5聯盟所有選擇“6”）作為T（c1）;長整型數字長整型數字
            

             >集ansi_mode＝假；——無聲積分數值溢出>選擇投（12345作為非常小的整數）;57——返回NULL而不是錯誤>選擇投（“一個”作為整數）;零——try_cast()對這兩種模式都是安全的>選擇try_cast（“一個”作為整數）;零允許使用DOUBLE進行模糊的交叉轉換。>選擇c1+c2從值（“5”，“7.6”）作為T（c1，c2）;12．6——交叉轉換STRING到DOUBLE進行算術運算。>選擇typeof（5-“3”）;雙——隱式地將STRING轉換為INTEGER，等於10和10.1>選擇c1＝c2從值（10，“10.1”）作為T（c1，c2）;真正的——對集合操作提升為字符串>選擇typeof（c1）從（選擇5聯盟所有選擇“6”）作為T（c1）;字符串字符串
            