You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by GitBox <gi...@apache.org> on 2020/02/07 09:05:22 UTC
[GitHub] [spark] maropu opened a new pull request #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
maropu opened a new pull request #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489
<!--
Thanks for sending a pull request! Here are some tips for you:
1. If this is your first time, please read our contributor guidelines: https://spark.apache.org/contributing.html
2. Ensure you have added or run the appropriate tests for your PR: https://spark.apache.org/developer-tools.html
3. If the PR is unfinished, add '[WIP]' in your PR title, e.g., '[WIP][SPARK-XXXX] Your PR title ...'.
4. Be sure to keep the PR description updated to reflect all changes.
5. Please write your PR title to summarize what this PR proposes.
6. If possible, provide a concise example to reproduce the issue for a faster review.
-->
### What changes were proposed in this pull request?
<!--
Please clarify what changes you are proposing. The purpose of this section is to outline the changes and how this PR fixes the issue.
If possible, please consider writing useful notes for better and faster reviews in your PR. See the examples below.
1. If you refactor some codes with changing classes, showing the class hierarchy will help reviewers.
2. If you fix some SQL features, you can provide some references of other DBMSes.
3. If there is design documentation, please add the link.
4. If there is a discussion in the mailing list, please add the link.
-->
This pr intends to add a document for the ANSI mode.
TODO: Adds screenshots before/after this pr.
### Why are the changes needed?
<!--
Please clarify why the changes are needed. For instance,
1. If you propose a new API, clarify the use case for a new API.
2. If you fix a bug, you can clarify why it is a bug.
-->
For better document coverage and usability.
### Does this PR introduce any user-facing change?
<!--
If yes, please clarify the previous behavior and the change this PR proposes - provide the console output, description and/or an example to show the behavior difference if possible.
If no, write 'No'.
-->
No.
### How was this patch tested?
<!--
If tests were added, say they were added here. Please make sure to add some test cases that check the changes thoroughly including negative and positive cases if possible.
If it was tested in a way different from regular unit tests, please clarify how you tested step by step, ideally copy and paste-able, so that other reviewers can test and check, and descendants can verify in the future.
If tests were not added, please describe why they were not added and/or why it was difficult to add.
-->
N/A
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] gengliangwang commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
gengliangwang commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r378449795
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflows by default.
+This means that in case an operation causes overflows, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
+On the other hand, Spark SQL returns null for decimal overflows.
+When `spark.sql.ansi.enabled` is set to `true` and an overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.
+
+{% highlight sql %}
+-- `spark.sql.ansi.enabled=true`
+SELECT 2147483647 + 1;
+
+ java.lang.ArithmeticException: integer overflow
+
+-- `spark.sql.ansi.enabled=false`
+SELECT 2147483647 + 1;
+
+ +----------------+
+ |(2147483647 + 1)|
+ +----------------+
+ | -2147483648|
+ +----------------+
+
+{% endhighlight %}
+
+### Type Conversion
+
+Spark SQL has three kinds of type conversions: explicit casting, type coercion, and store assignment casting.
+When `spark.sql.ansi.enabled` is set to `true`, explicit casting by `CAST` syntax throws a number-format exception at runtime for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.
Review comment:
number-format exception is not the only runtime exception for ANSI mode
```
spark.sql("select cast(2147483648L as int)").show()
java.lang.ArithmeticException: Casting 2147483648 to int causes overflow
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] dongjoon-hyun commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
dongjoon-hyun commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r376739264
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflow by default.
+This means that in case an operation causes an overflow, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
+On the other hand, Spark SQL returns null for decimal overflow.
+When `spark.sql.ansi.enabled` is set to `true` and overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.
+
+{% highlight sql %}
+-- `spark.sql.ansi.enabled=true`
+SELECT 2147483647 + 1;
+
+ java.lang.ArithmeticException: integer overflow
+
+-- `spark.sql.ansi.enabled=false`
+SELECT 2147483647 + 1;
+
+ +----------------+
+ |(2147483647 + 1)|
+ +----------------+
+ | -2147483648|
+ +----------------+
+
+{% endhighlight %}
+
+### Type Conversion
+
+Spark SQL has three kinds of type conversions: explicit casting, type coercion, and store assignment casting.
+When `spark.sql.ansi.enabled` is set to `true`, explicit castings by `CAST` syntax throws a number-format exception at runtime for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.
+On the other hand, `INSERT INTO` syntax throws an analysis exception when the ANSI mode enabled via `spark.sql.storeAssignmentPolicy=ANSI`.
Review comment:
Just a question, shall we mention `CTAS(CREATE TABLE AS SELECT)` together?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] dongjoon-hyun commented on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
dongjoon-hyun commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583514361
cc @gatorsmile
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r378533837
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflows by default.
+This means that in case an operation causes overflows, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
+On the other hand, Spark SQL returns null for decimal overflows.
+When `spark.sql.ansi.enabled` is set to `true` and an overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.
+
+{% highlight sql %}
+-- `spark.sql.ansi.enabled=true`
+SELECT 2147483647 + 1;
+
+ java.lang.ArithmeticException: integer overflow
+
+-- `spark.sql.ansi.enabled=false`
+SELECT 2147483647 + 1;
+
+ +----------------+
+ |(2147483647 + 1)|
+ +----------------+
+ | -2147483648|
+ +----------------+
+
+{% endhighlight %}
+
+### Type Conversion
+
+Spark SQL has three kinds of type conversions: explicit casting, type coercion, and store assignment casting.
+When `spark.sql.ansi.enabled` is set to `true`, explicit casting by `CAST` syntax throws a number-format exception at runtime for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.
Review comment:
Oh, right. I'll update.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r376745386
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflow by default.
+This means that in case an operation causes an overflow, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
+On the other hand, Spark SQL returns null for decimal overflow.
+When `spark.sql.ansi.enabled` is set to `true` and overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.
+
+{% highlight sql %}
+-- `spark.sql.ansi.enabled=true`
+SELECT 2147483647 + 1;
+
+ java.lang.ArithmeticException: integer overflow
+
+-- `spark.sql.ansi.enabled=false`
+SELECT 2147483647 + 1;
+
+ +----------------+
+ |(2147483647 + 1)|
+ +----------------+
+ | -2147483648|
+ +----------------+
+
+{% endhighlight %}
+
+### Type Conversion
+
+Spark SQL has three kinds of type conversions: explicit casting, type coercion, and store assignment casting.
+When `spark.sql.ansi.enabled` is set to `true`, explicit castings by `CAST` syntax throws a number-format exception at runtime for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.
+On the other hand, `INSERT INTO` syntax throws an analysis exception when the ANSI mode enabled via `spark.sql.storeAssignmentPolicy=ANSI`.
Review comment:
Ah, good suggestion! I totally missed that syntax. I'll check.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585461739
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583304029
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] gengliangwang commented on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
gengliangwang commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585916875
Merged to master/branch-3.0
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] dongjoon-hyun commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
dongjoon-hyun commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r376739244
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflow by default.
+This means that in case an operation causes an overflow, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
+On the other hand, Spark SQL returns null for decimal overflow.
+When `spark.sql.ansi.enabled` is set to `true` and overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.
+
+{% highlight sql %}
+-- `spark.sql.ansi.enabled=true`
+SELECT 2147483647 + 1;
+
+ java.lang.ArithmeticException: integer overflow
+
+-- `spark.sql.ansi.enabled=false`
+SELECT 2147483647 + 1;
+
+ +----------------+
+ |(2147483647 + 1)|
+ +----------------+
+ | -2147483648|
+ +----------------+
+
+{% endhighlight %}
+
+### Type Conversion
+
+Spark SQL has three kinds of type conversions: explicit casting, type coercion, and store assignment casting.
+When `spark.sql.ansi.enabled` is set to `true`, explicit castings by `CAST` syntax throws a number-format exception at runtime for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.
Review comment:
`throws` -> `throw`?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #27489: [SPARK-30703][SQL][DOCS]
Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583793811
**[Test build #118083 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/118083/testReport)** for PR 27489 at commit [`6b0d5d6`](https://github.com/apache/spark/commit/6b0d5d62dc407f5ba64dbc4594cec723bfa90daa).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583382751
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583379131
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/22799/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583793834
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/118083/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] dongjoon-hyun commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
dongjoon-hyun commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r376739264
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflow by default.
+This means that in case an operation causes an overflow, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
+On the other hand, Spark SQL returns null for decimal overflow.
+When `spark.sql.ansi.enabled` is set to `true` and overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.
+
+{% highlight sql %}
+-- `spark.sql.ansi.enabled=true`
+SELECT 2147483647 + 1;
+
+ java.lang.ArithmeticException: integer overflow
+
+-- `spark.sql.ansi.enabled=false`
+SELECT 2147483647 + 1;
+
+ +----------------+
+ |(2147483647 + 1)|
+ +----------------+
+ | -2147483648|
+ +----------------+
+
+{% endhighlight %}
+
+### Type Conversion
+
+Spark SQL has three kinds of type conversions: explicit casting, type coercion, and store assignment casting.
+When `spark.sql.ansi.enabled` is set to `true`, explicit castings by `CAST` syntax throws a number-format exception at runtime for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.
+On the other hand, `INSERT INTO` syntax throws an analysis exception when the ANSI mode enabled via `spark.sql.storeAssignmentPolicy=ANSI`.
Review comment:
Just a question, shall we mention `CTAS` together?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #27489: [SPARK-30703][SQL][DOCS]
Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585461206
**[Test build #118327 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/118327/testReport)** for PR 27489 at commit [`64b42b4`](https://github.com/apache/spark/commit/64b42b4cd919a15d7c3f85b3527a1a916e472558).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583793827
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585461739
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583792398
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/22848/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583300502
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583382751
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583304029
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583792398
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/22848/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #27489: [SPARK-30703][SQL][DOCS]
Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585465693
**[Test build #118327 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/118327/testReport)** for PR 27489 at commit [`64b42b4`](https://github.com/apache/spark/commit/64b42b4cd919a15d7c3f85b3527a1a916e472558).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585465812
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/118327/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583793834
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/118083/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on issue #27489: [SPARK-30703][SQL][DOCS]
Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583674918
also cc: @gengliangwang @cloud-fan
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r378566824
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflows by default.
+This means that in case an operation causes overflows, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
+On the other hand, Spark SQL returns null for decimal overflows.
+When `spark.sql.ansi.enabled` is set to `true` and an overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.
+
+{% highlight sql %}
+-- `spark.sql.ansi.enabled=true`
+SELECT 2147483647 + 1;
+
+ java.lang.ArithmeticException: integer overflow
+
+-- `spark.sql.ansi.enabled=false`
+SELECT 2147483647 + 1;
+
+ +----------------+
+ |(2147483647 + 1)|
+ +----------------+
+ | -2147483648|
+ +----------------+
+
+{% endhighlight %}
+
+### Type Conversion
+
+Spark SQL has three kinds of type conversions: explicit casting, type coercion, and store assignment casting.
+When `spark.sql.ansi.enabled` is set to `true`, explicit casting by `CAST` syntax throws a number-format exception at runtime for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.
Review comment:
updated.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583304035
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/118026/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r376741698
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflow by default.
+This means that in case an operation causes an overflow, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
+On the other hand, Spark SQL returns null for decimal overflow.
+When `spark.sql.ansi.enabled` is set to `true` and overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.
+
+{% highlight sql %}
+-- `spark.sql.ansi.enabled=true`
+SELECT 2147483647 + 1;
+
+ java.lang.ArithmeticException: integer overflow
+
+-- `spark.sql.ansi.enabled=false`
+SELECT 2147483647 + 1;
+
+ +----------------+
+ |(2147483647 + 1)|
+ +----------------+
+ | -2147483648|
+ +----------------+
+
+{% endhighlight %}
+
+### Type Conversion
+
+Spark SQL has three kinds of type conversions: explicit casting, type coercion, and store assignment casting.
+When `spark.sql.ansi.enabled` is set to `true`, explicit castings by `CAST` syntax throws a number-format exception at runtime for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.
+On the other hand, `INSERT INTO` syntax throws an analysis exception when the ANSI mode enabled via `spark.sql.storeAssignmentPolicy=ANSI`.
+
+Currently, the ANSI mode affects explicit casting and assignment casting only.
+In future releases, the behaviour of type coercion might change along with the other two type conversion rules.
+
+{% highlight sql %}
+-- Examples of explicit casting
+
+-- `spark.sql.ansi.enabled=true`
+SELECT CAST('a' AS INT);
+
+ java.lang.NumberFormatException: invalid input syntax for type numeric: a
+
+-- `spark.sql.ansi.enabled=false` (This is a legacy behaviour until Spark 2.x)
Review comment:
Ur, I see. I'll update.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583304035
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/118026/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583303920
**[Test build #118026 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/118026/testReport)** for PR 27489 at commit [`ed76c3e`](https://github.com/apache/spark/commit/ed76c3e7513434e8ca2f6abf51b2c48ee54dfa0b).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583378748
**[Test build #118034 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/118034/testReport)** for PR 27489 at commit [`a8681de`](https://github.com/apache/spark/commit/a8681de17a77664245a9ec398e1421adb93b5644).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on issue #27489: [SPARK-30703][SQL][DOCS]
Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585973707
Thanks, again, @gengliangwang and @dongjoon-hyun ~
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r376746028
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflow by default.
+This means that in case an operation causes an overflow, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
+On the other hand, Spark SQL returns null for decimal overflow.
+When `spark.sql.ansi.enabled` is set to `true` and overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.
+
+{% highlight sql %}
+-- `spark.sql.ansi.enabled=true`
+SELECT 2147483647 + 1;
+
+ java.lang.ArithmeticException: integer overflow
+
+-- `spark.sql.ansi.enabled=false`
+SELECT 2147483647 + 1;
+
+ +----------------+
+ |(2147483647 + 1)|
+ +----------------+
+ | -2147483648|
+ +----------------+
+
+{% endhighlight %}
+
+### Type Conversion
+
+Spark SQL has three kinds of type conversions: explicit casting, type coercion, and store assignment casting.
+When `spark.sql.ansi.enabled` is set to `true`, explicit castings by `CAST` syntax throws a number-format exception at runtime for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.
+On the other hand, `INSERT INTO` syntax throws an analysis exception when the ANSI mode enabled via `spark.sql.storeAssignmentPolicy=ANSI`.
+
+Currently, the ANSI mode affects explicit casting and assignment casting only.
+In future releases, the behaviour of type coercion might change along with the other two type conversion rules.
+
+{% highlight sql %}
+-- Examples of explicit casting
+
+-- `spark.sql.ansi.enabled=true`
+SELECT CAST('a' AS INT);
+
+ java.lang.NumberFormatException: invalid input syntax for type numeric: a
+
+-- `spark.sql.ansi.enabled=false` (This is a legacy behaviour until Spark 2.x)
Review comment:
Changed it to `This is a default behaviour`. I dropped the `in 3.0.0` phrase in the statement because to I thought update it in future releases (4.0, 5.0, ...) looks a bit annoying.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #27489: [SPARK-30703][SQL][DOCS]
Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583792804
**[Test build #118083 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/118083/testReport)** for PR 27489 at commit [`6b0d5d6`](https://github.com/apache/spark/commit/6b0d5d62dc407f5ba64dbc4594cec723bfa90daa).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r376759528
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflow by default.
+This means that in case an operation causes an overflow, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
+On the other hand, Spark SQL returns null for decimal overflow.
+When `spark.sql.ansi.enabled` is set to `true` and overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.
+
+{% highlight sql %}
+-- `spark.sql.ansi.enabled=true`
+SELECT 2147483647 + 1;
+
+ java.lang.ArithmeticException: integer overflow
+
+-- `spark.sql.ansi.enabled=false`
+SELECT 2147483647 + 1;
+
+ +----------------+
+ |(2147483647 + 1)|
+ +----------------+
+ | -2147483648|
+ +----------------+
+
+{% endhighlight %}
+
+### Type Conversion
+
+Spark SQL has three kinds of type conversions: explicit casting, type coercion, and store assignment casting.
+When `spark.sql.ansi.enabled` is set to `true`, explicit castings by `CAST` syntax throws a number-format exception at runtime for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.
+On the other hand, `INSERT INTO` syntax throws an analysis exception when the ANSI mode enabled via `spark.sql.storeAssignmentPolicy=ANSI`.
Review comment:
I've checked CTAS (CTAS cannot exist with schema definition);
```
scala> sql("create table t1 (v string)")
scala> sql("create table t2 (v int) as select * from t1")
org.apache.spark.sql.catalyst.parser.ParseException:
Operation not allowed: Schema may not be specified in a Create Table As Select (CTAS) statement(line 1, pos 0)
```
Any other concern?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on issue #27489: [SPARK-30703][SQL][DOCS]
Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585733392
Thanks for the check, @gengliangwang !
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585465812
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/118327/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585465801
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] kiszk commented on a change in pull request #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
kiszk commented on a change in pull request #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r376313165
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is enabled by default.
Review comment:
Is it better to write default value?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r380033235
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,127 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy` (See a table below for details).
+When `spark.sql.ansi.enabled` is set to `true`, Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules.
+
+<table class="table">
+<tr><th>Property Name</th><th>Default</th><th>Meaning</th></tr>
+<tr>
+ <td><code>spark.sql.ansi.enabled</code></td>
+ <td>false</td>
+ <td>
+ When true, Spark tries to conform to the ANSI SQL specification:
+ 1. Spark will throw a runtime exception if an overflow occurs in any operation on integral/decimal field.
+ 2. Spark will forbid using the reserved keywords of ANSI SQL as identifiers in the SQL parser.
+ </td>
+</tr>
+<tr>
+ <td><code>spark.sql.storeAssignmentPolicy</code></td>
+ <td>ANSI</td>
+ <td>
+ When inserting a value into a column with different data type, Spark will perform type coercion.
+ Currently, we support 3 policies for the type coercion rules: ANSI, legacy and strict. With ANSI policy,
+ Spark performs the type coercion as per ANSI SQL. In practice, the behavior is mostly the same as PostgreSQL.
+ It disallows certain unreasonable type conversions such as converting string to int or double to boolean.
+ With legacy policy, Spark allows the type coercion as long as it is a valid Cast, which is very loose.
+ e.g. converting string to int or double to boolean is allowed.
+ It is also the only behavior in Spark 2.x and it is compatible with Hive.
+ With strict policy, Spark doesn't allow any possible precision loss or data truncation in type coercion,
+ e.g. converting double to int or decimal to double is not allowed.
+ </td>
+</tr>
+</table>
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflows by default.
+This means that in case an operation causes overflows, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
Review comment:
Ah, yes. I'll fix later. (If other developers plan to open a PR for typo fixes, it would be helpful to include this fix as well.)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583379131
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/22799/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583378748
**[Test build #118034 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/118034/testReport)** for PR 27489 at commit [`a8681de`](https://github.com/apache/spark/commit/a8681de17a77664245a9ec398e1421adb93b5644).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583297883
WIP
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583382758
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/118034/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] kiszk commented on a change in pull request #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
kiszk commented on a change in pull request #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r376311138
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is enabled by default.
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflow by default.
+This means that in case an operation causes an overflow, the result is the same that the same operation returns in a Java/Scala program (eg. if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
Review comment:
nit: `eg.` -> `e.g.`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583300502
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on issue #27489: [SPARK-30703][SQL][DOCS]
Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583675462
I wrote a basic part of the ANSI mode. Please tell me if you have more we should explicitly describe here for the mode.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] dongjoon-hyun commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
dongjoon-hyun commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r376739344
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflow by default.
+This means that in case an operation causes an overflow, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
+On the other hand, Spark SQL returns null for decimal overflow.
+When `spark.sql.ansi.enabled` is set to `true` and overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.
+
+{% highlight sql %}
+-- `spark.sql.ansi.enabled=true`
+SELECT 2147483647 + 1;
+
+ java.lang.ArithmeticException: integer overflow
+
+-- `spark.sql.ansi.enabled=false`
+SELECT 2147483647 + 1;
+
+ +----------------+
+ |(2147483647 + 1)|
+ +----------------+
+ | -2147483648|
+ +----------------+
+
+{% endhighlight %}
+
+### Type Conversion
+
+Spark SQL has three kinds of type conversions: explicit casting, type coercion, and store assignment casting.
+When `spark.sql.ansi.enabled` is set to `true`, explicit castings by `CAST` syntax throws a number-format exception at runtime for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.
+On the other hand, `INSERT INTO` syntax throws an analysis exception when the ANSI mode enabled via `spark.sql.storeAssignmentPolicy=ANSI`.
+
+Currently, the ANSI mode affects explicit casting and assignment casting only.
+In future releases, the behaviour of type coercion might change along with the other two type conversion rules.
+
+{% highlight sql %}
+-- Examples of explicit casting
+
+-- `spark.sql.ansi.enabled=true`
+SELECT CAST('a' AS INT);
+
+ java.lang.NumberFormatException: invalid input syntax for type numeric: a
+
+-- `spark.sql.ansi.enabled=false` (This is a legacy behaviour until Spark 2.x)
Review comment:
`legacy` can mislead the users. We may need to point out again this is the default behavior in 3.0.0, too.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583300514
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/22791/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583793827
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] dongjoon-hyun commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
dongjoon-hyun commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r376739344
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflow by default.
+This means that in case an operation causes an overflow, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
+On the other hand, Spark SQL returns null for decimal overflow.
+When `spark.sql.ansi.enabled` is set to `true` and overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.
+
+{% highlight sql %}
+-- `spark.sql.ansi.enabled=true`
+SELECT 2147483647 + 1;
+
+ java.lang.ArithmeticException: integer overflow
+
+-- `spark.sql.ansi.enabled=false`
+SELECT 2147483647 + 1;
+
+ +----------------+
+ |(2147483647 + 1)|
+ +----------------+
+ | -2147483648|
+ +----------------+
+
+{% endhighlight %}
+
+### Type Conversion
+
+Spark SQL has three kinds of type conversions: explicit casting, type coercion, and store assignment casting.
+When `spark.sql.ansi.enabled` is set to `true`, explicit castings by `CAST` syntax throws a number-format exception at runtime for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.
+On the other hand, `INSERT INTO` syntax throws an analysis exception when the ANSI mode enabled via `spark.sql.storeAssignmentPolicy=ANSI`.
+
+Currently, the ANSI mode affects explicit casting and assignment casting only.
+In future releases, the behaviour of type coercion might change along with the other two type conversion rules.
+
+{% highlight sql %}
+-- Examples of explicit casting
+
+-- `spark.sql.ansi.enabled=true`
+SELECT CAST('a' AS INT);
+
+ java.lang.NumberFormatException: invalid input syntax for type numeric: a
+
+-- `spark.sql.ansi.enabled=false` (This is a legacy behaviour until Spark 2.x)
Review comment:
This can mislead the users. We may need to point out again this is the default behavior in 3.0.0, too.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583300514
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/22791/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r388643951
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,127 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy` (See a table below for details).
+When `spark.sql.ansi.enabled` is set to `true`, Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules.
+
+<table class="table">
+<tr><th>Property Name</th><th>Default</th><th>Meaning</th></tr>
+<tr>
+ <td><code>spark.sql.ansi.enabled</code></td>
+ <td>false</td>
+ <td>
+ When true, Spark tries to conform to the ANSI SQL specification:
+ 1. Spark will throw a runtime exception if an overflow occurs in any operation on integral/decimal field.
+ 2. Spark will forbid using the reserved keywords of ANSI SQL as identifiers in the SQL parser.
+ </td>
+</tr>
+<tr>
+ <td><code>spark.sql.storeAssignmentPolicy</code></td>
+ <td>ANSI</td>
+ <td>
+ When inserting a value into a column with different data type, Spark will perform type coercion.
+ Currently, we support 3 policies for the type coercion rules: ANSI, legacy and strict. With ANSI policy,
+ Spark performs the type coercion as per ANSI SQL. In practice, the behavior is mostly the same as PostgreSQL.
+ It disallows certain unreasonable type conversions such as converting string to int or double to boolean.
+ With legacy policy, Spark allows the type coercion as long as it is a valid Cast, which is very loose.
+ e.g. converting string to int or double to boolean is allowed.
+ It is also the only behavior in Spark 2.x and it is compatible with Hive.
+ With strict policy, Spark doesn't allow any possible precision loss or data truncation in type coercion,
+ e.g. converting double to int or decimal to double is not allowed.
+ </td>
+</tr>
+</table>
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflows by default.
+This means that in case an operation causes overflows, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
Review comment:
https://github.com/apache/spark/pull/27819
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583299932
**[Test build #118026 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/118026/testReport)** for PR 27489 at commit [`ed76c3e`](https://github.com/apache/spark/commit/ed76c3e7513434e8ca2f6abf51b2c48ee54dfa0b).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] HyukjinKwon commented on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
HyukjinKwon commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-586908266
Looks good. Thanks, @maropu.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583299932
**[Test build #118026 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/118026/testReport)** for PR 27489 at commit [`ed76c3e`](https://github.com/apache/spark/commit/ed76c3e7513434e8ca2f6abf51b2c48ee54dfa0b).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583792397
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585461746
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/23084/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] gengliangwang commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
gengliangwang commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r378452795
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
Review comment:
I am +1 with showing a table here, which can be more clear.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583379125
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583382758
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/118034/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] gengliangwang commented on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
gengliangwang commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585366797
@maropu Thanks for the work!
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585465801
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] dongjoon-hyun commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
dongjoon-hyun commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r376739233
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflow by default.
+This means that in case an operation causes an overflow, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
+On the other hand, Spark SQL returns null for decimal overflow.
+When `spark.sql.ansi.enabled` is set to `true` and overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.
Review comment:
`overflow` -> `an overflow`?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r376745648
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
Review comment:
Ah, I see. But, when I was writing this doc, I assumed the basic info about `spark.sql.storeAssignmentPolicy` would appear in the SQL Conf doc: https://github.com/apache/spark/pull/27459
Better to describe the two options in a tabular format here, too?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583792804
**[Test build #118083 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/118083/testReport)** for PR 27489 at commit [`6b0d5d6`](https://github.com/apache/spark/commit/6b0d5d62dc407f5ba64dbc4594cec723bfa90daa).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583382646
**[Test build #118034 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/118034/testReport)** for PR 27489 at commit [`a8681de`](https://github.com/apache/spark/commit/a8681de17a77664245a9ec398e1421adb93b5644).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r378533598
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
Review comment:
ok
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] gengliangwang closed pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
gengliangwang closed pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [WIP][SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583379125
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] dongjoon-hyun commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
dongjoon-hyun commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r376739104
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,87 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy`.
+When `spark.sql.ansi.enabled` is set to `true` (`false` by default), Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules and this setting is a default value.
Review comment:
Shall we summarize as a table like https://spark.apache.org/docs/3.0.0-preview2/configuration.html#application-properties ? That will be more easier to explain and to understand and to maintain(add new conf later or change the default value).
For example, the default value of `spark.sql.storeAssignmentPolicy` is not mentioned in this paragraph.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r380023202
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,127 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy` (See a table below for details).
+When `spark.sql.ansi.enabled` is set to `true`, Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules.
+
+<table class="table">
+<tr><th>Property Name</th><th>Default</th><th>Meaning</th></tr>
+<tr>
+ <td><code>spark.sql.ansi.enabled</code></td>
+ <td>false</td>
+ <td>
+ When true, Spark tries to conform to the ANSI SQL specification:
+ 1. Spark will throw a runtime exception if an overflow occurs in any operation on integral/decimal field.
+ 2. Spark will forbid using the reserved keywords of ANSI SQL as identifiers in the SQL parser.
+ </td>
+</tr>
+<tr>
+ <td><code>spark.sql.storeAssignmentPolicy</code></td>
+ <td>ANSI</td>
+ <td>
+ When inserting a value into a column with different data type, Spark will perform type coercion.
+ Currently, we support 3 policies for the type coercion rules: ANSI, legacy and strict. With ANSI policy,
+ Spark performs the type coercion as per ANSI SQL. In practice, the behavior is mostly the same as PostgreSQL.
+ It disallows certain unreasonable type conversions such as converting string to int or double to boolean.
+ With legacy policy, Spark allows the type coercion as long as it is a valid Cast, which is very loose.
+ e.g. converting string to int or double to boolean is allowed.
+ It is also the only behavior in Spark 2.x and it is compatible with Hive.
+ With strict policy, Spark doesn't allow any possible precision loss or data truncation in type coercion,
Review comment:
I'm wondering if we should remove the STRICT mode. It's not ANSI compliant and no other SQL system has this behavior.
cc @rdblue @brkyvz @rxin
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#discussion_r380022536
##########
File path: docs/sql-ref-ansi-compliance.md
##########
@@ -19,6 +19,127 @@ license: |
limitations under the License.
---
+Spark SQL has two options to comply with the SQL standard: `spark.sql.ansi.enabled` and `spark.sql.storeAssignmentPolicy` (See a table below for details).
+When `spark.sql.ansi.enabled` is set to `true`, Spark SQL follows the standard in basic behaviours (e.g., arithmetic operations, type conversion, and SQL parsing).
+Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table.
+The casting behaviours are defined as store assignment rules in the standard.
+When `spark.sql.storeAssignmentPolicy` is set to `ANSI`, Spark SQL complies with the ANSI store assignment rules.
+
+<table class="table">
+<tr><th>Property Name</th><th>Default</th><th>Meaning</th></tr>
+<tr>
+ <td><code>spark.sql.ansi.enabled</code></td>
+ <td>false</td>
+ <td>
+ When true, Spark tries to conform to the ANSI SQL specification:
+ 1. Spark will throw a runtime exception if an overflow occurs in any operation on integral/decimal field.
+ 2. Spark will forbid using the reserved keywords of ANSI SQL as identifiers in the SQL parser.
+ </td>
+</tr>
+<tr>
+ <td><code>spark.sql.storeAssignmentPolicy</code></td>
+ <td>ANSI</td>
+ <td>
+ When inserting a value into a column with different data type, Spark will perform type coercion.
+ Currently, we support 3 policies for the type coercion rules: ANSI, legacy and strict. With ANSI policy,
+ Spark performs the type coercion as per ANSI SQL. In practice, the behavior is mostly the same as PostgreSQL.
+ It disallows certain unreasonable type conversions such as converting string to int or double to boolean.
+ With legacy policy, Spark allows the type coercion as long as it is a valid Cast, which is very loose.
+ e.g. converting string to int or double to boolean is allowed.
+ It is also the only behavior in Spark 2.x and it is compatible with Hive.
+ With strict policy, Spark doesn't allow any possible precision loss or data truncation in type coercion,
+ e.g. converting double to int or decimal to double is not allowed.
+ </td>
+</tr>
+</table>
+
+The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.
+
+### Arithmetic Operations
+
+In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflows by default.
+This means that in case an operation causes overflows, the result is the same that the same operation returns in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number).
Review comment:
`the result is the same that` -> `the result is the same with`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] dongjoon-hyun commented on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
dongjoon-hyun commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585936456
Thank you, @maropu and @gengliangwang !
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585461746
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/23084/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-583792397
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on issue #27489:
[SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on issue #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode
URL: https://github.com/apache/spark/pull/27489#issuecomment-585461206
**[Test build #118327 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/118327/testReport)** for PR 27489 at commit [`64b42b4`](https://github.com/apache/spark/commit/64b42b4cd919a15d7c3f85b3527a1a916e472558).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org