Differences in SQL statements between the MaxCompute V1.0 data type edition and Hive-compatible data type edition - MaxCompute

This topic describes the differences in operators, data type conversion, and built-in functions between the MaxCompute V1.0 data type edition and Hive-compatible data type edition.

Operators

BITAND (&)

If the input parameters are of the BIGINT type and the calculation result of BITAND is LONG_MIN(-2⁶³), NULL is returned in the MaxCompute V1.0 data type edition and LONG_MIN is returned in the Hive-compatible data type edition.

Sample code:

MaxCompute V1.0 data type edition

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

Hive-compatible data type edition

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

Hive

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

BITOR (|)

If the input parameters are of the BIGINT type and the calculation result of BITOR is LONG_MIN(-2⁶³), NULL is returned in the MaxCompute V1.0 data type edition and LONG_MIN is returned in the Hive-compatible data type edition.

Sample code:

MaxCompute V1.0 data type edition

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

Hive-compatible data type edition

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

Hive

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

BITXOR (^)

If the input parameters are of the BIGINT type and the calculation result of BITXOR is LONG_MIN(-2⁶³), NULL is returned in the MaxCompute V1.0 data type edition and LONG_MIN is returned in the Hive-compatible data type edition.

Sample code:

MaxCompute V1.0 data type edition

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

Hive-compatible data type edition

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

Hive

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

EQ (=)
If the input parameters are of the DOUBLE type, the check for equality is more relaxed in the MaxCompute V1.0 data type edition. If the values of two input parameters are close to each other, the values may be considered equal. In the Hive-compatible data type edition, the check for equality is stricter.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
select a = 1.0 from values (1.000000000000001) t(a);
-- The return value is true because the two values are close to each other.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
select a = 1.0 from values (1.000000000000001) t(a);
-- The return value is false.
```
- Hive
```
select 1.0 = 1.000000000000001 ;
-- The return value is false.
```
NEQ (!=)
If the input parameters are of the DOUBLE type, the check for equality is more relaxed in the MaxCompute V1.0 data type edition. If the values of two input parameters are close to each other, the values may be considered equal. In the Hive-compatible data type edition, the check for equality is stricter.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
select a != 1.0 from values (1.000000000000001) t(a);
-- The return value is false because the two values are close to each other.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
select a != 1.0 from values (1.000000000000001) t(a);
-- The return value is true.
```
- Hive
```
select 1.000000000000001 != 1.0 ;
-- The return value is true.
```
GE (>=)
If the input parameters are of the DOUBLE type, the check for equality is more relaxed in the MaxCompute V1.0 data type edition. If the values of two input parameters are close to each other, the values may be considered equal. Therefore, in scenarios where the values of input parameters are close to each other, the return value of GE may be true even if the value of the first input parameter is less than the value of the second input parameter.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
select 1.0 >= a from values (1.000000000000001) t(a);
-- The return value is true because the two values are close to each other and are considered equal.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
select 1.0 >= a from values (1.000000000000001) t(a);
-- The return value is false.
```
- Hive
```
select 1.0 >= 1.000000000000001;
-- The return value is false.
```
GT (>)
If the input parameters are of the DOUBLE type, the check for equality is more relaxed in the MaxCompute V1.0 data type edition. If the values of two input parameters are close to each other, the values may be considered equal. Therefore, in scenarios where the values of input parameters are close to each other, the return value of GT may be false even if the value of the first input parameter is greater than the value of the second input parameter.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;l
select a > 1.0 from values (1.000000000000001) t(a);
-- The return value is false because the two values are close to each other and are considered equal.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
select a > 1.0 from values (1.000000000000001) t(a);
-- The return value is true.
```
- Hive
```
select 1.000000000000001>1.0;
-- The return value is true.
```
LE (<=)
If the input parameters are of the DOUBLE type, the check for equality is more relaxed in the MaxCompute V1.0 data type edition. If the values of two input parameters are close to each other, the values may be considered equal. Therefore, in scenarios where the values of input parameters are close to each other, the return value of LE may be true even if the value of the first input parameter is greater than the value of the second input parameter.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
select a <= 1.0 from values (1.000000000000001) t(a);
-- The return value is true because the two values are close to each other and are considered equal.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
select a <= 1.0 from values (1.000000000000001) t(a);
-- The return value is false.
```
- Hive
```
select 1.000000000000001 <= 1.0 ;
-- The return value is false.
```
LT (<)
If the input parameters are of the DOUBLE type, the check for equality is more relaxed in the MaxCompute V1.0 data type edition. If the values of two input parameters are close to each other, the values may be considered equal. Therefore, in scenarios where the values of input parameters are close to each other, the return value of LT may be false even if the value of the first input parameter is less than the value of the second input parameter.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
select 1.0 < a from values (1.000000000000001) t(a);
-- The return value is false because the two values are close to each other and are considered equal.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
select 1.0 < a from values (1.000000000000001) t(a);
-- The return value is true.
```
- Hive
```
select 1.0 < 1.000000000000001;
-- The return value is true.
```

