Differences in using operators, type conversions, and built-in functions in MaxCompute between normal mode and Hive-compatible mode - MaxCompute

This topic describes the differences in operators, type conversions, and built-in functions between normal mode and Hive-compatible mode in MaxCompute.

Operators

BITAND (&)

If the input parameter is a BIGINT and the result of the operation is LONG_MIN (-2⁶³), normal mode returns NULL, whereas Hive-compatible mode returns LONG_MIN.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
select cast((a & b) as string) from
values(-9223372036854775807L, -9223372036854775792L) t(a, b);
-- Returns NULL.

Hive-compatible mode

set odps.sql.hive.compatible=true;
select cast((a & b) as string) from
values(-9223372036854775807L, -9223372036854775792L) t(a, b);
-- Returns -9223372036854775808.

Hive

select cast((-9223372036854775807L & -9223372036854775792L) as string);
-- Returns -9223372036854775808.

BITOR (|)

If the input parameter is a BIGINT and the result of the operation is LONG_MIN (-2⁶³), normal mode returns NULL, whereas Hive-compatible mode returns LONG_MIN.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
select cast((a | b) as string) from
values(java.lang.Long.MIN_VALUE, 0L) t(a, b);
-- Returns NULL.

Hive-compatible mode

set odps.sql.hive.compatible=true;
select cast((a | b) as string) from
values(java.lang.Long.MIN_VALUE, 0L) t(a, b);
-- Returns -9223372036854775808.

Hive

select cast(-9223372036854775808 as bigint) | 0;
-- Returns -9223372036854775808.

BITXOR (^)

If the input parameter is a BIGINT and the result of the operation is LONG_MIN (-2⁶³), normal mode returns NULL, whereas Hive-compatible mode returns LONG_MIN.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
select cast((a ^ b) as string) from
values(java.lang.Long.MIN_VALUE, 0L) t(a, b);
-- Returns NULL.

Hive-compatible mode

set odps.sql.hive.compatible=true;
select cast((a ^ b) as string) from
values(java.lang.Long.MIN_VALUE, 0L) t(a, b);
-- Returns -9223372036854775808.

Hive

select cast(-9223372036854775808 as bigint) ^ 0;
-- Returns -9223372036854775808.

EQ (=)

If the input parameters are of the DOUBLE type, normal mode performs a less strict equality check than Hive-compatible mode. In normal mode, two input values are considered equal if they are close enough.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
select a = 1.0 from values (1.000000000000001) t(a);
-- The result is true because the two numbers are close enough.

Hive-compatible mode

set odps.sql.hive.compatible=true;
select a = 1.0 from values (1.000000000000001) t(a);
-- The result is false.

Hive

select 1.0 = 1.000000000000001 ;
-- The result is false.

NEQ (!=)

Examples:

Normal mode

set odps.sql.hive.compatible=false;
select a != 1.0 from values (1.000000000000001) t(a);
-- The result is false because the two numbers are close enough.

Hive-compatible mode

set odps.sql.hive.compatible=true;
select a != 1.0 from values (1.000000000000001) t(a);
-- The result is true.

Hive

select 1.000000000000001 != 1.0 ;
-- The result is true.

