MaxCompute:Overview

Precautions

For more information about the mappings between the built-in functions of MaxCompute and the built-in functions of open source systems, see Mappings between built-in functions of MaxCompute and built-in functions of Hive, MySQL, and Oracle.

Date functions

The following table describes date functions provided by MaxCompute SQL. You can select an appropriate date function based on your business requirements to complete date calculations and conversions.

Mathematical functions

Window functions

• Syntax

  Syntax of window functions:

  <function_name>([distinct][<expression> [, ...]]) over (<window_definition>)
  <function_name>([distinct][<expression> [, ...]]) over <window_name>

  • function_name: the name of a built-in window function, aggregate function, or user-defined aggregate function (UDAF).

  • expression: the format of a window function. The format is subject to the function syntax.

  • windowing_definition: the definition of a window. For more information about the syntax, see windowing_definition.

  • window_name: the name of a window. You can use the window keyword to configure a window and use windowing_definition to specify the name of the window. Syntax of named_window_def:

    window <window_name> as (<window_definition>)

    Position of named_window_def in an SQL statement:

    select ... from ... [where ...] [group by ...] [having ...] named_window_def [order by ...] [limit ...]

• windowing_definition

  Syntax

  --partition_clause:
  [partition by <expression> [, ...]]
  --orderby_clause:
  [order by <expression> [asc|desc][nulls {first|last}] [, ...]]
  [<frame_clause>]

  If you use a window function in a SELECT statement, data is partitioned and sorted based on PARTITION BY and ORDER BY in windowing_definition when the window function is executed. If the SELECT statement does not include PARTITION BY, only one partition exists and the partition contains all data. If the SELECT statement does not include ORDER BY, data in a partition is arranged in a random order, and a data stream is generated. After the data stream is generated, a group of rows is extracted from the data stream based on frame_clause in windowing_definition to create a window for the current row. The window function calculates the data included in the window to which the current row belongs.

  • partition by <expression> [, ...]: optional. This parameter specifies the partition information. If the values of partition key columns are the same for a group of rows, these rows are included in the same window. For more information about the format of PARTITION BY, see Table operations.

  • order by <expression> [asc|desc][nulls {first|last}] [, ...]: optional. This parameter specifies how to sort rows of data in a window.

    Note

    If the values of the column that is specified in order by are the same, the sorting result may not be accurate. To reduce the random ordering of data, make sure that the values of the column that is specified in order by are unique.

  • frame_clause: optional. This parameter is used to determine the data boundaries of a window. The frame_clause section in this topic provides details about this parameter.

