普通和hive兼容模式下sql的差异

–odps sql
––
–author:宋文理
–create time:2023-03-08 15:23:52
––
– 差异分为三块
– 1.运算符的差异
– 2.类型转换的差异
– 3.内建函数的差异

– 以下是运算符的差异：

– BITAND（&）
– 当输入参数是BIGINT类型的时候，如果BITAND的计算结果是LONG_MIN(-263)，在普通模式下会返回NULL，
– 而Hive模式仍然是LONG_MIN。
select cast((3&5)as string); --返回1
– 示例如下
– 普通模式
set odps.sql.hive.compatible=false;
select cast((a & b) as string) from
values(-9223372036854775807L, -9223372036854775792L) t(a, b);
–返回NULL

– Hive兼容模式
set odps.sql.hive.compatible=true;
select cast((a & b) as string) from
values(-9223372036854775807L, -9223372036854775792L) t(a, b);

–返回-9223372036854775808
Hive
select cast((-9223372036854775807L & -9223372036854775792L) as string);
–返回-9223372036854775808
select cast((-9 & -2) as string); --返回-10
select cast((-2 & -3)as string); --返回-4
select cast((1 & 2)as string); --返回0
select cast((-2 & -3)as string); --返回-4
select cast((-2 & -3)as string); --返回-4
select cast(-2 as string);

INSERT overwrite TABLE ods_std_wxthct_tbv_trandetailcount_di partition (ds = ‘${bdp.system.bizdate}^{\prime })selectDEPTID,TRANID,ARTIID,DEALID,STKLOCID,TRANCOUNTID,PROVID,COUNTDATE,TRANDATE,CLDATE,CLOPTRID,CLCHECKOPTRIDfromod{s}_{s}t{g}_{w}xthc{t}_{t}b{v}_{t}randetailcoun{t}_{d}iawherecast(a.DEPTIDasbigint)>0andcast(a.ARTIIDasbigint)>0anda.ds{=}^{\prime }${bdp.system.bizdate}’;

– BITOR（|）
– 当输入参数是BIGINT类型的时候，如果BITOR的计算结果是LONG_MIN(-263)，在普通模式下会返回NULL，
– 而Hive模式仍然是LONG_MIN。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select cast((a | b) as string) from
values(java.lang.Long.MIN_VALUE, 0L) t(a, b);
–返回NULL

select cast((a | b) as string) from
values(8,1) t(a, b); --返回9
select cast((a | b) as string) from
values(8,6) t(a, b); --返回14

select cast((a | b) as string) from
values(8,-1) t(a, b); --返回-1

– Hive兼容模式
set odps.sql.hive.compatible=true;
select cast((a | b) as string) from
values(java.lang.Long.MIN_VALUE, 0L) t(a, b);
–返回-9223372036854775808
– Hive
select cast(-9223372036854775808 as bigint) | 0;
– 返回-9223372036854775808
– BITXOR（^）
– 当输入参数是BIGINT类型的时候，如果BITXOR的计算结果是LONG_MIN(-263)，在普通模式下会返回NULL，
– 而Hive模式仍然是LONG_MIN。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select cast((a ^ b) as string) from
values(java.lang.Long.MIN_VALUE, 0L) t(a, b);
–返回NULL

– Hive兼容模式
set odps.sql.hive.compatible=true;
select cast((a ^ b) as string) from
values(java.lang.Long.MIN_VALUE, 0L) t(a, b);
–返回-9223372036854775808

– Hive
select cast(-9223372036854775808 as bigint) ^ 0;
–返回-9223372036854775808
– EQ（=）
– 当输入参数是DOUBLE类型的时候，普通模式下对相等的检查更加宽松，如果两个输入参数足够接近，就认为它们相等；
– 而Hive兼容模式对相等的检查更加严格。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select a = 1.0 from values (1.000000000000001) t(a);
select 1.0=1.0000000000000000000000001;
– 结果是true，因为这两个数足够接近