GE (>=)
If the input parameters are of the DOUBLE type, normal mode performs a less strict equality check. Two input values are considered equal if they are close enough. As a result, the GE operator may return true even if the first input parameter is less than the second, provided the values are close enough.
Examples:
- Normal mode
```
set odps.sql.hive.compatible=false;
select 1.0 >= a from values (1.000000000000001) t(a);
-- The result is true because the two numbers are close enough and considered equal.
```
- Hive-compatible mode
```
set odps.sql.hive.compatible=true;
select 1.0 >= a from values (1.000000000000001) t(a);
-- The result is false.
```
- Hive
```
select 1.0 >= 1.000000000000001;
-- The result is false.
```
GT (>)
If the input parameters are of the DOUBLE type, normal mode performs a less strict equality check. Two input values are considered equal if they are close enough. As a result, the GT operator may return false even if the first input parameter is greater than the second, provided the values are close enough.
Examples:
- Normal mode
```
set odps.sql.hive.compatible=false;
select a > 1.0 from values (1.000000000000001) t(a);
-- The result is false because the two numbers are close enough and considered equal.
```
- Hive-compatible mode
```
set odps.sql.hive.compatible=true;
select a > 1.0 from values (1.000000000000001) t(a);
-- The result is true.
```
- Hive
```
select 1.000000000000001>1.0;
-- The result is true.
```
LE (<=)
If the input parameters are of the DOUBLE type, normal mode performs a less strict equality check. Two input values are considered equal if they are close enough. As a result, the LE operator may return true even if the first input parameter is greater than the second, provided the values are close enough.
Examples:
- Normal mode
```
set odps.sql.hive.compatible=false;
select a <= 1.0 from values (1.000000000000001) t(a);
-- The result is true because the two numbers are close enough and considered equal.
```
- Hive-compatible mode
```
set odps.sql.hive.compatible=true;
select a <= 1.0 from values (1.000000000000001) t(a);
-- The result is false.
```
- Hive
```
select 1.000000000000001 <= 1.0 ;
-- The result is false.
```
LT (<)
If the input parameters are of the DOUBLE type, normal mode performs a less strict equality check. Two input values are considered equal if they are close enough. As a result, the LT operator may return false even if the first input parameter is less than the second, provided the values are close enough.
Examples:
- Normal mode
```
set odps.sql.hive.compatible=false;
select 1.0 < a from values (1.000000000000001) t(a);
-- The result is false because the two numbers are close enough and considered equal.
```
- Hive-compatible mode
```
set odps.sql.hive.compatible=true;
select 1.0 < a from values (1.000000000000001) t(a);
-- The result is true.
```
- Hive
```
select 1.0 < 1.000000000000001;
-- The result is true.
```

PLUS (+)

The two modes handle out-of-range results differently. Normal mode may report an error for an overflow, whereas Hive-compatible mode does not. Strict mode (odps.function.strictmode=true) is enabled for both modes in the examples.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select (100L + a) from values (9223372036854775807L) t(a);
-- An error is reported because the result overflows.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select (100L + a) from values (9223372036854775807L) t(a);
-- The result overflows, but no error is reported.

Hive

select (100L + 9223372036854775807L) ;
-- Returns -9223372036854775709. The result overflows, but no error is reported.

MINUS (-)

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select (-100L - a) from values (9223372036854775807L) t(a);
-- An error is reported because the result overflows.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select (-100L - a) from values (9223372036854775807L) t(a);
-- The result overflows, but no error is reported.

Hive

select (-100L - 9223372036854775807L) ;
-- Returns 9223372036854775709.

MPL (*)

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select (a * 9223372036854775807L) from values (9223372036854775807L) t(a);
-- An error is reported because the result overflows.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select (a * 9223372036854775807L) from values (9223372036854775807L) t(a);
-- The result overflows, but no error is reported.

Hive

select (9223372036854775807L * 9223372036854775807L) ;
-- Returns 1. The result overflows, but no error is reported.

DIV (/)

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;

select 1 / a from values (0L) t(a);
-- An error is reported in strict mode.

select 1.0 / a from values (0.0) t(a);
-- An error is reported in strict mode.

select 1BD / a from values (0BD) t(a);
-- An error is reported in strict mode.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;

select 1 / a from values (0L) t(a);
-- NULL is returned.

select 1.0 / a from values (0.0) t(a);
-- NULL is returned.

select 1BD / a from values (0BD) t(a);
-- NULL is returned.

Hive

select 1 / 0L;
-- Returns NULL.

select 1.0 / 0.0;
-- Returns NULL.

select 1BD / 0BD;
-- Returns NULL.

Type conversions

TOBIGINT

If an input parameter is invalid, normal mode may report an error, whereas Hive-compatible mode returns NULL. Strict mode (odps.function.strictmode=true) is enabled for both modes in the examples.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select cast(a as bigint) from values ('hello') t(a);
-- An error is reported because the input parameter is invalid.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select cast(a as bigint) from values ('hello') t(a);
-- Returns NULL.

Hive

select cast('hello' as bigint) ;
-- Returns NULL.

TODECIMAL

