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(溢出)

除數%

返回除/除數後的餘數。

10

錯誤

乘數*乘數

返回乘數乘以乘數。

100 y100 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”作為Tc1c2);不能解決(T.c1 + T.c2)由於數據類型不匹配(T.c1 + T.c2)需要數字時間間隔一天第二個時間間隔一年時間間隔類型字符串——將STRING提升為最小通用類型(STRING, INTEGER——> BIGINT)用於算術運算。>選擇typeof5-“3”);長整型數字——通過運行時檢查將STRING提升為最小通用類型(INTEGER, STRING——> BIGINT)>選擇c1c210“10.1”作為Tc1c2);無效的輸入語法類型數字101返回而不是使用“try_cast”——將STRING提升為最小通用類型(STRING, INTEGER——> BIGINT),用於集操作和運行時檢查。>選擇typeofc1選擇5聯盟所有選擇“6”作為Tc1);長整型數字長整型數字
>ansi_mode——無聲積分數值溢出>選擇12345作為非常小的整數);57——返回NULL而不是錯誤>選擇“一個”作為整數);——try_cast()對這兩種模式都是安全的>選擇try_cast“一個”作為整數);允許使用DOUBLE進行模糊的交叉轉換。>選擇c1+c2“5”“7.6”作為Tc1c2);126——交叉轉換STRING到DOUBLE進行算術運算。>選擇typeof5-“3”);——隱式地將STRING轉換為INTEGER,等於10和10.1>選擇c1c210“10.1”作為Tc1c2);真正的——對集合操作提升為字符串>選擇typeofc1選擇5聯盟所有選擇“6”作為Tc1);字符串字符串