– Hive兼容模式
set odps.sql.hive.compatible=true;
select a = 1.0 from values (1.000000000000001) t(a);
– 结果是false

– Hive
select 1.0 = 1.000000000000001 ;
– 结果是false
– NEQ（!=）
– 当输入参数是DOUBLE类型的时候，普通模式下对相等的检查更加宽松，如果两个输入参数足够接近，就认为它们相等；
– 而Hive兼容模式对相等的检查更加严格。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select a != 1.0 from values (1.000000000000001) t(a);
– 结果是false，因为这两个数足够接近

– Hive兼容模式
set odps.sql.hive.compatible=true;
select a != 1.0 from values (1.000000000000001) t(a);
– 结果是true
– Hive
select 1.000000000000001 != 1.0 ;
– 结果是true
– GE（>=）
– 当输入参数是DOUBLE类型的时候，普通模式下对相等的检查更加宽松，如果两个输入参数足够接近，
– 就认为它们相等这会导致即使第1个输入参数小于第2个参数，但是只要他们足够接近，GE的返回结果也可能是true。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select 1.0 >= a from values (1.000000000000001) t(a);
– 结果是true，因为这两个数足够接近，认为它们相等

– Hive兼容模式
set odps.sql.hive.compatible=true;
select 1.0 >= a from values (1.000000000000001) t(a);
– 结果是false
– Hive
select 1.0 >= 1.000000000000001;
– 结果是false

– GT（>）
– 当输入参数是DOUBLE类型的时候，普通模式下对相等的检查更加宽松，如果两个输入参数足够接近，
– 就认为它们相等这会导致即使第1个输入参数大于第2个参数，但是只要他们足够接近，GT的返回结果也可能是false。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select a > 1.0 from values (1.000000000000001) t(a);
– 结果是false，因为这两个数足够接近，认为它们相等

– Hive兼容模式
set odps.sql.hive.compatible=true;
select a > 1.0 from values (1.000000000000001) t(a);
– 结果是true
– Hive
select 1.000000000000001>1.0;
– 结果是true

– LE（<=）
– 当输入参数是DOUBLE类型的时候，普通模式下对相等的检查更加宽松，如果两个输入参数足够接近，
– 就认为它们相等这会导致即使第1个输入参数大于第2个参数，但是只要他们足够接近，LE的返回结果也可能是true。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select a <= 1.0 from values (1.000000000000001) t(a);
– 结果是true，因为这两个数足够接近，认为它们相等

– Hive兼容模式
set odps.sql.hive.compatible=true;
select a <= 1.0 from values (1.000000000000001) t(a);
– 结果是false
– Hive
select 1.000000000000001 <= 1.0 ;
– 结果是false
– LT（<）
– 当输入参数是DOUBLE类型的时候，普通模式下对相等的检查更加宽松，如果两个输入参数足够接近，
– 就认为它们相等这会导致即使第1个输入参数小于第2个参数，但是只要他们足够接近，LE的返回结果也可能是false。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select 1.0 < a from values (1.000000000000001) t(a);
– 结果是false，因为这两个数足够接近，认为它们相等

– Hive兼容模式
set odps.sql.hive.compatible=true;
select 1.0 < a from values (1.000000000000001) t(a);
– 结果是true
– Hive
select 1.0 < 1.000000000000001;
– 结果是true
– PLUS（+）
– 计算结果超范围时的处理不同，在普通模式时可能会报错，在Hive兼容模式计算结果溢出不会报错，
– 同时为两种模式开启严格模式（odps.function.strictmode=true）。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select (100L + a) from values (9223372036854775807L) t(a);
– 报错，计算结果溢出

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select (100L + a) from values (9223372036854775807L) t(a);
– 计算结果溢出，但是不会报错
– Hive
select (100L + 9223372036854775807L) ;
– 返回-9223372036854775709，计算结果溢出，但是不会报错