If an input parameter is invalid, normal mode may report an error, whereas Hive-compatible mode returns NULL. Strict mode (odps.function.strictmode=true) is enabled for both modes in the examples.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select cast(a as decimal) from values ('hello') t(a);
-- An error is reported because the input parameter is invalid.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strict.mode=true;
select cast(a as decimal) from values ('hello') t(a);
-- Returns NULL.

Hive

select cast('hello' as decimal) ;
-- Returns NULL.

TODOUBLE

If an input parameter is invalid, normal mode may report an error, whereas Hive-compatible mode returns NULL. Strict mode (odps.function.strictmode=true) is enabled for both modes in the examples.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select cast(a as double) from values ('hello') t(a);
-- An error is reported because the input parameter is invalid.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select cast(a as double) from values ('hello') t(a);
-- Returns NULL.

Hive

select cast('hello' as double) ;
-- Returns NULL.

TOSMALLINT

If an input parameter is invalid or out of range, normal mode may report an error, whereas Hive-compatible mode returns NULL. Strict mode (odps.function.strictmode=true) is enabled for both modes in the examples.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
set odps.sql.type.system.odps2=true;

select cast(a as smallint) from values ('hello') t(a);
-- An error is reported because the input parameter is invalid.

select cast(a as smallint) from values (9223372036854775807L) t(a);
-- An error is reported because the data overflows.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
set odps.sql.type.system.odps2=true;

select cast(a as smallint) from values ('hello') t(a);
-- Returns NULL.

select cast(a as smallint) from values (9223372036854775807L) t(a);
-- Data overflows, but no error is reported.

Hive

select cast('hello' as smallint);
-- Returns NULL.

select cast(9223372036854775807L as smallint);
-- Returns -1. Data overflows, but no error is reported.

TOTINYINT

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
set odps.sql.type.system.odps2=true;

select cast(a as tinyint) from values ('hello') t(a);
-- An error is reported because the input parameter is invalid.

select cast(a as tinyint) from values (9223372036854775807L) t(a);
-- An error is reported because the data overflows.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
set odps.sql.type.system.odps2=true;

select cast(a as tinyint) from values ('hello') t(a);
-- Returns NULL.

select cast(a as tinyint) from values (9223372036854775807L) t(a);
-- Data overflows, but no error is reported.

Hive

select cast('hello' as tinyint) ;
-- Returns NULL.

select cast(9223372036854775807L as tinyint) ;
-- Returns -1. Data overflows, but no error is reported.

Built-in functions

ACOS

If the input value is outside the range [-1,1], normal mode returns NULL and may report an error, whereas Hive-compatible mode returns NAN.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
select acos(a) from values(1.5) t(a);
-- Returns NULL and may report an error.

Hive-compatible mode

set odps.sql.hive.compatible=true;
select acos(a) from values(1.5) t(a);
-- Returns NAN.

Hive
```
select acos(1.5);
-- Returns NAN.
```

ASCII

The ASCII function returns a BIGINT in normal mode and an INT in Hive-compatible mode.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
explain select ascii('abcde');
-- Shows the return type is BIGINT.

Hive-compatible mode

set odps.sql.hive.compatible=true;
explain select ascii('abcde');
-- Shows the return type is INT.

Hive

explain select ascii('abcde');
-- Shows the return type is INT.

ASIN

If the input value is outside the range [-1,1], normal mode returns NULL and may report an error, whereas Hive-compatible mode returns NAN.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
select asin(a) from values(1.5) t(a);
-- Returns NULL and may report an error.

Hive-compatible mode

set odps.sql.hive.compatible=true;
select asin(a) from values(1.5) t(a);
-- Returns NAN.

Hive

select asin(a) from values(1.5) t(a);
-- Returns NAN.

CEIL

If the input parameter is a DECIMAL, the CEIL function returns a BIGINT in normal mode and a DECIMAL in Hive-compatible mode.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
explain select ceil(1.2BD);
-- Shows the return type is BIGINT.

Hive-compatible mode

set odps.sql.hive.compatible=true;
explain select ceil(1.2BD);
-- Shows the return type is DECIMAL.

Hive

explain select ceil(1.2BD);
-- Shows the return type is DECIMAL(2,0).

CHR

If the input parameter is out of range, normal mode reports an error, whereas Hive-compatible mode returns an empty string.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
select chr(-100L);
-- An error is reported because the input parameter is invalid.

