This topic compares functions built in MaxCompute, MySQL, and Oracle.

Function type MaxCompute HIVE MySQL Oracle Partition pruning supported in MaxCompute SQL
Date functions DATEDIFF DATEDIFF DATEDIFF MONTHS_BETWEEN
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
DATE_ADD DATE_ADD DATE_ADD N/A
  • Not supported in MaxCompute mode. We recommend that you use the DATEADD function instead.
  • Supported in Hive-compatible mode
DATEPART N/A DATE_FORMAT EXTRACT (datetime)
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
DATETRUNC TRUNC DATE_FORMAT EXTRACT (datetime)
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
FROM_UNIXTIME FROM_UNIXTIME FROM_UNIXTIME N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
GETDATE CURRENT_DATE NOW CURRENT_DATE
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
ISDATE N/A STR_TO_DATE (If false is returned, the string cannot be converted to DATE.) N/A
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
LASTDAY LAST_DAY LAST_DAY LAST_DAY
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
TO_DATE TO_DATE STR_TO_DATE() DATE
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
TO_CHAR N/A DATE_FORMAT TO_CHAR (datetime)
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
UNIX_TIMESTAMP UNIX_TIMESTAMP UNIX_TIMESTAMP N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
WEEKDAY N/A WEEKDAY N/A
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
WEEKOFYEAR WEEKOFYEAR WEEKOFYEAR N/A
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
YEAR YEAR YEAR YEAR
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
QUARTER QUARTER QUARTER QUARTER
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
MONTH MONTH MONTH MONTH
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
DAY DAY DAY DAY
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
DAYOFMONTH DAYOFMONTH DAYOFMONTH N/A
  • Not supported in MaxCompute mode
  • Supported in Hive-compatible mode
HOUR HOUR HOUR HOUR
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
MINUTE MINUTE MINUTE MINUTE
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
CURRENT_TIMESTAMP CURRENT_TIMESTAMP CURRENT_TIMESTAMP CURRENT_TIMESTAMP
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
ADD_MONTHS ADD_MONTHS ADDDATE ADD_MONTHS
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
LAST_DAY LAST_DAY LAST_DAY N/A
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
NEXT_DAY NEXT_DAY N/A NEXT_DAY
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
MONTHS_BETWEEN MONTHS_BETWEEN timestampdiff MONTHS_BETWEEN
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
Mathematical functions ABS ABS ABS ABS
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
ACOS ACOS ACOS ACOS
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
ASIN ASIN ASIN ASIN
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
ATAN ATAN ATAN ATAN
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
CEIL CEIL CEIL CEIL
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
CONV CONV CONV N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
COS COS COS COS
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
COSH COSH N/A COSH
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
COT COT COT COT
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
EXP EXP EXP EXP
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
FLOOR FLOOR FLOOR FLOOR
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
LN LN LN LN
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
LOG LOG LOG LOG
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
POW POW POW POWER
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
RAND RAND RAND N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
ROUND ROUND ROUND ROUND
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
SIN SIN SIN SIN
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
SINH SINH N/A SINH
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
SQRT SQRT SQRT SQRT
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
TAN TAN TAN TAN
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
TANH TANH N/A TANH
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
TRUNC TRUNC TRUNCATE TRUNC
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
LOG2 LOG2 LOG2 LOG
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
LOG10 LOG10 LOG10 LOG
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
BIN BIN BIN BITAND
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
HEX HEX HEX RAWTOHEX
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
UNHEX UNHEX UNHEX HEXTORAW
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
RADIANS RADIANS RADIANS RADIANS
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
DEGREES DEGREES DEGREES DEGREES
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
SIGN SIGN SIGN SIGN
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
E E N/A N/A
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
PI PI PI PI
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
FACTORIAL FACTORIAL N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
CBRT CBRT N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
SHIFTLEFT SHIFTLEFT << N/A
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
SHIFTRIGHT SHIFTRIGHT >> N/A
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
SHIFTRIGHTUNSIGNED SHIFTRIGHTUNSIGNED >>> N/A
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
Window functions DENSE_RANK DENSE_RANK DENSE_RANK DENSE_RANK
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
RANK RANK RANK RANK
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
LAG LAG LAG LAG
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
LEAD LEAD LEAD LEAD
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
PERCENT_RANK PERCENT_RANK PERCENT_RANK PERCENT_RANK
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
ROW_NUMBER ROW_NUMBER ROW_NUMBER ROW_NUMBER
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
CLUSTER_SAMPLE N/A N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
NTILE NTILE NTILE NTILE
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
Aggregate functions COUNT COUNT COUNT COUNT
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
AVG AVG AVG AVG
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
MAX MAX MAX MAX
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
MIN MIN MIN MIN
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
MEDIAN N/A N/A MEDIAN
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
STDDEV STDDEV STDDEV STDDEV
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
STDDEV_SAMP STDDEV_SAMP STDDEV_SAMP STDDEV_SAMP
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
SUM SUM SUM SUM
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
WM_CONCAT N/A GROUP_CONCAT WM_CONCAT
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
COLLECT_LIST COLLECT LIST N/A COLLECT
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
COLLECT_SET COLLECT SET N/A COLLECT
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
VARIANCE/VAR_POP VARIANCE/VAR_POP VAR_POP VARIANCE/VAR_POP
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
VAR_SAMP VAR_SAMP VAR_SAMP VAR_SAMP
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
COVAR_POP COVAR_POP N/A COVAR_POP
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
COVAR_SAMP COVAR_SAMP N/A COVAR_SAMP
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
PERCENTILE PERCENTILE N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
String functions CHAR_MATCHCOUNT N/A N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
CHR CHR CHAR CHR
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
CONCAT CONCAT CONCAT CONCAT
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
GET_JSON_OBJECT GET_JSON_OBJECT JSON_EXTRACT() N/A
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
INSTR INSTR INSTR INSTR
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
IS_ENCODING N/A N/A N/A
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
KEYVALUE N/A N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
LENGTH LENGTH LENGTH LENGTH
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
LENGTHB LENGTHB LENGTHB LENGTHB
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
MD5 MD5 MD5 N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
REGEXP_EXTRACT REGEXP_EXTRACT N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
REGEXP_INSTR N/A REGEXP_INSTR REGEXP_INSTR
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
REGEXP_REPLACE REGEXP_REPLACE REGEXP_REPLACE REGEXP_REPLACE
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
REGEXP_SUBSTR N/A REGEXP_SUBSTR REGEXP_SUBSTR
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
REGEXP_COUNT N/A N/A REGEXP_COUNT
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
SPLIT_PART N/A N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
SUBSTR SUBSTR SUBSTR SUBSTR
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
SUBSTRING SUBSTRING SUBSTRING SUBSTR
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
TOLOWER LOWER LOWER LOWER
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
TOUPPER UPPER UPPER UPPER
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
TRIM TRIM TRIM TRIM
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
LTRIM LTRIM LTRIM LTRIM
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
RTRIM RTRIM RTRIM LTRIM
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
REVERSE REVERSE REVERSE REVERSE
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
SPACE SPACE SPACE SPACE
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
REPEAT REPEAT REPEAT REPEAT
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
ASCII ASCII ASCII ASCII
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
CONCAT_WS CONCAT_WS CONCAT_WS N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
LPAD LPAD LPAD LPAD
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
RPAD RPAD RPAD RPAD
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
REPLACE N/A REPLACE REPLACE
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
SOUNDEX SOUNDEX SOUNDEX SOUNDEX
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
SUBSTRING_INDEX SUBSTRING_INDEX SUBSTRING_INDEX N/A
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
TRANSLATE TRANSLATE N/A TRANSLATE
  • Not supported in MaxCompute mode
  • Not supported in Hive-compatible mode
