This is an automated email from the ASF dual-hosted git repository.
tanner pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new aa5397bed4 [CALCITE-5821] Add FORMAT_NUMBER function (enabled in Hive
and Spark library)
aa5397bed4 is described below
commit aa5397bed42313865d3a43908427ad5f12b27ef3
Author: Runkang He <[email protected]>
AuthorDate: Sun Jul 9 22:27:00 2023 +0800
[CALCITE-5821] Add FORMAT_NUMBER function (enabled in Hive and Spark
library)
---
.../calcite/adapter/enumerable/RexImpTable.java | 2 +
.../apache/calcite/runtime/CalciteResource.java | 3 +
.../org/apache/calcite/runtime/SqlFunctions.java | 56 ++++++++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 10 +++
.../org/apache/calcite/sql/type/OperandTypes.java | 3 +
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../calcite/runtime/CalciteResource.properties | 1 +
site/_docs/reference.md | 2 +
.../org/apache/calcite/test/SqlOperatorTest.java | 99 ++++++++++++++++++++++
9 files changed, 177 insertions(+)
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index b4a5cb8ed9..097da9bbc1 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -175,6 +175,7 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.EXTRACT_XML;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FLOOR_BIG_QUERY;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_DATE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_DATETIME;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_NUMBER;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_TIME;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_TIMESTAMP;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FROM_BASE32;
@@ -521,6 +522,7 @@ public class RexImpTable {
defineMethod(SHA256, BuiltInMethod.SHA256.method, NullPolicy.STRICT);
defineMethod(SHA512, BuiltInMethod.SHA512.method, NullPolicy.STRICT);
defineMethod(SUBSTRING, BuiltInMethod.SUBSTRING.method,
NullPolicy.STRICT);
+ defineMethod(FORMAT_NUMBER, BuiltInMethod.FORMAT_NUMBER.method,
NullPolicy.STRICT);
defineMethod(LEFT, BuiltInMethod.LEFT.method, NullPolicy.ANY);
defineMethod(RIGHT, BuiltInMethod.RIGHT.method, NullPolicy.ANY);
defineMethod(LPAD, BuiltInMethod.LPAD.method, NullPolicy.STRICT);
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index 9456e3aab9..230f2ea0ca 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -903,6 +903,9 @@ public interface CalciteResource {
@BaseMessage("Substring error: negative substring length not allowed")
ExInst<CalciteException> illegalNegativeSubstringLength();
+ @BaseMessage("Illegal arguments for 'FORMAT_NUMBER' function: negative
decimal value not allowed")
+ ExInst<CalciteException> illegalNegativeDecimalValue();
+
@BaseMessage("Illegal arguments: The length of the keys array {0,number,#}
is not equal to the length of the values array {1,number,#} in MAP_FROM_ARRAYS
function")
ExInst<CalciteException> illegalArgumentsInMapFromArraysFunc(int arg0, int
arg1);
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index c64b70ad18..b6a7e1be0b 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -886,6 +886,62 @@ public class SqlFunctions {
return c.substring(s0, e0);
}
+ /** SQL FORMAT_NUMBER(value, decimalOrFormat) function. */
+ public static String formatNumber(long value, int decimalVal) {
+ DecimalFormat numberFormat = getNumberFormat(decimalVal);
+ return numberFormat.format(value);
+ }
+
+ public static String formatNumber(double value, int decimalVal) {
+ DecimalFormat numberFormat = getNumberFormat(decimalVal);
+ return numberFormat.format(value);
+ }
+
+ public static String formatNumber(BigDecimal value, int decimalVal) {
+ DecimalFormat numberFormat = getNumberFormat(decimalVal);
+ return numberFormat.format(value);
+ }
+
+ public static String formatNumber(long value, String format) {
+ DecimalFormat numberFormat = getNumberFormat(format);
+ return numberFormat.format(value);
+ }
+
+ public static String formatNumber(double value, String format) {
+ DecimalFormat numberFormat = getNumberFormat(format);
+ return numberFormat.format(value);
+ }
+
+ public static String formatNumber(BigDecimal value, String format) {
+ DecimalFormat numberFormat = getNumberFormat(format);
+ return numberFormat.format(value);
+ }
+
+ public static String getFormatPattern(int decimalVal) {
+ StringBuilder pattern = new StringBuilder();
+ pattern.append("#,###,###,###,###,###,##0");
+
+ if (decimalVal > 0) {
+ pattern.append(".");
+ for (int i = 0; i < decimalVal; i++) {
+ pattern.append("0");
+ }
+ }
+ return pattern.toString();
+ }
+
+ private static DecimalFormat getNumberFormat(String pattern) {
+ return NumberUtil.decimalFormat(pattern);
+ }
+
+ private static DecimalFormat getNumberFormat(int decimalVal) {
+ if (decimalVal < 0) {
+ throw RESOURCE.illegalNegativeDecimalValue().ex();
+ }
+ String pattern = getFormatPattern(decimalVal);
+ return getNumberFormat(pattern);
+ }
+
/** SQL UPPER(string) function. */
public static String upper(String s) {
return s.toUpperCase(Locale.ROOT);
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index 21503e4b73..badd4aae9c 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -1400,6 +1400,16 @@ public abstract class SqlLibraryOperators {
OperandTypes.BINARY,
SqlFunctionCategory.STRING);
+ /** The "FORMAT_NUMBER(value, decimalOrFormat)" function. */
+ @LibraryOperator(libraries = {HIVE, SPARK})
+ public static final SqlFunction FORMAT_NUMBER =
+ SqlBasicFunction.create("FORMAT_NUMBER",
+ ReturnTypes.VARCHAR_NULLABLE,
+ OperandTypes.or(
+ OperandTypes.NUMERIC_NUMERIC,
+ OperandTypes.NUMERIC_CHARACTER),
+ SqlFunctionCategory.STRING);
+
/** The "TO_CHAR(timestamp, format)" function;
* converts {@code timestamp} to string according to the given {@code
format}.
*/
diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
index b59bf574a7..d60c6fa72c 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
@@ -364,6 +364,9 @@ public abstract class OperandTypes {
// Second operand optional (operand index 0, 1)
number -> number == 1);
+ public static final SqlSingleOperandTypeChecker NUMERIC_CHARACTER =
+ family(SqlTypeFamily.NUMERIC, SqlTypeFamily.CHARACTER);
+
public static final SqlSingleOperandTypeChecker NUMERIC_INTEGER =
family(SqlTypeFamily.NUMERIC, SqlTypeFamily.INTEGER);
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 53edb703e4..057045c656 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -429,6 +429,7 @@ public enum BuiltInMethod {
INITCAP(SqlFunctions.class, "initcap", String.class),
SUBSTRING(SqlFunctions.class, "substring", String.class, int.class,
int.class),
+ FORMAT_NUMBER(SqlFunctions.class, "formatNumber", long.class, int.class),
LPAD(SqlFunctions.class, "lpad", String.class, int.class, String.class),
RPAD(SqlFunctions.class, "rpad", String.class, int.class, String.class),
STARTS_WITH(SqlFunctions.class, "startsWith", String.class, String.class),
diff --git
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index 88c36db887..1033899a30 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -295,6 +295,7 @@ DialectDoesNotSupportFeature=Dialect does not support
feature: ''{0}''
IllegalNegativePadLength=Second argument for LPAD/RPAD must not be negative
IllegalEmptyPadPattern=Third argument (pad pattern) for LPAD/RPAD must not be
empty
IllegalNegativeSubstringLength=Substring error: negative substring length not
allowed
+IllegalNegativeDecimalValue=Illegal arguments for 'FORMAT_NUMBER' function:
negative decimal value not allowed
IllegalArgumentsInMapFromArraysFunc=Illegal arguments: The length of the keys
array {0,number,#} is not equal to the length of the values array {1,number,#}
in MAP_FROM_ARRAYS function
TrimError=Trim error: trim character must be exactly 1 character
InvalidTypesForArithmetic=Invalid types for arithmetic: {0} {1} {2}
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 904f30df24..6b2f517469 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2731,6 +2731,8 @@ BigQuery's type system uses confusingly different names
for types and functions:
| b | FLOOR(value) | Similar to standard
`FLOOR(value)` except if *value* is an integer type, the return type is a double
| b | FORMAT_DATE(string, date) | Formats *date*
according to the specified format *string*
| b | FORMAT_DATETIME(string, timestamp) | Formats *timestamp*
according to the specified format *string*
+| h s | FORMAT_NUMBER(value, decimalVal) | Formats the number
*value* like '#,###,###.##', rounded to decimal places *decimalVal*. If
*decimalVal* is 0, the result has no decimal point or fractional part
+| h s | FORMAT_NUMBER(value, format) | Formats the number
*value* to MySQL's FORMAT *format*, like '#,###,###.##0.00'
| b | FORMAT_TIME(string, time) | Formats *time*
according to the specified format *string*
| b | FORMAT_TIMESTAMP(string timestamp) | Formats *timestamp*
according to the specified format *string*
| s | GETBIT(value, position) | Equivalent to
`BIT_GET(value, position)`
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index e5ff71d424..4d251614ce 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -8605,6 +8605,105 @@ public class SqlOperatorTest {
}
}
+ @Test void testFormatNumber() {
+ final SqlOperatorFixture f0 =
fixture().setFor(SqlLibraryOperators.FORMAT_NUMBER);
+ f0.checkFails("^format_number(123, 2)^",
+ "No match found for function signature FORMAT_NUMBER\\(<NUMERIC>,
<NUMERIC>\\)",
+ false);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ // test with tinyint type
+ f.checkString("format_number(cast(1 as tinyint), 4)", "1.0000",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(cast(1 as tinyint),
'#,###,###,###,###,###,##0.0000')",
+ "1.0000",
+ "VARCHAR NOT NULL");
+
+ // test with smallint type
+ f.checkString("format_number(cast(1 as smallint), 4)", "1.0000",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(cast(1234 as smallint),
'#,###,###,###,###,###,##0.0000000')",
+ "1,234.0000000",
+ "VARCHAR NOT NULL");
+
+ // test with integer type
+ f.checkString("format_number(cast(1 as integer), 4)", "1.0000",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(cast(1234 as integer),
'#,###,###,###,###,###,##0.0000000')",
+ "1,234.0000000",
+ "VARCHAR NOT NULL");
+
+ // test with bigint type
+ f.checkString("format_number(cast(0 as bigint), 0)", "0",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(cast(1 as bigint), 4)", "1.0000",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(cast(1234 as bigint), 7)", "1,234.0000000",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(cast(1234 as bigint),
'#,###,###,###,###,###,##0.0000000')",
+ "1,234.0000000",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(cast(-1 as bigint), 4)", "-1.0000",
+ "VARCHAR NOT NULL");
+
+ // test with float type
+ f.checkString("format_number(cast(12332.123456 as float), 4)",
"12,332.1235",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(cast(123456.123456789 as float),
'########.###')",
+ "123456.123",
+ "VARCHAR NOT NULL");
+
+ // test with double type
+ f.checkString("format_number(cast(1234567.123456789 as double), 7)",
"1,234,567.1234568",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(cast(1234567.123456789 as double),
'##,###,###.##')",
+ "1,234,567.12",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(cast(-0.123456789 as double), 15)",
"-0.123456789000000",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(cast(-0.123456789 as double),"
+ + " '#,###,###,###,###,###,##0.000000000000000')",
+ "-0.123456789000000",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(cast(0.000000 as double), 1)", "0.0",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(cast(0.000000 as double),
'#,###,###,###,###,###,##0.0')",
+ "0.0",
+ "VARCHAR NOT NULL");
+
+ // test with decimal type
+ f.checkString("format_number(1234567.123456789, 7)", "1,234,567.1234568",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(1234567.123456789, '##,###,###.##')",
+ "1,234,567.12",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(-0.123456789, 15)", "-0.123456789000000",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(-0.123456789,"
+ + " '#,###,###,###,###,###,##0.000000000000000')",
+ "-0.123456789000000",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(0.000000, 1)", "0.0",
+ "VARCHAR NOT NULL");
+ f.checkString("format_number(0.0, '#,###,###,###,###,###,##0.0000')",
+ "0.0000",
+ "VARCHAR NOT NULL");
+
+ // test with illegal argument
+ f.checkFails("format_number(12332.123456, -1)",
+ "Illegal arguments for 'FORMAT_NUMBER' function:"
+ + " negative decimal value not allowed",
+ true);
+
+ // test with null values
+ f.checkNull("format_number(cast(null as integer), 1)");
+ f.checkNull("format_number(0, cast(null as integer))");
+ f.checkNull("format_number(0, cast(null as varchar))");
+ f.checkNull("format_number(cast(null as integer), cast(null as
integer))");
+ f.checkNull("format_number(cast(null as integer), cast(null as
varchar))");
+ };
+ f0.forEachLibrary(list(SqlLibrary.HIVE, SqlLibrary.SPARK), consumer);
+ }
+
@Test void testTrimFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.TRIM, VmName.EXPAND);