Hive-compatible mode

set odps.sql.hive.compatible=true;
select chr(-100L);
-- Returns an empty string.

Hive

select chr(-100L);
-- Returns an empty string.

CONCAT_WS

If an input parameter is NULL, normal mode returns NULL, whereas Hive-compatible mode ignores the NULL parameter.
If an input parameter is an empty array, normal mode returns NULL, whereas Hive-compatible mode returns an empty string. The syntax is as follows:
```
string concat_ws(string <separator>, array<string>  arr)
```

Examples:

Normal mode

set odps.sql.hive.compatible=false;
select concat_ws(',', 'a', null, 'b');
-- Returns NULL.

select concat_ws(',', array());
-- Returns NULL.

Hive-compatible mode

set odps.sql.hive.compatible=true;
select concat_ws(',', 'a', null, 'b');
-- The following result is returned:
+-----+
| _c0 |
+-----+
| a,b |
+-----+

select concat_ws(',', array());
-- Returns an empty string.

Hive

select concat_ws(',', 'a', null, 'b');
-- The following result is returned:
+-----+
| _c0 |
+-----+
| a,b |
+-----+

select concat_ws(',', array());
-- Returns an empty string.

COT
If the input parameter is 0 or another number that results in infinity, normal mode returns NULL and may report an error, whereas Hive-compatible mode returns INF.
Examples:
- Normal mode
```
set odps.sql.hive.compatible=false;
select cot(a) from values(0.0) t(a);
-- Returns NULL and may report an error.
```
- Hive-compatible mode
```
set odps.sql.hive.compatible=true;
select cot(a) from values(0.0) t(a);
-- Returns INF.
```
- Hive does not support this function.

EXP

If the result of the EXP function overflows and strict mode (odps.function.strictmode=true) is enabled, normal mode may report an error, whereas Hive-compatible mode returns INF.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select exp(a) from values (1000L) t(a);
-- An error is reported: Data overflow.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select exp(a) from values (1000L) t(a);
-- Returns INF.

Hive
```
select exp(1000L) ;
-- Returns INF.
```

FIND_IN_SET

The FIND_IN_SET function returns a BIGINT in normal mode and an INT in Hive-compatible mode.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
explain select find_in_set('ab', 'abc,hello,ab,c');
-- Shows the return type is BIGINT.

Hive-compatible mode

set odps.sql.hive.compatible=true;
explain select find_in_set('ab', 'abc,hello,ab,c');
-- Shows the return type is INT.

Hive

explain select find_in_set('ab', 'abc,hello,ab,c');
-- Shows the return type is INT.

FLOOR

If the input parameter is a DECIMAL, the FLOOR function returns a BIGINT in normal mode and a DECIMAL in Hive-compatible mode.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
explain select floor(1.2BD);
-- Shows the return type is BIGINT.

Hive-compatible mode

set odps.sql.hive.compatible=true;
explain select floor(1.2BD);
-- Shows the return type is DECIMAL.

Hive

explain select floor(1.2BD);
-- Shows the return type is DECIMAL(2,0).

FROM_UNIXTIME

Normal mode supports only the following function signature and does not allow you to specify a time format.
```
DATETIME FROM_UNIXTIME(BIGINT time)
```
Hive-compatible mode supports the following two function signatures. Both signatures return a STRING. The first signature lets you specify an output time format, which uses SimpleDateFormat. For more information, see SimpleDateFormat.
```
STRING FROM_UNIXTIME(BIGINT time, STRING format)

STRING FROM_UNIXTIME(BIGINT time)
```

Examples:

Normal mode

set odps.sql.hive.compatible=false;

select from_unixtime(12345678, 'yyyy-MM-dd HH:mm:ss');
-- An error is reported. from_unixtime allows only one input parameter.

select weekday(from_unixtime(0));
-- The command runs successfully. from_unixtime returns a DATETIME, and weekday accepts a DATETIME input parameter.

Hive-compatible mode

set odps.sql.hive.compatible=true;

select from_unixtime(12345678, 'yyyy-MM-dd HH:mm:ss');
-- The following result is returned:
+---------------------+
| _c0                 |
+---------------------+
| 1970-05-24 05:21:18 |
+---------------------+

