This is an automated email from the ASF dual-hosted git repository. maxgekk pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push: new a6576de [SPARK-34755][SQL] Support the utils for transform number format a6576de is described below commit a6576de9719204f6a87d2fc5e2e344bd1d0017a3 Author: Jiaan Geng <belie...@163.com> AuthorDate: Wed Dec 29 11:07:06 2021 +0300 [SPARK-34755][SQL] Support the utils for transform number format ### What changes were proposed in this pull request? Data Type Formatting Functions: `to_number` and `to_char` is very useful. The implement has many different between `Postgresql` ,`Oracle` and `Phoenix`. So, this PR follows the implement of `to_number` in `Oracle` that give a strict parameter verification. So, this PR follows the implement of `to_number` in `Phoenix` that uses BigDecimal. This PR support the patterns for numeric formatting as follows: Pattern | Description -- | -- 9 | Value with the specified number of digits 0 | Value with leading zeros . (period) | Decimal point , (comma) | Group (thousand) separator S | Sign anchored to number (uses locale) $ | a value with a leading dollar sign D | Decimal point (uses locale) G | Group separator (uses locale) There are some mainstream database support the syntax. **PostgreSQL:** https://www.postgresql.org/docs/12/functions-formatting.html **Oracle:** https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TO_NUMBER.html#GUID-D4807212-AFD7-48A7-9AED-BEC3E8809866 **Vertica** https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Formatting/TO_NUMBER.htm?tocpath=SQL%20Reference%20Manual%7CSQL%20Functions%7CFormatting%20Functions%7C_____7 **Redshift** https://docs.aws.amazon.com/redshift/latest/dg/r_TO_NUMBER.html **DB2** https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_1544.htm **Teradata** https://docs.teradata.com/r/kmuOwjp1zEYg98JsB8fu_A/TH2cDXBn6tala29S536nqg **Snowflake:** https://docs.snowflake.net/manuals/sql-reference/functions/to_decimal.html **Exasol** https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/to_number.htm#TO_NUMBER **Phoenix** http://phoenix.incubator.apache.org/language/functions.html#to_number **Singlestore** https://docs.singlestore.com/v7.3/reference/sql-reference/numeric-functions/to-number/ **Intersystems** https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_TONUMBER Note: Based on discussion offline with cloud-fan ten months ago, this PR only implement the utils for transform number format. Because the utils should be review better. ### Why are the changes needed? `to_number` and `to_char` are very useful for formatted currency to number conversion. ### Does this PR introduce _any_ user-facing change? No. ### How was this patch tested? Jenkins test Closes #31847 from beliefer/SPARK-34755. Lead-authored-by: Jiaan Geng <belie...@163.com> Co-authored-by: gengjiaan <gengji...@360.cn> Signed-off-by: Max Gekk <max.g...@gmail.com> --- .../spark/sql/catalyst/util/NumberUtils.scala | 189 ++++++++++++ .../spark/sql/errors/QueryCompilationErrors.scala | 8 + .../spark/sql/errors/QueryExecutionErrors.scala | 6 + .../spark/sql/catalyst/util/NumberUtilsSuite.scala | 317 +++++++++++++++++++++ 4 files changed, 520 insertions(+) diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/NumberUtils.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/NumberUtils.scala new file mode 100644 index 0000000..6efde2a --- /dev/null +++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/NumberUtils.scala @@ -0,0 +1,189 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.spark.sql.catalyst.util + +import java.math.BigDecimal +import java.text.{DecimalFormat, NumberFormat, ParsePosition} +import java.util.Locale + +import org.apache.spark.sql.errors.{QueryCompilationErrors, QueryExecutionErrors} +import org.apache.spark.sql.types.Decimal +import org.apache.spark.unsafe.types.UTF8String + +object NumberUtils { + + private val pointSign = '.' + private val letterPointSign = 'D' + private val commaSign = ',' + private val letterCommaSign = 'G' + private val minusSign = '-' + private val letterMinusSign = 'S' + private val dollarSign = '$' + + private val commaSignStr = commaSign.toString + + private def normalize(format: String): String = { + var notFindDecimalPoint = true + val normalizedFormat = format.toUpperCase(Locale.ROOT).map { + case '9' if notFindDecimalPoint => '#' + case '9' if !notFindDecimalPoint => '0' + case `letterPointSign` => + notFindDecimalPoint = false + pointSign + case `letterCommaSign` => commaSign + case `letterMinusSign` => minusSign + case `pointSign` => + notFindDecimalPoint = false + pointSign + case other => other + } + // If the comma is at the beginning or end of number format, then DecimalFormat will be invalid. + // For example, "##,###," or ",###,###" for DecimalFormat is invalid, so we must use "##,###" + // or "###,###". + normalizedFormat.stripPrefix(commaSignStr).stripSuffix(commaSignStr) + } + + private def isSign(c: Char): Boolean = { + Set(pointSign, commaSign, minusSign, dollarSign).contains(c) + } + + private def transform(format: String): String = { + if (format.contains(minusSign)) { + val positiveFormatString = format.replaceAll("-", "") + s"$positiveFormatString;$format" + } else { + format + } + } + + private def check(normalizedFormat: String, numberFormat: String) = { + def invalidSignPosition(format: String, c: Char): Boolean = { + val signIndex = format.indexOf(c) + signIndex > 0 && signIndex < format.length - 1 + } + + if (normalizedFormat.count(_ == pointSign) > 1) { + throw QueryCompilationErrors.multipleSignInNumberFormatError( + s"'$letterPointSign' or '$pointSign'", numberFormat) + } else if (normalizedFormat.count(_ == minusSign) > 1) { + throw QueryCompilationErrors.multipleSignInNumberFormatError( + s"'$letterMinusSign' or '$minusSign'", numberFormat) + } else if (normalizedFormat.count(_ == dollarSign) > 1) { + throw QueryCompilationErrors.multipleSignInNumberFormatError(s"'$dollarSign'", numberFormat) + } else if (invalidSignPosition(normalizedFormat, minusSign)) { + throw QueryCompilationErrors.nonFistOrLastCharInNumberFormatError( + s"'$letterMinusSign' or '$minusSign'", numberFormat) + } else if (invalidSignPosition(normalizedFormat, dollarSign)) { + throw QueryCompilationErrors.nonFistOrLastCharInNumberFormatError( + s"'$dollarSign'", numberFormat) + } + } + + /** + * Convert string to numeric based on the given number format. + * The format can consist of the following characters: + * '9': digit position (can be dropped if insignificant) + * '0': digit position (will not be dropped, even if insignificant) + * '.': decimal point (only allowed once) + * ',': group (thousands) separator + * 'S': sign anchored to number (uses locale) + * 'D': decimal point (uses locale) + * 'G': group separator (uses locale) + * '$': specifies that the input value has a leading $ (Dollar) sign. + * + * @param input the string need to converted + * @param numberFormat the given number format + * @return decimal obtained from string parsing + */ + def parse(input: UTF8String, numberFormat: String): Decimal = { + val normalizedFormat = normalize(numberFormat) + check(normalizedFormat, numberFormat) + + val precision = normalizedFormat.filterNot(isSign).length + val formatSplits = normalizedFormat.split(pointSign) + val scale = if (formatSplits.length == 1) { + 0 + } else { + formatSplits(1).filterNot(isSign).length + } + val transformedFormat = transform(normalizedFormat) + val numberFormatInstance = NumberFormat.getInstance() + val numberDecimalFormat = numberFormatInstance.asInstanceOf[DecimalFormat] + numberDecimalFormat.setParseBigDecimal(true) + numberDecimalFormat.applyPattern(transformedFormat) + val inputStr = input.toString.trim + val inputSplits = inputStr.split(pointSign) + if (inputSplits.length == 1) { + if (inputStr.filterNot(isSign).length > precision - scale) { + throw QueryExecutionErrors.invalidNumberFormatError(numberFormat) + } + } else if (inputSplits(0).filterNot(isSign).length > precision - scale || + inputSplits(1).filterNot(isSign).length > scale) { + throw QueryExecutionErrors.invalidNumberFormatError(numberFormat) + } + val number = numberDecimalFormat.parse(inputStr, new ParsePosition(0)) + Decimal(number.asInstanceOf[BigDecimal]) + } + + /** + * Convert numeric to string based on the given number format. + * The format can consist of the following characters: + * '9': digit position (can be dropped if insignificant) + * '0': digit position (will not be dropped, even if insignificant) + * '.': decimal point (only allowed once) + * ',': group (thousands) separator + * 'S': sign anchored to number (uses locale) + * 'D': decimal point (uses locale) + * 'G': group separator (uses locale) + * '$': specifies that the input value has a leading $ (Dollar) sign. + * + * @param input the decimal to format + * @param numberFormat the format string + * @return The string after formatting input decimal + */ + def format(input: Decimal, numberFormat: String): String = { + val normalizedFormat = normalize(numberFormat) + check(normalizedFormat, numberFormat) + + val transformedFormat = transform(normalizedFormat) + val bigDecimal = input.toJavaBigDecimal + val decimalPlainStr = bigDecimal.toPlainString + if (decimalPlainStr.length > transformedFormat.length) { + transformedFormat.replaceAll("0", "#") + } else { + val decimalFormat = new DecimalFormat(transformedFormat) + var resultStr = decimalFormat.format(bigDecimal) + // Since we trimmed the comma at the beginning or end of number format in function + // `normalize`, we restore the comma to the result here. + // For example, if the specified number format is "99,999," or ",999,999", function + // `normalize` normalize them to "##,###" or "###,###". + // new DecimalFormat("##,###").parse(12454) and new DecimalFormat("###,###").parse(124546) + // will return "12,454" and "124,546" respectively. So we add ',' at the end and head of + // the result, then the final output are "12,454," or ",124,546". + if (numberFormat.last == commaSign || numberFormat.last == letterCommaSign) { + resultStr = resultStr + commaSign + } + if (numberFormat.charAt(0) == commaSign || numberFormat.charAt(0) == letterCommaSign) { + resultStr = commaSign + resultStr + } + + resultStr + } + } + +} diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryCompilationErrors.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryCompilationErrors.scala index 79de8b6..300ba03 100644 --- a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryCompilationErrors.scala +++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryCompilationErrors.scala @@ -2365,4 +2365,12 @@ object QueryCompilationErrors { def tableNotSupportTimeTravelError(tableName: Identifier): UnsupportedOperationException = { new UnsupportedOperationException(s"Table $tableName does not support time travel.") } + + def multipleSignInNumberFormatError(message: String, numberFormat: String): Throwable = { + new AnalysisException(s"Multiple $message in '$numberFormat'") + } + + def nonFistOrLastCharInNumberFormatError(message: String, numberFormat: String): Throwable = { + new AnalysisException(s"$message must be the first or last char in '$numberFormat'") + } } diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala index 6f0ed23..eb6e814 100644 --- a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala +++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala @@ -1920,4 +1920,10 @@ object QueryExecutionErrors { s". To solve this try to set $maxDynamicPartitionsKey" + s" to at least $numWrittenParts.") } + + def invalidNumberFormatError(format: String): Throwable = { + new IllegalArgumentException( + s"Format '$format' used for parsing string to number or " + + "formatting number to string is invalid") + } } diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/NumberUtilsSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/NumberUtilsSuite.scala new file mode 100644 index 0000000..66a17dc --- /dev/null +++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/NumberUtilsSuite.scala @@ -0,0 +1,317 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.spark.sql.catalyst.util + +import org.apache.spark.SparkFunSuite +import org.apache.spark.sql.AnalysisException +import org.apache.spark.sql.catalyst.util.NumberUtils.{format, parse} +import org.apache.spark.sql.types.Decimal +import org.apache.spark.unsafe.types.UTF8String + +class NumberUtilsSuite extends SparkFunSuite { + + private def failParseWithInvalidInput( + input: UTF8String, numberFormat: String, errorMsg: String): Unit = { + val e = intercept[IllegalArgumentException](parse(input, numberFormat)) + assert(e.getMessage.contains(errorMsg)) + } + + private def failParseWithAnalysisException( + input: UTF8String, numberFormat: String, errorMsg: String): Unit = { + val e = intercept[AnalysisException](parse(input, numberFormat)) + assert(e.getMessage.contains(errorMsg)) + } + + private def failFormatWithAnalysisException( + input: Decimal, numberFormat: String, errorMsg: String): Unit = { + val e = intercept[AnalysisException](format(input, numberFormat)) + assert(e.getMessage.contains(errorMsg)) + } + + test("parse") { + failParseWithInvalidInput(UTF8String.fromString("454"), "", + "Format '' used for parsing string to number or formatting number to string is invalid") + + // Test '9' and '0' + failParseWithInvalidInput(UTF8String.fromString("454"), "9", + "Format '9' used for parsing string to number or formatting number to string is invalid") + failParseWithInvalidInput(UTF8String.fromString("454"), "99", + "Format '99' used for parsing string to number or formatting number to string is invalid") + + Seq( + ("454", "999") -> Decimal(454), + ("054", "999") -> Decimal(54), + ("404", "999") -> Decimal(404), + ("450", "999") -> Decimal(450), + ("454", "9999") -> Decimal(454), + ("054", "9999") -> Decimal(54), + ("404", "9999") -> Decimal(404), + ("450", "9999") -> Decimal(450) + ).foreach { case ((str, format), expected) => + assert(parse(UTF8String.fromString(str), format) === expected) + } + + failParseWithInvalidInput(UTF8String.fromString("454"), "0", + "Format '0' used for parsing string to number or formatting number to string is invalid") + failParseWithInvalidInput(UTF8String.fromString("454"), "00", + "Format '00' used for parsing string to number or formatting number to string is invalid") + + Seq( + ("454", "000") -> Decimal(454), + ("054", "000") -> Decimal(54), + ("404", "000") -> Decimal(404), + ("450", "000") -> Decimal(450), + ("454", "0000") -> Decimal(454), + ("054", "0000") -> Decimal(54), + ("404", "0000") -> Decimal(404), + ("450", "0000") -> Decimal(450) + ).foreach { case ((str, format), expected) => + assert(parse(UTF8String.fromString(str), format) === expected) + } + + // Test '.' and 'D' + failParseWithInvalidInput(UTF8String.fromString("454.2"), "999", + "Format '999' used for parsing string to number or formatting number to string is invalid") + failParseWithInvalidInput(UTF8String.fromString("454.23"), "999.9", + "Format '999.9' used for parsing string to number or formatting number to string is invalid") + + Seq( + ("454.2", "999.9") -> Decimal(454.2), + ("454.2", "000.0") -> Decimal(454.2), + ("454.2", "999D9") -> Decimal(454.2), + ("454.2", "000D0") -> Decimal(454.2), + ("454.23", "999.99") -> Decimal(454.23), + ("454.23", "000.00") -> Decimal(454.23), + ("454.23", "999D99") -> Decimal(454.23), + ("454.23", "000D00") -> Decimal(454.23), + ("454.0", "999.9") -> Decimal(454), + ("454.0", "000.0") -> Decimal(454), + ("454.0", "999D9") -> Decimal(454), + ("454.0", "000D0") -> Decimal(454), + ("454.00", "999.99") -> Decimal(454), + ("454.00", "000.00") -> Decimal(454), + ("454.00", "999D99") -> Decimal(454), + ("454.00", "000D00") -> Decimal(454), + (".4542", ".9999") -> Decimal(0.4542), + (".4542", ".0000") -> Decimal(0.4542), + (".4542", "D9999") -> Decimal(0.4542), + (".4542", "D0000") -> Decimal(0.4542), + ("4542.", "9999.") -> Decimal(4542), + ("4542.", "0000.") -> Decimal(4542), + ("4542.", "9999D") -> Decimal(4542), + ("4542.", "0000D") -> Decimal(4542) + ).foreach { case ((str, format), expected) => + assert(parse(UTF8String.fromString(str), format) === expected) + } + + failParseWithAnalysisException(UTF8String.fromString("454.3.2"), "999.9.9", + "Multiple 'D' or '.' in '999.9.9'") + failParseWithAnalysisException(UTF8String.fromString("454.3.2"), "999D9D9", + "Multiple 'D' or '.' in '999D9D9'") + failParseWithAnalysisException(UTF8String.fromString("454.3.2"), "999.9D9", + "Multiple 'D' or '.' in '999.9D9'") + failParseWithAnalysisException(UTF8String.fromString("454.3.2"), "999D9.9", + "Multiple 'D' or '.' in '999D9.9'") + + // Test ',' and 'G' + Seq( + ("12,454", "99,999") -> Decimal(12454), + ("12,454", "00,000") -> Decimal(12454), + ("12,454", "99G999") -> Decimal(12454), + ("12,454", "00G000") -> Decimal(12454), + ("12,454,367", "99,999,999") -> Decimal(12454367), + ("12,454,367", "00,000,000") -> Decimal(12454367), + ("12,454,367", "99G999G999") -> Decimal(12454367), + ("12,454,367", "00G000G000") -> Decimal(12454367), + ("12,454,", "99,999,") -> Decimal(12454), + ("12,454,", "00,000,") -> Decimal(12454), + ("12,454,", "99G999G") -> Decimal(12454), + ("12,454,", "00G000G") -> Decimal(12454), + (",454,367", ",999,999") -> Decimal(454367), + (",454,367", ",000,000") -> Decimal(454367), + (",454,367", "G999G999") -> Decimal(454367), + (",454,367", "G000G000") -> Decimal(454367) + ).foreach { case ((str, format), expected) => + assert(parse(UTF8String.fromString(str), format) === expected) + } + + // Test '$' + Seq( + ("$78.12", "$99.99") -> Decimal(78.12), + ("$78.12", "$00.00") -> Decimal(78.12), + ("78.12$", "99.99$") -> Decimal(78.12), + ("78.12$", "00.00$") -> Decimal(78.12) + ).foreach { case ((str, format), expected) => + assert(parse(UTF8String.fromString(str), format) === expected) + } + + failParseWithAnalysisException(UTF8String.fromString("78$.12"), "99$.99", + "'$' must be the first or last char in '99$.99'") + failParseWithAnalysisException(UTF8String.fromString("$78.12$"), "$99.99$", + "Multiple '$' in '$99.99$'") + + // Test '-' and 'S' + Seq( + ("454-", "999-") -> Decimal(-454), + ("454-", "999S") -> Decimal(-454), + ("-454", "-999") -> Decimal(-454), + ("-454", "S999") -> Decimal(-454), + ("454-", "000-") -> Decimal(-454), + ("454-", "000S") -> Decimal(-454), + ("-454", "-000") -> Decimal(-454), + ("-454", "S000") -> Decimal(-454), + ("12,454.8-", "99G999D9S") -> Decimal(-12454.8), + ("00,454.8-", "99G999.9S") -> Decimal(-454.8) + ).foreach { case ((str, format), expected) => + assert(parse(UTF8String.fromString(str), format) === expected) + } + + failParseWithAnalysisException(UTF8String.fromString("4-54"), "9S99", + "'S' or '-' must be the first or last char in '9S99'") + failParseWithAnalysisException(UTF8String.fromString("4-54"), "9-99", + "'S' or '-' must be the first or last char in '9-99'") + failParseWithAnalysisException(UTF8String.fromString("454.3--"), "999D9SS", + "Multiple 'S' or '-' in '999D9SS'") + } + + test("format") { + assert(format(Decimal(454), "") === "") + + // Test '9' and '0' + Seq( + (Decimal(454), "9") -> "#", + (Decimal(454), "99") -> "##", + (Decimal(454), "999") -> "454", + (Decimal(54), "999") -> "54", + (Decimal(404), "999") -> "404", + (Decimal(450), "999") -> "450", + (Decimal(454), "9999") -> "454", + (Decimal(54), "9999") -> "54", + (Decimal(404), "9999") -> "404", + (Decimal(450), "9999") -> "450", + (Decimal(454), "0") -> "#", + (Decimal(454), "00") -> "##", + (Decimal(454), "000") -> "454", + (Decimal(54), "000") -> "054", + (Decimal(404), "000") -> "404", + (Decimal(450), "000") -> "450", + (Decimal(454), "0000") -> "0454", + (Decimal(54), "0000") -> "0054", + (Decimal(404), "0000") -> "0404", + (Decimal(450), "0000") -> "0450" + ).foreach { case ((decimal, str), expected) => + assert(format(decimal, str) === expected) + } + + // Test '.' and 'D' + Seq( + (Decimal(454.2), "999.9") -> "454.2", + (Decimal(454.2), "000.0") -> "454.2", + (Decimal(454.2), "999D9") -> "454.2", + (Decimal(454.2), "000D0") -> "454.2", + (Decimal(454), "999.9") -> "454.0", + (Decimal(454), "000.0") -> "454.0", + (Decimal(454), "999D9") -> "454.0", + (Decimal(454), "000D0") -> "454.0", + (Decimal(454), "999.99") -> "454.00", + (Decimal(454), "000.00") -> "454.00", + (Decimal(454), "999D99") -> "454.00", + (Decimal(454), "000D00") -> "454.00", + (Decimal(0.4542), ".9999") -> ".####", + (Decimal(0.4542), ".0000") -> ".####", + (Decimal(0.4542), "D9999") -> ".####", + (Decimal(0.4542), "D0000") -> ".####", + (Decimal(4542), "9999.") -> "4542.", + (Decimal(4542), "0000.") -> "4542.", + (Decimal(4542), "9999D") -> "4542.", + (Decimal(4542), "0000D") -> "4542." + ).foreach { case ((decimal, str), expected) => + assert(format(decimal, str) === expected) + } + + failFormatWithAnalysisException(Decimal(454.32), "999.9.9", + "Multiple 'D' or '.' in '999.9.9'") + failFormatWithAnalysisException(Decimal(454.32), "999D9D9", + "Multiple 'D' or '.' in '999D9D9'") + failFormatWithAnalysisException(Decimal(454.32), "999.9D9", + "Multiple 'D' or '.' in '999.9D9'") + failFormatWithAnalysisException(Decimal(454.32), "999D9.9", + "Multiple 'D' or '.' in '999D9.9'") + + // Test ',' and 'G' + Seq( + (Decimal(12454), "99,999") -> "12,454", + (Decimal(12454), "00,000") -> "12,454", + (Decimal(12454), "99G999") -> "12,454", + (Decimal(12454), "00G000") -> "12,454", + (Decimal(12454367), "99,999,999") -> "12,454,367", + (Decimal(12454367), "00,000,000") -> "12,454,367", + (Decimal(12454367), "99G999G999") -> "12,454,367", + (Decimal(12454367), "00G000G000") -> "12,454,367", + (Decimal(12454), "99,999,") -> "12,454,", + (Decimal(12454), "00,000,") -> "12,454,", + (Decimal(12454), "99G999G") -> "12,454,", + (Decimal(12454), "00G000G") -> "12,454,", + (Decimal(454367), ",999,999") -> ",454,367", + (Decimal(454367), ",000,000") -> ",454,367", + (Decimal(454367), "G999G999") -> ",454,367", + (Decimal(454367), "G000G000") -> ",454,367" + ).foreach { case ((decimal, str), expected) => + assert(format(decimal, str) === expected) + } + + // Test '$' + Seq( + (Decimal(78.12), "$99.99") -> "$78.12", + (Decimal(78.12), "$00.00") -> "$78.12", + (Decimal(78.12), "99.99$") -> "78.12$", + (Decimal(78.12), "00.00$") -> "78.12$" + ).foreach { case ((decimal, str), expected) => + assert(format(decimal, str) === expected) + } + + failFormatWithAnalysisException(Decimal(78.12), "99$.99", + "'$' must be the first or last char in '99$.99'") + failFormatWithAnalysisException(Decimal(78.12), "$99.99$", + "Multiple '$' in '$99.99$'") + + // Test '-' and 'S' + Seq( + (Decimal(-454), "999-") -> "454-", + (Decimal(-454), "999S") -> "454-", + (Decimal(-454), "-999") -> "-454", + (Decimal(-454), "S999") -> "-454", + (Decimal(-454), "000-") -> "454-", + (Decimal(-454), "000S") -> "454-", + (Decimal(-454), "-000") -> "-454", + (Decimal(-454), "S000") -> "-454", + (Decimal(-12454.8), "99G999D9S") -> "12,454.8-", + (Decimal(-454.8), "99G999.9S") -> "454.8-" + ).foreach { case ((decimal, str), expected) => + assert(format(decimal, str) === expected) + } + + failFormatWithAnalysisException(Decimal(-454), "9S99", + "'S' or '-' must be the first or last char in '9S99'") + failFormatWithAnalysisException(Decimal(-454), "9-99", + "'S' or '-' must be the first or last char in '9-99'") + failFormatWithAnalysisException(Decimal(-454.3), "999D9SS", + "Multiple 'S' or '-' in '999D9SS'") + } + +} --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org