PLUS (+)

The processing mechanism is different if the calculation result exceeds a specific range. If the calculation result overflows, an error may be returned in the MaxCompute V1.0 data type edition but no error is returned in the Hive-compatible data type edition. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.

Sample code:

MaxCompute V1.0 data type edition

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

Hive-compatible data type edition

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

Hive

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

MINUS (-)
The processing mechanism is different if the calculation result exceeds a specific range. If the calculation result overflows, an error may be returned in the MaxCompute V1.0 data type edition but no error is returned in the Hive-compatible data type edition. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select (-100L - a) from values (9223372036854775807L) t(a);
-- An error is returned. The calculation result overflows.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select (-100L - a) from values (9223372036854775807L) t(a);
-- The calculation result overflows, but no error is returned.
```
- Hive
```
select (-100L - 9223372036854775807L) ;
-- 9223372036854775709 is returned.
```

MPL (*)

Sample code:

MaxCompute V1.0 data type edition

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

Hive-compatible data type edition

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

Hive

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

DIV (/)

Sample code:

MaxCompute V1.0 data type edition

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

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

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

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

Hive-compatible data type edition

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;
-- NULL is returned.

select 1.0 / 0.0;
-- NULL is returned.

select 1BD / 0BD;
-- NULL is returned.

Data type conversion

TOBIGINT
If the input parameters are invalid, an error may be returned in the MaxCompute V1.0 data type edition. In the Hive-compatible data type edition, NULL is returned. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select cast(a as bigint) from values ('hello') t(a);
-- An error is returned. The input parameters are invalid.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select cast(a as bigint) from values ('hello') t(a);
-- NULL is returned.
```
- Hive
```
select cast('hello' as bigint) ;
-- NULL is returned.
```
TODECIMAL
If the input parameters are invalid, an error may be returned in the MaxCompute V1.0 data type edition. In the Hive-compatible data type edition, NULL is returned. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select cast(a as decimal) from values ('hello') t(a);
-- An error is returned. The input parameters are invalid.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select cast(a as decimal) from values ('hello') t(a);
-- NULL is returned.
```
- Hive
```
select cast('hello' as decimal) ;
-- NULL is returned.
```
TODOUBLE
If the input parameters are invalid, an error may be returned in the MaxCompute V1.0 data type edition. In the Hive-compatible data type edition, NULL is returned. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select cast(a as double) from values ('hello') t(a);
-- An error is returned. The input parameters are invalid.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select cast(a as double) from values ('hello') t(a);
-- NULL is returned.
```
- Hive
```
select cast('hello' as double) ;
-- NULL is returned.
```

TOSMALLINT

If the input parameters are invalid or exceed a specific range, an error may be returned in the MaxCompute V1.0 data type edition. In the Hive-compatible data type edition, NULL is returned. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.

Sample code:

MaxCompute V1.0 data type edition

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 returned. The input parameters are invalid.