select weekday(from_unixtime(0));
-- The command fails because from_unixtime returns a STRING, and weekday does not accept a STRING input parameter.

Hive

select from_unixtime(12345678, 'yyyy-MM-dd HH:mm:ss');
-- The following result is returned:
+---------------------+
| _c0                 |
+---------------------+
| 1970-05-24 05:21:18 |
+---------------------+

select weekday(from_unixtime(0));
-- Hive does not support the weekday function.

FROM_UTC_TIMESTAMP

If the input parameter is out of range and strict mode (odps.function.strictmode=true) is enabled, normal mode may report an error, whereas Hive-compatible mode returns NULL.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select from_utc_timestamp(1501557840000000, 'UTC');
-- An error is reported because the input parameter is out of range.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select from_utc_timestamp(1501557840000000, 'UTC');
-- Returns NULL.

Hive

select from_utc_timestamp(1501557840000000, 'UTC');
-- An error is reported: FAILED: IllegalArgumentException Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

HASH

The HASH function returns a BIGINT in normal mode and an INT in Hive-compatible mode.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
explain select hash('0', '2', '4');
-- The return type is BIGINT.

Hive-compatible mode

set odps.sql.hive.compatible=true;
explain select hash(0, 2, 4);
-- The return type is INT.

Hive

explain select hash(0, 2, 4);
-- The return type is INT.

IS_ENCODING
This function checks if an input string can be converted from a source character set (from_encoding) to a target character set (to_encoding). This function can also be used to determine if the input contains garbled characters. You can typically set from_encoding to UTF-8 and to_encoding to GBK.
- In normal mode, the function returns true only if the input string can be successfully decoded using from_encoding and then encoded using to_encoding.
- In Hive compatible mode, a false result is returned only if the input string is UTF-8 encoded and can be encoded using both from_encoding and to_encoding.
Examples:
- Normal mode
```
set odps.sql.hive.compatible=false;
select is_encoding('Chinese characters', 'gbk', 'utf-8');
-- Returns false. 'Chinese characters' is UTF-8 encoded and cannot be decoded by GBK.
```
- Hive-compatible mode
```
set odps.sql.hive.compatible=true;
select is_encoding('Chinese characters', 'gbk', 'utf-8');
-- Returns true. 'Chinese characters' can be encoded as both GBK and UTF-8.
```
- Hive does not support this syntax.

INSTR

If the input parameters are two strings, the function returns a BIGINT in normal mode and an INT in Hive-compatible mode.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
explain select instr('Tech on the net', 'e');
-- Returns a BIGINT.

Hive-compatible mode

set odps.sql.hive.compatible=true;
explain select instr('Tech on the net', 'e');
-- Returns an INT.

Hive

explain select instr('Tech on the net', 'e');
-- Returns an INT.

LENGTH

The LENGTH function returns a BIGINT in normal mode and an INT in Hive-compatible mode.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
explain select length('hello');
-- Returns a BIGINT.

Hive-compatible mode

set odps.sql.hive.compatible=true;
explain select length('hello');
-- Returns an INT.

Hive

explain select length('hello');
-- Returns an INT.

LENGTHB

The LENGTHB function returns a BIGINT in normal mode and an INT in Hive-compatible mode.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
explain select lengthb('hello');
-- Returns a BIGINT.

Hive-compatible mode

set odps.sql.hive.compatible=true;
explain select lengthb('hello');
-- Returns an INT.

Hive does not support this function.

If the input parameter is out of range and strict mode (odps.function.strictmode=true) is enabled, normal mode may report an error, whereas Hive-compatible mode returns NULL.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select ln(a) from values(-1.0) t(a);
-- An error is reported because the input parameter is out of range.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select ln(a) from values(-1.0) t(a);
-- Returns NULL.

Hive
```
select ln(-1.0) ;
-- Returns NULL.
```

LOCATE

The LOCATE function returns a BIGINT in normal mode and an INT in Hive-compatible mode.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
explain select locate('ab', 'abchelloabc');
-- Returns a BIGINT.

Hive-compatible mode

set odps.sql.hive.compatible=true;
explain select locate('ab', 'abchelloabc');
-- Returns an INT.

