From reviews-return-1041028-archive-asf-public=cust-asf.ponee.io@spark.apache.org Mon Feb 17 07:49:13 2020 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [207.244.88.153]) by mx-eu-01.ponee.io (Postfix) with SMTP id AADD8180636 for ; Mon, 17 Feb 2020 08:49:12 +0100 (CET) Received: (qmail 70399 invoked by uid 500); 17 Feb 2020 07:49:12 -0000 Mailing-List: contact reviews-help@spark.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Delivered-To: mailing list reviews@spark.apache.org Received: (qmail 70378 invoked by uid 99); 17 Feb 2020 07:49:12 -0000 Received: from ec2-52-202-80-70.compute-1.amazonaws.com (HELO gitbox.apache.org) (52.202.80.70) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 17 Feb 2020 07:49:12 +0000 From: GitBox To: reviews@spark.apache.org Subject: [GitHub] [spark] cloud-fan commented on a change in pull request #27489: [SPARK-30703][SQL][DOCS] Add a document for the ANSI mode Message-ID: <158192575199.23373.2357264442241742271.gitbox@gitbox.apache.org> References: In-Reply-To: Date: Mon, 17 Feb 2020 07:49:11 -0000 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit 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. + + + + + + + + + + + +
Property NameDefaultMeaning
spark.sql.ansi.enabledfalse + 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. +
spark.sql.storeAssignmentPolicyANSI + 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