select cast(a as smallint) from values (9223372036854775807L) t(a);
-- An error is returned. Data overflows.

Hive-compatible data type edition

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

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

Hive

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

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

TOTINYINT

Sample code:

MaxCompute V1.0 data type edition

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 returned. The input parameters are invalid.

select cast(a as tinyint) from values (9223372036854775807L) t(a);
-- An error is returned. Data overflows.

Hive-compatible data type edition

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

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

Hive

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

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

Built-in functions

ACOS
If the value of an input parameter is out of the range [-1,1], the return value in the MaxCompute V1.0 data type edition is different from the return value in the Hive-compatible data type edition. In the MaxCompute V1.0 data type edition, NULL is returned and an error may be returned. In the Hive-compatible data type edition, NAN is returned.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
select acos(a) from values(1.5) t(a);
-- NULL is returned and an error may be returned.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
select acos(a) from values(1.5) t(a);
-- NAN is returned.
```
- Hive
```
select acos(1.5);
-- NAN is returned.
```
ASCII
In the MaxCompute V1.0 data type edition, the return value of the ASCII function is of the BIGINT data type. In the Hive-compatible data type edition, the return value of the ASCII function is of the INT data type.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
explain select ascii('abcde');
-- The return value is of the BIGINT data type.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
explain select ascii('abcde');
-- The return value is of the INT data type.
```
- Hive
```
explain select ascii('abcde');
-- The return value is of the INT data type
```
ASIN
If the value of an input parameter is out of the range [-1,1], the return value in the MaxCompute V1.0 data type edition is different from the return value in the Hive-compatible data type edition. In the MaxCompute V1.0 data type edition, NULL is returned and an error may be returned. In the Hive-compatible data type edition, NAN is returned.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
select asin(a) from values(1.5) t(a);
-- NULL is returned and an error may be returned.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
select asin(a) from values(1.5) t(a);
-- NAN is returned.
```
- Hive
```
select asin(a) from values(1.5) t(a);
-- NAN is returned.
```
CEIL
If the input parameters of the CEIL function are of the DECIMAL data type, the return value in the MaxCompute V1.0 data type edition is of the BIGINT data type and the return value in the Hive-compatible data type is of the DECIMAL data type.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
explain select ceil(1.2BD);
-- The return value is of the BIGINT data type.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
explain select ceil(1.2BD);
-- The return value is of the DECIMAL data type.
```
- Hive
```
explain select ceil(1.2BD);
-- The return value is of the DECIMAL(2,0) data type.
```
CHR
If the value of an input parameter is out of the range, the return value in the MaxCompute V1.0 data type edition is different from the return value in the Hive-compatible data type edition. In the MaxCompute V1.0 data type edition, an error is returned. In the Hive-compatible data type edition, an empty string is returned.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
select chr(-100L);
-- An error is returned. The input parameters are invalid.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
select chr(-100L);
-- An empty string is returned.
```
- Hive
```
select chr(-100L);
-- An empty string is returned.
```
CONCAT_WS
- If the value of an input parameter is NULL, the return value in the MaxCompute V1.0 data type edition is different from the return value in the Hive-compatible data type edition. In the MaxCompute V1.0 data type edition, NULL is returned. In the Hive-compatible data type edition, the value NULL in the input parameter is ignored.
- If the value of an input parameter is an empty array, NULL is returned in the MaxCompute V1.0 data type edition and an empty string is returned in the Hive-compatible data type edition. Syntax:
```
string concat_ws(string <separator>, array<string>  arr)
```
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
select concat_ws(',', 'a', null, 'b');
-- NULL is returned.

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

select concat_ws(',', array());
-- An empty string is returned.
```
- Hive
```
select concat_ws(',', 'a', null, 'b');
-- The following result is returned:
+-----+
| _c0 |
+-----+
| a,b |
+-----+

select concat_ws(',', array());
-- An empty string is returned.
```
COT
If the value of the input parameter is 0 or a number that causes the calculation result to be infinite, NULL is returned in the MaxCompute V1.0 data type edition and an error may be returned. However, INF is returned in the Hive-compatible data type edition.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
select cot(a) from values(0.0) t(a);
-- NULL is returned and an error may be returned.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
select cot(a) from values(0.0) t(a);
-- INF is returned.
```
- Hive does not support this function.
EXP
If the calculation result of the EXP function exceeds the value range of the output data type and the strict mode is enabled for both data type editions, an error may be returned in the MaxCompute V1.0 data type edition and INF is returned in the Hive-compatible data type edition. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select exp(a) from values (1000L) t(a);
-- An error is returned. Data overflows.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select exp(a) from values (1000L) t(a);
-- INF is returned.
```
- Hive
```
select exp(1000L) ;
-- INF is returned.
```