Hive

explain select locate('ab', 'abchelloabc');
-- Returns an INT.

LOG

If the input parameter is out of range and strict mode (odps.function.strictmode=true) is enabled, normal mode may report an error, whereas Hive-compatible mode returns NULL.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select log(a, 10) from values(-3.0) t(a);
-- An error is reported because the input parameter is out of range.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select log(a, 10) from values(-3.0) t(a);
-- Returns NULL.

Hive
```
select log(-3.0, 10) ;
-- Returns NULL.
```

MOD

If an input parameter is invalid and strict mode (odps.function.strictmode=true) is enabled, normal mode may report an error, whereas Hive-compatible mode returns NULL.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select  1L % a from values(0L) t(a);
-- An error is reported because the data overflows.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select  1L % a from values(0L) t(a);
-- Returns NULL.

Hive
```
select  1L % 0L;
-- Returns NULL.
```

PMOD

If an input parameter is invalid and strict mode (odps.function.strictmode=true) is enabled, normal mode may report an error, whereas Hive-compatible mode returns NULL.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select pmod(1L, a) from values(0L) t(a);
-- An error is reported because the data overflows.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select pmod(1L, a) from values(0L) t(a);
-- Returns NULL.

Hive
```
select pmod(1L, 0L) ;
-- Returns NULL.
```

POW

If the result overflows and strict mode (odps.function.strictmode=true) is enabled, normal mode may report an error, whereas Hive-compatible mode returns INF.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strict.mode=true;
select pow(a, 1000L) from values(1000L) t(a);
-- An error is reported because the data overflows.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strict.mode=true;
select pow(a, 1000L) from values(1000L) t(a);
-- Returns INF.

Hive

select pow(1000L, 1000L) ;
-- Returns INF.

REPEAT

If the number of repetitions is negative, normal mode reports an error, whereas Hive-compatible mode returns an empty string.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
select repeat('hi', n) from values (-1L) t(n);
-- An error is reported because the input parameter is out of range.

Hive-compatible mode

set odps.sql.hive.compatible=true;
select repeat('hi', n) from values (-1L) t(n);
-- Returns an empty string.

Hive does not support this function.

REVERSE

If the input string contains Chinese characters, normal mode reverses the string byte by byte, which may result in garbled characters. Hive-compatible mode processes the string using UTF-8, which prevents garbled characters.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
select reverse(a) from values ('hello中国world') t(a);
--The result contains garbled characters.

Hive-compatible mode

set odps.sql.hive.compatible=true;
select reverse(a) from values ('helloChinaworld') t(a);
-- The result does not contain garbled characters.

Hive

select reverse('helloChinaworld') ;
-- Returns dlrowanihColleh

ROUND

In normal mode, the ROUND function supports only DOUBLE and DECIMAL input data types. Other data types are implicitly converted to one of these two types. In Hive-compatible mode, the function supports more data types, such as DOUBLE, DECIMAL, BIGINT, INT, SMALLINT, and TINYINT.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
explain select round(a) from values(2L) t(a);
-- The execution plan shows that the input data is converted from BIGINT to DOUBLE, and the result is a DOUBLE.

Hive-compatible mode

set odps.sql.hive.compatible=true;
explain select round(a) from values(2L) t(a);
-- The execution plan shows that the input is a BIGINT and the output is a BIGINT.

Hive

explain select round(2L) ;
-- The execution plan shows that the input is a BIGINT and the output is a BIGINT.

SIGN

If the input parameter is a DECIMAL, the function returns a BIGINT in normal mode and an INT in Hive-compatible mode.
If the input parameter is a DOUBLE and its absolute value is very close to 0, the function returns 0 in normal mode.

Examples:

Normal mode

set odps.sql.hive.compatible=false;

set odps.sql.hive.compatible=false;         
explain select sign(a) from values(cast(2 as decimal(10,0))) t(a);     
--According to the execution plan, the return type is bigint.         

select sign(a) from values (0.000000000000009) t(a);     
--The return value is 0.0 because the input parameter is very close to 0.

Hive-compatible mode

set odps.sql.hive.compatible=true;

explain select sign(a) from values(2BD) t(a);
-- The execution plan shows the return type is INT.

