You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by gu...@apache.org on 2023/04/10 02:10:25 UTC
[spark] branch branch-3.4 updated: [SPARK-43072][DOC] Include TIMESTAMP_NTZ type in ANSI Compliance doc
This is an automated email from the ASF dual-hosted git repository.
gurwls223 pushed a commit to branch branch-3.4
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.4 by this push:
new dc4493262fe [SPARK-43072][DOC] Include TIMESTAMP_NTZ type in ANSI Compliance doc
dc4493262fe is described below
commit dc4493262feb13b8e71ce45903ff89edbcf99fd7
Author: Gengliang Wang <ge...@apache.org>
AuthorDate: Mon Apr 10 11:10:04 2023 +0900
[SPARK-43072][DOC] Include TIMESTAMP_NTZ type in ANSI Compliance doc
### What changes were proposed in this pull request?
There are important syntax rules about Cast/Store assignment/Type precedent list in the [ANSI Compliance doc](https://spark.apache.org/docs/latest/sql-ref-ansi-compliance.html)
As we are going to release [timestamp_ntz](https://issues.apache.org/jira/browse/SPARK-35662) type in Spark 3.4.0, we should update the doc page as well.
### Why are the changes needed?
Better documentation
### Does this PR introduce _any_ user-facing change?
No
### How was this patch tested?
Manual build and verify
<img width="1183" alt="image" src="https://user-images.githubusercontent.com/1097932/230692965-02ec5a6e-8b8a-48dc-8049-9a87d26b2ce5.png">
<img width="1068" alt="image" src="https://user-images.githubusercontent.com/1097932/230692988-bd35508c-0577-44c5-8448-f8d3b0aef2ea.png">
<img width="764" alt="image" src="https://user-images.githubusercontent.com/1097932/230693005-cb61a760-ea11-4e6d-bdcb-2738c7c507c6.png">
Closes #40711 from gengliangwang/ntz_in_ansi_doc.
Authored-by: Gengliang Wang <ge...@apache.org>
Signed-off-by: Hyukjin Kwon <gu...@apache.org>
(cherry picked from commit c5db391b5d8447fe1384f7f957e4f200801c6823)
Signed-off-by: Hyukjin Kwon <gu...@apache.org>
---
docs/img/type-precedence-list.png | Bin 93171 -> 104106 bytes
docs/sql-ref-ansi-compliance.md | 88 +++++++++++++++++++-------------------
2 files changed, 45 insertions(+), 43 deletions(-)
diff --git a/docs/img/type-precedence-list.png b/docs/img/type-precedence-list.png
index 5d9a3079bf0..34b7de9f1f9 100644
Binary files a/docs/img/type-precedence-list.png and b/docs/img/type-precedence-list.png differ
diff --git a/docs/sql-ref-ansi-compliance.md b/docs/sql-ref-ansi-compliance.md
index 4124e958e39..bf099df10b4 100644
--- a/docs/sql-ref-ansi-compliance.md
+++ b/docs/sql-ref-ansi-compliance.md
@@ -82,22 +82,23 @@ Besides, the ANSI SQL mode disallows the following type conversions which are al
The valid combinations of source and target data type in a `CAST` expression are given by the following table.
āYā indicates that the combination is syntactically valid without restriction and āNā indicates that the combination is not valid.
-| Source\Target | Numeric | String | Date | Timestamp | Interval | Boolean | Binary | Array | Map | Struct |
-|-----------|--------|--------|------|-----------|----------|---------|--------|-------|-----|--------|
-| Numeric | <span style="color:red">**Y**</span> | <span style="color:red">**Y**</span> | N | <span style="color:red">**Y**</span> | <span style="color:red">**Y**</span> | Y | N | N | N | N |
-| String | <span style="color:red">**Y**</span> | Y | <span style="color:red">**Y**</span> | <span style="color:red">**Y**</span> | <span style="color:red">**Y**</span> | <span style="color:red">**Y**</span> | Y | N | N | N |
-| Date | N | Y | Y | Y | N | N | N | N | N | N |
-| Timestamp | <span style="color:red">**Y**</span> | Y | Y | Y | N | N | N | N | N | N |
-| Interval | <span style="color:red">**Y**</span> | Y | N | N | Y | N | N | N | N | N |
-| Boolean | Y | Y | N | N | N | Y | N | N | N | N |
-| Binary | N | Y | N | N | N | N | Y | N | N | N |
-| Array | N | Y | N | N | N | N | N | <span style="color:red">**Y**</span> | N | N |
-| Map | N | Y | N | N | N | N | N | N | <span style="color:red">**Y**</span> | N |
-| Struct | N | Y | N | N | N | N | N | N | N | <span style="color:red">**Y**</span> |
+| Source\Target | Numeric | String | Date | Timestamp | Timestamp_NTZ | Interval | Boolean | Binary | Array | Map | Struct |
+|---------------|--------------------------------------|--------------------------------------|--------------------------------------|--------------------------------------|--------------------------------------|--------------------------------------|--------------------------------------|--------|--------------------------------------|--------------------------------------|--------------------------------------|
+| Numeric | <span style="color:red">**Y**</span> | <span style="color:red">**Y**</span> | N | <span style="color:red">**Y**</span> | N | <span style="color:red">**Y**</span> | Y | N | N | N | N |
+| String | <span style="color:red">**Y**</span> | Y | <span style="color:red">**Y**</span> | <span style="color:red">**Y**</span> | <span style="color:red">**Y**</span> | <span style="color:red">**Y**</span> | <span style="color:red">**Y**</span> | Y | N | N | N |
+| Date | N | Y | Y | Y | Y | N | N | N | N | N | N |
+| Timestamp | <span style="color:red">**Y**</span> | Y | Y | Y | Y | N | N | N | N | N | N |
+| Timestamp_NTZ | N | Y | Y | Y | Y | N | N | N | N | N | N |
+| Interval | <span style="color:red">**Y**</span> | Y | N | N | N | Y | N | N | N | N | N |
+| Boolean | Y | Y | N | N | N | N | Y | N | N | N | N |
+| Binary | N | Y | N | N | N | N | N | Y | N | N | N |
+| Array | N | Y | N | N | N | N | N | N | <span style="color:red">**Y**</span> | N | N |
+| Map | N | Y | N | N | N | N | N | N | N | <span style="color:red">**Y**</span> | N |
+| Struct | N | Y | N | N | N | N | N | N | N | N | <span style="color:red">**Y**</span> |
In the table above, all the `CAST`s with new syntax are marked as red <span style="color:red">**Y**</span>:
* CAST(Numeric AS Numeric): raise an overflow exception if the value is out of the target data type's range.
-* CAST(String AS (Numeric/Date/Timestamp/Interval/Boolean)): raise a runtime exception if the value can't be parsed as the target data type.
+* CAST(String AS (Numeric/Date/Timestamp/Timestamp_NTZ/Interval/Boolean)): raise a runtime exception if the value can't be parsed as the target data type.
* CAST(Timestamp AS Numeric): raise an overflow exception if the number of seconds since epoch is out of the target data type's range.
* CAST(Numeric AS Timestamp): raise an overflow exception if numeric value times 1000000(microseconds per second) is out of the range of Long type.
* CAST(Array AS Array): raise an exception if there is any on the conversion of the elements.
@@ -170,18 +171,19 @@ While casting of a decimal with a fraction to an interval type with SECOND as th
### Store assignment
As mentioned at the beginning, when `spark.sql.storeAssignmentPolicy` is set to `ANSI`(which is the default value), Spark SQL complies with the ANSI store assignment rules on table insertions. The valid combinations of source and target data type in table insertions are given by the following table.
-| Source\Target | Numeric | String | Date | Timestamp | Interval | Boolean | Binary | Array | Map | Struct |
-|:-------------:|:-------:|:------:|:----:|:---------:|:--------:|:-------:|:------:|:-----:|:---:|:------:|
-| Numeric | Y | Y | N | N | N | N | N | N | N | N |
-| String | N | Y | N | N | N | N | N | N | N | N |
-| Date | N | Y | Y | Y | N | N | N | N | N | N |
-| Timestamp | N | Y | Y | Y | N | N | N | N | N | N |
-| Interval | N | Y | N | N | N* | N | N | N | N | N |
-| Boolean | N | Y | N | N | N | Y | N | N | N | N |
-| Binary | N | Y | N | N | N | N | Y | N | N | N |
-| Array | N | N | N | N | N | N | N | Y** | N | N |
-| Map | N | N | N | N | N | N | N | N | Y** | N |
-| Struct | N | N | N | N | N | N | N | N | N | Y** |
+| Source\Target | Numeric | String | Date | Timestamp | Timestamp_NTZ | Interval | Boolean | Binary | Array | Map | Struct |
+|:-------------:|:-------:|:------:|:----:|:---------:|---------------|:--------:|:-------:|:------:|:-----:|:---:|:------:|
+| Numeric | Y | Y | N | N | N | N | N | N | N | N | N |
+| String | N | Y | N | N | N | N | N | N | N | N | N |
+| Date | N | Y | Y | Y | Y | N | N | N | N | N | N |
+| Timestamp | N | Y | Y | Y | Y | N | N | N | N | N | N |
+| Timestamp_NTZ | N | Y | Y | Y | Y | N | N | N | N | N | N |
+| Interval | N | Y | N | N | N | N* | N | N | N | N | N |
+| Boolean | N | Y | N | N | N | N | Y | N | N | N | N |
+| Binary | N | Y | N | N | N | N | N | Y | N | N | N |
+| Array | N | N | N | N | N | N | N | N | Y** | N | N |
+| Map | N | N | N | N | N | N | N | N | N | Y** | N |
+| Struct | N | N | N | N | N | N | N | N | N | N | Y** |
\* Spark doesn't support interval type table column.
@@ -200,24 +202,24 @@ org.apache.spark.SparkArithmeticException: [CAST_OVERFLOW_IN_TABLE_INSERT] Fail
When `spark.sql.ansi.enabled` is set to `true`, Spark SQL uses several rules that govern how conflicts between data types are resolved.
At the heart of this conflict resolution is the Type Precedence List which defines whether values of a given data type can be promoted to another data type implicitly.
-| Data type | precedence list(from narrowest to widest) |
-|-----------|---------------------------------------------------------------|
-| Byte | Byte -> Short -> Int -> Long -> Decimal -> Float* -> Double |
-| Short | Short -> Int -> Long -> Decimal-> Float* -> Double |
-| Int | Int -> Long -> Decimal -> Float* -> Double |
-| Long | Long -> Decimal -> Float* -> Double |
-| Decimal | Decimal -> Float* -> Double |
-| Float | Float -> Double |
-| Double | Double |
-| Date | Date -> Timestamp |
-| Timestamp | Timestamp |
-| String | String, Long -> Double, Date -> Timestamp, Boolean, Binary ** |
-| Binary | Binary |
-| Boolean | Boolean |
-| Interval | Interval |
-| Map | Map*** |
-| Array | Array*** |
-| Struct | Struct*** |
+| Data type | precedence list(from narrowest to widest) |
+|-----------|---------------------------------------------------------------------------------|
+| Byte | Byte -> Short -> Int -> Long -> Decimal -> Float* -> Double |
+| Short | Short -> Int -> Long -> Decimal-> Float* -> Double |
+| Int | Int -> Long -> Decimal -> Float* -> Double |
+| Long | Long -> Decimal -> Float* -> Double |
+| Decimal | Decimal -> Float* -> Double |
+| Float | Float -> Double |
+| Double | Double |
+| Date | Date -> Timestamp_NTZ -> Timestamp |
+| Timestamp | Timestamp |
+| String | String, Long -> Double, Date -> Timestamp_NTZ -> Timestamp , Boolean, Binary ** |
+| Binary | Binary |
+| Boolean | Boolean |
+| Interval | Interval |
+| Map | Map*** |
+| Array | Array*** |
+| Struct | Struct*** |
\* For least common type resolution float is skipped to avoid loss of precision.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org