FIND_IN_SET

In the MaxCompute V1.0 data type edition, the return value of the FIND_IN_SET function is of the BIGINT data type. In the Hive-compatible data type edition, the return value of the FIND_IN_SET function is of the INT data type.

Sample code:

MaxCompute V1.0 data type edition

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

Hive-compatible data type edition

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

Hive

explain select find_in_set('ab', 'abc,hello,ab,c');
-- The return value is of the INT data type.

FLOOR
If the input parameters of the FLOOR function are of the DECIMAL data type, the return value in the MaxCompute V1.0 data type edition is of the BIGINT data type and the return value in the Hive-compatible data type edition is of the DECIMAL data type.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
explain select floor(1.2BD);
-- The return value is of the BIGINT data type.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
explain select floor(1.2BD);
-- The return value is of the DECIMAL data type.
```
- Hive
```
explain select floor(1.2BD);
-- The return value is of the DECIMAL(2,0) data type.
```

FROM_UNIXTIME

In the MaxCompute V1.0 data type edition, the function signature in the following format is supported. The function signature in the format that includes the specified time is not supported.
```
DATETIME FROM_UNIXTIME(BIGINT time)
```
In the Hive-compatible data type edition, the function signatures in the following formats are supported. The return values of the function signatures in these formats are of the STRING data type. The first signature allows you to specify the output time format. The output time format is specified by the SimpleDateFormat parameter. For more information, see SimpleDateFormat.
```
STRING FROM_UNIXTIME(BIGINT time, STRING format)

