You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by am...@apache.org on 2016/03/05 14:54:19 UTC
hive git commit: HIVE-5370 : Add user specifed format as argument for
format_number udf (Reviwed by Ashutosh Chauhan)
Repository: hive
Updated Branches:
refs/heads/master 33005ac10 -> cc6d29ff3
HIVE-5370 : Add user specifed format as argument for format_number udf (Reviwed by Ashutosh Chauhan)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/cc6d29ff
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/cc6d29ff
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/cc6d29ff
Branch: refs/heads/master
Commit: cc6d29ff324f857b6870a04461b84b6edb1406f2
Parents: 33005ac
Author: Amareshwari Sriramadasu <am...@apache.org>
Authored: Sat Mar 5 19:24:03 2016 +0530
Committer: Amareshwari Sriramadasu <am...@apache.org>
Committed: Sat Mar 5 19:24:03 2016 +0530
----------------------------------------------------------------------
.../ql/udf/generic/GenericUDFFormatNumber.java | 77 +++++++++-----
.../clientnegative/udf_format_number_wrong6.q | 2 -
.../queries/clientpositive/udf_format_number.q | 36 +++++--
.../udf_format_number_wrong1.q.out | 2 +-
.../udf_format_number_wrong2.q.out | 2 +-
.../udf_format_number_wrong4.q.out | 2 +-
.../udf_format_number_wrong6.q.out | 1 -
.../clientpositive/udf_format_number.q.out | 101 ++++++++++++++-----
8 files changed, 155 insertions(+), 68 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/hive/blob/cc6d29ff/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFFormatNumber.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFFormatNumber.java b/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFFormatNumber.java
index 71ca8f2..ed03b39 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFFormatNumber.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFFormatNumber.java
@@ -27,8 +27,10 @@ import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.serde.serdeConstants;
+import org.apache.hadoop.hive.serde2.objectinspector.ConstantObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector;
+import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector.PrimitiveCategory;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.DoubleObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.FloatObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.IntObjectInspector;
@@ -39,7 +41,7 @@ import org.apache.hadoop.io.Text;
/**
* Generic UDF for format_number function
- * <code>FORMAT_NUMBER(X, D)</code>.
+ * <code>FORMAT_NUMBER(X, D or F)</code>.
* This is supposed to function like MySQL's FORMAT,
* http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#
* function_format
@@ -47,26 +49,32 @@ import org.apache.hadoop.io.Text;
* @see org.apache.hadoop.hive.ql.udf.generic.GenericUDF
*/
@Description(name = "format_number",
- value = "_FUNC_(X, D) - Formats the number X to "
- + "a format like '#,###,###.##', rounded to D decimal places,"
+ value = "_FUNC_(X, D or F) - Formats the number X to "
+ + "a format like '#,###,###.##', rounded to D decimal places, Or"
+ + " Uses the format specified F to format,"
+ " and returns the result as a string. If D is 0, the result"
+ " has no decimal point or fractional part."
+ " This is supposed to function like MySQL's FORMAT",
extended = "Example:\n"
+ " > SELECT _FUNC_(12332.123456, 4) FROM src LIMIT 1;\n"
- + " '12,332.1235'")
+ + " '12,332.1235'\n"
+ + " > SELECT _FUNC_(12332.123456, '##################.###') FROM"
+ + " src LIMIT 1;\n"
+ + " '12332.123'")
public class GenericUDFFormatNumber extends GenericUDF {
private transient ObjectInspector[] argumentOIs;
private transient final Text resultText = new Text();
private transient final StringBuilder pattern = new StringBuilder("");
private transient final DecimalFormat numberFormat = new DecimalFormat("");
private transient int lastDValue = -1;
+ private transient PrimitiveCategory dType;
@Override
- public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
+ public ObjectInspector initialize(ObjectInspector[] arguments)
+ throws UDFArgumentException {
if (arguments.length != 2) {
throw new UDFArgumentLengthException(
- "The function FORMAT_NUMBER(X, D) needs two arguments.");
+ "The function FORMAT_NUMBER(X, D or F) needs two arguments.");
}
switch (arguments[0].getCategory()) {
@@ -94,7 +102,8 @@ public class GenericUDFFormatNumber extends GenericUDF {
+ serdeConstants.TINYINT_TYPE_NAME + "\""
+ " or \"" + serdeConstants.SMALLINT_TYPE_NAME + "\""
+ " or \"" + serdeConstants.INT_TYPE_NAME + "\""
- + " or \"" + serdeConstants.BIGINT_TYPE_NAME + "\", but \""
+ + " or \"" + serdeConstants.BIGINT_TYPE_NAME + "\""
+ + " or \"" + serdeConstants.STRING_TYPE_NAME + "\", but \""
+ arguments[1].getTypeName() + "\" was found.");
}
@@ -124,20 +133,32 @@ public class GenericUDFFormatNumber extends GenericUDF {
+ arguments[0].getTypeName() + "\" was found.");
}
- switch (dObjectInspector.getPrimitiveCategory()) {
+ dType = dObjectInspector.getPrimitiveCategory();
+ switch (dType) {
case VOID:
case BYTE:
case SHORT:
case INT:
case LONG:
break;
+ case STRING:
+ if (!(arguments[1] instanceof ConstantObjectInspector)) {
+ throw new UDFArgumentTypeException(1, "Format string passed must be a constant STRING." + arguments[1]
+ .toString());
+ }
+ ConstantObjectInspector constantOI = (ConstantObjectInspector)arguments[1];
+ String fValue = constantOI.getWritableConstantValue().toString();
+ DecimalFormat dFormat = new DecimalFormat(fValue);
+ numberFormat.applyPattern(dFormat.toPattern());
+ break;
default:
throw new UDFArgumentTypeException(1, "Argument 2"
+ " of function FORMAT_NUMBER must be \""
+ serdeConstants.TINYINT_TYPE_NAME + "\""
+ " or \"" + serdeConstants.SMALLINT_TYPE_NAME + "\""
+ " or \"" + serdeConstants.INT_TYPE_NAME + "\""
- + " or \"" + serdeConstants.BIGINT_TYPE_NAME + "\", but \""
+ + " or \"" + serdeConstants.BIGINT_TYPE_NAME + "\""
+ + " or \"" + serdeConstants.STRING_TYPE_NAME + "\", but \""
+ arguments[1].getTypeName() + "\" was found.");
}
@@ -147,34 +168,38 @@ public class GenericUDFFormatNumber extends GenericUDF {
@Override
public Object evaluate(DeferredObject[] arguments) throws HiveException {
+
Object arg0;
Object arg1;
+
if ((arg0 = arguments[0].get()) == null || (arg1 = arguments[1].get()) == null) {
return null;
}
- int dValue = ((IntObjectInspector) argumentOIs[1]).get(arg1);
+ if (!dType.equals(PrimitiveCategory.STRING)) {
+ int dValue = ((IntObjectInspector) argumentOIs[1]).get(arg1);
- if (dValue < 0) {
- throw new HiveException("Argument 2 of function FORMAT_NUMBER must be >= 0, but \""
- + dValue + "\" was found");
- }
+ if (dValue < 0) {
+ throw new HiveException("Argument 2 of function FORMAT_NUMBER must be >= 0, but \""
+ + dValue + "\" was found");
+ }
- if (dValue != lastDValue) {
- // construct a new DecimalFormat only if a new dValue
- pattern.delete(0, pattern.length());
- pattern.append("#,###,###,###,###,###,##0");
+ if (dValue != lastDValue) {
+ // construct a new DecimalFormat only if a new dValue
+ pattern.delete(0, pattern.length());
+ pattern.append("#,###,###,###,###,###,##0");
- //decimal place
- if (dValue > 0) {
- pattern.append(".");
- for (int i = 0; i < dValue; i++) {
- pattern.append("0");
+ //decimal place
+ if (dValue > 0) {
+ pattern.append(".");
+ for (int i = 0; i < dValue; i++) {
+ pattern.append("0");
+ }
}
+ DecimalFormat dFormat = new DecimalFormat(pattern.toString());
+ lastDValue = dValue;
+ numberFormat.applyPattern(dFormat.toPattern());
}
- DecimalFormat dFormat = new DecimalFormat(pattern.toString());
- lastDValue = dValue;
- numberFormat.applyPattern(dFormat.toPattern());
}
double xDoubleValue = 0.0;
http://git-wip-us.apache.org/repos/asf/hive/blob/cc6d29ff/ql/src/test/queries/clientnegative/udf_format_number_wrong6.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientnegative/udf_format_number_wrong6.q b/ql/src/test/queries/clientnegative/udf_format_number_wrong6.q
deleted file mode 100644
index e5b11b9..0000000
--- a/ql/src/test/queries/clientnegative/udf_format_number_wrong6.q
+++ /dev/null
@@ -1,2 +0,0 @@
--- invalid argument type
-SELECT format_number(12332.123456, "4") FROM src LIMIT 1;
http://git-wip-us.apache.org/repos/asf/hive/blob/cc6d29ff/ql/src/test/queries/clientpositive/udf_format_number.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/udf_format_number.q b/ql/src/test/queries/clientpositive/udf_format_number.q
index 28f087d..0d60c93 100644
--- a/ql/src/test/queries/clientpositive/udf_format_number.q
+++ b/ql/src/test/queries/clientpositive/udf_format_number.q
@@ -9,25 +9,31 @@ DESCRIBE FUNCTION EXTENDED format_number;
EXPLAIN
SELECT format_number(12332.123456, 4),
format_number(12332.1,4),
- format_number(12332.2,0) FROM src tablesample (1 rows);
+ format_number(12332.2,0),
+ format_number(12332.2,'##################.###')
+ FROM src tablesample (1 rows);
SELECT format_number(12332.123456, 4),
format_number(12332.1,4),
- format_number(12332.2,0)
+ format_number(12332.2,0),
+ format_number(12332.2,'##################.###')
FROM src tablesample (1 rows);
-- positive numbers
SELECT format_number(0.123456789, 12),
format_number(12345678.123456789, 5),
format_number(1234567.123456789, 7),
- format_number(123456.123456789, 0)
+ format_number(123456.123456789, 0),
+ format_number(123456.123456789, '##################.###')
FROM src tablesample (1 rows);
-- negative numbers
SELECT format_number(-123456.123456789, 0),
format_number(-1234567.123456789, 2),
format_number(-0.123456789, 15),
- format_number(-12345.123456789, 4)
+ format_number(-0.123456789, '##################.###'),
+ format_number(-12345.123456789, 4),
+ format_number(-12345.123456789, '##################.###')
FROM src tablesample (1 rows);
-- zeros
@@ -35,7 +41,9 @@ SELECT format_number(0.0, 4),
format_number(0.000000, 1),
format_number(000.0000, 1),
format_number(00000.0000, 1),
- format_number(-00.0, 4)
+ format_number(00000.0000, '##################.###'),
+ format_number(-00.0, 4),
+ format_number(-00.0, '##################.###')
FROM src tablesample (1 rows);
-- integers
@@ -43,7 +51,8 @@ SELECT format_number(0, 0),
format_number(1, 4),
format_number(12, 2),
format_number(123, 5),
- format_number(1234, 7)
+ format_number(1234, 7),
+ format_number(1234, '##################.###')
FROM src tablesample (1 rows);
-- long and double boundary
@@ -61,7 +70,8 @@ FROM src tablesample (1 rows);
-- floats
SELECT format_number(CAST(12332.123456 AS FLOAT), 4),
format_number(CAST(12332.1 AS FLOAT), 4),
- format_number(CAST(-12332.2 AS FLOAT), 0)
+ format_number(CAST(-12332.2 AS FLOAT), 0),
+ format_number(CAST(-12332.2 AS FLOAT), '##################.###')
FROM src tablesample (1 rows);
-- decimals
@@ -69,7 +79,8 @@ SELECT format_number(12332.123456BD, 4),
format_number(12332.123456BD, 2),
format_number(12332.1BD, 4),
format_number(-12332.2BD, 0),
- format_number(CAST(12332.567 AS DECIMAL(8, 1)), 4)
+ format_number(CAST(12332.567 AS DECIMAL(8, 1)), 4),
+ format_number(12332.1BD, '##################.###')
FROM src tablesample (1 rows);
-- nulls
@@ -77,3 +88,12 @@ SELECT
format_number(cast(null as int), 0),
format_number(12332.123456BD, cast(null as int)),
format_number(cast(null as int), cast(null as int));
+
+-- format number with format string passed
+SELECT format_number(-9223372036854775807, '##################.###'),
+ format_number(9223372036854775807, '##################.###'),
+ format_number(4.9E-324, '##################.###'),
+ format_number(1.7976931348623157E308, '##################.###'),
+ format_number(null, '##################.###')
+FROM src tablesample (1 rows);
+
http://git-wip-us.apache.org/repos/asf/hive/blob/cc6d29ff/ql/src/test/results/clientnegative/udf_format_number_wrong1.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientnegative/udf_format_number_wrong1.q.out b/ql/src/test/results/clientnegative/udf_format_number_wrong1.q.out
index c3cb800..925c3c0 100644
--- a/ql/src/test/results/clientnegative/udf_format_number_wrong1.q.out
+++ b/ql/src/test/results/clientnegative/udf_format_number_wrong1.q.out
@@ -1 +1 @@
-FAILED: SemanticException [Error 10015]: Line 2:7 Arguments length mismatch '12332.123456': The function FORMAT_NUMBER(X, D) needs two arguments.
+FAILED: SemanticException [Error 10015]: Line 2:7 Arguments length mismatch '12332.123456': The function FORMAT_NUMBER(X, D or F) needs two arguments.
http://git-wip-us.apache.org/repos/asf/hive/blob/cc6d29ff/ql/src/test/results/clientnegative/udf_format_number_wrong2.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientnegative/udf_format_number_wrong2.q.out b/ql/src/test/results/clientnegative/udf_format_number_wrong2.q.out
index 1fe8a7c..d98854c 100644
--- a/ql/src/test/results/clientnegative/udf_format_number_wrong2.q.out
+++ b/ql/src/test/results/clientnegative/udf_format_number_wrong2.q.out
@@ -1 +1 @@
-FAILED: SemanticException [Error 10015]: Line 2:7 Arguments length mismatch '3': The function FORMAT_NUMBER(X, D) needs two arguments.
+FAILED: SemanticException [Error 10015]: Line 2:7 Arguments length mismatch '3': The function FORMAT_NUMBER(X, D or F) needs two arguments.
http://git-wip-us.apache.org/repos/asf/hive/blob/cc6d29ff/ql/src/test/results/clientnegative/udf_format_number_wrong4.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientnegative/udf_format_number_wrong4.q.out b/ql/src/test/results/clientnegative/udf_format_number_wrong4.q.out
index 3953ef1..6eca7f8 100644
--- a/ql/src/test/results/clientnegative/udf_format_number_wrong4.q.out
+++ b/ql/src/test/results/clientnegative/udf_format_number_wrong4.q.out
@@ -1 +1 @@
-FAILED: SemanticException [Error 10016]: Line 2:35 Argument type mismatch '4.01': Argument 2 of function FORMAT_NUMBER must be "tinyint" or "smallint" or "int" or "bigint", but "double" was found.
+FAILED: SemanticException [Error 10016]: Line 2:35 Argument type mismatch '4.01': Argument 2 of function FORMAT_NUMBER must be "tinyint" or "smallint" or "int" or "bigint" or "string", but "double" was found.
http://git-wip-us.apache.org/repos/asf/hive/blob/cc6d29ff/ql/src/test/results/clientnegative/udf_format_number_wrong6.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientnegative/udf_format_number_wrong6.q.out b/ql/src/test/results/clientnegative/udf_format_number_wrong6.q.out
deleted file mode 100644
index d51991f..0000000
--- a/ql/src/test/results/clientnegative/udf_format_number_wrong6.q.out
+++ /dev/null
@@ -1 +0,0 @@
-FAILED: SemanticException [Error 10016]: Line 2:35 Argument type mismatch '"4"': Argument 2 of function FORMAT_NUMBER must be "tinyint" or "smallint" or "int" or "bigint", but "string" was found.
http://git-wip-us.apache.org/repos/asf/hive/blob/cc6d29ff/ql/src/test/results/clientpositive/udf_format_number.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/udf_format_number.q.out b/ql/src/test/results/clientpositive/udf_format_number.q.out
index 4a2c80d..1fa4d5c 100644
--- a/ql/src/test/results/clientpositive/udf_format_number.q.out
+++ b/ql/src/test/results/clientpositive/udf_format_number.q.out
@@ -12,24 +12,30 @@ POSTHOOK: query: -- Test format_number() UDF
DESCRIBE FUNCTION format_number
POSTHOOK: type: DESCFUNCTION
-format_number(X, D) - Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. This is supposed to function like MySQL's FORMAT
+format_number(X, D or F) - Formats the number X to a format like '#,###,###.##', rounded to D decimal places, Or Uses the format specified F to format, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. This is supposed to function like MySQL's FORMAT
PREHOOK: query: DESCRIBE FUNCTION EXTENDED format_number
PREHOOK: type: DESCFUNCTION
POSTHOOK: query: DESCRIBE FUNCTION EXTENDED format_number
POSTHOOK: type: DESCFUNCTION
-format_number(X, D) - Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. This is supposed to function like MySQL's FORMAT
+format_number(X, D or F) - Formats the number X to a format like '#,###,###.##', rounded to D decimal places, Or Uses the format specified F to format, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. This is supposed to function like MySQL's FORMAT
Example:
> SELECT format_number(12332.123456, 4) FROM src LIMIT 1;
'12,332.1235'
+ > SELECT format_number(12332.123456, '##################.###') FROM src LIMIT 1;
+ '12332.123'
PREHOOK: query: EXPLAIN
SELECT format_number(12332.123456, 4),
format_number(12332.1,4),
- format_number(12332.2,0) FROM src tablesample (1 rows)
+ format_number(12332.2,0),
+ format_number(12332.2,'##################.###')
+ FROM src tablesample (1 rows)
PREHOOK: type: QUERY
POSTHOOK: query: EXPLAIN
SELECT format_number(12332.123456, 4),
format_number(12332.1,4),
- format_number(12332.2,0) FROM src tablesample (1 rows)
+ format_number(12332.2,0),
+ format_number(12332.2,'##################.###')
+ FROM src tablesample (1 rows)
POSTHOOK: type: QUERY
STAGE DEPENDENCIES:
Stage-0 is a root stage
@@ -44,31 +50,34 @@ STAGE PLANS:
Row Limit Per Split: 1
Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: COMPLETE
Select Operator
- expressions: '12,332.1235' (type: string), '12,332.1000' (type: string), '12,332' (type: string)
- outputColumnNames: _col0, _col1, _col2
- Statistics: Num rows: 500 Data size: 140000 Basic stats: COMPLETE Column stats: COMPLETE
+ expressions: '12,332.1235' (type: string), '12,332.1000' (type: string), '12,332' (type: string), '12332.2' (type: string)
+ outputColumnNames: _col0, _col1, _col2, _col3
+ Statistics: Num rows: 500 Data size: 185500 Basic stats: COMPLETE Column stats: COMPLETE
ListSink
PREHOOK: query: SELECT format_number(12332.123456, 4),
format_number(12332.1,4),
- format_number(12332.2,0)
+ format_number(12332.2,0),
+ format_number(12332.2,'##################.###')
FROM src tablesample (1 rows)
PREHOOK: type: QUERY
PREHOOK: Input: default@src
#### A masked pattern was here ####
POSTHOOK: query: SELECT format_number(12332.123456, 4),
format_number(12332.1,4),
- format_number(12332.2,0)
+ format_number(12332.2,0),
+ format_number(12332.2,'##################.###')
FROM src tablesample (1 rows)
POSTHOOK: type: QUERY
POSTHOOK: Input: default@src
#### A masked pattern was here ####
-12,332.1235 12,332.1000 12,332
+12,332.1235 12,332.1000 12,332 12332.2
PREHOOK: query: -- positive numbers
SELECT format_number(0.123456789, 12),
format_number(12345678.123456789, 5),
format_number(1234567.123456789, 7),
- format_number(123456.123456789, 0)
+ format_number(123456.123456789, 0),
+ format_number(123456.123456789, '##################.###')
FROM src tablesample (1 rows)
PREHOOK: type: QUERY
PREHOOK: Input: default@src
@@ -77,17 +86,20 @@ POSTHOOK: query: -- positive numbers
SELECT format_number(0.123456789, 12),
format_number(12345678.123456789, 5),
format_number(1234567.123456789, 7),
- format_number(123456.123456789, 0)
+ format_number(123456.123456789, 0),
+ format_number(123456.123456789, '##################.###')
FROM src tablesample (1 rows)
POSTHOOK: type: QUERY
POSTHOOK: Input: default@src
#### A masked pattern was here ####
-0.123456789000 12,345,678.12346 1,234,567.1234568 123,456
+0.123456789000 12,345,678.12346 1,234,567.1234568 123,456 123456.123
PREHOOK: query: -- negative numbers
SELECT format_number(-123456.123456789, 0),
format_number(-1234567.123456789, 2),
format_number(-0.123456789, 15),
- format_number(-12345.123456789, 4)
+ format_number(-0.123456789, '##################.###'),
+ format_number(-12345.123456789, 4),
+ format_number(-12345.123456789, '##################.###')
FROM src tablesample (1 rows)
PREHOOK: type: QUERY
PREHOOK: Input: default@src
@@ -96,18 +108,22 @@ POSTHOOK: query: -- negative numbers
SELECT format_number(-123456.123456789, 0),
format_number(-1234567.123456789, 2),
format_number(-0.123456789, 15),
- format_number(-12345.123456789, 4)
+ format_number(-0.123456789, '##################.###'),
+ format_number(-12345.123456789, 4),
+ format_number(-12345.123456789, '##################.###')
FROM src tablesample (1 rows)
POSTHOOK: type: QUERY
POSTHOOK: Input: default@src
#### A masked pattern was here ####
--123,456 -1,234,567.12 -0.123456789000000 -12,345.1235
+-123,456 -1,234,567.12 -0.123456789000000 -0.123 -12,345.1235 -12345.123
PREHOOK: query: -- zeros
SELECT format_number(0.0, 4),
format_number(0.000000, 1),
format_number(000.0000, 1),
format_number(00000.0000, 1),
- format_number(-00.0, 4)
+ format_number(00000.0000, '##################.###'),
+ format_number(-00.0, 4),
+ format_number(-00.0, '##################.###')
FROM src tablesample (1 rows)
PREHOOK: type: QUERY
PREHOOK: Input: default@src
@@ -117,18 +133,21 @@ SELECT format_number(0.0, 4),
format_number(0.000000, 1),
format_number(000.0000, 1),
format_number(00000.0000, 1),
- format_number(-00.0, 4)
+ format_number(00000.0000, '##################.###'),
+ format_number(-00.0, 4),
+ format_number(-00.0, '##################.###')
FROM src tablesample (1 rows)
POSTHOOK: type: QUERY
POSTHOOK: Input: default@src
#### A masked pattern was here ####
-0.0000 0.0 0.0 0.0 -0.0000
+0.0000 0.0 0.0 0.0 0 -0.0000 -0
PREHOOK: query: -- integers
SELECT format_number(0, 0),
format_number(1, 4),
format_number(12, 2),
format_number(123, 5),
- format_number(1234, 7)
+ format_number(1234, 7),
+ format_number(1234, '##################.###')
FROM src tablesample (1 rows)
PREHOOK: type: QUERY
PREHOOK: Input: default@src
@@ -138,12 +157,13 @@ SELECT format_number(0, 0),
format_number(1, 4),
format_number(12, 2),
format_number(123, 5),
- format_number(1234, 7)
+ format_number(1234, 7),
+ format_number(1234, '##################.###')
FROM src tablesample (1 rows)
POSTHOOK: type: QUERY
POSTHOOK: Input: default@src
#### A masked pattern was here ####
-0 1.0000 12.00 123.00000 1,234.0000000
+0 1.0000 12.00 123.00000 1,234.0000000 1234
PREHOOK: query: -- long and double boundary
-- 9223372036854775807 is LONG_MAX
-- -9223372036854775807 is one more than LONG_MIN,
@@ -176,7 +196,8 @@ POSTHOOK: Input: default@src
PREHOOK: query: -- floats
SELECT format_number(CAST(12332.123456 AS FLOAT), 4),
format_number(CAST(12332.1 AS FLOAT), 4),
- format_number(CAST(-12332.2 AS FLOAT), 0)
+ format_number(CAST(-12332.2 AS FLOAT), 0),
+ format_number(CAST(-12332.2 AS FLOAT), '##################.###')
FROM src tablesample (1 rows)
PREHOOK: type: QUERY
PREHOOK: Input: default@src
@@ -184,18 +205,20 @@ PREHOOK: Input: default@src
POSTHOOK: query: -- floats
SELECT format_number(CAST(12332.123456 AS FLOAT), 4),
format_number(CAST(12332.1 AS FLOAT), 4),
- format_number(CAST(-12332.2 AS FLOAT), 0)
+ format_number(CAST(-12332.2 AS FLOAT), 0),
+ format_number(CAST(-12332.2 AS FLOAT), '##################.###')
FROM src tablesample (1 rows)
POSTHOOK: type: QUERY
POSTHOOK: Input: default@src
#### A masked pattern was here ####
-12,332.1230 12,332.0996 -12,332
+12,332.1230 12,332.0996 -12,332 -12332.2
PREHOOK: query: -- decimals
SELECT format_number(12332.123456BD, 4),
format_number(12332.123456BD, 2),
format_number(12332.1BD, 4),
format_number(-12332.2BD, 0),
- format_number(CAST(12332.567 AS DECIMAL(8, 1)), 4)
+ format_number(CAST(12332.567 AS DECIMAL(8, 1)), 4),
+ format_number(12332.1BD, '##################.###')
FROM src tablesample (1 rows)
PREHOOK: type: QUERY
PREHOOK: Input: default@src
@@ -205,12 +228,13 @@ SELECT format_number(12332.123456BD, 4),
format_number(12332.123456BD, 2),
format_number(12332.1BD, 4),
format_number(-12332.2BD, 0),
- format_number(CAST(12332.567 AS DECIMAL(8, 1)), 4)
+ format_number(CAST(12332.567 AS DECIMAL(8, 1)), 4),
+ format_number(12332.1BD, '##################.###')
FROM src tablesample (1 rows)
POSTHOOK: type: QUERY
POSTHOOK: Input: default@src
#### A masked pattern was here ####
-12,332.1235 12,332.12 12,332.1000 -12,332 12,332.6000
+12,332.1235 12,332.12 12,332.1000 -12,332 12,332.6000 12332.1
PREHOOK: query: -- nulls
SELECT
format_number(cast(null as int), 0),
@@ -228,3 +252,24 @@ POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
#### A masked pattern was here ####
NULL NULL NULL
+PREHOOK: query: -- format number with format string passed
+SELECT format_number(-9223372036854775807, '##################.###'),
+ format_number(9223372036854775807, '##################.###'),
+ format_number(4.9E-324, '##################.###'),
+ format_number(1.7976931348623157E308, '##################.###'),
+ format_number(null, '##################.###')
+FROM src tablesample (1 rows)
+PREHOOK: type: QUERY
+PREHOOK: Input: default@src
+#### A masked pattern was here ####
+POSTHOOK: query: -- format number with format string passed
+SELECT format_number(-9223372036854775807, '##################.###'),
+ format_number(9223372036854775807, '##################.###'),
+ format_number(4.9E-324, '##################.###'),
+ format_number(1.7976931348623157E308, '##################.###'),
+ format_number(null, '##################.###')
+FROM src tablesample (1 rows)
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@src
+#### A masked pattern was here ####
+-9223372036854775807 9223372036854775807 0 179769313486231570000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 NULL