select sign(a) from values (0.000000000000009) t(a);
-- The return value is 1.0.

Hive

explain select sign(2BD);
-- The execution plan shows the return type is INT.

select sign(0.000000000000009) ;
-- The return value is 1.

SIZE

The SIZE function returns a BIGINT in normal mode and an INT in Hive-compatible mode.
If the input parameter is NULL, the function returns NULL in normal mode, whereas it returns -1 in Hive-compatible mode.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
explain select size(array('a','b'));
-- The execution plan shows the return type is BIGINT.

select size(a) from values (cast(NULL as array<bigint>)) t(a);
-- The result is NULL.

Hive-compatible mode

set odps.sql.hive.compatible=true;
explain select size(array('a','b'));
-- The execution plan shows the return type is INT.

select size(a) from values (cast(NULL as array<bigint>)) t(a);
-- The result is -1.

Hive

explain select size(array('a','b'));
-- The execution plan shows the return type is INT.

SPLIT

If the separator is an empty string, the function returns an empty array in normal mode. In Hive-compatible mode, the function splits the input string into individual characters based on the UTF-8 format.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
select split(a, '') from values ('helloChinaworld') t(a);
-- Returns an empty array.

Hive-compatible mode

set odps.sql.hive.compatible=true;
select split(a, '') from values ('helloChinaworld') t(a);
-- Returns [, h, e, l, l, o, C, h, i, n, a, w, o, r, l, d, ]

Hive

select split('helloChinaworld', '') ;
-- Returns ["h","e","l","l","o","C","h","i","n","a","w","o","r","l","d",""]

SQRT

If the input parameter is less than 0 and strict mode (odps.function.strictmode=true) is enabled, normal mode may report an error, whereas Hive-compatible mode returns NULL.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select sqrt(a) from values (-100.0) t(a);
-- An error is reported because the input parameter is less than 0.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select sqrt(a) from values (-100.0) t(a);
-- Returns NULL.

Hive
```
select sqrt(-100.0);
-- Returns NULL.
```

SUBSTR

If the start position is 0, the function returns an empty string in normal mode. In Hive-compatible mode, the behavior is the same as when the start position is 1.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select substr(a, 0) from values ('hello, world') t(a);
-- Returns an empty string.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select substr(a, 0) from values ('hello, world') t(a);
-- The following result is returned:
+--------------+
| _c0          |
+--------------+
| hello, world |
+--------------+

Hive

select substr('hello, world', 0);
-- The following result is returned:
+--------------+
| _c0          |
+--------------+
| hello, world |
+--------------+

UNIX_TIMESTAMP

Normal mode does not support two STRING input parameters and reports an error if they are used. Hive-compatible mode supports two STRING input parameters, as shown in the following signature. The function returns a BIGINT. The format parameter specifies the time format. For more information, see SimpleDateFormat.

bigint FROM_UNIXTIME(string timeString, String format)

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select unix_timestamp('99999-01-01 00:00:00');
-- An error is reported because the input parameter is invalid.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select unix_timestamp('99999-01-01 00:00:00');
-- Returns NULL.

Hive

select unix_timestamp('2022/7/8', 'yyyy/MM/dd');
-- The following result is returned:
+------------+
| _c0        |
+------------+
| 1657209600 |
+------------+
select unix_timestamp('99999-01-01 00:00:00');
-- Returns NULL.

URL_DECODE

If an input parameter is invalid and strict mode (odps.function.strictmode=true) is enabled, normal mode may report an error, whereas Hive-compatible mode returns NULL.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;

select url_decode(a) from values ('%2') t(a);
-- An error is reported because the input parameter is invalid.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;

select url_decode(a) from values ('%2') t(a);
-- Returns NULL.

Hive does not support this function.

URL_ENCODE

If an input parameter is invalid or the conversion fails and strict mode (odps.function.strictmode=true) is enabled, normal mode may report an error, whereas Hive-compatible mode returns NULL.

Examples:

Normal mode

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;

select url_encode(a, 'ascii') from values ('example') t(a);
-- Error: The input parameter is invalid.

Hive-compatible mode

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;

select url_encode(a, 'ascii') from values ('Sample') t(a);
-- Returns NULL.

Hive does not support this function.