URL_DECODE N/A N/A N/A
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
URL_ENCODE N/A N/A PERCENTILE_CONT
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
CRC32 CRC32 CRC32 N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
Other functions CAST CAST CAST CAST
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
COALESCE COALESCE COALESCE COALESCE
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
DECODE DECODE N/A DECODE
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
GET_IDCARD_AGE N/A N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
GET_IDCARD_BIRTHDAY N/A N/A N/A
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
GET_IDCARD_SEX N/A N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
GREATEST GREATEST GREATEST N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
ORDINAL N/A N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
LEAST LEAST LEAST LEAST
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
MAX_PT N/A N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
UUID N/A UUID UID
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
SAMPLE N/A N/A N/A
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
IF IF IF IF
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
CASE WHEN CASE WHEN CASE WHEN CASE WHEN
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
SPLIT SPLIT SPLIT N/A
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
STR_TO_MAP STR_TO_MAP N/A N/A
  • Supported in MaxCompute mode
  • Not supported in Hive-compatible mode
EXPLODE EXPLODE N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
MAP MAP N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
MAP_KEYS MAP_KEYS N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
MAP_VALUES MAP_VALUES N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
NVL NVL IFNULL N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
ARRAY ARRAY N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
SIZE SIZE N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
ARRAY_CONTAINS ARRAY_CONTAINS N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
POSEXPLODE POSEXPLODE N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
TRANS_ARRAY N/A N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
INLINE INLINE N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
NAMED_STRUCT N/A N/A N/A
  • Supported in MaxCompute mode
  • Supported in Hive-compatible mode
Note The MaxCompute mode is enabled by default. To use the Hive-compatible mode, run the following commands:
-- Switch to the Hive-compatible mode at the project level.
setproject odps.sql.hive.compatible=True;
-- Switch to the Hive-compatible mode at the session level.
set odps.sql.hive.compatible=True;