Hive-Compatible Mode SQL Behavioral Differences in MaxCompute - MaxCompute

This topic covers the behavioral differences in operators, type conversions, and built-in functions between normal mode (odps.sql.hive.compatible=false) and Hive-compatible mode (odps.sql.hive.compatible=true) in MaxCompute.

How the two modes differ

The two modes follow different philosophies for handling edge cases:

Normal mode is strict: invalid inputs or out-of-range results raise an error when strict mode (odps.function.strictmode=true) is enabled, or return NULL.
Hive-compatible mode is lenient: the same edge cases silently return NULL, NAN, INF, or an empty string—matching Apache Hive behavior.

This pattern applies across all operators, type conversions, and built-in functions listed in this topic. Use the following sections to look up a specific operator or function and understand exactly how the output differs between the two modes.

Quick reference

Category	Count	Common pattern
Operators	13	Normal mode: error or NULL on edge cases; Hive-compatible mode: wrap-around or NULL
Type conversions	5	Normal mode: error on invalid input; Hive-compatible mode: NULL
Built-in functions	34	Varies by function; see individual entries

Operators

Bitwise operators: BITAND (&), BITOR (|), BITXOR (^)

When the input is BIGINT and the result equals LONG_MIN (-2^63^), normal mode returns NULL, whereas Hive-compatible mode returns LONG_MIN.

BITAND (&)

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 (|)

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 (^)

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.

Comparison operators on DOUBLE: EQ (=), NEQ (!=), GE (>=), GT (>), LE (<=), LT (<)

When both inputs are DOUBLE, normal mode uses a less strict equality check: two values are considered equal if they are close enough. Hive-compatible mode uses exact comparison, matching Hive behavior.

This affects all six comparison operators. In the examples below, 1.000000000000001 and 1.0 are close enough to be treated as equal in normal mode.

EQ (=)

Normal mode

set odps.sql.hive.compatible=false;
select a = 1.0 from values (1.000000000000001) t(a);
-- Returns 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);
-- Returns false.

Hive

select 1.0 = 1.000000000000001;
-- Returns false.

NEQ (!=)

Normal mode

set odps.sql.hive.compatible=false;
select a != 1.0 from values (1.000000000000001) t(a);
-- Returns 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);
-- Returns true.

Hive

select 1.000000000000001 != 1.0;
-- Returns true.

GE (>=)

When the two values are close enough, normal mode treats them as equal. As a result, GE may return true even when the first value is slightly less than the second.

Normal mode

set odps.sql.hive.compatible=false;
select 1.0 >= a from values (1.000000000000001) t(a);
-- Returns true because the two numbers are close enough and are considered equal.

Hive-compatible mode

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

Hive

select 1.0 >= 1.000000000000001;
-- Returns false.

GT (>)

When the two values are close enough, normal mode treats them as equal. As a result, GT may return false even when the first value is slightly greater than the second.

Normal mode

set odps.sql.hive.compatible=false;
select a > 1.0 from values (1.000000000000001) t(a);
-- Returns false because the two numbers are close enough and are considered equal.

Hive-compatible mode

set odps.sql.hive.compatible=true;
select a > 1.0 from values (1.000000000000001) t(a);
-- Returns true.

Hive

select 1.000000000000001 > 1.0;
-- Returns true.

LE (<=)

When the two values are close enough, normal mode treats them as equal. As a result, LE may return true even when the first value is slightly greater than the second.

Normal mode

set odps.sql.hive.compatible=false;
select a <= 1.0 from values (1.000000000000001) t(a);
-- Returns true because the two numbers are close enough and are considered equal.

Hive-compatible mode

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

Hive

select 1.000000000000001 <= 1.0;
-- Returns false.

LT (<)

When the two values are close enough, normal mode treats them as equal. As a result, LT may return false even when the first value is slightly less than the second.

Normal mode

set odps.sql.hive.compatible=false;
select 1.0 < a from values (1.000000000000001) t(a);
-- Returns false because the two numbers are close enough and are considered equal.

Hive-compatible mode

set odps.sql.hive.compatible=true;
select 1.0 < a from values (1.000000000000001) t(a);
-- Returns true.

Hive

select 1.0 < 1.000000000000001;
-- Returns true.

Arithmetic operators with overflow: PLUS (+), MINUS (-), MPL (*), DIV (/)

All four arithmetic operators handle overflow differently. In normal mode, an overflow raises an error when strict mode (odps.function.strictmode=true) is enabled. In Hive-compatible mode, the result wraps around silently with no error.

PLUS (+)

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 (-)

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 (*)

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 (/)

Division by zero also follows the strict vs. lenient pattern. Normal mode raises an error in strict mode; Hive-compatible mode returns NULL.

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);
-- Returns NULL.

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

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

Hive

select 1 / 0L;
-- Returns NULL.

select 1.0 / 0.0;
-- Returns NULL.

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

Type conversions

All five type conversion functions follow the same pattern: when the input is invalid or out of range, normal mode raises an error in strict mode, whereas Hive-compatible mode returns NULL.

TOBIGINT

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

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

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

When the input is invalid or overflows, normal mode raises an error. Hive-compatible mode returns NULL for invalid inputs and silently wraps on overflow.

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

When the input is invalid or overflows, normal mode raises an error. Hive-compatible mode returns NULL for invalid inputs and silently wraps on overflow.

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 and ASIN

Both functions behave identically across modes. When the input is outside the valid range [-1, 1], normal mode returns NULL and may raise an error. Hive-compatible mode returns NAN.

