MaxCompute:LOG10

Static values

-- Enable MaxCompute V2.0 data types.
SET odps.sql.type.system.odps2 = true;
-- Disable strict mode to allow NULL inputs.
SET odps.sql.udf.strict.mode = false;

-- Returns NULL (strict mode is disabled).
SELECT log10(NULL);
-- Throws ODPS-0121095 (0 is not a positive number).
SELECT log10(0);
-- Returns 0.9030899869919435.
SELECT log10(8);

Table data

The following examples use a sample table named mf_math_fun_t. Create the table and insert sample data:

CREATE TABLE IF NOT EXISTS mf_math_fun_t (
    int_data     INT,
    bigint_data  BIGINT,
    double_data  DOUBLE,
    decimal_data DECIMAL,
    float_data   FLOAT,
    string_data  STRING
);

INSERT INTO mf_math_fun_t VALUES
(null, -10,  0.525,  0.525BD,              CAST(0.525  AS FLOAT), '10'),
(-20,  null, -0.1,  -0.1BD,               CAST(-0.1   AS FLOAT), '-10'),
(0,   -1,    null,   20.45BD,             CAST(-1     AS FLOAT), '30'),
(-40,  4,    0.89,   null,                CAST(0.89   AS FLOAT), '-30'),
(5,   -50,  -1,     -1BD,                 null,                  '50'),
(-60,  6,    1.5,    1.5BD,               CAST(1.5    AS FLOAT), '-50'),
(-1,  -70,  -7.5,  -7.5BD,               CAST(-7.5   AS FLOAT),  null),
(-80,  1,   -10.2, -10.2BD,              CAST(-10.2  AS FLOAT), '-1'),
(9,   -90,   2.58,  2.58BD,              CAST(2.58   AS FLOAT), '0'),
(-100, 10,  -5.8,  -5.8BD,               CAST(-5.8   AS FLOAT), '-90');

Query the table to confirm the data:

SELECT * FROM mf_math_fun_t;

+------------+-------------+-------------+--------------+------------+-------------+
| int_data   | bigint_data | double_data | decimal_data | float_data | string_data |
+------------+-------------+-------------+--------------+------------+-------------+
| NULL       | -10         | 0.525       | 0.525        | 0.525      | 10          |
| -20        | NULL        | -0.1        | -0.1         | -0.1       | -10         |
| 0          | -1          | NULL        | 20.45        | -1.0       | 30          |
| -40        | 4           | 0.89        | NULL         | 0.89       | -30         |
| 5          | -50         | -1.0        | -1           | NULL       | 50          |
| -60        | 6           | 1.5         | 1.5          | 1.5        | -50         |
| -1         | -70         | -7.5        | -7.5         | -7.5       | NULL        |
| -80        | 1           | -10.2       | -10.2        | -10.2      | -1          |
| 9          | -90         | 2.58        | 2.58         | 2.58       | 0           |
| -100       | 10          | -5.8        | -5.8         | -5.8       | -90         |
+------------+-------------+-------------+--------------+------------+-------------+

Apply log10 to all columns. Non-positive values and NULL inputs return NULL because strict mode is disabled:

-- Disable strict mode to return NULL for invalid inputs instead of throwing an exception.
set odps.sql.udf.strict.mode = false;
-- Enable MaxCompute V2.0 data types. Run this together with the SQL statement.
set odps.sql.type.system.odps2 = true;

SELECT
    log10(int_data)     AS int_new,
    log10(bigint_data)  AS bigint_new,
    log10(double_data)  AS double_new,
    log10(decimal_data) AS decimal_new,
    log10(float_data)   AS float_new,
    log10(string_data)  AS string_new
FROM mf_math_fun_t;

+--------------------+--------------------+---------------------+---------------------+-----------------------+--------------------+
| int_new            | bigint_new         | double_new          | decimal_new         | float_new             | string_new         |
+--------------------+--------------------+---------------------+---------------------+-----------------------+--------------------+
| NULL               | NULL               | -0.2798406965940431 | -0.2798406965940431 | -0.27984071631668606  | 1.0                |
| NULL               | NULL               | NULL                | NULL                | NULL                  | NULL               |
| NULL               | NULL               | NULL                | 1.3106933123433606  | NULL                  | 1.4771212547196624 |
| NULL               | 0.6020599913279623 | -0.0506099933550872 | NULL                | -0.050610000335573106 | NULL               |
| 0.6989700043360187 | NULL               | NULL                | NULL                | NULL                  | 1.6989700043360185 |
| NULL               | 0.7781512503836435 | 0.17609125905568124 | 0.17609125905568124 | 0.17609125905568124   | NULL               |
| NULL               | NULL               | NULL                | NULL                | NULL                  | NULL               |
| NULL               | 0.0                | NULL                | NULL                | NULL                  | NULL               |
| 0.9542425094393249 | NULL               | 0.4116197059632301  | 0.4116197059632301  | 0.411619693120579     | NULL               |
| NULL               | 1.0                | NULL                | NULL                | NULL                  | NULL               |
+--------------------+--------------------+---------------------+---------------------+-----------------------+--------------------+