– MINUS（-）
– 计算结果超范围时的处理不同，在普通模式时可能会报错，在Hive兼容模式计算结果溢出不会报错，
– 同时为两种模式开启严格模式（odps.function.strictmode=true）。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select (-100L - a) from values (9223372036854775807L) t(a);
– 报错，计算结果溢出

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select (-100L - a) from values (9223372036854775807L) t(a);
– 计算结果溢出，但是不会报错
– Hive
select (-100L - 9223372036854775807L) ;
– 返回 9223372036854775709
– MPL（*）
– 计算结果超范围时的处理不同，在普通模式时可能会报错，在Hive兼容模式计算结果溢出不会报错，
– 同时为两种模式开启严格模式（odps.function.strictmode=true）。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select (a * 9223372036854775807L) from values (9223372036854775807L) t(a);
– 报错，计算结果溢出

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select (a * 9223372036854775807L) from values (9223372036854775807L) t(a);
– 计算结果溢出，但是不会报错
– Hive
select (9223372036854775807L * 9223372036854775807L) ;
– 返回1；计算结果溢出，但是不会报错
– – DIV（/）
– 计算结果超范围时的处理不同，在普通模式时可能会报错，在Hive兼容模式计算结果溢出不会报错，
– 同时为两种模式开启严格模式（odps.function.strictmode=true）。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;

select 1 / a from values (0L) t(a);
– strict模式下报错

select 1.0 / a from values (0.0) t(a);
– strict模式下报错

select 1BD / a from values (0BD) t(a);
– strict模式下报错

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;

select 1 / a from values (0L) t(a);
– 返回NULL

select 1.0 / a from values (0.0) t(a);
– 返回NULL

select 1BD / a from values (0BD) t(a);
– 返回NULL
Hive
select 1 / 0L;
– 返回NULL

select 1.0 / 0.0;
– 返回NULL

select 1BD / 0BD;
– 返回NULL

– 以下是类型转换的差异

– TOBIGINT
– 当输入参数不合法时，普通模式下可能会报错，Hive兼容模式返回NULL，
– 同时为两种模式开启即严格模式（odps.function.strictmode=true）。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select cast(a as bigint) from values (‘hello’) t(a);
– 报错，输入参数不合法

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select cast(a as bigint) from values (‘hello’) t(a);
– 返回NULL
– Hive
select cast(‘hello’ as bigint) ;
– 返回NULL
– TODECIMAL
– 当输入参数不合法时，普通模式下可能会报错，Hive兼容模式返回NULL，
– 同时为两种模式开启即严格模式（odps.function.strictmode=true）。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select cast(a as decimal) from values (‘hello’) t(a);
– 报错，输入参数不合法

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.sql.udf.strict.mode=true;
select cast(a as decimal) from values (‘hello’) t(a);
– 返回NULL
– Hive
select cast(‘hello’ as decimal) ;
– 返回NULL
– TODOUBLE
– 当输入参数不合法时，普通模式下可能会报错，Hive兼容模式返回NULL，
– 同时为两种模式开启即严格模式（odps.function.strictmode=true）。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select cast(a as double) from values (‘hello’) t(a);
– 报错，输入参数不合法

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select cast(a as double) from values (‘hello’) t(a);
– 返回NULL
– Hive
select cast(‘hello’ as double) ;
– 返回NULL
– TOSMALLINT
– 当输入参数不合法或超出范围时，普通模式下可能会报错，Hive兼容模式返回NULL，
– 同时为两种模式开启即严格模式（odps.function.strictmode=true）。

– 示例如下。
– 普通模式
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);
– 报错，输入参数不合法

select cast(a as smallint) from values (9223372036854775807L) t(a);
– 报错，数据溢出

– Hive兼容模式
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

select cast(a as smallint) from values (9223372036854775807L) t(a);
– 数据溢出但是不报错

Hive
select cast(‘hello’ as smallint);
– 返回NULL

select cast(9223372036854775807L as smallint);
– 返回-1，数据溢出但是不报错

– TOTINYINT
– 当输入参数不合法或超出范围时，普通模式下可能会报错，Hive兼容模式返回NULL，
– 同时为两种模式开启即严格模式（odps.function.strictmode=true）。

