--! qt:dataset:src
set hive.fetch.task.conversion=more;

use default;
-- Test format_number() UDF

DESCRIBE FUNCTION format_number;
DESCRIBE FUNCTION EXTENDED format_number;

EXPLAIN
SELECT format_number(12332.123456, 4),
    format_number(12332.1,4),
    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,'##################.###')
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, '##################.###')
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(-0.123456789, '##################.###'),
    format_number(-12345.123456789, 4),
    format_number(-12345.123456789, '##################.###')
FROM src tablesample (1 rows);

-- zeros
SELECT format_number(0.0, 4),
    format_number(0.000000, 1),
    format_number(000.0000, 1),
    format_number(00000.0000, 1),
    format_number(00000.0000, '##################.###'),
    format_number(-00.0, 4),
    format_number(-00.0, '##################.###')
FROM src tablesample (1 rows);

-- 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, '##################.###')
FROM src tablesample (1 rows);

-- long and double boundary
-- 9223372036854775807 is LONG_MAX
-- -9223372036854775807 is one more than LONG_MIN,
-- due to HIVE-2733, put it here to check LONG_MIN boundary
-- 4.9E-324 and 1.7976931348623157E308 are Double.MIN_VALUE and Double.MAX_VALUE
-- check them for Double boundary
SELECT format_number(-9223372036854775807, 10),
    format_number(9223372036854775807, 20),
    format_number(4.9E-324, 324),
    format_number(1.7976931348623157E308, 308)
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), '##################.###')
FROM src tablesample (1 rows);

-- 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(12332.1BD, '##################.###')
FROM src tablesample (1 rows);

-- nulls
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);