STRING FROM_UNIXTIME(BIGINT time)
```

Sample code:

MaxCompute V1.0 data type edition

set odps.sql.hive.compatible=false;

select from_unixtime(12345678, 'yyyy-MM-dd HH:mm:ss');
-- An error is returned. The FROM_UNIXTIME function allows only one input parameter and does not allow two input parameters.

select weekday(from_unixtime(0));
-- The execution is successful. The return value of the FROM_UNIXTIME function is of the DATETIME data type. The input parameters of the WEEKDAY function can be of the DATETIME data type.

Hive-compatible data type edition

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 execution fails because the return value of the FROM_UNIXTIME function is of the STRING data type but the input parameters of the WEEKDAY function cannot be of the STRING data type.

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 parameters exceed a specific range and the strict mode is enabled for both data type editions, an error may be returned in the MaxCompute V1.0 data type edition and NULL is returned in the Hive-compatible data type edition. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.

Sample code:

MaxCompute V1.0 data type edition

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

Hive-compatible data type edition

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

Hive

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

HASH
In the MaxCompute V1.0 data type edition, the return value of the HASH function is of the BIGINT data type. In the Hive-compatible data type edition, the return value of the HASH function is of the INT data type.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
explain select hash(0, 2, 4);
-- The return value is of the BIGINT data type.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
explain select hash(0, 2, 4);
-- The return value is of the INT data type.
```
- Hive
```
explain select hash(0, 2, 4);
-- The return value is of the INT data type.
```
IS_ENCODING
This function determines whether the input string can be converted from the character set that is specified by the from_encoding parameter into the character set that is specified by the to_encoding parameter. This function can also be used to determine whether the input string contains garbled characters. In most cases, the from_encoding parameter is set to UTF-8 and the to_encoding parameter is set to GBK.
- In the MaxCompute V1.0 data type edition, false is returned only when the input string can be decoded by using the encoding method that is specified by the from_encoding parameter and encoded by using the encoding method that is specified by the to_encoding parameter.
- In the Hive-compatible data type edition, false is returned only when the input string is encoded in UTF-8 and can be encoded by the encoding methods that are specified by the from_encoding and to_encoding parameters.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
select is_encoding ('Chinese', 'gbk', 'utf-8');
-- 'Chinese' is encoded in UTF-8 and cannot be decoded in GBK. Therefore, false is returned.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
select is_encoding ('Chinese', 'gbk', 'utf-8');
-- 'Chinese' can be converted into either the GBK encoding or the UTF-8 encoding. Therefore, true is returned.
```
- Hive does not support this syntax.

INSTR

If the input parameters are two strings, the return value in the MaxCompute V1.0 data type edition is of the BIGINT data type and the return value in the Hive-compatible data type edition is of the INT data type.

Sample code:

MaxCompute V1.0 data type edition

set odps.sql.hive.compatible=false;
explain select instr('Tech on the net', 'e');
-- The return value is of the BIGINT data type.

Hive-compatible data type edition

set odps.sql.hive.compatible=true;
explain select instr('Tech on the net', 'e');
-- The return value is of the INT data type.

Hive

explain select instr('Tech on the net', 'e');
-- The return value is of the INT data type.

LENGTH
In the MaxCompute V1.0 data type edition, the return value of the LENGTH function is of the BIGINT data type. In the Hive-compatible data type edition, the return value of the LENGTH function is of the INT data type.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
explain select length('hello');
-- The return value is of the BIGINT data type.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
explain select length('hello');
-- The return value is of the INT data type.
```
- Hive
```
explain select length('hello');
-- The return value is of the INT data type.
```
LENGTHB
In the MaxCompute V1.0 data type edition, the return value of the LENGTHB function is of the BIGINT data type. In the Hive-compatible data type edition, the return value of the LENGTHB function is of the INT data type.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
explain select lengthb('hello');
-- The return value is of the BIGINT data type.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
explain select lengthb('hello');
-- The return value is of the INT data type.
```
- Hive does not support this function.
LN
If the input parameters exceed a specific range and the strict mode is enabled for both data type editions, an error may be returned in the MaxCompute V1.0 data type edition and NULL is returned in the Hive-compatible data type edition. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select ln(a) from values(-1.0) t(a);
-- An error is returned. The input parameter is out of range.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select ln(a) from values(-1.0) t(a);
-- NULL is returned.
```
- Hive
```
select ln(-1.0) ;
-- NULL is returned.
```

LOCATE

In the MaxCompute V1.0 data type edition, the return value of the LOCATE function is of the BIGINT data type. In the Hive-compatible data type edition, the return value of the LOCATE function is of the INT data type.

Sample code:

MaxCompute V1.0 data type edition

set odps.sql.hive.compatible=false;
explain select locate('ab', 'abchelloabc');
-- The return value is of the BIGINT data type.

Hive-compatible data type edition

set odps.sql.hive.compatible=true;
explain select locate('ab', 'abchelloabc');
-- The return value is of the INT data type.

Hive

explain select locate('ab', 'abchelloabc');
-- The return value is of the INT data type.