– 示例如下。
– 普通模式
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);
– 报错，输入参数不合法

select cast(a as tinyint) from values (9223372036854775807L) t(a);
– 报错，数据溢出

– Hive兼容模式
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

select cast(a as tinyint) from values (9223372036854775807L) t(a);
– 数据溢出但是不报错
– Hive
select cast(‘hello’ as tinyint) ;
– 返回NULL

select cast(9223372036854775807L as tinyint) ;
– 返回-1，数据溢出但是不报错

– 以下是内建函数的差异:

ACOS
– 取值超出范围[-1,1]时，普通模式和Hive模式的表现不同。普通模式返回NULL，并且可能会报错；而Hive模式返回NAN。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select acos(a) from values(1.5) t(a);
–返回NULL，并且有可能会报错

– Hive兼容模式
set odps.sql.hive.compatible=true;
select acos(a) from values(1.5) t(a);
–返回NAN
– Hive
select acos(1.5);
–返回NAN

ASCII
– 普通模式ASCII函数返回值类型为BIGINT，Hive兼容模式ASCII函数返回值类型为INT。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
explain select ascii(‘abcde’);
–显示返回值类型是bigint

– Hive兼容模式
set odps.sql.hive.compatible=true;
explain select ascii(‘abcde’);
–显示返回值类型是int
– Hive
explain select ascii(‘abcde’);
–显示返回值类型是int

ASIN
– 取值超出范围[-1,1]时，普通模式和Hive模式的表现不同。普通模式返回NULL，并且可能会报错；而Hive模式返回NAN。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select asin(a) from values(1.5) t(a);
–返回NULL，并且有可能会报错

– Hive兼容模式
set odps.sql.hive.compatible=true;
select asin(a) from values(1.5) t(a);
–返回NAN
– Hive
select asin(a) from values(1.5) t(a);
–返回NAN

CEIL
– 普通模式CEIL函数输入参数类型是DECIMAL的时候，返回值类型为BIGINT；
– Hive兼容模式CEIL函数输入参数类型是DECIMAL的时候，返回值类型为DECIMAL。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
explain select ceil(1.2BD);
–显示返回值类型是bigint

– Hive兼容模式
set odps.sql.hive.compatible=true;
explain select ceil(1.2BD);
–显示返回值类型是decimal
– Hive
explain select ceil(1.2BD);
–显示返回值类型是decimal(2,0)
– CHR
– 输入参数取值超出范围，普通模式与Hive模式的返回结果不同。
– 普通模式会报错，而Hive兼容模式返回空字符串。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select chr(-100L);
–报错，输入参数不合法
– Hive兼容模式
set odps.sql.hive.compatible=true;
select chr(-100L);
–返回空字符串
– Hive
select chr(-100L);
–返回空字符串

CONCAT_WS
– 输入参数中有NULL，普通模式与Hive模式的返回结果不同。普通模式返回NULL，而Hive兼容模式会忽略输入参数中的NULL。
– 输入参数中有空数组，如下所示。普通模式下会返回NULL，而Hive兼容模式下会返回空字符串。
– string concat_ws(string , array arr)
– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select concat_ws(‘,’, ‘a’, null, ‘b’);
– 返回NULL
select concat_ws(‘,’, array());
– 返回NULL

– Hive兼容模式
set odps.sql.hive.compatible=true;
select concat_ws(‘,’, ‘a’, null, ‘b’);
– 返回如下值：
±----+
| _c0 |
±----+
| a,b |
±----+

select concat_ws(‘,’, array());
–返回空字符串
– Hive
select concat_ws(‘,’, ‘a’, null, ‘b’);
– 返回如下值：
±----+
| _c0 |
±----+
| a,b |
±----+

select concat_ws(‘,’, array());
–返回空字符串
COT
– 当输入参数为0（或者是其他导致计算结果为无穷大的数）时，普通模式下会返回NULL，
– 并且可能会报错；而Hive兼容模式返回INF。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select cot(a) from values(0.0) t(a);
–返回NULL，并且有可能会报错