• frame_clause

  Syntax

  -- Syntax 1 
  {ROWS|RANGE|GROUPS} <frame_start> [<frame_exclusion>]
  -- Syntax 2 
  {ROWS|RANGE|GROUPS} between <frame_start> and <frame_end> [<frame_exclusion>]

  frame_clause is a closed interval that is used to determine the data boundaries of a window. The data boundaries are determined based on the rows that are specified by frame_start and frame_end.

  • ROWS|RANGE|GROUPS: required. ROWS, RANGE, and GROUPS indicate the types of frame_clause. The implementation rules of frame_start and frame_end vary based on the type of frame_clause. Take note of the following points:

    • ROWS: The data boundaries of a window are determined based on the number of rows.

    • RANGE: The data boundaries of a window are determined based on the comparison results of the values of the column that is specified in order by. In most cases, order by is specified in windowing_definition. If order by is not specified in windowing_definition, the values of the column that is specified in order by are the same for all rows in a partition. NULL values are considered equivalent.

    • GROUPS: In a partition, rows that have the same value of the column specified in order by form a group. If order by is not specified, all rows in the partition form a group. NULL values are considered equivalent.

  • frame_start and frame_end: the start and end rows of a window. frame_start is required. frame_end is optional. If frame_end is not specified, the default value CURRENT ROW is used.

    The row specified by frame_start must precede or be the same as the row specified by frame_end. Compared with the row specified by frame_end, the row specified by frame_start is closer to the first row in a window after all data in the window is sorted based on the column that is specified in order by of windowing_definition. The following table describes the valid values and logic of frame_start and frame_end when the type of frame_clause is ROWS, RANGE, or GROUPS.

    frame_clause type	Value of frame_start or frame_end	Description
    ROWS, RANGE, and GROUPS	UNBOUNDED PRECEDING	Indicates the first row of a partition. Rows are counted from 1.
    	UNBOUNDED FOLLOWING	Indicates the last row of a partition.
    ROWS	CURRENT ROW	Indicates the current row. Each row of data corresponds to a result calculated by a window function. The current row indicates the row whose data is calculated by using a window function.
    	offset PRECEDING	Indicates the Nth row that precedes the current row at a given offset. For example, if 0 PRECEDING indicates the current row, 1 PRECEDING indicates the previous row. offset must be a non-negative integer.
    	offset FOLLOWING	Indicates the Nth row that follows the current row at a given offset. For example, if 0 FOLLOWING indicates the current row, 1 FOLLOWING indicates the next row. offset must be a non-negative integer.
    RANGE	CURRENT ROW	If frame_start is set to CURRENT ROW, it indicates the first row that has the same value of the column specified in order by as the current row. If frame_end is set to CURRENT ROW, it indicates the last row that has the same value of the column specified in order by as the current row.
    	offset PRECEDING	The rows that are specified by frame_start and frame_end are determined based on the sorting order that is specified by order by. For example, if data in a window is sorted by X, Xi indicates the X value that corresponds to the ith row, and Xc indicates the X value that corresponds to the current row. Positions of rows specified by frame_start and frame_end: order by is set to asc: frame_start indicates the first row that meets the following requirement: Xc - Xi ≤ offset. frame_end indicates the last row that meets the following requirement: Xc - Xi ≥ offset. order by is set to desc: frame_start indicates the first row that meets the following requirement: Xi - Xc ≤ offset. frame_end indicates the last row that meets the following requirement: Xi - Xc ≥ offset. The column that is specified in order by can be of the following data types: TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, DATETIME, DATE, and TIMESTAMP. Syntax for offset of the DATE type: N: indicates N days or N seconds. It must be a non-negative integer. For an offset of the DATETIME or TIMESTAMP type, it indicates N seconds. For an offset of the DATE type, it indicates N days. interval 'N' {YEAR\MONTH\DAY\HOUR\MINUTE\SECOND}: indicates N years, months, days, hours, minutes, or seconds. For example, INTERVAL '3' YEAR indicates 3 years. INTERVAL 'N-M' YEAR TO MONTH: indicates N years and M months. For example, INTERVAL '1-3' YEAR TO MONTH indicates 1 year and 3 months. INTERVAL 'D[ H[:M[:S[:N]]]]' DAY TO SECOND: indicates D days, H hours, M minutes, S seconds, and N nanoseconds. For example, INTERVAL '1 2:3:4:5' DAY TO SECOND indicates 1 day, 2 hours, 3 minutes, 4 seconds, and 5 nanoseconds.
    	offset FOLLOWING	The rows that are specified by frame_start and frame_end are determined based on the sorting order that is specified by order by. For example, if data in a window is sorted by X, Xi indicates the X value that corresponds to the ith row, and Xc indicates the X value that corresponds to the current row. Positions of rows specified by frame_start and frame_end: order by is set to asc: frame_start indicates the first row that meets the following requirement: Xi - Xc ≥ offset. frame_end indicates the last row that meets the following requirement: Xi - Xc ≤ offset. order by is set to desc: frame_start indicates the first row that meets the following requirement: Xc - Xi >= offset. frame_end indicates the last row that meets the following requirement: Xc - Xi <= offset.
    GROUPS	CURRENT ROW	If frame_start is set to CURRENT ROW, it indicates the first row of the group to which the current row belongs. If frame_end is set to CURRENT ROW, it indicates the last row of the group to which the current row belongs.
    	offset PRECEDING	If frame_start is set to offset PRECEDING, it indicates the first row of the Nth group that precedes the group of the current row at a given offset. If frame_end is set to offset PRECEDING, it indicates the last row of the Nth group that precedes the group of the current row at a given offset. Note You cannot set frame_start to UNBOUNDED FOLLOWING, and you cannot set frame_end to UNBOUNED PRECEDING.
    	offset FOLLOWING	If frame_start is set to offset FOLLOWING, it indicates the first row of the Nth group that follows the group of the current row at a given offset. If frame_end is set to offset FOLLOWING, it indicates the last row of the Nth group that follows the group of the current row at a given offset. Note You cannot set frame_start to UNBOUNDED FOLLOWING, and you cannot set frame_end to UNBOUNED PRECEDING.

  • frame_exclusion: optional. This parameter is used to remove specific rows from a window. Valid values:

    • EXCLUDE NO OTHERS: No rows are excluded from the window.

    • EXCLUDE CURRENT ROW: The current row is excluded from the window.

    • EXCLUDE GROUP: indicates that an entire group of rows in a partition is excluded from the window. In the group, all rows have the same value of the column that is specified in order by as the current row.

    • EXCLUDE TIES: An entire group of rows, except for the current row, are excluded from the window.

  Default frame_clause

  If you do not specify frame_clause, MaxCompute uses the default frame_clause to determine the data boundaries of a window. Values of the default frame_clause:

  • If odps.sql.hive.compatible is set to true, the following default frame_clause is used. This rule applies to most SQL systems.

    RANGE between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS

  • If odps.sql.hive.compatible is set to false, order by is specified in windowing_definition, and one of the following window functions is used, the default frame_clause in ROWS mode is used: AVG, COUNT, MAX, MIN, STDDEV, STEDEV_POP, STDDEV_SAMP, and SUM.

    ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS

  Example of data boundaries of a window

  In this example, a table named tbl contains three columns that are of the BIGINT type: pid, oid, and rid. The tbl table contains the following data:

  +------------+------------+------------+
  | pid        | oid        | rid        |
  +------------+------------+------------+
  | 1          | NULL       | 1          |
  | 1          | NULL       | 2          |
  | 1          | 1          | 3          |
  | 1          | 1          | 4          |
  | 1          | 2          | 5          |
  | 1          | 4          | 6          |
  | 1          | 7          | 7          |
  | 1          | 11         | 8          |
  | 2          | NULL       | 9          |
  | 2          | NULL       | 10         |
  +------------+------------+------------+

  You can replace ellipses (...) in the following SQL statements with windowing_definition to display the data in the windows in which each row of data is included.

  Note

  If a value in the window column in the returned result is NULL, no data is contained in the window.

  • Windows in ROWS mode

    • windowing_definition 1

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW
      -- Sample SQL statement: 
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

      The following result is returned:

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [1]    |
      | 1          | NULL       | 2          | [1, 2] |
      | 1          | 1          | 3          | [1, 2, 3] |
      | 1          | 1          | 4          | [1, 2, 3, 4] |
      | 1          | 2          | 5          | [1, 2, 3, 4, 5] |
      | 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
      | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
      | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 2          | NULL       | 9          | [9]    |
      | 2          | NULL       | 10         | [9, 10] |
      +------------+------------+------------+--------+

    • windowing_definition 2

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
      -- Sample SQL statement: 
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as window from tbl;

      The following result is returned:

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | 1          | 3          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | 1          | 4          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | 2          | 5          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | 4          | 6          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 2          | NULL       | 9          | [9, 10] |
      | 2          | NULL       | 10         | [9, 10] |
      +------------+------------+------------+--------+

    • windowing_definition 3

      partition by pid order by oid ROWS between 1 FOLLOWING and 3 FOLLOWING
      -- Sample SQL statement: 
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid ROWS between 1 FOLLOWING and 3 FOLLOWING) as window from tbl;

      The following result is returned:

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [2, 3, 4] |
      | 1          | NULL       | 2          | [3, 4, 5] |
      | 1          | 1          | 3          | [4, 5, 6] |
      | 1          | 1          | 4          | [5, 6, 7] |
      | 1          | 2          | 5          | [6, 7, 8] |
      | 1          | 4          | 6          | [7, 8] |
      | 1          | 7          | 7          | [8]    |
      | 1          | 11         | 8          | NULL   |
      | 2          | NULL       | 9          | [10]   |
      | 2          | NULL       | 10         | NULL   |
      +------------+------------+------------+--------+

    • windowing_definition 4

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW
      -- Sample SQL statement: 
      select pid, 
      oid, 
      rid, 
      collect_list(rid) over(partition by pid order by 
      oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW) as window from tbl;

      The following result is returned:

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | NULL   |
      | 1          | NULL       | 2          | [1]    |
      | 1          | 1          | 3          | [1, 2] |
      | 1          | 1          | 4          | [1, 2, 3] |
      | 1          | 2          | 5          | [1, 2, 3, 4] |
      | 1          | 4          | 6          | [1, 2, 3, 4, 5] |
      | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
      | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
      | 2          | NULL       | 9          | NULL   |
      | 2          | NULL       | 10         | [9]    |
      +------------+------------+------------+--------+

    • windowing_definition 5

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP
      -- Sample SQL statement: 
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP) as window from tbl;

      The following result is returned:

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | NULL   |
      | 1          | NULL       | 2          | NULL   |
      | 1          | 1          | 3          | [1, 2] |
      | 1          | 1          | 4          | [1, 2] |
      | 1          | 2          | 5          | [1, 2, 3, 4] |
      | 1          | 4          | 6          | [1, 2, 3, 4, 5] |
      | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
      | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
      | 2          | NULL       | 9          | NULL   |
      | 2          | NULL       | 10         | NULL   |
      +------------+------------+------------+--------+

    • windowing_definition 6

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES
      -- Sample SQL statement: 
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES) as window from tbl;                            

      The following result is returned:

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [1]    |
      | 1          | NULL       | 2          | [2]    |
      | 1          | 1          | 3          | [1, 2, 3] |
      | 1          | 1          | 4          | [1, 2, 4] |
      | 1          | 2          | 5          | [1, 2, 3, 4, 5] |
      | 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
      | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
      | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 2          | NULL       | 9          | [9]    |
      | 2          | NULL       | 10         | [10]   |
      +------------+------------+------------+--------+

      The differences between EXCLUDE CURRENT ROW and EXCLUDE GROUP can be obtained based on the comparison between the window column values of rows with the rid column values of 2, 4, and 10 in windowing_definition 5 and windowing_definition 6. If frame_exclusion is set to EXCLUDE GROUP, the rows that have the same pid column value in a partition are extracted when the rows have the same oid column value as the current row.

  • Windows in RANGE mode

    • windowing_definition 1

      partition by pid order by oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW
      -- Sample SQL statement: 
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

      The following result is returned:

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [1, 2] |
      | 1          | NULL       | 2          | [1, 2] |
      | 1          | 1          | 3          | [1, 2, 3, 4] |
      | 1          | 1          | 4          | [1, 2, 3, 4] |
      | 1          | 2          | 5          | [1, 2, 3, 4, 5] |
      | 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
      | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
      | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 2          | NULL       | 9          | [9, 10] |
      | 2          | NULL       | 10         | [9, 10] |
      +------------+------------+------------+--------+

      If frame_end is set to CURRENT ROW, the last row that has the same value of the oid column in order by as the current row is obtained. Therefore, the window column value of the row whose rid column value is 1 is [1, 2].

    • windowing_definition 2

      partition by pid order by oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING
      -- Sample SQL statement: 
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING) as window from tbl;

      The following result is returned:

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | 1          | 3          | [3, 4, 5, 6, 7, 8] |
      | 1          | 1          | 4          | [3, 4, 5, 6, 7, 8] |
      | 1          | 2          | 5          | [5, 6, 7, 8] |
      | 1          | 4          | 6          | [6, 7, 8] |
      | 1          | 7          | 7          | [7, 8] |
      | 1          | 11         | 8          | [8]    |
      | 2          | NULL       | 9          | [9, 10] |
      | 2          | NULL       | 10         | [9, 10] |
      +------------+------------+------------+--------+

    • windowing_definition 3

      partition by pid order by oid RANGE between 3 PRECEDING and 1 PRECEDING
      -- Sample SQL statement: 
      
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid RANGE between 3 PRECEDING and 1 PRECEDING) as window from tbl;

      The following result is returned:

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [1, 2] |
      | 1          | NULL       | 2          | [1, 2] |
      | 1          | 1          | 3          | NULL   |
      | 1          | 1          | 4          | NULL   |
      | 1          | 2          | 5          | [3, 4] |
      | 1          | 4          | 6          | [3, 4, 5] |
      | 1          | 7          | 7          | [6]    |
      | 1          | 11         | 8          | NULL   |
      | 2          | NULL       | 9          | [9, 10] |
      | 2          | NULL       | 10         | [9, 10] |
      +------------+------------+------------+--------+

      For the row whose value of oid in order by is NULL, if frame_start is set to offset PRECEDING or offset FOLLOWING, the row is the first row whose value of oid in order by is NULL only if the offset is not unbounded. If frame_end is set to offset PRECEDING or offset FOLLOWING, the row is the last row whose value of oid in order by is NULL only if the offset is not unbounded.

  • Windows in GROUPS mode

    windowing_definition

    partition by pid order by oid GROUPS between 2 PRECEDING and CURRENT ROW
    -- Sample SQL statement: 
    select pid, 
           oid, 
           rid, 
    collect_list(rid) over(partition by pid order by 
    oid GROUPS between 2 PRECEDING and CURRENT ROW) as window from tbl;

    The following result is returned:

    +------------+------------+------------+--------+
    | pid        | oid        | rid        | window |
    +------------+------------+------------+--------+
    | 1          | NULL       | 1          | [1, 2] |
    | 1          | NULL       | 2          | [1, 2] |
    | 1          | 1          | 3          | [1, 2, 3, 4] |
    | 1          | 1          | 4          | [1, 2, 3, 4] |
    | 1          | 2          | 5          | [1, 2, 3, 4, 5] |
    | 1          | 4          | 6          | [3, 4, 5, 6] |
    | 1          | 7          | 7          | [5, 6, 7] |
    | 1          | 11         | 8          | [6, 7, 8] |
    | 2          | NULL       | 9          | [9, 10] |
    | 2          | NULL       | 10         | [9, 10] |
    +------------+------------+------------+--------+