LOG
If the input parameters exceed a specific range and the strict mode is enabled for both data type editions, an error may be returned in the MaxCompute V1.0 data type edition and NULL is returned in the Hive-compatible data type edition. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
set oodps.function.strictmode=true;
select log(a, 10) from values(-3.0) t(a);
-- An error is returned. The input parameter is out of range.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select log(a, 10) from values(-3.0) t(a);
-- NULL is returned.
```
- Hive
```
select log(-3.0, 10) ;
-- NULL is returned.
```
MOD
If the input parameters are invalid and the strict mode is enabled for both data type editions, an error may be returned in the MaxCompute V1.0 data type edition and NULL is returned in the Hive-compatible data type edition. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select  1L % a from values(0L) t(a);
-- An error is returned. Data overflows.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select  1L % a from values(0L) t(a);
-- NULL is returned.
```
- Hive
```
select  1L % 0L;
-- NULL is returned.
```
PMOD
If the input parameters are invalid and the strict mode is enabled for both data type editions, an error may be returned in the MaxCompute V1.0 data type edition and NULL is returned in the Hive-compatible data type edition. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select pmod(1L, a) from values(0L) t(a);
-- An error is returned. Data overflows.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select pmod(1L, a) from values(0L) t(a);
-- NULL is returned.
```
- Hive
```
select pmod(1L, 0L) ;
-- NULL is returned.
```
POW
If the calculation result overflows and the strict mode is enabled for both data type editions, an error may be returned in the MaxCompute V1.0 data type edition and INF is returned in the Hive-compatible data type edition. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
set odps.function.strict.mode=true;
select pow(a, 1000L) from values(1000L) t(a);
-- An error is returned. Data overflows.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
set odps.function.strict.mode=true;
select pow(a, 1000L) from values(1000L) t(a);
-- INF is returned.
```
- Hive
```
select pow(1000L, 1000L) ;
-- INF is returned.
```
REPEAT
If the number of repeated strings is less than zero, an error is returned in the MaxCompute V1.0 data type edition and an empty string is returned in the Hive-compatible data type edition.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
select repeat('hi', n) from values (-1L) t(n);
-- An error is returned. The input parameter is out of range.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
select repeat('hi', n) from values (-1L) t(n);
-- An empty string is returned.
```
- Hive does not support this function.
REVERSE
If the input parameters contain Chinese characters, the reverse operation is performed based on bytes and the return value may contain garbled characters in the MaxCompute V1.0 data type edition. In the Hive-compatible data type edition, the input parameters are processed by using UTF-8 encoding and no garbled characters exist.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
select reverse(a) from values ('hello中国world') t(a);
-- The return value contains garbled characters.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
select reverse(a) from values ('hello中国world') t(a);
-- The return value does not contain garbled characters.
```
- Hive
```
select reverse('hello中国world') ;
-- The value dlrow国中olleh is returned.
```

ROUND

When you use the ROUND function in the MaxCompute V1.0 data type edition, the input parameters must be of the DOUBLE or DECIMAL data type. Other data types are converted into the DOUBLE or DECIMAL data type. When you use the ROUND function in the Hive-compatible data type edition, the input parameters support various data types, including DOUBLE, DECIMAL, BIGINT, INT, SMALLINT, and TINYINT.

Sample code:

MaxCompute V1.0 data type edition

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

Hive-compatible data type edition

set odps.sql.hive.compatible=true;
explain select round(a) from values(2L) t(a);
-- In the execution plan, the data type of the input parameter is BIGINT and the data type of the output is BIGINT.

Hive

explain select round(2L) ;
-- In the execution plan, the data type of the input parameter is BIGINT and the data type of the output is BIGINT.

SIGN

If the input parameters are of the DECIMAL data type, the return value in the MaxCompute V1.0 data type edition is of the BIGINT type and the return value in the Hive-compatible data type edition is of the INT type.
If the input parameter is of the DOUBLE data type and the absolute value of the input parameter is extremely close to 0, the return value in the MaxCompute V1.0 data type edition is 0.

Sample code:

MaxCompute V1.0 data type edition

set odps.sql.hive.compatible=false;

explain select sign(a) from values(2BD) t(a);
-- In the execution plan, the return value is of the BIGINT data type.

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

Hive-compatible data type edition

set odps.sql.hive.compatible=true;

explain select sign(a) from values(2BD) t(a);
-- In the execution plan, the return value is of the INT data type.

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

Hive

explain select sign(2BD);
-- In the execution plan, the return value is of the INT data type.

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

SIZE