– Hive兼容模式
set odps.sql.hive.compatible=true;
select cot(a) from values(0.0) t(a);
–返回INF
– Hive不支持此函数。

EXP
– 当使用EXP函数计算的结果超出输出类型的值域范围时，同时为两种模式开启严格模式（odps.function.strictmode=true），
– 在普通模式下可能会报错，而Hive兼容模式返回INF。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select exp(a) from values (1000L) t(a);
– 报错 Data overflow

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select exp(a) from values (1000L) t(a);
– 返回INF
– Hive
select exp(1000L) ;
– 返回INF

FIND_IN_SET
– 普通模式FIND_IN_SET函数返回值类型为BIGINT，Hive兼容模式FIND_IN_SET函数返回值类型为INT。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
explain select find_in_set(‘ab’, ‘abc,hello,ab,c’);
–显示返回值类型是bigint

– Hive兼容模式
set odps.sql.hive.compatible=true;
explain select find_in_set(‘ab’, ‘abc,hello,ab,c’);
–显示返回值类型是int
– Hive
explain select find_in_set(‘ab’, ‘abc,hello,ab,c’);
–显示返回值类型是int

FLOOR
– 普通模式FLOOR函数输入参数类型是DECIMAL的时候，返回值类型为BIGINT；
– Hive兼容模式FLOOR函数输入参数类型是DECIMAL的时候，返回值类型为DECIMAL。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
explain select floor(1.2BD);
–显示返回值类型是bigint

– Hive兼容模式
set odps.sql.hive.compatible=true;
explain select floor(1.2BD);
–显示返回值类型是decimal
– Hive
explain select floor(1.2BD);
–显示返回值类型是decimal(2,0)

FROM_UNIXTIME
– 普通模式支持如下形式的function signature，不支持指定时间的格式。
– DATETIME FROM_UNIXTIME(BIGINT time)
– Hive兼容模式支持如下两种形式的function signature，这两种function sigature都返回STRING类型。
– 第1个signature允许指定输出的时间格式，输出的时间格式受SimpleDateFormat的控制，详情请参见SimpleDateFormat。
– STRING FROM_UNIXTIME(BIGINT time, STRING format)

– STRING FROM_UNIXTIME(BIGINT time)
– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;

select from_unixtime(12345678, ‘yyyy-MM-dd HH:mm:ss’);
–报错，from_unixtime只允许1个输入参数，不允许2个输入参数

select weekday(from_unixtime(0));
– 执行成功，from_unixtime返回datetime类型，weekday接受datetime类型的输入参数

– Hive兼容模式
set odps.sql.hive.compatible=true;

select from_unixtime(12345678, ‘yyyy-MM-dd HH:mm:ss’);
–返回结果如下：
±----+
| _c0 |
±----+
| 1970-05-24 05:21:18 |
±----+

select weekday(from_unixtime(0));
– 执行失败，因为from_unixtime返回string类型，而weekday不接受string类型输入参数。
Hive
select from_unixtime(12345678, ‘yyyy-MM-dd HH:mm:ss’);
–返回结果如下：
±----+
| _c0 |
±----+
| 1970-05-24 05:21:18 |
±----+

select weekday(from_unixtime(0));
– hive不支持weekday函数

FROM_UTC_TIMESTAMP
– 当输入参数超范围时，且同时为两种模式开启严格模式（odps.function.strictmode=true），
– 普通模式返回可能会报错，Hive兼容模式返回NULL。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select from_utc_timestamp(1501557840000000, ‘UTC’);
– 报错，输入参数超出范围

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select from_utc_timestamp(1501557840000000, ‘UTC’);
– 返回NULL
– Hive
select from_utc_timestamp(1501557840000000, ‘UTC’);
– 报错，FAILED: IllegalArgumentException Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

– HASH
– 普通模式下HASH函数返回BIGINT类型，Hive兼容模式HASH函数下返回INT类型。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
explain select hash(0, 2, 4);
–返回值类型是bigint