ACOS

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.
```

ASIN

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.

ASCII

ASCII returns BIGINT in normal mode and INT in Hive-compatible mode.

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.

CEIL

When the input is DECIMAL, CEIL returns BIGINT in normal mode and DECIMAL in Hive-compatible mode.

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

When the input is out of range, normal mode raises an error. Hive-compatible mode returns an empty string.

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

Two differences apply:

If an input parameter is NULL, normal mode returns NULL. Hive-compatible mode skips the NULL value.
If the input is an empty array (using the string concat_ws(string <separator>, array<string> arr) signature), normal mode returns NULL. Hive-compatible mode returns an empty string.

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

When the input is 0 or another value that would produce infinity, normal mode returns NULL and may raise an error. Hive-compatible mode returns INF. Hive does not support this function.

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.

EXP

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

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

FIND_IN_SET returns BIGINT in normal mode and INT in Hive-compatible mode.

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

When the input is DECIMAL, FLOOR returns BIGINT in normal mode and DECIMAL in Hive-compatible mode.

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

The two modes support different function signatures:

Normal mode: DATETIME FROM_UNIXTIME(BIGINT time) — accepts one parameter and returns DATETIME. Specifying a time format raises an error.
Hive-compatible mode: STRING FROM_UNIXTIME(BIGINT time) and STRING FROM_UNIXTIME(BIGINT time, STRING format) — both return STRING. The optional format parameter uses SimpleDateFormat.

Because the return types differ, functions that accept DATETIME input (such as weekday) work in normal mode but fail in Hive-compatible mode.

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));
-- Runs successfully. from_unixtime returns DATETIME, and weekday accepts DATETIME.

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 STRING, and weekday does not accept STRING.

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

When the input is out of range and strict mode (odps.function.strictmode=true) is enabled, normal mode raises an error. Hive-compatible mode returns NULL.

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

HASH returns BIGINT in normal mode and INT in Hive-compatible mode.

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

IS_ENCODING checks whether an input string can be converted from a source character set (from_encoding) to a target character set (to_encoding). A common use case is detecting garbled characters by setting from_encoding to UTF-8 and to_encoding to GBK.

The two modes use different logic:

Normal mode: returns true only if the input can be successfully decoded using from_encoding and then encoded using to_encoding.
Hive-compatible mode: returns false only if the input is UTF-8 encoded and can be encoded using both from_encoding and to_encoding.

Hive does not support this function.

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.

INSTR

When both inputs are strings, INSTR returns BIGINT in normal mode and INT in Hive-compatible mode.

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

LENGTH returns BIGINT in normal mode and INT in Hive-compatible mode.

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

LENGTHB returns BIGINT in normal mode and INT in Hive-compatible mode. Hive does not support this function.

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.

LN

When the input is out of range and strict mode (odps.function.strictmode=true) is enabled, normal mode raises an error. Hive-compatible mode returns NULL.

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

LOCATE returns BIGINT in normal mode and INT in Hive-compatible mode.

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

When the input is out of range and strict mode (odps.function.strictmode=true) is enabled, normal mode raises an error. Hive-compatible mode returns NULL.

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

When the input is invalid and strict mode (odps.function.strictmode=true) is enabled, normal mode raises an error. Hive-compatible mode returns NULL.

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

When the input is invalid and strict mode (odps.function.strictmode=true) is enabled, normal mode raises an error. Hive-compatible mode returns NULL.

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

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

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

When the number of repetitions is negative, normal mode raises an error. Hive-compatible mode returns an empty string. Hive does not support this function.

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.

REVERSE

When the input string contains multibyte characters (such as Chinese characters), normal mode reverses byte by byte, which may produce garbled characters. Hive-compatible mode reverses the string by UTF-8 character, preserving multibyte characters correctly.

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, ROUND accepts only DOUBLE and DECIMAL inputs; other types are implicitly converted to one of these. In Hive-compatible mode, ROUND natively supports more types, including DOUBLE, DECIMAL, BIGINT, INT, SMALLINT, and TINYINT—and returns the same type as the input.

Normal mode

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

Hive-compatible mode

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

Hive

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

SIGN

Two differences apply:

When the input is DECIMAL, SIGN returns BIGINT in normal mode and INT in Hive-compatible mode.
When the input is a DOUBLE value whose absolute value is very close to 0, normal mode returns 0.

Normal mode

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);
-- Returns 0.0 because the input 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);
-- Returns 1.0.

Hive

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

select sign(0.000000000000009);
-- Returns 1.

SIZE

Two differences apply:

SIZE returns BIGINT in normal mode and INT in Hive-compatible mode.
When the input is NULL, SIZE returns NULL in normal mode and -1 in Hive-compatible mode.

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);
-- Returns 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);
-- Returns -1.

Hive

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

SPLIT

When the separator is an empty string, normal mode returns an empty array. Hive-compatible mode splits the input into individual UTF-8 characters.

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

When the input is less than 0 and strict mode (odps.function.strictmode=true) is enabled, normal mode raises an error. Hive-compatible mode returns NULL.

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

When the start position is 0, normal mode returns an empty string. Hive-compatible mode treats position 0 the same as position 1 and returns the full string from the beginning.

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 accepts only a single STRING input and raises an error if two parameters are provided. Hive-compatible mode supports two STRING parameters: bigint UNIX_TIMESTAMP(string timeString, String format), where format follows SimpleDateFormat. Both modes return BIGINT.

When the input is out of range, normal mode raises an error. Hive-compatible mode returns NULL.

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

When the input is invalid and strict mode (odps.function.strictmode=true) is enabled, normal mode raises an error. Hive-compatible mode returns NULL. Hive does not support this function.

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.

URL_ENCODE

When the input is invalid or the conversion fails and strict mode (odps.function.strictmode=true) is enabled, normal mode raises an error. Hive-compatible mode returns NULL. Hive does not support this function.

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.