FAQ
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 <amareshwari@apache.org>
Authored: Sat Mar 5 19:24:03 2016 +0530
Committer: Amareshwari Sriramadasu <amareshwari@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

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupcommits @
categorieshive, hadoop
postedMar 5, '16 at 1:54p
activeMar 5, '16 at 1:54p
posts1
users1
websitehive.apache.org

1 user in discussion

Amareshwari: 1 post

People

Translate

site design / logo © 2021 Grokbase