– Hive兼容模式
set odps.sql.hive.compatible=true;
explain select hash(0, 2, 4);
–返回值类型是int
– Hive
explain select hash(0, 2, 4);
–返回值类型是int

IS_ENCODING
– 判断输入的字符串是否可以从指定的一个字符集from_encoding转为另一个字符集to_encoding。也可以用于判断输入是否为乱码，
– 通常您可以将from_encoding设为UTF-8，to_encoding设为GBK。
– 普通模式下，输入字符串必须要能够用from_encoding解码成功，并能按照to_encoding编码，结果才能返回false。
– Hive兼容模式下，输入字符串必须是UTF-8编码，并且需要能同时被from_encoding和to_encoding编码，结果才返回false。
– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select is_encoding(‘中文’, ‘gbk’, ‘utf-8’);
– 返回false，'中文’是utf-8编码，不能用gbk解码，所以返回false

– Hive兼容模式
set odps.sql.hive.compatible=true;
select is_encoding(‘中文’, ‘gbk’, ‘utf-8’);
– 返回true，'中文’既可以转换成gbk编码，又可以转换成utf-8编码
– Hive不支持此语法。

INSTR
– 输入参数为两个字符串时，普通模式下返回值类型为BIGINT，Hive兼容模式下返回值类型为INT。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
explain select instr(‘Tech on the net’, ‘e’);
–返回bigint类型

– Hive兼容模式
set odps.sql.hive.compatible=true;
explain select instr(‘Tech on the net’, ‘e’);
–返回int类型
– Hive
explain select instr(‘Tech on the net’, ‘e’);
–返回的是int类型

LENGTH
– 普通模式下使用LENGTH函数返回值类型为BIGINT，Hive兼容模式下使用LENGTH函数返回值类型为INT。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
explain select length(‘hello’);
–返回bigint类型
– Hive兼容模式
set odps.sql.hive.compatible=true;
explain select length(‘hello’);
–返回int类型
– Hive
explain select length(‘hello’);
–返回int类型

LENGTHB
– 普通模式下使用LENGTHB函数返回值类型为BIGINT，Hive兼容模式下使用LENGTHB函数返回值类型为INT。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
explain select lengthb(‘hello’);
–返回bigint类型

– Hive兼容模式
set odps.sql.hive.compatible=true;
explain select lengthb(‘hello’);
–返回int类型
– Hive不支持该函数。

LN
– 当输入参数超范围时，同时为两种模式开启严格模式（odps.function.strictmode=true），
– 在普通模式下可能会报错，在Hive兼容模式下会返回NULL。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select ln(a) from values(-1.0) t(a);
– 报错，输入参数超出范围

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select ln(a) from values(-1.0) t(a);
– 返回NULL
– Hive
select ln(-1.0) ;
– 返回NULL

LOCATE
– 普通模式下使用LOCATE函数返回值类型为BIGINT，Hive兼容模式下使用LOCATE函数返回值类型为INT。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
explain select locate(‘ab’, ‘abchelloabc’);
–返回bigint类型

– Hive兼容模式
set odps.sql.hive.compatible=true;
explain select locate(‘ab’, ‘abchelloabc’);
–返回int类型
– Hive
explain select locate(‘ab’, ‘abchelloabc’);
–返回的int类型
LOG
– 当输入参数超范围时，同时为两种模式开启严格模式（odps.function.strictmode=true），
– 在普通模式下可能会报错，在Hive兼容模式下会返回NULL。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set oodps.function.strictmode=true;
select log(a, 10) from values(-3.0) t(a);
– 报错，输入参数超出范围

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select log(a, 10) from values(-3.0) t(a);
– 返回NULL
– Hive
select log(-3.0, 10) ;
– 返回NULL