In the MaxCompute V1.0 data type edition, the return value of the SIZE function is of the BIGINT data type. In the Hive-compatible data type edition, the return value of the SIZE function is of the INT data type.
If the input parameter is NULL, NULL is returned in the MaxCompute V1.0 data type edition and -1 is returned in the Hive-compatible data type edition.

Sample code:

MaxCompute V1.0 data type edition

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

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

Hive-compatible data type edition

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

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

Hive

explain select size(array('a','b'));
-- In the execution plan, the return value is of the INT data type.

SPLIT

If the delimiter of the input parameters is an empty string, an empty array is returned in the MaxCompute V1.0 data type edition. In the Hive-compatible data type edition, the input string is split based on the UTF-8 format.

Sample code:

MaxCompute V1.0 data type edition

set odps.sql.hive.compatible=false;
select split(a, '') from values ('hellochinaworld') t(a);
-- An empty array is returned.

Hive-compatible data type edition

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

Hive

select split('hellochinaworld', '');
-- ["h","e","l","l","o","c","h","i","n","a","w","o","r","l","d",""] is returned.

SQRT
If the value of an input parameter is less than 0 and the strict mode is enabled for both data type editions, an error may be returned in the MaxCompute V1.0 data type edition and NULL is returned in the Hive-compatible data type edition. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select sqrt(a) from values (-100.0) t(a);
-- An error is returned. The value of the input parameter is less than 0.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select sqrt(a) from values (-100.0) t(a);
-- NULL is returned.
```
- Hive
```
select sqrt(-100.0);
-- NULL is returned.
```

SUBSTR

If the start position in the input parameters is 0, an empty string is returned in the MaxCompute V1.0 data type edition. In the Hive-compatible data type edition, the return value is the same as the return value in scenarios in which the start position in the input parameters is 1.

Sample code:

MaxCompute V1.0 data type edition

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

Hive-compatible data type edition

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

If two input parameters are of the STRING data type, an error is returned in the MaxCompute V1.0 data type edition and no error is returned in the Hive-compatible data type edition. In the following sample code, the return value is of the BIGINT data type. You can use the format parameter to specify the time format. For more information about the time format, see SimpleDateFormat.

bigint FROM_UNIXTIME(string timeString, String format)

Sample code:

MaxCompute V1.0 data type edition

set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select unix_timestamp(a) from values ('99999-01-01 00:00:00');
-- An error is returned. The input parameters are invalid.

Hive-compatible data type edition

set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select unix_timestamp(a) from values ('99999-01-01 00:00:00');
-- NULL is returned.

Hive

select unix_timestamp('2022/7/8', 'yyyy/mm/dd');
-- The following result is returned:
+------------+
| _c0        |
+------------+
| 1641571620 |
+------------+
select unix_timestamp('99999-01-01 00:00:00') ;
-- 3093496416000 is returned.
select unix_timestamp('99999-01-01 00:00:00');
-- 3093496416000 is returned.

URL_DECODE
If the input parameters are invalid and the strict mode is enabled for both data type editions, an error may be returned in the MaxCompute V1.0 data type edition and NULL is returned in the Hive-compatible data type edition. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;

select url_decode(a) from values ('%2') t(a);
-- An error is returned. The input parameters are invalid.
                            
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;

select url_decode(a) from values ('%2') t(a);
-- NULL is returned.
                            
```
- Hive does not support this function.
URL_ENCODE
If the input parameters are invalid or the data type conversion fails and the strict mode is enabled for both data type editions, an error may be returned in the MaxCompute V1.0 data type edition and NULL is returned in the Hive-compatible data type edition. In the following sample code, the odps.function.strictmode parameter is set to true, which indicates that the strict mode is enabled for both data type editions.
Sample code:
- MaxCompute V1.0 data type edition
```
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;

select url_encode(a, 'ascii') from values ('Sample') t(a);
-- An error is returned. The input parameters are invalid.
```
- Hive-compatible data type edition
```
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;

select url_encode(a, 'ascii') from values ('Sample') t(a);
-- NULL is returned.
```
- Hive does not support this function.