Aggregate functions

String functions

Complex type functions

Encryption and decryption functions

Other functions

Limits on JSON functions

• The development tools that are supported for the JSON type include odpscmd and MaxCompute Studio. External ecosystems such as DataWorks and Dataphin are not supported. If you need to use the JSON type together with an external ecosystem, you must check whether the external ecosystem is supported for the JSON type. The following table describes the items that you must take note of when you use odpscmd and MaxCompute Studio as development tools for the JSON type.

  odpscmd	MaxCompute Studio
  You must upgrade the client to the latest version. Otherwise, the desc json_table command cannot be used. You must set the se_instance_tunnel parameter in the conf\odps_config.ini file in the installation path of the client to false. Otherwise, an error is reported when you perform a query.	MaxCompute Studio allows you only to query JSON data. It does not allow you to upload or download JSON data.

• If you want to use another type of engine, such as Hologres, to read data from a MaxCompute table, JSON data in the table cannot be read.

• Columns of the JSON type cannot be added to a MaxCompute table.

• You are not allowed to compare data of the JSON type with data of other types, execute SQL statements that contain the ORDER BY or GROUP BY clause on data of the JSON type, or use columns of the JSON type as join keys.

• The integer part and decimal part of a value of the JSON NUMBER type are stored by using the BIGINT type and the DOUBLE type separately. If the integer part exceeds the range that is supported by the BIGINT type, an integer overflow error occurs. Conversion of the decimal part to the DOUBLE type leads to precision loss.

• Strings that can be used to generate JSON data do not include \u0000, which is the Unicode character for NULL.

• Java UDFs and Python UDFs do not support the JSON type.

Limits on string functions

The following functions support only English characters:

• TRIM, RTRIM, and LTRIM: The value of the trimChars parameter can contain only English characters.

• REVERSE: This function supports only English characters in the Hive-compatible data type edition.

• SOUNDEX: This function supports only English characters.

• TOLOWER: This function is used to convert English characters in a string into lowercase characters.

• TOUPPER: This function is used to convert English characters in a string into uppercase characters.

• INITCAP: This function is used to convert the first letter of each word in English in a string into an uppercase letter and the other letters of each word into lowercase characters.