MOD
– 当输入参数不合法时，同时为两种模式开启严格模式（odps.function.strictmode=true），
– 在普通模式下可能会报错，在Hive兼容模式下会返回NULL。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select 1L % a from values(0L) t(a);
– 报错，数据溢出

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select 1L % a from values(0L) t(a);
– 返回NULL
– Hive
select 1L % 0L;
– 返回NULL

PMOD
– 当输入参数不合法时，同时为两种模式开启严格模式（odps.function.strictmode=true），
– 在普通模式下可能会报错，在Hive兼容模式下会返回NULL。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select pmod(1L, a) from values(0L) t(a);
– 报错，数据溢出

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select pmod(1L, a) from values(0L) t(a);
– 返回NULL

– Hive
select pmod(1L, 0L) ;
– 返回NULL
– POW
– 当计算结果溢出时，同时为两种模式开启严格模式（odps.function.strictmode=true），
– 在普通模式下可能会报错，在Hive兼容模式下会返回INF。

– 示例如下。
– 普通模式
– set odps.sql.hive.compatible=false;
set odps.sql.udf.strict.mode=true;
select pow(a, 1000L) from values(1000L) t(a);
– 报错，数据溢出

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.sql.udf.strict.mode=true;
select pow(a, 1000L) from values(1000L) t(a);
– 返回INF
– Hive
select pow(1000L, 1000L) ;
– 返回INF

REPEAT
– 当REPEAT的个数小于零时，在普通模式下会报错，在Hive兼容模式下会返回空字符串。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select repeat(‘hi’, n) from values (-1L) t(n);
–报错，输入参数超出范围

– Hive兼容模式
set odps.sql.hive.compatible=true;
select repeat(‘hi’, n) from values (-1L) t(n);
– --返回空字符串
– Hive不支持该函数。

REVERSE
– 当输入参数中包含中文字符时，在普通模式下按照字节来进行reverse操作，返回值可能有乱码；
– 在Hive兼容模式下按照utf8来处理，不会出现乱码。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select reverse(a) from values (‘hello中国world’) t(a);
–返回结果中有乱码

– Hive兼容模式
set odps.sql.hive.compatible=true;
select reverse(a) from values (‘hello中国world’) t(a);
–返回结果中没有乱码
– Hive
select reverse(‘hello中国world’) ;
–返回dlrow国中olleh

ROUND
– 在普通模式下使用ROUND函数，本质上只支持DOUBLE或DECIMAL两种输入数据类型，其他数据类型会转换为这两种类型；
– 在Hive兼容模式下，支持：DOUBLE、DECIMAL、BIGINT、INT、SMALLINT、TINYINT等数据类型。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
explain select round(a) from values(2L) t(a);
–从执行计划看，输入数据从bigint类型转换成double类型，计算结果是double类型

– Hive兼容模式
set odps.sql.hive.compatible=true;
explain select round(a) from values(2L) t(a);
–从执行计划看，输入bigint类型，输出是bigint类型
– Hive
explain select round(2L) ;
–从执行计划看，输入bigint类型，输出是bigint类型

SIGN
– 输入参数数据类型为DECIMAL时，普通模式下返回BIGINT类型，Hive兼容模式下返回INT类型。
– 输入参数数据类型为DOUBLE时，普通模式下如果输入参数的绝对值和0非常接近，则返回值为0。
– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;

explain select sign(a) from values(2BD) t(a);
–从执行计划看，返回bigint类型

select sign(a) from values (0.000000000000009) t(a);
–返回值是0.0，因为输入参数和0非常紧急
– Hive兼容模式
set odps.sql.hive.compatible=true;

explain select sign(a) from values(2BD) t(a);
–从执行计划看，返回int类型

select sign(a) from values (0.000000000000009) t(a);
– 返回值是1.0
– Hive
explain select sign(2BD);
–从执行计划看，返回int类型

select sign(0.000000000000009) ;
–返回值是1

SIZE
– 普通模式下使用SIZE函数返回值类型为BIGINT，Hive兼容模式下使用SIZE函数返回值类型为INT。
– 输入参数为NULL时，普通模式下返回NULL，Hive兼容模式返回-1。
– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
explain select size(array(‘a’,‘b’));
–从执行计划看，返回bigint类型

select size(a) from values (cast(NULL as array)) t(a);
–返回结果是NULL

– Hive兼容模式
set odps.sql.hive.compatible=true;
explain select size(array(‘a’,‘b’));
–从执行计划看，返回int类型

select size(a) from values (cast(NULL as array)) t(a);
–返回结果是-1
– Hive
explain select size(array(‘a’,‘b’));
–从执行计划看，返回int类型

SPLIT
– 当输入参数的分隔符为空字符串时，普通模式下返回空数组，Hive兼容模式下按照UTF-8格式分割输入字符串。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
select split(a, ‘’) from values (‘hello中国world’) t(a);
–返回空array

– Hive兼容模式
set odps.sql.hive.compatible=true;
select split(a, ‘’) from values (‘hello中国world’) t(a);
–返回[, h, e, l, l, o, 中, 国, w, o, r, l, d, ]
– Hive
select split(‘hello中国world’, ‘’) ;
–返回[“h”,“e”,“l”,“l”,“o”,“中”,“国”,“w”,“o”,“r”,“l”,“d”,“”]
SQRT
– 当输入参数小于0时，同时为两种模式开启严格模式（odps.function.strictmode=true），
– 在普通模式下可能会报错，在Hive兼容模式下会返回NULL。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select sqrt(a) from values (-100.0) t(a);
– 报错，输入参数小于0

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select sqrt(a) from values (-100.0) t(a);
– 返回NULL
Hive
select sqrt(-100.0);
– 返回NULL

SUBSTR
– 当输入参数中的起始位置为0时，普通模式下返回空字符串，Hive兼容模式下与起始位置为1时相同。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select substr(a, 0) from values (‘hello, world’) t(a);
– 返回空字符串

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select substr(a, 0) from values (‘hello, world’) t(a);
– 返回如下结果：
±----+
| _c0 |
±----+
| hello, world |
±----+
Hive
select substr(‘hello, world’, 0);
– 返回如下结果：
±----+
| _c0 |
±----+
| hello, world |
±----+

UNIX_TIMESTAMP
– 普通模式下不支持两个输入参数类型为STRING，会报错；Hive兼容模式下支持两个STRING类型的输入参数，
– 如下所示，返回值为BIGINT类型，通过format指定时间格式，时间格式请参见SimpleDateFormat。
– bigint FROM_UNIXTIME(string timeString, String format)
– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select unix_timestamp(a) from values (‘99999-01-01 00:00:00’);
– 报错，输入参数不合法

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select unix_timestamp(a) from values (‘99999-01-01 00:00:00’);
– 返回NULL
– Hive
select unix_timestamp(‘2022/7/8’, ‘yyyy/mm/dd’);
–返回结果如下：
±-----------+
| _c0 |
±-----------+
| 1641571620 |
±-----------+
select unix_timestamp(‘99999-01-01 00:00:00’) ;
–返回：3093496416000
select unix_timestamp(‘99999-01-01 00:00:00’);
–返回：3093496416000

URL_DECODE
– 当输入参数不合法时，且同时为两种模式开启严格模式（odps.function.strictmode=true），
– 普通模式返回可能会报错，Hive兼容模式返回NULL。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;

select url_decode(a) from values (‘%2’) t(a);
– 报错，输入参数不合法

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;

select url_decode(a) from values (‘%2’) t(a);
– 返回NULL

– Hive不支持该函数。
URL_ENCODE
– 当输入参数不合法或者转换失败时，且同时为两种模式开启严格模式（odps.function.strictmode=true），
– 普通模式返回可能会报错，Hive兼容模式返回NULL。

– 示例如下。
– 普通模式
set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;

select url_encode(a, ‘ascii’) from values (‘示例’) t(a);
– 报错，输入参数不合法

– Hive兼容模式
set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;

select url_encode(a, ‘ascii’) from values (‘示例’) t(a);
– 返回NULL
– Hive不支持该函数。